GORM教程

1.安装

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

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

2. 入门示例

package main

import (
  "gorm.io/gorm"
  "gorm.io/driver/sqlite"
)

type Product struct {
  gorm.Model
  Code  string
  Price uint
}

func main() {
  db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }

  // 迁移 schema
  db.AutoMigrate(&Product{})

  // Create
  db.Create(&Product{Code: "D42", Price: 100})

  // Read
  var product Product
  db.First(&product, 1) // 根据整型主键查找
  db.First(&product, "code = ?", "D42") // 查找 code 字段值为 D42 的记录

  // Update - 将 product 的 price 更新为 200
  db.Model(&product).Update("Price", 200)
  // Update - 更新多个字段
  db.Model(&product).Updates(Product{Price: 200, Code: "F42"}) // 仅更新非零值字段
  db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})

  // Delete - 删除 product
  db.Delete(&product, 1)
}

3. 结构体与表的映射

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"log"
	"os"
	"time"
)

// BaseModel 公用结构体
type BaseModel struct {
	Id         int        `gorm:"primarykey"`
	CreateTime *time.Time `gorm:"autoCreateTime"`
	UpdateTime *time.Time `gorm:"autoUpdateTime"`
}

// Teacher 老师
type Teacher struct {
	BaseModel
	Name   string `gorm:"type:varchar(32);unique, not null"`
	Tno    int
	Pwd    string `gorm:"type:varchar(100);not null"`
	Tel    string `gorm:"type:char(11)"`
	Birth  *time.Time
	Remark string `gorm:"type:varchar(255)"`
}

// Class 班级
type Class struct {
	BaseModel
	Name string `gorm:"type:varchar(32);unique, not null"`
	Num  int
	// 导员/班主任
	TeacherID int
	Teacher   Teacher `gorm:"constraint:OnDelete:CASCADE;"` // 删除时吉连
}

// Course 课程
type Course struct {
	BaseModel
	Credit int // 学分
	Period int // 周期

	// 多对一
	TeacherID int
	Teacher   Teacher `gorm:"constraint:OnDelete:CASCADE;"`
}

// Student 学生
type Student struct {
	BaseModel
	Sno    int
	Pwd    string `gorm:"type:varchar(100);not null"`
	Tel    string `gorm:"type:char(11);not null"`
	Gender byte   `gorm:"default:1"`
	Birth  *time.Time
	Remark string `gorm:"type:varchar(255)"`

	// 多对一
	ClassID int
	Class   Class `gorm:"foreignKey:ClassID"`
	// 多对多
	Courses []Course `gorm:"many2many:student2course;constraint:OnDelete:CASCADE;"` // （ many2many:类型 --  student2course：表名 ）-> 语法糖
}

func main() {
	// 创建日志对象
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags),
		logger.Config{
			// SlowThreshold: time.Second, // 慢 SQL 阈值
			LogLevel: logger.Info, // Log level
		})

	// 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
	dsn := "root:root@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: newLogger, // 日志配置
	})
	fmt.Println(db, err)

	// 迁移表
	db.AutoMigrate(&Teacher{})
	db.AutoMigrate(&Class{})
	db.AutoMigrate(&Course{})
	db.AutoMigrate(&Student{})
}

4.添加表记录

