• GORM学习笔记

    GORM学习笔记

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

    1、数据准备

    # 班级表
create table class(
    id int primary key auto_increment,
    name varchar(20),
    description varchar(100)
);

# 学生表
create table student(
    id int primary key auto_increment,
    sn varchar(20),
    name varchar(20),
    email varchar(20),
    class_id int,
    monitor_id int,
    constraint student_class_id foreign key (class_id) references class(id),
    constraint student_monitor_id foreign key (monitor_id) references student(id)
);

# 课程表
create table course(
    id int primary key auto_increment,
    name varchar(20)
);

# 选课表
create table selection(
    student_id int,
    course_id int,
    constraint score_student_id foreign key (student_id) references student(id),
    constraint score_course_id foreign key (course_id) references course(id)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    2、快速开始

    2.1、安装依赖

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

    • 1
    • 2

    2.2、db

    package model

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

var DB *gorm.DB
var err error

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)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    2.3、bean

    package bean

type Student struct {
    Id    int
    Sn    string
    Name  string
    Email string
    ClassId
    MonitorId int
}

func (s Student) TableName() string {
    return "student"
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    2.4、controller

    package controller

import (
	"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",
	//	})
	//}
	// 修改方式2
	tx := 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",
		})
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83

    2.5、router

    package router

import (
    "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)
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    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"`
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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
	// 条件查询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)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    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)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    3.6、计数

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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",
    })
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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",
	})
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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",
	})
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    4.3、原生查询

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    5、多表查询

    5.1、一对一

    1、bean
    package bean

type Student struct {
    Id    int
    Sn    string
    Name  string
    Email string
    // column指定当前表中的字段名，references指定关联表中的字段名
    ClassId   int `gorm:"column:class_id;references:id"`
    MonitorId int
    // 指定外键（注意是外键）
    Class Class `gorm:"foreignKey:ClassId"`
}

