dbquery/db.go

package dbquery

import (
	"database/sql"
	"log"

	"errors"
	"strings"
	"time"

	"git.tetele.net/tgo/helper"

	_ "github.com/go-sql-driver/mysql"
)

/**
 * 创建数据
 */
func Insert(dbName, table string, data map[string]string) (int64, error) {
	var insertId int64
	var err error
	if dbName == "" && table == "" {
		return insertId, errors.New("没有数据表")
	}
	dbName = getTableName(dbName, table)

	if len(data) < 1 {
		return insertId, errors.New("没有要写入的数据")
	}
	keyList := make([]string, len(data))
	keyStr := make([]string, len(data))
	valueList := make([]interface{}, len(data))

	var i int = 0

	for key, value := range data {
		keyList[i] = key
		keyStr[i] = "?"
		valueList[i] = value
		i++
	}

	result, err := DB.Exec("insert into "+dbName+" ("+strings.Join(keyList, ",")+") value("+strings.Join(keyStr, ",")+")", valueList...)

	if err != nil {
		log.Println("ERROR|插入", dbName, "数据失败,", err)
		return insertId, err
	} else {
		insertId, _ = result.LastInsertId()
		time.Sleep(time.Second * 2)
		return insertId, nil
	}

}

/**
 * 修改数据
 */
func Update(dbName, table string, data map[string]string, where map[string]string) (int64, error) {
	var rowsAffected int64
	var err error
	if dbName == "" && table == "" {
		return rowsAffected, errors.New("没有数据表")
	}

	dbName = getTableName(dbName, table)
	if len(data) < 1 {
		return rowsAffected, errors.New("同有更新的数据")
	}
	if len(where) < 1 {
		return rowsAffected, errors.New("没有更新条件")
	}

	keyList := make([]string, len(data))
	valueList := make([]interface{}, len(data), len(data)+len(where))

	whereStr := make([]string, len(where))

	var i int = 0

	empty := false

	for key, value := range data {
		keyList[i] = key + "=?"
		valueList[i] = value
		i++
	}
	i = 0
	for key, value := range where {
		if value == "" {
			empty = true
			break
		}
		whereStr[i] = key + "=?"
		valueList = append(valueList, value)
		i++
	}

	if empty {
		log.Println("ERROR|修改数据表", dbName, "时条件中有空数据,条件：", where, "数据：", data)
		return rowsAffected, errors.New("条件中有空数据")
	}
	result, err := DB.Exec("update "+dbName+" set "+strings.Join(keyList, " , ")+" where "+strings.Join(whereStr, " and "), valueList...)

	if err != nil {
		log.Println("ERROR|修改", dbName, "数据失败,", err)
		return rowsAffected, err
	} else {
		rowsAffected, _ = result.RowsAffected()
		return rowsAffected, nil
	}

}

/**
 * 删除数据
 * @param count 删除数量
 */
func Delete(dbName, table string, data map[string]string, del_count ...string) (int64, error) {
	var count int64
	var err error
	if dbName == "" && table == "" {
		return count, errors.New("没有数据表")
	}

	dbName = getTableName(dbName, table)

	if len(data) < 1 {
		return count, errors.New("没有要删除的数据")
	}
	keyList := make([]string, len(data))
	valueList := make([]interface{}, len(data))

	var i int = 0

	empty := false
	for key, value := range data {
		if value == "" {
			empty = true
			break
		}
		keyList[i] = key + "=?"
		valueList[i] = value
		i++
	}

	if empty {
		log.Println("ERROR|删除数据表", dbName, "时条件中有空数据,条件：", data)
		return count, errors.New("条件中有空数据")
	}

	var limitStr string = ""

	if len(del_count) > 0 {
		limitStr = " limit " + del_count[0]

	}
	result, err := DB.Exec("delete from "+dbName+" where "+strings.Join(keyList, " and ")+limitStr, valueList...)

	if err != nil {
		log.Println("ERROR|删除", dbName, "数据失败,", err)
		return count, err
	} else {
		count, _ = result.RowsAffected()
		return count, nil
	}

}

/**
 * 查找一条记录
 * @param dbName 数据表名
 * @param title  查询字段名
 */
