mysql 连接字符串 golang_redis mysql 连接池 之 golang 实现

1 mysql 连接池代码

package lib

import (

"database/sql"

"fmt"

"strconv"

"github.com/arnehormann/sqlinternals/mysqlinternals"

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

)

var MYSQL map[string]string = map[string]string{

"host": "127.0.0.1:3306",

"database": "",

"user": "",

"password": "",

"maxOpenConns": "0",

"maxIdleConns": "0",

}

type SqlConnPool struct {

DriverName string

DataSourceName string

MaxOpenConns int64

MaxIdleConns int64

SqlDB *sql.DB // 连接池

}

var DB *SqlConnPool

func init() {

dataSourceName := fmt.Sprintf("%s:%s@tcp(%s)/%s", MYSQL["user"], MYSQL["password"], MYSQL["host"], MYSQL["database"])

maxOpenConns, _ := strconv.ParseInt(MYSQL["maxOpenConns"], 10, 64)

maxIdleConns, _ := strconv.ParseInt(MYSQL["maxIdleConns"], 10, 64)

DB = &SqlConnPool{

DriverName: "mysql",

DataSourceName: dataSourceName,

MaxOpenConns: maxOpenConns,

MaxIdleConns: maxIdleConns,

}

if err := DB.open(); err != nil {

panic("init db failed")

}

}

// 封装的连接池的方法

func (p *SqlConnPool) open() error {

var err error

p.SqlDB, err = sql.Open(p.DriverName, p.DataSourceName)

p.SqlDB.SetMaxOpenConns(int(p.MaxOpenConns))

p.SqlDB.SetMaxIdleConns(int(p.MaxIdleConns))

return err

}

func (p *SqlConnPool) Close() error {

return p.SqlDB.Close()

}

func (p *SqlConnPool) Query(queryStr string, args ...interface{}) ([]map[string]interface{}, error) {

rows, err := p.SqlDB.Query(queryStr, args...)

defer rows.Close()

if err != nil {

return []map[string]interface{}{}, err

}

// 返回属性字典

columns, err := mysqlinternals.Columns(rows)

// 获取字段类型

scanArgs := make([]interface{}, len(columns))

values := make([]sql.RawBytes, len(columns))

for i, _ := range values {

scanArgs[i] = &values[i]

}

rowsMap := make([]map[string]interface{}, 0, 10)

for rows.Next() {

rows.Scan(scanArgs...)

rowMap := make(map[string]interface{})

for i, value := range values {

rowMap[columns[i].Name()] = bytes2RealType(value, columns[i].MysqlType())

}

rowsMap = append(rowsMap, rowMap)

}

if err = rows.Err(); err != nil {

return []map[string]interface{}{}, err

}

return rowsMap, nil

}

func (p *SqlConnPool) execute(sqlStr string, args ...interface{}) (sql.Result, error) {

return p.SqlDB.Exec(sqlStr, args...)

}

func (p *SqlConnPool) Update(updateStr string, args ...interface{}) (int64, error) {

result, err := p.execute(updateStr, args...)

if err != nil {

return 0, err

}

affect, err := result.RowsAffected()

return affect, err

}

func (p *SqlConnPool) Insert(insertStr string, args ...interface{}) (int64, error) {

result, err := p.execute(insertStr, args...)

if err != nil {

return 0, err

}

lastid, err := result.LastInsertId()

return lastid, err

}

func (p *SqlConnPool) Delete(deleteStr string, args ...interface{}) (int64, error) {

result, err := p.execute(deleteStr, args...)

if err != nil {

return 0, err

}

affect, err := result.RowsAffected()

return affect, err

}

type SqlConnTransaction struct {

SqlTx *sql.Tx // 单个事务连接

}

开启一个事务

func (p *SqlConnPool) Begin() (*SqlConnTransaction, error) {

var oneSqlConnTransaction = &SqlConnTransaction{}

var err error

if pingErr := p.SqlDB.Ping(); pingErr == nil {

oneSqlConnTransaction.SqlTx, err = p.SqlDB.Begin()

}

return oneSqlConnTransaction, err

}

