GORM学习笔记

官方文档：https://gorm.io/zh_CN/docs/

1、数据准备

create table student
(id         int auto_incrementprimary key,sn         varchar(20) null,name       varchar(20) null,email      varchar(20) null,class_id   int         null,monitor_id int         null
);

2、快速开始

2.1、安装依赖

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

2.2、db

package modelimport ("fmt""gorm.io/driver/mysql""gorm.io/gorm"
)var DB *gorm.DB
var err error// Student 属性需大写
type Student struct {Id        intSn        stringName      stringEmail     stringClassId   intMonitorId int
}func (s Student) TableName() string {return "student"
}func init() {dsn := "root:Chace2233.@tcp(127.0.0.1:3306)/study?charset=utf8&parseTime=True&loc=Local"DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})if err != nil {fmt.Println(err)}fmt.Println(DB)
}

2.3、controller

package controllerimport ("fmt""ginstudy/model""github.com/gin-gonic/gin""net/http""strconv"
)type StudentController struct {}func (student StudentController) Select(c *gin.Context) {var users []model.Student// 全量查询//model.DB.Find(&users)// 条件查询model.DB.Where("id<6").Find(&users)c.JSON(http.StatusOK, users)
}func (student StudentController) Insert(c *gin.Context) {var user = model.Student{Sn: "10019", Name: "aaa", Email: "aaa@aynu.com", ClassId: 1, MonitorId: 1}tx := model.DB.Create(&user)fmt.Println(tx.Error)if tx.Error != nil {c.JSON(http.StatusOK, gin.H{"insert":  "error","message": tx.Error,})} else {c.JSON(http.StatusOK, gin.H{"insert": "success",})}
}func (student StudentController) Update(c *gin.Context) {id, _ := strconv.Atoi(c.Param("id"))// 修改方式1//var user = model.Student{Id: id}//model.DB.Find(&user)//user.Name = "5A+"//tx := model.DB.Save(&user)//if tx.Error != nil {//	c.JSON(http.StatusOK, gin.H{//		"update":  "error",//		"message": tx.Error,//	})//} else {//	c.JSON(http.StatusOK, gin.H{//		"update": "success",//	})//}// 修改方式2tx := model.DB.Model(&model.Student{}).Where("id=?", id).Update("name", "gorm")if tx.Error != nil {c.JSON(http.StatusOK, gin.H{"update":  "error","message": tx.Error,})} else {c.JSON(http.StatusOK, gin.H{"update": "success",})}
}func (student StudentController) Delete(c *gin.Context) {id, _ := strconv.Atoi(c.Param("id"))var user = model.Student{Id: id}tx := model.DB.Delete(&user)if tx.Error != nil {c.JSON(http.StatusOK, gin.H{"delete":  "error","message": tx.Error,})} else {c.JSON(http.StatusOK, gin.H{"delete": "success",})}
}

2.4、router

package routerimport ("ginstudy/controller""github.com/gin-gonic/gin"
)func StudentRouter(r *gin.Engine) {group := r.Group("/student")studentController := controller.StudentController{}{group.GET("/", studentController.Select)group.POST("/insert", studentController.Insert)group.PUT("/update/:id", studentController.Update)group.DELETE("/delete/:id", studentController.Delete)}
}

3、查询

3.1、字段别名

type Student struct {Id        int    `json:"id"`Sn        string `json:"sn"`Name      string `json:"name"`Email     string `json:"email"`ClassId   int    `json:"class-id"`MonitorId int    `json:"monitor-id"`
}

3.2、条件查询

func (student StudentController) Select(c *gin.Context) {var users []model.Student// 全量查询//model.DB.Find(&users)// 条件查询1//model.DB.Where("id<6").Find(&users)//model.DB.Where("id<?", 6).Find(&users)// 条件查询2//model.DB.Where("id>1 and id<5").Find(&users)//model.DB.Where("id>? and id<=?", 1, 5).Find(&users)// 条件查询3//model.DB.Where("id in (?)", []int{1, 5}).Find(&users)// 条件查询4//model.DB.Where("name like ?", "%zhou%").Find(&users)// 条件查询5//model.DB.Where("id between ? and ?", 1, 3).Find(&users)// 条件查询6//model.DB.Where("id = ? or id = ?", 1, 3).Find(&users)model.DB.Where("id = ? or id = ?", 1, 3).Or("id = ?", 5).Find(&users)c.JSON(http.StatusOK, users)
}

3.3、指定字段

其他字段置空，可自定义结构体（注意指定表名）

func (student StudentController) Select(c *gin.Context) {var users []model.Student// 全量查询model.DB.Select("id,name").Find(&users)c.JSON(http.StatusOK, users)
}

3.4、排序

func (student StudentController) Select(c *gin.Context) {var users []model.Student// 全量查询model.DB.Order("id desc").Find(&users)c.JSON(http.StatusOK, users)
}

3.5、分页查询

func (student StudentController) Select(c *gin.Context) {var users []model.Student// 全量查询model.DB.Order("id desc").Offset(3).Limit(2).Find(&users)c.JSON(http.StatusOK, users)
}

3.6、计数

func (student StudentController) Select(c *gin.Context) {var users []model.Student// 全量查询var count int64model.DB.Find(&users).Count(&count)fmt.Println(count)c.JSON(http.StatusOK, users)
}

4、原生SQL

4.1、原生插入

func (student StudentController) Exec(c *gin.Context) {model.DB.Exec("INSERT INTO student (sn, name, email, class_id, monitor_id) VALUES ('10020', 'ceshi', 'ceshi@qq.com', 1, 2)")c.JSON(http.StatusOK, gin.H{"status": "success",})
}

4.2、原生修改

func (student StudentController) Exec(c *gin.Context) {model.DB.Exec("update student set name=?  where sn=?", "gorm", 10019)c.JSON(http.StatusOK, gin.H{"status": "success",})
}

4.3、原生删除

func (student StudentController) Exec(c *gin.Context) {model.DB.Exec("delete from student where sn=?", 10019)c.JSON(http.StatusOK, gin.H{"status": "success",})
}

4.3、原生查询

func (student StudentController) Exec(c *gin.Context) {var users []model.Studentmodel.DB.Raw("select * from student").Scan(&users)c.JSON(http.StatusOK, gin.H{"status": "success","data":   users,})
}