Golang Gin框架搭建项目（四）gorm/mysql

1、安装包：

go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql

2、帮助类MySqlUtil：

package utils

import (
	"fmt"
	"gin_demo/config"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"strconv"
)

type mySqlUtil struct {
	DB *gorm.DB
}

//全局变量, 外部使用utils.MySqlClient来访问
var MySqlClient mySqlUtil

func InitMySqlUtil(config config.DatabaseConfig) error {
	dsn := buildDatabaseDSN(config)
	fmt.Println(dsn)
	dd, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Warn),
	})
	if err != nil {
		panic("[SetupDefaultDatabase#newConnection error]: " + err.Error() + " " + dsn)
	}

	sqlDB, err := dd.DB()
	if err != nil {
		panic("[SetupDefaultDatabase#newConnection error]: " + err.Error() + " " + dsn)
	}
	if config.MaxOpenConns > 0 {
		sqlDB.SetMaxOpenConns(config.MaxOpenConns)
	}
	if config.MaxIdleConns > 0 {
		sqlDB.SetMaxIdleConns(config.MaxIdleConns)
	}

	fmt.Printf("\nDefault atabase connection successful: %s\n", dsn)
	//初始化全局redis结构体
	MySqlClient = mySqlUtil{DB: dd}
	return nil
}

func buildDatabaseDSN(config config.DatabaseConfig) string {
	switch config.Driver {
	case "mysql":
		return fmt.Sprintf(
			"%s:%s@tcp(%s:%s)/%s?%s",
			config.User,
			config.Password,
			config.Host,
			strconv.Itoa(config.Port),
			config.DbName,
			config.Options,
		)
	case "postgres":
		return fmt.Sprintf(
			"host=%s port=%s user=%s dbname=%s password=%s options='%s'",
			config.Host,
			strconv.Itoa(config.Port),
			config.User,
			config.DbName,
			config.Password,
			config.Options,
		)
	case "sqlite3":
		return config.DbName
	case "mssql":
		return fmt.Sprintf(
			"sqlserver://%s:%s@%s:%s?database=%s&%s",
			config.User,
			config.Password,
			config.Host,
			strconv.Itoa(config.Port),
			config.DbName,
			config.Options,
		)
	}

	panic("DB Connection not supported:" + config.Driver)
	return ""
}

3、定义一个含有基础增删改查的类，其它数据库操作类都继承这个：

package dao

import (
	"errors"
	"fmt"
	"gin_demo/utils"
)

type BaseDao struct {
}

//新增
func (BaseDao) Add(model interface{}) (err error) {
	err = utils.MySqlClient.DB.Create(model).Error
	return
}

//根据Id查询
func (BaseDao) Get(model interface{}, id int) error {
	fmt.Println("UserInfoDao Get")
	if id < 1 {
		return errors.New("请输入id")
	}
	_ = utils.MySqlClient.DB.First(&model, id)
	return nil
}

//更新
func (BaseDao) UpdateModel(value interface{}) (err error) {
	err = utils.MySqlClient.DB.Save(value).Error
	return
}

//删除
func (BaseDao) DeleteModel(value interface{}) (err error) {
	err = utils.MySqlClient.DB.Delete(value).Error
	return
}

4、用户相关的增删改查，继承BaseDao：UserInfoDao

package dao

import (
	"gin_demo/models"
	"gin_demo/utils"
)

type UserInfoDao struct {
	BaseDao
}

var UserInfo = UserInfoDao{}

//修改用户名
func (UserInfoDao) UpdateUserName(name string, id int) error {
	err := utils.MySqlClient.DB.Model(&models.UserInfo{}).Where("id = ?", id).Update("name", name).Error
	return err
}

//分页查询用户列表
func (UserInfoDao) GetUserPageList(index int, size int) ([]models.UserInfo, error) {
	results := make([]models.UserInfo, 0)
	err := utils.MySqlClient.DB.Model(&models.UserInfo{}).Where("age < ?", 20).Where("name LIKE ?", "%王").Limit(size).Offset(index * size).Find(&results).Error
	return results, err
}

package models
//数据库表结构
type UserInfo struct {
	Id   int    `gorm:"primaryKey;autoIncrement" form:"id"`
	Name string `form:"name"`
	Age  int    `form:"age"`
}

// 自定义表名
func (UserInfo) TableName() string {
	return "userinfo"
}

 

5、main.go初始化数据库：

package main

import (
	"gin_demo/config"
	"gin_demo/routers"
	"gin_demo/utils"
	"github.com/gin-gonic/gin"
)

func main() {
	conf, err := config.ParseConfig("./config/app.json")
	if err != nil {
		panic("读取配置文件失败，" + err.Error())
	}
	//fmt.Printf("conf:%#v\n", conf)

	utils.InitRedisUtil(conf.RedisConfig.Addr, conf.RedisConfig.Port, conf.RedisConfig.Password)
	utils.InitMySqlUtil(conf.Database)
	//创建一个默认的路由引擎
	engine := gin.Default()
	routers.RegisterRouter(engine)
	engine.Run(":9090")
}

6、使用：

package controller

import (
	"gin_demo/config"
	"gin_demo/dao"
	"gin_demo/models"
	"github.com/gin-gonic/gin"
	"net/http"
	"strconv"
)

type UserController struct {
}

//新增用户
func (controller *UserController) Add(context *gin.Context) {
	//name := context.PostForm("name")
	var user models.UserInfo
	err := context.ShouldBindJSON(&user)
	if err != nil {
		context.JSON(http.StatusOK, gin.H{"msg": "参数错误"})
		return
	}
	dao.UserInfo.Add(&user)

	context.JSON(http.StatusOK, gin.H{"user": user})
}

//查询用户
func (controller *UserController) Get(context *gin.Context) {
	id, _ := strconv.Atoi(context.Query("id"))
	println("id:", id)

	var user models.UserInfo
	dao.UserInfo.Get(&user, id)
	context.JSON(http.StatusOK, gin.H{
		"id":   id,
		"conf": config.GetConfig(),
		"user": user,
		//"hello": hello,
	})
}

//分页查询
func (controller *UserController) GetPageList(context *gin.Context) {
	index, _ := strconv.Atoi(context.Query("index"))
	println("index:", index)
	size, _ := strconv.Atoi(context.Query("size"))
	println("size:", size)

	list, _ := dao.UserInfo.GetUserPageList(index, size)
	context.JSON(http.StatusOK, gin.H{
		"users": list,
	})
}

//分页查询
func (controller *UserController) UpdateUserName(context *gin.Context) {
	name := context.PostForm("name")
	println("name:", name)
	id, _ := strconv.Atoi(context.PostForm("id"))
	println("id:", id)

	_ = dao.UserInfo.UpdateUserName(name, id)
	context.JSON(http.StatusOK, gin.H{
		"code": 1,
	})
}