func GetData(dbName, table string, title string, where map[string]string, limit map[string]string) (int, map[string]string, error) {
	var count int = 0
	info := make(map[string]string)

	if dbName == "" && table == "" {
		return count, info, errors.New("没有数据表")
	}

	dbName = getTableName(dbName, table)

	if len(title) < 1 {
		return count, info, errors.New("没有指定查询内容")
	}

	var limitStr string = ""

	if limit != nil && len(limit) > 0 {
		var from string = "0" //开始

		if _, ok := limit["order"]; ok {
			limitStr += " order by " + limit["order"]
		}

		if _, ok := limit["from"]; ok {
			from = limit["from"]
		}
		limitStr += " limit " + from + ",1"

	} else {
		limitStr = " limit 1"
	}
	if len(where) < 1 {
		return count, info, errors.New("Query condition is empty")
	}
	keyList := make([]string, len(where))
	valueList := make([]interface{}, len(where))

	var i int = 0

	empty := false
	for key, value := range where {
		if value == "" {
			empty = true
			break
		}
		keyList[i] = key + " = ? "
		valueList[i] = value
		i++
	}

	if empty {
		return count, info, errors.New("Query condition is empty")
	}
	var rows *sql.Rows
	var err error
	var queryNum int = 0
	for queryNum < 3 { //如发生错误，继续查询3次，防止数据库连接断开问题

		rows, err = DB.Query("SELECT "+title+" FROM "+dbName+" where "+strings.Join(keyList, " and ")+" "+limitStr, valueList...)

		if err == nil {
			break
		} else {
			log.Println("ERROR", "Query from", dbName, "failed,", err, "| SELECT "+title+" FROM "+dbName+" where "+strings.Join(keyList, " and ")+" "+limitStr)

			time.Sleep(time.Millisecond * 500)
		}
		queryNum++
	}

	defer rows.Close()
	if err != nil {
		return count, info, err
	}

	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))

	for i := range values {
		scanArgs[i] = &values[i]
	}

	var index string
	var rowerr error
	for rows.Next() {
		rowerr = rows.Scan(scanArgs...)
		if rowerr == nil {
			for i, col := range values {
				if col != nil {
					index = helper.StrFirstToUpper(columns[i])
					info[index] = helper.ToString(col)
				}
			}
			count++
		} else {
			log.Println("ERROR", "rows scan error", rowerr, dbName, keyList, valueList)
		}
	}
	if rowerr != nil {
		return count, info, rowerr
	}
	return count, info, nil

}
func GetInfo(dbName, table string, title string, where map[string]string) (map[string]string, error) {

	count, info, gzErr := GetData(dbName, table, title, where, nil)
	if gzErr != nil {
		return info, gzErr
	} else {
		if count < 1 {
			return info, errors.New("No data")
		}
		return info, nil
	}

}

/**
 * 查询列表
 * 2018/04/19
 */
func GetList(dbName, table string, title string, where map[string]string, limit map[string]string) ([]map[string]string, error) {

	var list []map[string]string
	if dbName == "" && table == "" {
		return list, errors.New("没有数据表")
	}

	dbName = getTableName(dbName, table)

	var rows *sql.Rows
	var err error
	var queryNum int = 0

	var limitStr string = ""

	if len(limit) > 0 {

		var offset string = "0" //偏移量，个数
		var from string = ""    //开始

		if _, ok := limit["order"]; ok {
			limitStr += " order by " + limit["order"]
		}

		if _, ok := limit["offset"]; ok {
			offset = limit["offset"]
		}
		if _, ok := limit["from"]; ok {
			from = limit["from"]
		}
		if offset != "0" && from != "" {
			limitStr += " limit " + from + "," + offset
		}
	}

	if len(where) > 0 {
		valueList := make([]interface{}, len(where))
		whereStr := make([]string, len(where))
		i := 0
		var keys []string
		for key, value := range where {
			key = strings.Trim(key, " ")
			value = strings.Trim(value, " ")
			if value == "" || key == "" {
				continue
				// return list, errors.New("Query condition is empty")
			}
			if strings.Contains(key, " ") {
				//key中包含空格，判断是否不等于条件
				keys = strings.Split(key, " ")
				if helper.IsInStringArray(judg(), strings.Trim(keys[1], " ")) {
					whereStr[i] = strings.Trim(keys[0], " ") + " " + strings.Trim(keys[1], " ") + " ?"
				}
			} else {
				whereStr[i] = key + " = ?"
				valueList[i] = value
			}
			i++
		}

		if len(whereStr) == 0 || len(valueList) == 0 {
			return list, errors.New("Query condition is empty")
		}

		for queryNum < 5 { //如发生错误，继续查询5次，防止数据库连接断开问题

			rows, err = DB.Query("select "+title+" from "+dbName+" where "+strings.Join(whereStr, " and ")+" "+limitStr, valueList...)

			if err == nil {
				break
			} else {
				time.Sleep(time.Millisecond * 500)
			}
			queryNum++
		}

	} else {
		for queryNum < 5 { //如发生错误，继续查询5次，防止数据库连接断开问题

			rows, err = DB.Query("select " + title + " from " + dbName + " " + limitStr)

			if err == nil {
				break
			} else {
				time.Sleep(time.Millisecond * 500)
			}
			queryNum++
		}
	}

	if err != nil {
		return list, err
	}
	defer rows.Close()

	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))
	for i := range values {
		scanArgs[i] = &values[i]
	}

	var record map[string]string
	var index string
	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)
		record = make(map[string]string)

		for i, col := range values {
			if col != nil {
				index = helper.StrFirstToUpper(columns[i])
				record[index] = helper.ToString(col)
			}
		}
		list = append(list, record)
	}

	return list, nil
}