// 封装的单个事务连接的方法

func (t *SqlConnTransaction) Rollback() error {

return t.SqlTx.Rollback()

}

func (t *SqlConnTransaction) Commit() error {

return t.SqlTx.Commit()

}

func (t *SqlConnTransaction) Query(queryStr string, args ...interface{}) ([]map[string]interface{}, error) {

rows, err := t.SqlTx.Query(queryStr, args...)

defer rows.Close()

if err != nil {

return []map[string]interface{}{}, err

}

// 返回属性字典

columns, err := mysqlinternals.Columns(rows)

// 获取字段类型

scanArgs := make([]interface{}, len(columns))

values := make([]sql.RawBytes, len(columns))

for i, _ := range values {

scanArgs[i] = &values[i]

}

rowsMap := make([]map[string]interface{}, 0, 10)

for rows.Next() {

rows.Scan(scanArgs...)

rowMap := make(map[string]interface{})

for i, value := range values {

rowMap[columns[i].Name()] = bytes2RealType(value, columns[i].MysqlType())

}

rowsMap = append(rowsMap, rowMap)

}

if err = rows.Err(); err != nil {

return []map[string]interface{}{}, err

}

return rowsMap, nil

}

func (t *SqlConnTransaction) execute(sqlStr string, args ...interface{}) (sql.Result, error) {

return t.SqlTx.Exec(sqlStr, args...)

}

func (t *SqlConnTransaction) Update(updateStr string, args ...interface{}) (int64, error) {

result, err := t.execute(updateStr, args...)

if err != nil {

return 0, err

}

affect, err := result.RowsAffected()

return affect, err

}

func (t *SqlConnTransaction) Insert(insertStr string, args ...interface{}) (int64, error) {

result, err := t.execute(insertStr, args...)

if err != nil {

return 0, err

}

lastid, err := result.LastInsertId()

return lastid, err

}

func (t *SqlConnTransaction) Delete(deleteStr string, args ...interface{}) (int64, error) {

result, err := t.execute(deleteStr, args...)

if err != nil {

return 0, err

}

affect, err := result.RowsAffected()

return affect, err

}

// others

func bytes2RealType(src []byte, columnType string) interface{} {

srcStr := string(src)

var result interface{}

switch columnType {

case "TINYINT":

fallthrough

case "SMALLINT":

fallthrough

case "INT":

fallthrough

case "BIGINT":

result, _ = strconv.ParseInt(srcStr, 10, 64)

case "CHAR":

fallthrough

case "VARCHAR":

fallthrough

case "BLOB":

fallthrough

case "TIMESTAMP":

fallthrough

case "DATETIME":

result = srcStr

case "FLOAT":

fallthrough

case "DOUBLE":

fallthrough

case "DECIMAL":

result, _ = strconv.ParseFloat(srcStr, 64)

default:

result = nil

}

return result

}

2 redis 连接池代码

package lib

import (

"strconv"

"time"

"github.com/garyburd/redigo/redis"

)

var REDIS map[string]string = map[string]string{

"host": "127.0.0.1:6379",

"database": "0",

"password": "",

"maxOpenConns": "0",

"maxIdleConns": "0",

}

var Cache *RedisConnPool

type RedisConnPool struct {

redisPool *redis.Pool

}

func init() {

Cache = &RedisConnPool{}

maxOpenConns, _ := strconv.ParseInt(REDIS["maxOpenConns"], 10, 64)

maxIdleConns, _ := strconv.ParseInt(REDIS["maxIdleConns"], 10, 64)

database, _ := strconv.ParseInt(REDIS["database"], 10, 64)

Cache.redisPool = newPool(REDIS["host"], REDIS["password"], int(database), int(maxOpenConns), int(maxIdleConns))

if Cache.redisPool == nil {

panic("init redis failed！")

}

}

