GO MySQL数据库操作封装

Go  语言操作数据库,

数据库连接封装：

package Test_db

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"sync"
)

var (
	DB       *sql.DB
	poolOnce sync.Once
)

func NewConnection() (*sql.DB, error) {
	m_db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/Test")

	if err != nil {
		// fmt.Println("数据源的名称不合法！")
		log.Fatalln("db connect err:", err)
		return nil, err
	}
	//Ping检查与数据库的连接是否仍有效，如果需要会创建连接。
	connect_err := m_db.Ping()
	if connect_err != nil {
		log.Fatalf("连接失败！error:%v", connect_err)
		return nil, err
	}
	// fmt.Println("连接数据库成功")

	m_db.SetMaxIdleConns(100)
	m_db.SetMaxOpenConns(300)
	// m_db.SetConnMaxLifetime(time.Second * 300)
	m_db.SetConnMaxLifetime(0)
	DB = m_db
	//结构体实例化
	GetDBStats()
	return m_db, nil
}

func GetDB() (*sql.DB, error) {
	var err error
	poolOnce.Do(func() {
		DB, err = NewConnection()
		if err != nil {
			fmt.Println("Connection err :", err)
		}
	})

	err = DB.Ping()
	if err != nil {
		DB.Close()
		DB, err = NewConnection()
		if err != nil {
			return nil, err
		}
	}
	return DB, nil
}

func GetDBStats() {
	info1 := fmt.Sprintf("数据库状态 ：\n最大连接数：%d,  当前总连接数；%d,  已使用: %d, 空闲数量：%d \n",
		DB.Stats().MaxOpenConnections,
		DB.Stats().OpenConnections,
		DB.Stats().InUse,
		DB.Stats().Idle)
	info2 := fmt.Sprintf("等待连接数量：%d,  等待创建新连接时长(秒)：%f, 空闲超限关闭数量：%d, 空闲超时关闭数量：%d, 连接超时关闭数量：%d \n",
		DB.Stats().WaitCount,
		DB.Stats().WaitDuration.Seconds(),
		DB.Stats().MaxIdleClosed,
		DB.Stats().MaxIdleTimeClosed,
		DB.Stats().MaxLifetimeClosed,
	)
	info := info1 + info2
	_, _ = fmt.Println(info)
}

数据库操作封装：

package Test_db

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"reflect"
	"strings"
	utils 
)

func QueryRecord(sql string) (string, error) {
	conn, err := GetDB()
	if err != nil {
		return " ", err
	}
	stm, pre_err := conn.Prepare(sql)
	if pre_err != nil {
		return " ", errors.New("QueryRecord prepare err: " + pre_err.Error())
	}
	defer stm.Close()
	rows, err := stm.Query()
	if err != nil {
		return " ", err
	}
	defer rows.Close()
	columns, err := rows.Columns()
	if err != nil {
		return " ", err
	}
	count := len(columns)
	value := make([]map[string]interface{}, 0)
	for rows.Next() {
		values := make([]interface{}, count)
		valuePtrs := make([]interface{}, count)
		for i, _ := range columns {
			valuePtrs[i] = &values[i]
		}
		if err := rows.Scan(valuePtrs...); err != nil {
			log.Fatal(err)
		}

		var m map[string]interface{}
		m = make(map[string]interface{})
		for i := range columns {
			var v interface{}
			val := values[i]
			b, ok := val.([]byte)
			if ok {
				str := string(b)
				if (strings.HasPrefix(str, "{") && strings.HasSuffix(str, "}")) ||
					(strings.HasPrefix(str, "[{") && strings.HasSuffix(str, "}]")) {
					_ = json.Unmarshal([]byte(str), &v)
				} else {
					v = string(b)
				}
			} else {
				v = val
			}
			m[columns[i]] = v
		}
		value = append(value, m)

	}
	jsonValue, err := json.Marshal(value)
	if err != nil {
		return " ", err
	}
	return string(jsonValue), nil
}

