golang 数据库连接池

简介

本文介绍如何使用gorm 2 连接池的使用代码例子,该连接池支持Mysql、postgres、sqlite三种数据库。

安装依赖

go get gorm.io/gorm
go get gorm.io/driver/mysql
go get gorm.io/driver/postgres
go get gorm.io/driver/sqlite

数据库配置文件

• 配置文件

{
  "database": {
    "name": "db1",
    "password": "123456",
    "user": "root",
    "type": "mysql",
    "host": "127.0.0.1",
    "port": "3306",
    "table_prefix": ""
  }
}

• 读取配置文件代码

package config

import (
    "encoding/json"
    "os"
)

// Config 配置对象
type Config struct {
    Database *Database `json:"database"`
}

// GlobalConfigSetting 配置实例
var GlobalConfigSetting = &Config{}

// Setup 配置
func Setup() {
    filePtr, err := os.Open("config/config.json") //config的文件目录
    if err != nil {
        return
    }
    defer filePtr.Close()
    // 创建json解码器
    decoder := json.NewDecoder(filePtr)
    err = decoder.Decode(GlobalConfigSetting)
    DatabaseSetting = GlobalConfigSetting.Database

}

// Database 数据库配置对象
type Database struct {
    Type        string `json:"type"`
    User        string `json:"user"`
    Password    string `json:"password"`
    Host        string `json:"host"`
    Port        string `json:"port"`
    Name        string `json:"name"`
    TablePrefix string `json:"table_prefix"`
}

// DatabaseSetting 数据库配置对象 实例
var DatabaseSetting = &Database{}

连接池代码

package dao

import (
    "fmt"
    "log"
    "report/src/config"
    "time"

    "gorm.io/driver/mysql"
    "gorm.io/driver/postgres"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

// db连接
var db *gorm.DB

// Setup 初始化连接
func Setup() {
    // db = newConnection()
    var dbURI string
    var dialector gorm.Dialector
    if config.DatabaseSetting.Type == "mysql" {
        dbURI = fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8&parseTime=true",
            config.DatabaseSetting.User,
            config.DatabaseSetting.Password,
            config.DatabaseSetting.Host,
            config.DatabaseSetting.Port,
            config.DatabaseSetting.Name)
        dialector = mysql.New(mysql.Config{
            DSN:                       dbURI, // data source name
            DefaultStringSize:         256,   // default size for string fields
            DisableDatetimePrecision:  true,  // disable datetime precision, which not supported before MySQL 5.6
            DontSupportRenameIndex:    true,  // drop & create when rename index, rename index not supported before MySQL 5.7, MariaDB
            DontSupportRenameColumn:   true,  // `change` when rename column, rename column not supported before MySQL 8, MariaDB
            SkipInitializeWithVersion: false, // auto configure based on currently MySQL version
        })
    } else if config.DatabaseSetting.Type == "postgres" {
        dbURI = fmt.Sprintf("host=%s port=%s user=%s dbname=%s sslmode=disable password=%s",
            config.DatabaseSetting.Host,
            config.DatabaseSetting.Port,
            config.DatabaseSetting.User,
            config.DatabaseSetting.Name,
            config.DatabaseSetting.Password)
        dialector = postgres.New(postgres.Config{
            DSN:                  "user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai",
            PreferSimpleProtocol: true, // disables implicit prepared statement usage
        })
    } else { // sqlite3
        dbURI = fmt.Sprintf("test.db")
        dialector = sqlite.Open("test.db")
    }
    conn, err := gorm.Open(dialector, &gorm.Config{})
    if err != nil {
        log.Print(err.Error())
    }
    sqlDB, err := conn.DB()
    if err != nil {
        fmt.Error("connect db server failed.")
    }
    sqlDB.SetMaxIdleConns(10)                   // SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
    sqlDB.SetMaxOpenConns(100)                  // SetMaxOpenConns sets the maximum number of open connections to the database.
    sqlDB.SetConnMaxLifetime(time.Second * 600) // SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
    db = conn
}

// GetDB 开放给外部获得db连接
func GetDB() *gorm.DB {
    sqlDB, err := db.DB()
    if err != nil {
        fmt.Errorf("connect db server failed.")
        Setup()
    }
    if err := sqlDB.Ping(); err != nil {
        sqlDB.Close()
        Setup()
    }

    return db
}

使用示例

package domain

import (
    "report/src/dao"

    "gorm.io/gorm"
)

// Product 产品
type Product struct {
    gorm.Model
    Code  string
    Price uint
}

// Test 测试
func (Product) Test() {
    db := dao.GetDB()
    // 自动迁移模式
    db.AutoMigrate(&Product{})

    // 创建
    db.Create(&Product{Code: "L1212", Price: 1000})

}