func newPool(server, password string, database, maxOpenConns, maxIdleConns int) *redis.Pool {

return &redis.Pool{

MaxActive: maxOpenConns, // max number of connections

MaxIdle: maxIdleConns,

IdleTimeout: 10 * time.Second,

Dial: func() (redis.Conn, error) {

c, err := redis.Dial("tcp", server)

if err != nil {

return nil, err

}

if _, err := c.Do("AUTH", password); err != nil {

c.Close()

return nil, err

}

if _, err := c.Do("select", database); err != nil {

c.Close()

return nil, err

}

return c, err

},

TestOnBorrow: func(c redis.Conn, t time.Time) error {

_, err := c.Do("PING")

return err

},

}

}

// 关闭连接池

func (p *RedisConnPool) Close() error {

err := p.redisPool.Close()

return err

}

// 当前某一个数据库，执行命令

func (p *RedisConnPool) Do(command string, args ...interface{}) (interface{}, error) {

conn := p.redisPool.Get()

defer conn.Close()

return conn.Do(command, args...)

}

String(字符串)

func (p *RedisConnPool) SetString(key string, value interface{}) (interface{}, error) {

conn := p.redisPool.Get()

defer conn.Close()

return conn.Do("SET", key, value)

}

func (p *RedisConnPool) GetString(key string) (string, error) {

// 从连接池里面获得一个连接

conn := p.redisPool.Get()

// 连接完关闭，其实没有关闭，是放回池里，也就是队列里面，等待下一个重用

defer conn.Close()

return redis.String(conn.Do("GET", key))

}

func (p *RedisConnPool) GetBytes(key string) ([]byte, error) {

conn := p.redisPool.Get()

defer conn.Close()

return redis.Bytes(conn.Do("GET", key))

}

func (p *RedisConnPool) GetInt(key string) (int, error) {

conn := p.redisPool.Get()

defer conn.Close()

return redis.Int(conn.Do("GET", key))

}

func (p *RedisConnPool) GetInt64(key string) (int64, error) {

conn := p.redisPool.Get()

defer conn.Close()

return redis.Int64(conn.Do("GET", key))

}

Key(键)

func (p *RedisConnPool) DelKey(key string) (interface{}, error) {

conn := p.redisPool.Get()

defer conn.Close()

return conn.Do("DEL", key)

}

func (p *RedisConnPool) ExpireKey(key string, seconds int64) (interface{}, error) {

conn := p.redisPool.Get()

defer conn.Close()

return conn.Do("EXPIRE", key, seconds)

}

func (p *RedisConnPool) Keys(pattern string) ([]string, error) {

conn := p.redisPool.Get()

defer conn.Close()

return redis.Strings(conn.Do("KEYS", pattern))

}

func (p *RedisConnPool) KeysByteSlices(pattern string) ([][]byte, error) {

conn := p.redisPool.Get()

defer conn.Close()

return redis.ByteSlices(conn.Do("KEYS", pattern))

}

Hash(哈希表)

func (p *RedisConnPool) SetHashMap(key string, fieldValue map[string]interface{}) (interface{}, error) {

conn := p.redisPool.Get()

defer conn.Close()

return conn.Do("HMSET", redis.Args{}.Add(key).AddFlat(fieldValue)...)

}

func (p *RedisConnPool) GetHashMapString(key string) (map[string]string, error) {

conn := p.redisPool.Get()

defer conn.Close()

return redis.StringMap(conn.Do("HGETALL", key))

}

func (p *RedisConnPool) GetHashMapInt(key string) (map[string]int, error) {

conn := p.redisPool.Get()

defer conn.Close()

return redis.IntMap(conn.Do("HGETALL", key))

}

func (p *RedisConnPool) GetHashMapInt64(key string) (map[string]int64, error) {

conn := p.redisPool.Get()

defer conn.Close()

return redis.Int64Map(conn.Do("HGETALL", key))

}