func (s Student) TableName() string {
    return "student"
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    package bean

type Class struct {
    Id          int
    Name        string
    Description string
}

func (c Class) TableName() string {
    return "class"
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    2、controller
    package controller

import (
    "fmt"
    "github.com/gin-gonic/gin"
    "gormstudy/bean"
    "gormstudy/model"
    "net/http"
    "strconv"
)

type StudentController struct{}

func (student StudentController) Select(c *gin.Context) {
    var users []bean.Student
    model.DB.Preload("Class").Find(&users)
    c.JSON(http.StatusOK, users)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    5.2、一对多

    1、bean
    package bean

type Class struct {
  	// column指定当前表中的字段名，references指定关联表中的字段名
    Id          int `gorm:"column:id;references:class_id"`
    Name        string
    Description string
  	// 指定外键（注意是外键）
    Students    []Student `gorm:"foreignKey:ClassId"`
}

func (c Class) TableName() string {
    return "class"
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    type Student struct {
    Id        int
    Sn        string
    Name      string
    Email     string
    ClassId   int
    MonitorId int
}

func (s Student) TableName() string {
    return "student"
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    2、controller
    package controller

import (
    "github.com/gin-gonic/gin"
    "gormstudy/bean"
    "gormstudy/model"
    "net/http"
)

type ClassController struct{}

func (class ClassController) Select(c *gin.Context) {
    var classes []bean.Class
    model.DB.Preload("Students").Find(&classes)
    c.JSON(http.StatusOK, classes)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    5.5、多对多

    1、bean
    package bean

type Course struct {
	Id       int       `json:"id"`
	Name     string    `json:"name"`
	Students []Student `gorm:"many2many:selection;"`
}

func (s Course) TableName() string {
	return "course"
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    2、controller
    package controller

import (
	"ginstudy/bean"
	"ginstudy/model"
	"github.com/gin-gonic/gin"
	"net/http"
)

type CourseController struct{}

func (course CourseController) Select(c *gin.Context) {
	var courses []bean.Course
    // 查询所有课程的学生选修情况
	// model.DB.Preload("Students").Find(&courses)
    // 查询 JAVA 课程的学生选修情况
	model.DB.Preload("Students").Where("name=?", "JAVA").Find(&courses)
	c.JSON(http.StatusOK, courses)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    6、预加载SQL

    6.1、添加条件

    func (course CourseController) Select(c *gin.Context) {
	var courses []bean.Course
	// 查询 JAVA 课程的学生选修情况（排除学生姓名为 xumeng03 的）
	model.DB.Preload("Students", "name!=?", "xumeng03").Where("name=?", "JAVA").Find(&courses)
	c.JSON(http.StatusOK, courses)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    6.2、自定义

    func (course CourseController) Select(c *gin.Context) {
    var courses []bean.Course
    // 查询 JAVA 课程的学生选修情况（排除学生姓名为 xumeng03 的）
    model.DB.Preload("Students", func(db *gorm.DB) *gorm.DB {
       return db.Order("id desc")
    }).Where("name=?", "JAVA").Find(&courses)
    c.JSON(http.StatusOK, courses)
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    7、事务

    func (student StudentController) Transfer(c *gin.Context) {
    id1, _ := strconv.Atoi(c.Param("id1"))
    id2, _ := strconv.Atoi(c.Param("id2"))
    coin, _ := strconv.Atoi(c.Param("coin"))
    tx := model.DB.Begin()
    defer func() {
       tx.Rollback()
    }()
    var user1 = bean.Student{Id: id1}
    tx.Find(&user1)
    user1.Coin += coin
    tx.Save(&user1)
    var user2 = bean.Student{Id: id2}
    tx.Find(&user2)
    user2.Coin -= coin
    tx.Save(&user2)
    tx.Commit()
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    8、配置文件

    文档地址：https://ini.unknwon.io/

    8.1、安装依赖

    go get -u gopkg.in/ini.v1

    • 1

    8.2、数据准备

    author    = xumeng03
last-time = 2023-11-15 23:41:13

[APP]
name = gorm-study

[mysql]
ip       = 127.0.0.1
port     = 3306
username = root
password = Chace2233.
db       = study

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    8.3、读取数据

    package model

import (
	"fmt"
	"gopkg.in/ini.v1"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"os"
)

var DB *gorm.DB
var err error

func init() {
	cfg, err := ini.Load("./config/config.ini")
	if err != nil {
		fmt.Printf("Fail to read file: %v", err)
		os.Exit(1)
	}
	// author="xumeng03"
	fmt.Println(cfg.Section("").Key("author").String())
	// [APP]	name="gorm-study"
	fmt.Println(cfg.Section("APP").Key("name").String())
	// [mysql] ip=127.0.0.1	port=3306
	username := cfg.Section("mysql").Key("username").String()
	password := cfg.Section("mysql").Key("password").String()
	ip := cfg.Section("mysql").Key("ip").String()
	port := cfg.Section("mysql").Key("port").String()
	db := cfg.Section("mysql").Key("db").String()

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8&parseTime=True&loc=Local", username, password, ip, port, db)
	DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		// 配置跳过事务
		SkipDefaultTransaction: false,
	})
	if err != nil {
		fmt.Println(err)
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    8.3、写入数据

    package model

import (
    "fmt"
    "gopkg.in/ini.v1"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "os"
    "time"
)

var DB *gorm.DB
var err error

func init() {
    cfg, err := ini.Load("./config/config.ini")
    if err != nil {
       fmt.Printf("Fail to read file: %v", err)
       os.Exit(1)
    }
    // 读取数据
    fmt.Println(cfg.Section("").Key("author").String())
    fmt.Println(cfg.Section("APP").Key("name").String())
    username := cfg.Section("mysql").Key("username").String()
    password := cfg.Section("mysql").Key("password").String()
    ip := cfg.Section("mysql").Key("ip").String()
    port := cfg.Section("mysql").Key("port").String()
    db := cfg.Section("mysql").Key("db").String()
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8&parseTime=True&loc=Local", username, password, ip, port, db)
    DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
       // 配置跳过事务
       SkipDefaultTransaction: false,
    })
    if err != nil {
       fmt.Println(err)
    }
    // 写入数据
    cfg.Section("").Key("last-time").SetValue(time.Now().Format("2006-01-02 15:04:05"))
    cfg.SaveTo("./config/config.ini")
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
  • 相关阅读:
    工欲善其事必先利其器（Windows）
    Github最新霸榜，Alibaba架构师手写的分布式系统核心原理手册
    java面试题库app
    php设计模式重构篇
    下一代工具链「GitHub 热点速览 v.22.43」
    Matlab图像处理-HSV
    Swagger的界面太丑，试试knife4j的接口文档吧
    scipy Matlab-style IIR 滤波器设计下(Elliptic \Bessel \IIR-notch\IIR-peak\IIR-comb)
    bitset的学习笔记
    SpringBoot+Vue实现前后端分离的企业人事管理系统
  • 原文地址：https://blog.csdn.net/gyfghh/article/details/134411261