goInOut/dbOperate/dbHelper.go

package dbOperate

import (
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	_ "github.com/mattn/go-sqlite3"
	"log"
	"time"
)

const (
	postgres = iota + 1
	sqlite3
)

type DBHelper struct {
	dbClient   *sqlx.DB
	dbType     string
	ConnectStr string
}

func NewDBHelper(dbType string, connectStr string) *DBHelper {
	the := &DBHelper{}
	switch dbType {
	case "postgres":
		fallthrough
	case "mysql":
		fallthrough
	case "sqlite3":
		fallthrough
	case "有效的数据库类型":
		the.dbType = dbType
		the.ConnectStr = connectStr
	default:
		log.Panicf("不支持的数据库类型=> %s", dbType)

	}
	return the
}

// sqlite3 => connectStr := os.Getwd() + "/db/cz.db"
// mysql => "user:password@tcp(127.0.0.1:3306)/databaseName"
// pg => "host=192.168.10.64 port=5432 user=postgres password=123456 dbname=headscale sslmode=disable"
func (the *DBHelper) dbOpen() error {
	var err error
	tdb, err := sqlx.Open(the.dbType, the.ConnectStr)
	if err != nil {
		return err
	}
	if err = tdb.Ping(); err != nil {
		return err
	}
	the.dbClient = tdb
	return nil
}
func (the *DBHelper) Exec(dbRecordSQL string) error {
	if the.dbClient == nil {
		if openErr := the.dbOpen(); openErr != nil {
			//logger.Info("[%s]数据库链接失败,err=%v\n", time.Now().Format("2006-01-02 15:04:05.000"), openErr)
			return openErr
		}
	}
	execResult, execErr := the.dbClient.Exec(dbRecordSQL)
	defer the.dbClient.Close()
	if execErr != nil {
		return execErr
	}
	if n, err := execResult.RowsAffected(); n > 0 {
		return nil
	} else {
		log.Printf("[%s]执行sql[%s]失败\n", time.Now().Format("2006-01-02 15:04:05.000"), dbRecordSQL)
		return err
	}

}

func (the *DBHelper) Query(dest any, sql string) error {

	start := time.Now()

	if the.dbClient == nil {
		if err := the.dbOpen(); err != nil {
			log.Printf("数据库链接失败:%s", err.Error())
			return err
		}
	}
	err := the.dbClient.Select(dest, sql)
	if err != nil {
		log.Printf("数据库查询失败:%s,\n sql=%s", err.Error(), sql)
	}

	durTime := time.Since(start).Seconds()
	log.Printf("查询耗时:%v s", durTime)
	return err

}

func (the *DBHelper) Close() error {
	return the.Close()
}