golang连接池操作mysql数据库缓存字典表数据

连接池

package common

import (
	"database/sql"
	LOG "github.com/vinllen/log4go"
	"time"
)

type dbIndex struct {
	index int
	host string
	database string
	user string
	password string
	maxOpenNums int
	maxIdelNums int
}
var (
	mapConns map[dbIndex]*Conn
)

func Startup() {
	mapConns = make(map[dbIndex]*Conn)
}

func Connect(index int,h, d, u, p string,openNums,idleNums int) *Conn {
	var idx dbIndex
	idx.index = index
	idx.host = h
	idx.database = d
	idx.user = u
	idx.password = p
	idx.maxOpenNums = openNums
	idx.maxIdelNums = idleNums

	conn := connectMySQL(idx.host, idx.database, idx.user, idx.password, "utf8", idx.maxOpenNums, idx.maxIdelNums)
	if conn == nil {
		LOG.Error("connect mysql failed ...")
		return nil
	}

	mapConns[idx] = conn
	return conn
}

func StartWork() {
	go func() {
		timer := time.NewTicker(300 * time.Second)
		for {
			select {
			case <-timer.C:
				for idx,conn := range mapConns {
					//conn.SQLDB本身就是连接池，此处只是定期做检测处理
					err := conn.ping()
					if err != nil {
						conn.close()
						Connect(idx.index,idx.host,idx.database,idx.user,idx.password,idx.maxOpenNums,idx.maxIdelNums)
						break
					}
				}
			}
		}
	}()
}

func GetMysqlDBConn(index int) *sql.DB {
	for idx,_ := range mapConns {
		if idx.index == index {
			return mapConns[idx].SQLDB
		}
	}

	return nil
}

数据库连接及查询方法

package common

import (
	"database/sql" //sql连接池
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	LOG "github.com/vinllen/log4go"
	"log"
)

type Conn struct {
	DriverName string
	DataSourceName string
	MaxOpenConns int 		//用于设置最大打开的连接数，默认值为0表示不限制
	MaxIdleConns int		//用于设置闲置的连接数
	SQLDB *sql.DB
}

func connectMySQL(host, database, user, password, charset string, maxOpenConns, maxIdleConns int) *Conn {
	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=%s&autocommit=true", user, password, host, database, charset)
	db := &Conn {
		DriverName: "mysql",
		DataSourceName: dataSourceName,
		MaxOpenConns: maxOpenConns,
		MaxIdleConns: maxIdleConns,
	}
	var err error
	//sql.Open函数实际上是返回一个连接池对象，不是单个连接
	db.SQLDB, err = sql.Open(db.DriverName, db.DataSourceName)
	if err != nil {
		LOG.Error("open mysql failed err=%v", err)
		return nil
	}
	if err = db.SQLDB.Ping(); err != nil {
		LOG.Error("ping mysql failed err=%v", err)
		return nil
	}
	db.SQLDB.SetMaxOpenConns(db.MaxOpenConns)
	db.SQLDB.SetMaxIdleConns(db.MaxIdleConns)
	return db
}

func (db *Conn) close() error {
	return db.SQLDB.Close()
}

func (db *Conn) ping() error {
	return db.SQLDB.Ping()
}

func (db *Conn) execute(sqlStr string, args ...interface{}) (sql.Result, error) {
	return db.SQLDB.Exec(sqlStr, args...)
}

func (db *Conn) Update(sqlStr string, args ...interface{}) (int64, error) {
	res, err := db.execute(sqlStr, args...)
	if err != nil {
		return 0, err
	}

	affect, err := res.RowsAffected()
	return affect, err
}

func (db *Conn) Insert(sqlStr string, args ...interface{}) (int64, error) {
	result, err := db.execute(sqlStr, args...)
	if err != nil {
		return 0, err
	}

	lastid, err := result.LastInsertId()
	return lastid, err
}

func (db *Conn) Delete(sqlStr string, args ...interface{}) (int64, error) {
	result, err := db.execute(sqlStr, args...)
	if err != nil {
		return 0, err
	}

	affect, err := result.RowsAffected()
	return affect, err
}

func (db *Conn) Query(sqlStr string, args ...interface{}) ([]map[string]interface{}, error) {
	rows, err := db.SQLDB.Query(sqlStr, args...)
	if err != nil {
		log.Println(err)
		return []map[string]interface{}{}, 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]
	}
	rowsMap := make([]map[string]interface{}, 0, 10)
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		rowMap := make(map[string]interface{})
		for i, col := range values {
			if col != nil {
				rowMap[columns[i]] = string(col.([]byte))
			}
		}
		rowsMap = append(rowsMap, rowMap)
	}

	if err = rows.Err(); err != nil {
		return []map[string]interface{}{}, err
	}

	return rowsMap, nil
}

//特定id,name组成map输出
func (db *Conn) Query2(sqlStr string, args ...interface{}) (map[string] string, error) {
	rows, err := db.SQLDB.Query(sqlStr, args...)
	if err != nil {
		log.Println(err)
		return map[string] string {}, 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]
	}
	rowsMap := make(map[string] string, 400)
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		rowMap := make(map[string] string)
		for i, col := range values {
			if col != nil {
				rowMap[columns[i]] = string(col.([]byte))
			}
		}
		if id, ok := rowMap["id"]; ok{
			rowsMap[id] = rowMap["name"]
		}else{
			fmt.Println("key does not exist")
		}
	}

	if err = rows.Err(); err != nil {
		return map[string] string{}, err
	}

	return rowsMap, nil
}

缓存字典表数据（指定字段数据）

此demo只需要字典表的2个字段值，且不重复（根据code获得城市名称）
如需要缓存整个表的多个字段数据，可以参考connect.Query方法，亦可以自行改写

package common

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

//定义全局数据字典缓存变量
var m1 map[string]string
var m2 map[string]string

type User struct {
	Id int
	Name string
}

type GaoDe struct {
	GaoDeCode string
	CityName string
}

func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}
func init() {
	//sql.Open函数实际上是返回一个连接池对象，不是单个连接(mysql)

	//初始化
	m1 = make(map[string]string)
	m2 = make(map[string]string)

	Startup()
	//connect := Connect(2, "10.xx.xx.xx:3306", "base", "root", "root", 10, 5) //生产环境
	connect := Connect(2, "10.xx.xx.xx:3306", "base", "root", "root", 10, 5) //测试环境
	StartWork()

	//查询
	parkResult, err := connect.Query2("SELECT parkCode as id,parkName as name from t_center_basic_park")
	checkErr(err)
	m1 = parkResult

	gaoDeCityResult, err2 := connect.Query2("SELECT GaoDeCode as id, CityName as name from city where GaoDeCode is not null ")
	checkErr(err2)
	m2 = gaoDeCityResult

}

func FindParkCityName(c int) string {
	return m1[strconv.Itoa(c)]
}

func FindGaoDeCityName(g string) string {
	return m2[g]
}

调用上面的缓存

package main

import (
	"ETLSyncPro/src/com.xx/xxxx/common"
	"fmt"
)

func main() {
	var a = 63
	name := common.FindParkCityName(a)
	fmt.Println(name)
	var b = "0311"
	cityName := common.FindGaoDeCityName(b)
	fmt.Println(cityName)
}