func InsertDB(strTableName string, jsonValue interface{}) (int64, error) {
	strSql := fmt.Sprintf("INSERT INTO  %v  ( ", strTableName)
	var parms []string
	parms = utils.GetNameJosnValue(jsonValue)
	for i, v := range parms {
		if i == len(parms)-1 {
			strSql += "`" + v + "`"
		} else {
			strSql += "`" + v + "`, "
		}
	}
	strSql += ") VALUES "
	vals := []interface{}{}
	sqlQuery := fmt.Sprintf("(%s)", strings.Join(strings.Split(strings.Repeat("?", len(parms)), ""), ","))
	var n sql.NullString
	switch obj := jsonValue.(type) {
	case map[string]interface{}:
		strSql += sqlQuery
		for _, k := range parms {
			s := utils.GetNameToStringValue(k, obj)
			if len(s) == 0 {
				vals = append(vals, n)
			} else {
				vals = append(vals, s)
			}
		}
	case []interface{}:
		for index, v := range obj {
			if index == len(obj)-1 {
				strSql += sqlQuery
			} else {
				strSql += sqlQuery + ", "
			}
			m := v.(map[string]interface{})
			for _, k := range parms {
				s := utils.GetNameToStringValue(k, m)
				if len(s) == 0 {
					vals = append(vals, n)
				} else {
					vals = append(vals, s)
				}
			}
		}

	default:
		fmt.Println("type:", reflect.TypeOf(obj), "value: \n", obj)
	}

	conn, err := GetDB()
	if err != nil {
		return 0, err
	}

	tx, _ := conn.Begin()

	stmt, err := conn.Prepare(strSql)
	if err != nil {
		fmt.Printf("prepare failed, err:%v\n", err)
		return 0, err
	}
	defer stmt.Close()
	res, err := stmt.Exec(vals...)
	if err != nil {
		fmt.Println("err: ", err)
		fmt.Println("err: ", err.Error())
		tx.Rollback()
		return 0, err
	}

	tx.Commit() // 提交事务
	id, _ := res.LastInsertId()
	fmt.Printf("lastId: %d insert success! /n", id)
	return id, err
}

func UpdateDB(strTableName string, jsonValue interface{}, strCondition string) (bool, error) {

	strValues := ""
	switch obj := jsonValue.(type) {
	case map[string]interface{}:
		for k, _ := range obj {
			fmt.Println("k:", k, " \n")
			if strValues == "" {
				fmt.Println("k2:", k, " \n")
				strValues = fmt.Sprintf("%v='%v'", k, utils.GetNameToStringValue(k, obj))
			} else {
				strValues += fmt.Sprintf(",%v='%v'", k, utils.GetNameToStringValue(k, obj))
			}
		}
	case []interface{}:
		fmt.Println("Multiple updates are currently not supported !")
		break
		for _, v := range obj {
			m := v.(map[string]interface{})
			for k, _ := range m {
				if strValues == "" {
					strValues = fmt.Sprintf("%v='%v'", k, utils.GetNameToStringValue(k, m))
				} else {
					strValues += fmt.Sprintf(",%v='%v'", k, utils.GetNameToStringValue(k, m))
				}
			}
		}

	default:
		fmt.Println("type:", reflect.TypeOf(obj), "value: \n", obj)
	}

	if strValues == "" {
		return false, nil
	}
	strSql := fmt.Sprintf("UPDATE %v  SET %v  %v  ", strTableName, strValues, strCondition)

	vals := []interface{}{}
	conn, err := GetDB()
	if err != nil {
		return false, err
	}
	res, err := conn.Exec(strSql, vals...)
	if err != nil {
		return false, err
	}
	id, _ := res.LastInsertId()
	fmt.Printf("lastId: %d insert success! /n", id)
	return true, nil
}

func Delete(strSql string) (bool, error) {
	conn, err := GetDB()
	if err != nil {
		return false, err
	}
	res, err := conn.Exec(strSql)
	if err != nil {
		return false, err
	}
	fmt.Printf("res:%v  /n", res)
	return true, err
}

func Exec(strSql string) (bool, error) {
	conn, err := GetDB()
	if err != nil {
		return false, err
	}
	res, err := conn.Exec(strSql)
	if err != nil {
		return false, err
	}
	fmt.Printf("res:%v  /n", res)
	return true, err
}

Go 语言返回的也是json 格式，注意接口和map  数组的转换

utils 工具

package Test_utils

import (
	"encoding/json"
	"fmt"
	"reflect"
	"strconv"
	"strings"
)

