smart_canteen/app/db/db.go

package db

import (
	"database/sql"
	"fmt"
	"os"

	_ "github.com/go-sql-driver/mysql"
	"xorm.io/xorm"
	"xorm.io/xorm/log"

	"applet/app/cfg"
	"applet/app/utils/logx"
)

var Db *xorm.Engine

func InitDB(c *cfg.DBCfg) error {
	var err error
	if Db, err = xorm.NewEngine("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8mb4", c.User, c.Psw, c.Host, c.Name)); err != nil {
		return err
	}
	Db.SetConnMaxLifetime(c.MaxLifetime)
	Db.SetMaxOpenConns(c.MaxOpenConns)
	Db.SetMaxIdleConns(c.MaxIdleConns)
	if err = Db.Ping(); err != nil {
		return err
	}
	if c.ShowLog {
		Db.ShowSQL(true)
		Db.Logger().SetLevel(0)
		f, err := os.OpenFile(c.Path, os.O_APPEND|os.O_WRONLY|os.O_CREATE, 0777)
		if err != nil {
			os.RemoveAll(c.Path)
			if f, err = os.OpenFile(c.Path, os.O_APPEND|os.O_WRONLY|os.O_CREATE, 0777); err != nil {
				return err
			}
		}
		logger := log.NewSimpleLogger(f)
		logger.ShowSQL(true)
		Db.SetLogger(logger)
	}
	return nil
}

/********************************************* 公用方法 *********************************************/

// DbInsertBatch 数据批量插入
func DbInsertBatch(Db *xorm.Engine, m ...interface{}) error {
	if len(m) == 0 {
		return nil
	}
	id, err := Db.Insert(m...)
	if id == 0 || err != nil {
		return logx.Warn("cannot insert data :", err)
	}
	return nil
}

// QueryNativeString 查询原生sql
func QueryNativeString(Db *xorm.Engine, sql string, args ...interface{}) ([]map[string]string, error) {
	results, err := Db.SQL(sql, args...).QueryString()
	return results, err
}
func QueryNativeStringSess(sess *xorm.Session, sql string, args ...interface{}) ([]map[string]string, error) {
	results, err := sess.SQL(sql, args...).QueryString()
	return results, err
}

// CommonInsert 插入一条或多条数据
func CommonInsert(Db *xorm.Engine, data interface{}) (int64, error) {
	row, err := Db.Insert(data)
	return row, err
}

// UpdateComm 根据主键更新
func UpdateComm(Db *xorm.Engine, id interface{}, model interface{}) (int64, error) {
	row, err := Db.ID(id).Update(model)
	return row, err
}

// InsertOneComm 插入一条数据
func InsertOneComm(Db *xorm.Engine, model interface{}) (int64, error) {
	row, err := Db.InsertOne(model)
	return row, err
}

// GetComm 获取一条数据
// payload *model
// return *model,has,err
func GetComm(Db *xorm.Engine, model interface{}) (interface{}, bool, error) {
	has, err := Db.Get(model)
	if err != nil {
		_ = logx.Warn(err)
		return nil, false, err
	}
	return model, has, nil
}

// ExecuteOriginalSql 执行原生sql
func ExecuteOriginalSql(Db *xorm.Engine, sql string) (sql.Result, error) {
	result, err := Db.Exec(sql)
	if err != nil {
		_ = logx.Warn(err)
		return nil, err
	}
	return result, nil
}

// InsertCommWithSession common insert
func InsertCommWithSession(session *xorm.Session, model interface{}) (int64, error) {
	row, err := session.InsertOne(model)
	return row, err
}