/**
 * 查询总数
 * 2018/04/19
 */
func GetTotal(dbName, table string, args ...string) (total int) {

	if dbName == "" && table == "" {
		return
	}

	dbName = getTableName(dbName, table)

	var title string = "*"

	if len(args) > 0 {
		title = args[0]
	}

	var rows *sql.Rows
	var err error
	var queryNum int = 0

	for queryNum < 5 { //如发生错误，继续查询5次，防止数据库连接断开问题

		rows, err = DB.Query("select count(" + title + ") as count from " + dbName + " limit 1")

		if err == nil {
			break
		} else {
			time.Sleep(time.Millisecond * 500)
		}
		queryNum++
	}

	if err != nil {
		log.Println("ERROR|get", dbName, "total error", err)
		return
	}
	defer rows.Close()

	var count int

	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(&count)
		if err != nil {
			log.Println("ERROR|get", dbName, "total error", err)
		} else {
			total = count
		}
	}

	return
}

/**
 * 查询总数
 * 2020/06/04
 */
func GetCount(dbName, table string, where map[string]string, args ...string) (total int) {

	if dbName == "" && table == "" {
		return
	}
	dbName = getTableName(dbName, table)

	var title string = "*"

	if len(title) > 0 {
		title = args[0]
	}

	var rows *sql.Rows
	var err error
	var queryNum int = 0

	if len(where) > 0 {
		valueList := make([]interface{}, len(where))
		whereStr := make([]string, len(where))
		i := 0
		var keys []string
		for key, value := range where {
			key = strings.Trim(key, " ")
			value = strings.Trim(value, " ")
			if value == "" || key == "" {
				continue
				// return list, errors.New("Query condition is empty")
			}
			if strings.Contains(key, " ") {
				//key中包含空格，判断是否不等于条件
				keys = strings.Split(key, " ")
				if helper.IsInStringArray(judg(), strings.Trim(keys[1], " ")) {
					whereStr[i] = strings.Trim(keys[0], " ") + " " + strings.Trim(keys[1], " ") + " ?"
				}
			} else {
				whereStr[i] = key + " = ?"
				valueList[i] = value
			}
			i++
		}

		for queryNum < 5 { //如发生错误，继续查询5次，防止数据库连接断开问题

			rows, err = DB.Query("select count("+title+") as count from "+dbName+" where "+strings.Join(whereStr, " and ")+" limit 1", valueList...)

			if err == nil {
				break
			} else {
				time.Sleep(time.Millisecond * 500)
			}
			queryNum++
		}

	} else {
		for queryNum < 5 { //如发生错误，继续查询5次，防止数据库连接断开问题

			rows, err = DB.Query("select count(" + title + ") as count from " + dbName + " limit 1")

			if err == nil {
				break
			} else {
				time.Sleep(time.Millisecond * 500)
			}
			queryNum++
		}
	}

	if err != nil {
		log.Println("ERROR|get", dbName, "count error", err)
		return
	}
	defer rows.Close()

	var count int

	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(&count)
		if err != nil {
			log.Println("ERROR|get", dbName, "count error", err)
		} else {
			total = count
		}
	}

	return
}
func DoQuery(args ...interface{}) ([]map[string]string, error) {
	var list []map[string]string
	if len(args) < 1 {
		return list, errors.New("Query condition is empty")
	}
	queryStr := helper.ToString(args[0])
	if queryStr == "" {
		return list, errors.New("Query condition is empty")
	}
	var rows *sql.Rows
	var err error
	var queryNum int = 0

	for queryNum < 3 { //如发生错误，继续查询5次，防止数据库连接断开问题
		if len(args) > 1 {
			rows, err = DB.Query(queryStr, args[1:]...) //strings.Join(args[1:], ",")
			if err != nil {
				log.Println("ERROR|DoQuery error:", err)
			}
		} else {
			rows, err = DB.Query(queryStr)
			if err != nil {
				log.Println("ERROR|DoQuery error:", err)
			}
		}

		if err == nil {
			break
		} else {
			time.Sleep(time.Millisecond * 500)
		}
		queryNum++
	}

	if err != nil {
		return list, err
	}
	defer rows.Close()

	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))
	for i := range values {
		scanArgs[i] = &values[i]
	}

	var record map[string]string
	var index string
	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)
		record = make(map[string]string)

		for i, col := range values {
			if col != nil {
				index = helper.StrFirstToUpper(columns[i])
				record[index] = helper.ToString(col)
			}
		}
		list = append(list, record)
	}

	return list, nil
}