func addRecord(db *gorm.DB) {
	// 结构体的对象和表记录

	// 添加老师对象
	//t1 := Teacher{BaseModel: BaseModel{Id: 1}, Name: "Percy", Tno: 1001, Pwd: "root"}
	//db.Create(&t1)
	//t2 := Teacher{BaseModel: BaseModel{Id: 2}, Name: "Admin", Tno: 1002, Pwd: "admin"}
	//db.Create(&t2)
	//t3 := Teacher{BaseModel: BaseModel{Id: 3}, Name: "Root", Tno: 1001, Pwd: "root"}
	//db.Create(&t3)

	// 批量创建班级
	c1 := Class{Name: "计科1班", Num: 50, TeacherID: 1}
	c2 := Class{Name: "计科2班", Num: 47, TeacherID: 2}
	c3 := Class{Name: "计科3班", Num: 42, TeacherID: 3}
	c4 := Class{Name: "计科4班", Num: 45, TeacherID: 2}
	c5 := Class{Name: "计科5班", Num: 88, TeacherID: 1}

	classes := []Class{c1, c2, c3, c4, c5}
	db.Create(&classes)

	// 批量创建课程
	course01 := Course{Name: "Java", Credit: 3, Period: 16, TeacherID: 1}
	course02 := Course{Name: "Python", Credit: 2, Period: 8, TeacherID: 2}
	course03 := Course{Name: "GO", Credit: 3, Period: 12, TeacherID: 3}
	course04 := Course{Name: "C", Credit: 2, Period: 14, TeacherID: 1}
	course05 := Course{Name: "Vue", Credit: 1, Period: 18, TeacherID: 2}
	courses := []Course{course01, course02, course03, course04, course05}
	db.Create(&courses)

}

5.单表查询

func selectRecord(db *gorm.DB) {
	// (1) 查询全部记录
	var teachers []Teacher
	db.Find(&teachers)
	fmt.Println(teachers)
	
	for i, v := range teachers {
		fmt.Println(i, v.Tno, v.Name)
	}

	// 查询单条记录
	course := Course{}
	db.Take(&course) // Take 查询一条记录
	db.First(&course)                    // First 根据主键 id 排序后的第一条
	db.Last(&course)                     // Last 根据主键 id 排序最后一条
	db.Where("credit < ?", 3).Order("credit").Take(&course) // Where 表示条件，其中写 sql 部分
	fmt.Println(course)

	// Where 语句
	var courses []Course
	db.Where("credit > ?", 1).Find(&courses)
	db.Where("credit between ? and ?", 1, 3).Find(&courses)
	fmt.Println(courses)
	//// 计数
	var total int64
	db.Model(&Course{}).Where("credit between ? and ?", 1, 3).Count(&total)
	fmt.Println(total)
	// 结构体 Where 语句
	var courses []Course
	db.Where(Course{Credit: 3, Period: 12}).Find(&courses)
	fmt.Println(courses)
	// map的 Where 语句
	var courses []Course
	db.Where(map[string]any{"Credit": 1, "Period": 18}).Find(&courses)
	fmt.Println(courses)

	// 其他查询语句
	var course Course
	// Select 只要这两个字段
	db.Select("name,credit").Where("id = ?", 1).Take(&course)
	fmt.Println(course)
	// Omit 除了这两个字段其他字段都要
	db.Omit("name,credit").Where("id = ?", 1).Take(&course)
	fmt.Println(course)
	// Limit 分组
	var courses []Course
	db.Order("create_time desc").Limit(2).Offset(2).Find(&courses)
	fmt.Println(courses)
	// 查询每一个teacher_id的课程个数
	type Ret struct {
		TeacherId int
		Count     int
	}
	var ret []Ret
	db.Model(&Class{}).
		Select("teacher_id,Count(*) as count").
		Group("teacher_id").
		Having("count > ?", 1).
		Scan(&ret)
	fmt.Println(ret)
}

6.删除表记录

func deleteRecord(db *gorm.DB) {
	var course Course
	//// 删除单条
	db.Where("name = ?", "Java").First(&course)
	db.Delete(&course)
	//// 按条件删除
	db.Where("Credit < ?", 2).Delete(&Course{})
	// 删除所有记录
	var course = Course{}
	db.Where("1 = 1").Delete(&course)
}

7.更新表记录

