数据库公共方法
Mysql配置包
package conf
import (
"gorm.io/driver/mysql"
)
var mysqlOpts = &mysql.Config{
DSN: "链接地址", // DSN data source name
DefaultStringSize: 256, // string 类型字段的默认长度
DisableDatetimePrecision: true, // 禁用 datetime 精度,MySQL 5.6 之前的数据库不支持
DontSupportRenameColumn: true, // 用 `change` 重命名列,MySQL 8 之前的数据库和 MariaDB 不支持重命名列
SkipInitializeWithVersion: false, // 根据当前 MySQL 版本自动配置
}
func GetMysqlConf() *mysql.Config {
return mysqlOpts
}
package mysql
import (
"gorm.io/driver/mysql"
"gorm.io/gorm"
"xxx/conf"//数据库配置包
"xxx/logger"//日志配置包
)
var db *gorm.DB
func Setup() {
var err error
//连接数据库
db, err = gorm.Open(mysql.New(*conf.GetMysqlConf()), &gorm.Config{
SkipDefaultTransaction: true,
})
if err != nil {
logger.Fatal(logger.Fields{"from": "mysql open"}, err)
} else {
logger.Info(logger.Fields{"from": "mysql open"}, "mysql open")
}
}
func GetClient() *gorm.DB {
return db
}
main.go调用
package main
import (
mysql "xxx/mysql"
)
**自动加载**
func init() {
mysql.Setup()
}
开始操作数据库
package pc
import (
"errors"
"github.com/gin-gonic/gin"
"gorm.io/gorm"
"net/http"
"time"
"xxx/mysql"
)
// 定义返回结构体
type APIPointList struct {
//json定义返回的字段名称 gorm标识数据库对应字段以及默认值等
DistrictId uint `json:"district_id" gorm:"column:district_id;primaryKey;autoIncrement"` // 主键
DistrictName string `json:"district_name" gorm:"column:district_name;not null"` // 站点名称
}
//查询结构体
type PointListQuery struct {
//form查询条件必须在url中携带 binding 验证绑定的数据
DistrictName string `form:"district_name" binding:"-"`
Current int `form:"current" binding:"omitempty,gt=0"`
PageSize int `form:"pageSize" binding:"omitempty,gt=0"`
}
func GetPointList(c *gin.Context) {
var query PointListQuery
if err := c.ShouldBindQuery(&query); err != nil {
logger.Error(logger.Fields{
"from": "GetPointList",
}, err)
c.JSON(http.StatusBadRequest, gin.H{
"code": -1,
"errMsg": "Bad Request",
})
return
}
logger.Debug(logger.Fields{
"from": "GetPointList",
"query": query,
})
//链接数据库
db := mysql.GetClient()
//定义返回切片结构体
var list []APIPointList
//定义数据总数
var count int64
//定义分页默认值
var current = 1
var pageSize = 20
sql := db.Table("district")
if query.DistrictName != "" {
sql.Where("district_name LIKE ?", "%"+query.DistrictName+"%")
}
if query.Current > 0 {
current = query.Current
}
if query.PageSize > 0 {
pageSize = query.PageSize
}
//count会查询出当前符合条件所有的数据总数,注意Count必须在limit前
err = sql.Count(&count).Limit(pageSize).Offset((current - 1) * pageSize).Order("district_id desc").Find(&list).Error
if err != nil {
c.JSON(http.StatusOK, gin.H{
"code": -1,
"errMsg": err,
})
return
}
c.JSON(http.StatusOK, gin.H{
"code": 1,
"data": map[string]interface{}{
"total": count,
"list": list,
},
})
}
效果图如下