ORM框架Gorm-SQL构建器

连接数据库并初始数据

INSERT INTO `users` (`created_at`,`updated_at`,`name`,`age`) VALUES ('2022-03-13 13:15:54.089','2022-03-13 13:15:54.089','1',1),('2022-03-13
13:15:54.089','2022-03-13 13:15:54.089','2',2),('2022-03-13 13:15:54.089','2022-03-13 13:15:54.089','3',3),('2022-03-13 13:15:54.089','2022-03-13 13:15:54.089','4',4),('2022-03-13 13:15:54.089','2022-03-13 13:15:54.089','5',5),('2022-03-13 13:15:54.089','2022-03-13 13:15:54.089','6',6)

package main

import (
	"fmt"
	"log"
	"time"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

var db *gorm.DB

func init() {
	dsn := "root:root@tcp(127.0.0.1:3306)/go_orm?charset=utf8mb4&parseTime=True&loc=Local"
	d, err := gorm.Open(mysql.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Info)})
	if err != nil {
		log.Fatal(err)
	}
	db = d
}

type User struct {
	gorm.Model
	Name     string
	Age      int
	Birthday time.Time
}

func insertMany() {
	var users = []User{
		{Name: "1", Age: 1}, {Name: "2", Age: 2},
		{Name: "3", Age: 3}, {Name: "4", Age: 4},
		{Name: "5", Age: 5}, {Name: "6", Age: 6},
	}
	d := db.Select("Name", "Age").Create(&users)
	for _, user := range users {
		fmt.Printf("user.ID: %v\n", user.ID)
	}
	fmt.Printf("d.RowsAffected: %v\n", d.RowsAffected)
	fmt.Printf("d.Error: %v\n", d.Error)
}

func main() {
	insertMany()
}

原生SQL

func test1() {
	type Result struct {
		ID   int
		Name string
		Age  int
	}
	var result Result
	db.Raw("select id,name,age from users where id = ?", 1).Scan(&result)
	// select id,name,age from users where id = 1
	fmt.Printf("result: %+v\n", result)
	// result: {ID:1 Name:1 Age:1}
}

func test2() {
	var ageAvg float32
	db.Raw("select avg(age) from users").Scan(&ageAvg)
	// select avg(age) from users
	fmt.Printf("ageAvg: %v\n", ageAvg)
	// ageAvg: 3.5
}

func test3() {
	var users []User
	db.Raw("select id,name,age from users").Scan(&users)
	for _, user := range users {
		fmt.Printf("user: %v\n", user)
	}
}

func test3() {
	type Result struct {
		ID   int
		Name string
		Age  int
	}
	var users []Result
	db.Raw("select id,name,age from users").Scan(&users)
	for _, user := range users {
		fmt.Printf("user: %+v\n", user)
	}
}

func test4() {
	db.Exec("update users set age = ? where id = ?", 100, 1)
	// update users set age = 100 where id = 1
}

命名参数

通过sql.Named()

func test5() {
	var result User
	db.Select("id", "name", "age").Where("id = @varid", sql.Named("varid", 2)).Find(&result)
	// SELECT `id`,`name`,`age` FROM `users` WHERE id = 2 AND `users`.`deleted_at` IS NULL
	fmt.Printf("result.ID: %v\n", result.ID)
	// result.ID: 2
	fmt.Printf("result.Name: %v\n", result.Name)
	// result.Name: 2
	fmt.Printf("result.Age: %v\n", result.Age)
	// result.Age: 2
}

通过map

func test6() {
	var result User
	db.Select("id", "name", "age").Where("id = @varid", map[string]interface{}{"varid": 2}).First(&result)
	// SELECT `id`,`name`,`age` FROM `users` WHERE id = 2 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
	fmt.Printf("result.ID: %v\n", result.ID)
	// result.ID: 2
	fmt.Printf("result.Name: %v\n", result.Name)
	// result.Name: 2
	fmt.Printf("result.Age: %v\n", result.Age)
	// result.Age: 2
}

原生sql及命名参数

通过sql.Named()

func test7() {
	var result User
	db.Raw("SELECT * FROM users WHERE id = @varid", sql.Named("varid", 2)).Find(&result)
	//  SELECT * FROM users WHERE id = 2
	fmt.Printf("result.ID: %v\n", result.ID)
	// result.ID: 2
	fmt.Printf("result.Name: %v\n", result.Name)
	// result.Name: 2
	fmt.Printf("result.Age: %v\n", result.Age)
	// result.Age: 2
}

通过map

func test8() {
	var result User
	db.Raw("SELECT * FROM users WHERE id = @varid", map[string]interface{}{"varid": 2}).Find(&result)
	//  SELECT * FROM users WHERE id = 2
	fmt.Printf("result.ID: %v\n", result.ID)
	// result.ID: 2
	fmt.Printf("result.Name: %v\n", result.Name)
	// result.Name: 2
	fmt.Printf("result.Age: %v\n", result.Age)
	// result.Age: 2
}

dryrun模式

不执行的情况下生成sql

func test9() {
	var user User
	stmt := db.Session(&gorm.Session{DryRun: true}).First(&user, 1).Statement
	fmt.Printf("stmt.SQL.String(): %v\n", stmt.SQL.String())
	// stmt.SQL.String(): SELECT * FROM `users` WHERE `users`.`id` = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
	fmt.Printf("stmt.Vars: %v\n", stmt.Vars)
	// stmt.Vars: [1]
}

row&rows

row

func test10() {
	var name string
	var age int
	// 使用 GORM API 构建 SQL
	row := db.Table("users").Where("name = ?", "1").Select("name", "age").Row()
	row.Scan(&name, &age)
	fmt.Printf("name: %v\n", name)
	fmt.Printf("age: %v\n", age)
	// 使用原生 SQL
	row = db.Raw("select name, age from users where name = ?", "1").Row()
	row.Scan(&name, &age)
	fmt.Printf("name: %v\n", name)
	fmt.Printf("age: %v\n", age)
}

rows

func test11() {
	var name string
	var age int
	// 使用 GORM API 构建 SQL
	rows, _ := db.Model(&User{}).Where("id < ?", 3).Select("name, age").Rows()
	defer rows.Close()
	for rows.Next() {
		rows.Scan(&name, &age)
		fmt.Printf("1--name: %v age: %v\n", name, age)
	}
	// 原生 SQL
	rows, _ = db.Raw("select name, age from users where id < ?", 3).Rows()
	defer rows.Close()
	for rows.Next() {
		rows.Scan(&name, &age)
		fmt.Printf("2--name: %v age: %v\n", name, age)
	}
}

将sql.rows扫描至model

func test12() {
	rows, _ := db.Model(&User{}).Where("id < ?", 3).Rows()
	defer rows.Close()

	var user User
	for rows.Next() {
		// ScanRows 将一行扫描至 user
		db.ScanRows(rows, &user)
		fmt.Printf("user: %v\n", user)
	}
}