/*
bool, for JSON booleans
float64, for JSON numbers
string, for JSON strings
[]interface{}, for JSON arrays
map[string]interface{}, for JSON objects
nil for JSON null
种类（Kind）指的是对象归属的品种，在 reflect 包中有如下定义：
Invalid Kind = iota  // 非法类型
Bool								 // 布尔型
Int								   // 有符号整型
Int8								 // 有符号8位整型
Int16								 // 有符号16位整型
Int32								 // 有符号32位整型
Int64								 // 有符号64位整型
Uint								 // 无符号整型
Uint8								 // 无符号8位整型
Uint16							 // 无符号16位整型
Uint32							 // 无符号32位整型
Uint64							 // 无符号64位整型
Uintptr							 // 指针
Float32							 // 单精度浮点数
Float64							 // 双精度浮点数
Complex64						 // 32位复数类型
Complex128					 // 64位复数类型
Array								 // 数组
Chan								 // 通道
Func								 // 函数
Interface						 // 接口
Map								   // 映射
Ptr								   // 指针
Slice								 // 切片
String							 // 字符串
Struct							 // 结构体
UnsafePointer				 // 底层指针
*/

func GetNameJosnValue(jsonValue interface{}) []string {
	var parms []string
	switch obj := jsonValue.(type) {
	case map[string]interface{}:
		for k, _ := range obj {
			parms = append(parms, k)
		}
	case []interface{}:
		for _, v := range obj {
			for m, _ := range v.(map[string]interface{}) {
				parms = append(parms, m)
			}
			return parms
		}

	default:
		fmt.Println("type:", reflect.TypeOf(obj), "value: \n", obj)
	}
	return parms
}

func GetNameToStringValue(k string, m map[string]interface{}) string {
	value := ""
	objCheck := reflect.ValueOf(m[k])

	switch objCheck.Kind() {
	case reflect.String:
		value = m[k].(string)
	case reflect.Int:
		value = string(m[k].(int))
	case reflect.Int32:
		value = string(m[k].(int32))
	case reflect.Int64:
		value = strconv.FormatInt(m[k].(int64), 10)
	case reflect.Float32:
		value = fmt.Sprintf("%f", m[k].(float32))
	case reflect.Float64:
		value = strconv.FormatFloat(m[k].(float64), 'f', -1, 64)
	case reflect.Map:
		result, err := json.Marshal(m[k])
		if err != nil {
			fmt.Println("json Marshal failed :", err)
		}
		value = string(result)
	case reflect.Slice:
		tmp := m[k].([]interface{})
		result, err := json.Marshal(tmp)
		if err != nil {
			fmt.Println("json Marshal failed :", err)
		}
		value = string(result)
	case reflect.Interface:
		for _, v := range m {
			for t, _ := range v.(map[string]interface{}) {
				value = string(t)
			}
		}
	default:
		fmt.Printf("m[k] %v is of unknown type ：", m[k], reflect.TypeOf(m[k]))
	}

	return value
}

func InterfaceToMapByReflection(v interface{}) map[string]interface{} {
	result := make(map[string]interface{})
	fmt.Println("InterfaceToMapByReflection v:", reflect.TypeOf(v))

	switch obj := v.(type) {
	case map[string]interface{}:
		result = obj
	case []interface{}:
		for _, v := range obj {
			result = v.(map[string]interface{})
			break
		}
	default:
		fmt.Println("type:", reflect.TypeOf(obj), "value: \n", obj)
	}
	return result
}

func SplitSeps(s string, seps ...string) interface{} {
	result := []interface{}{}
	if len(seps) == 0 {
		return result
	}

	tmp := strings.Split(s, seps[0])
	for _, sep := range seps[1:] {
		for _, r := range tmp {
			temp := strings.Split(r, sep)
			m := make(map[string]interface{})
			m["key"] = temp[0]
			m["value"] = temp[1]
			result = append(result, m)
		}
	}
	return result
}

func SplitSepsToArray(s string, seps ...string) []string {
	if len(seps) == 0 {
		return []string{s}
	}

	result := strings.Split(s, seps[0])
	for _, sep := range seps[1:] {
		var temp []string
		for _, r := range result {
			temp = append(temp, strings.Split(r, sep)...)
		}
		result = temp
	}
	return result
}

func Split(s, sep string) []string {
	if s == "" {
		return nil
	}
	return strings.Split(s, sep)
}

func MapToJson(i interface{}) string {
	//将map数据转化为JSON格式字符串
	bytes, err := json.Marshal(i)

	//判断是否转换成功
	if err != nil {
		fmt.Println("JSON格式转换失败，错误信息为：", err)
	}
	return string(bytes)
}