func updateRecord(db *gorm.DB) {
	// 方式1 （不推荐：Save会将所有字段重新赋值）
	var course Course
	db.Where("name = ?", "Vue").First(&course)
	course.Credit = 8
	db.Save(&course) // 同步数据库

	// 方式2
	// 修改全部
	db.Model(&Course{}).Where("1 = 1").Update("Credit", 3) 
	// 根据条件修改
	db.Model(&Course{}).Where("Period < ?", 15).Update("Credit", 2)
	// 结构体修改多个值
	db.Model(&Course{}).Where("name like ?", "C%").Updates(&Course{Name: "C#", Credit: 10})
	// map的修改多个值
	db.Model(&Course{}).Where("name like ?", "C%").Updates(map[string]any{"credit": 20, "teacher_id": 3})
    // 表达式更新
	db.Model(&Course{}).Where("Period < ?", 15).Update("Period", gorm.Expr("Period + ?", 2))

}

7.多对多关联表的操作接口

	// 添加学生
	// 1. 创建对象即绑定多对多关系
	var courses []Course
	db.Where("name in ?", []string{"GO", "Vue"}).Find(&courses)
	s1 := Student{Name: "张三", Sno: 2001, Pwd: "123", ClassID: 5, Courses: courses}
	db.Create(&s1)
	// 2. 创建对象即绑定多对多关系 (方式二)
	var courses []Course
	db.Where("name in ?", []string{"C#", "Python"}).Find(&courses)
	s1 := Student{Name: "王五", Sno: 2003, Pwd: "123", ClassID: 5}
	db.Create(&s1)
	db.Model(&s1).Association("Courses").Append(&courses)
	// 3. 查询对象后再绑定多对多的关系
	var courses []Course
	db.Where("name in ?", []string{"C#", "Python"}).Find(&courses)
	var student = Student{}
	db.Where("name = ?", "张三").First(&student)
	db.Model(&student).Association("Courses").Append(&courses)

	/*
		补充
	*/
	// 解除多对多的绑定关系
	var courses []Course
	db.Where("name in ?", []string{"C#", "Python"}).Find(&courses)
	var student = Student{}
	db.Where("name = ?", "李四").First(&student)
	db.Model(&student).Association("Courses").Delete(&courses) // 解除某个学生绑定的某些课程
	db.Model(&student).Association("Courses").Clear() // 解除某个学生绑定的所有课程

	// 查询张三选修了那些课程
	var courses []Course
	var student = Student{}
	db.Where("name = ?", "张三").First(&student)
	db.Model(&student).Association("Courses").Find(&courses)
	fmt.Println(courses)

8.Preload 预加载

// preloadFunc 预加载
func preloadFunc(db *gorm.DB) {
	// 查询计科5班的班主任的名字 (多对一)
	var class Class
	db.Preload("Teacher").Where("name = ?", "计科5班").Find(&class)
	fmt.Println("class:", class)
	fmt.Println(class.Teacher.Tno)
	fmt.Println(class.Teacher.Name)
	
	// 查询张三所在班级的人数 (多对一)
	var student Student
	db.Preload("Class").Where("name = ?", "张三").Find(&student)
	fmt.Println("student:", student)
	fmt.Println("张三所在班级的人数:", student.Class.Num)

	// 查询张三的班级和选修课程名以及对应学分 (多对多)
	var student Student
	db.Preload("Class").Preload("Courses").Where("name = ?", "张三").Find(&student)
	fmt.Println("Student:", student)
	for i, course := range student.Courses {
		fmt.Println(i, course.Name, course.Credit)
	}

	// 预加载所有直接关联（只关联直接的一层）
	var student Student
	db.Preload(clause.Associations).Where("name = ?", "张三").Find(&student)
	fmt.Println("Student:", student)
	for i, course := range student.Courses {
		fmt.Println(i, course.Name, course.Credit)
	}

	//嵌套预加载
	//查询张三的班主任的名字
	var student Student
	db.Preload("Class").Preload("Class.Teacher").Preload("Courses").Where("name = ?", "张三").First(&student)
	fmt.Println("张三的班主任的名字:", student.Class.Teacher.Name)

	// 反向查询
	//计科5班有哪些学生
	var class Class
	db.Preload("Students").Where("name = ?", "计科5班").Find(&class)
	fmt.Println(class.Students)
}