GO GORM库的操作

ORM即Object Relational Mapping，对象关系映射。Relational指各种sql类的关系型数据库。Object指面向对象编程(object-oriented programming)中的对象。ORM在数据库记录和程序对象之间做一层映射转换，使程序中不用再去编写原生SQL，而是面向对象的思想去编写类、对象、调用相应的方法来完成数据库操作。

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

GORM是一个全能的、友好的、基于golang的ORM库。 GORM 倾向于约定，而不是配置。默认情况下，GORM 使用ID作为主键，使用结构体名的【蛇形复数】（小写+下划线分隔）作为表名，字段名的【蛇形】作为列名，并使用CreatedAt、UpdatedAt字段追踪创建、更新时间。

1.连接数据库：

dsn := "tester:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})

2.表：

type Student struct {
	Id         int    `gorm:"column:id;primaryKey"`
	Name       string `gorm:"column:name"`
	Province   string
	City       string    `gorm:"column:city"`
	Address    string    `gorm:"column:addr"`
	Score      float32   `gorm:"column:score"`
	Enrollment time.Time `gorm:"column:enrollment;type:date"`
}

//由于我们之前创建的表名为student，蛇形复数使得表名变为students，使用TableName()来修改默认的表名
func (Student) TableName() string {
	return "student"
}

3.插入：

func create(db *gorm.DB) {
	//插入一条记录
	student := Student{Name: "光绪", Province: "北京", City: "北京", Score: 38, Enrollment: time.Now()}
	db.Create(&student)
	//一次性插入多条
	students := []Student{{Name: "无极", Province: "北京", City: "北京", Score: 38, Enrollment: time.Now()}, {Name: "小王", Province: "上海", City: "上海", Score: 12, Enrollment: time.Now()}, {Name: "小亮", Province: "北京", City: "北京", Score: 20, Enrollment: time.Now()}}
	db.Create(students)
	//量太大时分批插入
	students = []Student{{Name: "大壮", Province: "北京", City: "北京", Score: 38, Enrollment: time.Now()}, {Name: "刘二", Province: "上海", City: "上海", Score: 12, Enrollment: time.Now()}, {Name: "文明", Province: "北京", City: "北京", Score: 20, Enrollment: time.Now()}}
	db = db.CreateInBatches(students, 2) //一次插入2条
	fmt.Printf("insert %d rows\n", db.RowsAffected)
	fmt.Println("=============insert end=============")
}

4.更新：

Model specify the model you would like to run db operations

Model函数通过结构体识别你想exec的表

func update(db *gorm.DB) {
	//根据where更新一列
	db.Model(&Student{}).Where("city=?", "北京").Update("score", 10)
	//更新多列
	db.Model(&Student{}).Where("city=?", "北京").Updates(map[string]interface{}{"score": 3, "addr": "海淀区"})
	//where里加入object的ID
	student := Student{Id: 2, City: "太原"}
	//相当于where id=2 and city='郑州','太原'
	db = db.Model(&student).Where("city=?", "郑州").Updates(map[string]interface{}{"score": 10, "addr": "中原区"})
	fmt.Printf("update %d rows\n", db.RowsAffected)
}

5.查询：

func print(students []Student){
	for _, ele := range students {
		fmt.Printf("id=%d, name=%s\n", ele.Id, ele.Name)
	}
	fmt.Println()
}
func query(db *gorm.DB) {
	//返回一条记录
	var student Student
	db.Where("city=?", "郑州").First(&student) //有First就有Last
	fmt.Println(student.Name)
	fmt.Println()
	//返回多条记录
	var students []Student
	db.Where("city=?", "郑州").Find(&students)
	print(students)
	students = []Student{} //清空student，防止前后影响
	db.Where("city in ?", []string{"郑州", "北京"}).Find(&students)
	print(students)
	fmt.Println("============where end============")
	//根据主键查询
	student = Student{} //清空student，防止前后影响
	students = []Student{}
	db.First(&student, 1)
	// db.Where("id=?",1).First(&student)
	fmt.Println(student.Name)
	fmt.Println()
	db.Find(&students, []int{1, 2, 3})
	// db.Where("id in ?", []int{1, 2, 3}).Find(&students)
	print(students)
	fmt.Println("============primary key end============")
	//根据map查询
	student = Student{}
	students = []Student{}
	db.Where(map[string]interface{}{"city": "郑州", "score": 0}).Find(&students)
	print(students)
	fmt.Println("============map end============")
	//limit
	student = Student{}
	students = []Student{}
	db.Where("city=?", "郑州").Or("city=?", "北京").Order("score").Limit(1).Offset(0).Find(&students)
	print(students)
	fmt.Println("============limit end============")
	//选择特定的字段
	student = Student{}
	db.Select("name").Take(&student)                                     //Take从结果中取一个，不保证是第一个或最后一个
	fmt.Printf("name=%s, province=%s\n", student.Name, student.Province) //只select了name，所以province是空的
	fmt.Println("============select specified column end============")
}

6.删除：

func delete(db *gorm.DB) {
	//用where删除
	db = db.Where("city in ?", []string{"常州", "成都"}).Delete(&Student{})
	fmt.Printf("delete %d rows\n", db.RowsAffected)
	//用主键删除
	db = db.Delete(&Student{}, 27)
	fmt.Printf("delete %d rows\n", db.RowsAffected)
	db = db.Delete(&Student{}, []int{28, 29, 30})
	fmt.Printf("delete %d rows\n", db.RowsAffected)
}

7.事务

type Tx interface {
    Commit() error  //提交事务
    Rollback() error  //回滚事务
}

//相当于把一群sql命令封装成一个原子操作，即--事务
func transaction(db *gorm.DB) {
	tx := db.Begin()
	defer tx.Rollback() //调用commit后也可以调用rollback，相当于rollback没起任何作用
	for i := 0; i < 10; i++ {
		student := Student{Name: "学生" + strconv.Itoa(i), Province: "北京", City: "北京", Score: 38, Enrollment: time.Now()}
		if err := tx.Create(&student).Error; err != nil { //注意是tx.Create，不是db.Create
			return //函数返回
		}
	}
	tx.Commit()
}