gorm
原创大约 7 分钟
简单查询
package main
import (
"database/sql"
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/logger"
"log"
"os"
"time"
)
// 创建用户表
type SysUser struct {
ID uint `gorm:"primaryKey; <-; comment: 主键ID"` // 主键，允许读和写
Username string `gorm:"not null; type:varchar(32); <-"` // not null，允许读和写
Nickname string `gorm:"type:varchar(32); <-"`
Email string `gorm:"type:varchar(32); <-"`
Password *string `gorm:"type:varchar(32); <-"`
Desc sql.NullString `gorm:"type:varchar(64); <-"`
Age uint `gorm:"type:int(4); <-"`
Deleted gorm.DeletedAt // 用于软删除字段的
Createtime string `gorm:"type:timestamp"`
}
type Usercart struct {
Userid uint `gorm:"type:int(11); <-"`
Cartid uint `gorm:"type:int(11); <-"`
Createtime string `gorm:"type:timestamp"`
}
// 第 1 种解决零值更新问题的方式
type NullString struct {
String string
Valid bool
}
var passwd = "123456"
var newpasswd = "456789"
//// 钩子方法
//func (s *SysUser) BeforeSave(tx *gorm.DB) (err error) {
// if s.Password == &passwd {
// s.Password = &newpasswd
// }
// return
//}
/*
简单查询
*/
func main() {
dsn := "root:123456@tcp(172.16.185.168:3306)/javabook?charset=utf8mb4&parseTime=True&loc=Local"
// 设置全局的logger，打印每一行sql
newLogger := logger.New(
log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
logger.Config{
SlowThreshold: time.Second, // Slow SQL threshold
LogLevel: logger.Warn, // Log level
IgnoreRecordNotFoundError: true, // Ignore ErrRecordNotFound error for logger
ParameterizedQueries: true, // Don't include params in the SQL log
Colorful: true, // Disable color
},
)
// Globally mode
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
Logger: newLogger,
})
if err != nil {
panic("failed to connect database")
}
// 创建表
err = db.AutoMigrate(&SysUser{})
if err != nil {
fmt.Println(err)
panic("failed to connect database")
}
//// 执行钩子方法：保存用户之前，将密码统一更新
//db.Session(&gorm.Session{}).Update("Username", "testgouzi")
// 插入记录
var sysUser1 = SysUser{
Username: "lixingyun",
Nickname: "qingqing",
Email: "lixingyun@163.com",
Password: &passwd, // 或者 new(string) , 或者 nil
Desc: sql.NullString{String: "描述", Valid: true},
Age: 32,
Createtime: "2024-06-03 10:00:00",
}
result := db.Create(&sysUser1)
// 生成的主键值
fmt.Println(sysUser1.ID)
fmt.Println(result.Error)
fmt.Println(result.RowsAffected)
// 查找记录
// 根据整型主键查找
db.First(&sysUser1, 1) // 等同于db.First(&user, "id = ?", 1)
fmt.Println(sysUser1)
// 更新记录
// 更新时使用了查找的结果，因为是指针，所以会更新到 sysUser 中
// Updates不会更新零值字段，但Update可以，而且连非空字段也会强制更新为空
db.Model(sysUser1).Update("Username", "")
// 解决零值字段更新的方式
// 1. 使用sql.NullString类型，例如：Desc sql.NullString，然后再定义NullString结构体，最麻烦
db.Model(&sysUser1).Updates(SysUser{Desc: sql.NullString{Valid: true}})
// 2. 将类型设置指针，例如：Password *string，稍微简单一点
empty := ""
db.Model(&sysUser1).Updates(SysUser{Password: &empty})
// 3. 使用Select的方式，最简单
db.Model(&sysUser1).Select("Email").Updates(SysUser{Email: ""})
// 再把数据更新回来
db.Model(&sysUser1).Updates(SysUser{
Username: "lixingyun",
Email: "lixingyun@163.com",
Password: &passwd,
Desc: sql.NullString{String: "描述", Valid: true},
})
// 批量插入
var sysUsers1 = []SysUser{
{Username: "xiangwang2", Nickname: "xw2", Email: "123@qq.com", Password: &passwd, Desc: sql.NullString{String: "描述1", Valid: true}, Age: 22, Createtime: "2023-01-01 10:00:00"},
{Username: "xiangwang3", Nickname: "xw3", Email: "456@qq.com", Password: &passwd, Desc: sql.NullString{String: "描述2", Valid: true}, Age: 32, Createtime: "2023-01-01 11:00:00"},
{Username: "xiangwang4", Nickname: "xw4", Email: "789@qq.com", Password: &passwd, Desc: sql.NullString{String: "描述3", Valid: true}, Age: 42, Createtime: "2023-01-01 12:00:00"},
{Username: "xiangwang5", Nickname: "xw5", Email: "987@qq.com", Password: &passwd, Desc: sql.NullString{String: "描述4", Valid: true}, Age: 52, Createtime: "2023-01-01 13:00:00"},
}
results1 := db.Create(&sysUsers1)
// 生成的主键值
for _, user := range sysUsers1 {
fmt.Println(user.ID)
}
fmt.Println(results1.Error)
fmt.Println(results1.RowsAffected)
// 指定每次批量插入的大小
var sysUsers2 = []SysUser{
{Username: "xiangwang6", Nickname: "xw6", Email: "123@qq.com", Password: &passwd, Desc: sql.NullString{String: "描述1", Valid: true}, Age: 22, Createtime: "2023-01-01 14:00:00"},
{Username: "xiangwang7", Nickname: "xw7", Email: "456@qq.com", Password: &passwd, Desc: sql.NullString{String: "描述2", Valid: true}, Age: 32, Createtime: "2023-01-01 15:00:00"},
{Username: "xiangwang8", Nickname: "xw8", Email: "789@qq.com", Password: &passwd, Desc: sql.NullString{String: "描述3", Valid: true}, Age: 42, Createtime: "2023-01-01 16:00:00"},
{Username: "xiangwang9", Nickname: "xw9", Email: "987@qq.com", Password: &passwd, Desc: sql.NullString{String: "描述4", Valid: true}, Age: 52, Createtime: "2023-01-01 17:00:00"},
}
// 每次插入两条记录
results2 := db.CreateInBatches(sysUsers2, 2)
// 生成的主键值
for _, user := range sysUsers1 {
fmt.Println(user.ID)
}
fmt.Println(results2.Error)
fmt.Println(results2.RowsAffected)
// 根据map创建用户
db.Model(&SysUser{}).Create([]map[string]interface{}{
{"Username": "xiangwang10", "Nickname": "xw10", "Email": "123@qq.com", "Password": &passwd, "Desc": sql.NullString{String: "描述10", Valid: true}, "Age": 22, "Createtime": "2023-01-01 18:00:00"},
{"Username": "xiangwang11", "Nickname": "xw11", "Email": "456@qq.com", "Password": &passwd, "Desc": sql.NullString{String: "描述11", Valid: true}, "Age": 22, "Createtime": "2023-01-01 18:00:00"},
})
// gorm的查询方式大致分为三种：
// 1. Where
// 2. Struct
// 3. Map
// 使用Where检索
var sysUsers3 []SysUser
// 注意：Where 需要直接使用数据库中的字段名，而非结构体中的字段名。这个地方有点绕
results3 := db.Where("username = ?", "xiangwang10").
Or("nickname = ?", "xw10").
Not("age = ?", 22).
Order("username, age desc").
Limit(3).Find(&sysUsers3)
fmt.Printf("使用Where检索，总共检索到 %d 条记录\r\n", results3.RowsAffected)
for _, user := range sysUsers3 {
fmt.Println(user)
}
// 使用结构体检索
var sysUsers4 []SysUser
results4 := db.Where(&SysUser{Username: "xiangwang10", Age: 18}).
Or(&SysUser{Nickname: "xw10"}).
Order("username, age desc").
Limit(3).Find(&sysUsers4)
fmt.Printf("使用结构体检索，总共检索到 %d 条记录\r\n", results4.RowsAffected)
for _, user := range sysUsers4 {
fmt.Println(user)
}
// 使用map检索，且连带零值一同检索
var sysUsers5 []SysUser
results5 := db.Where(map[string]interface{}{"username": "xiangwang10", "age": 22}).Find(&sysUsers5)
fmt.Printf("使用map检索，总共检索到 %d 条记录\r\n", results5.RowsAffected)
for _, user := range sysUsers5 {
fmt.Println(user)
}
var sysUser6 []SysUser
results6 := db.Find(&sysUser6, []int{1, 2, 3, 4})
// 也可以用 len(sysUser4) 代替 results3.RowsAffected
fmt.Printf("总共检索到 %d 条记录\r\n", results6.RowsAffected)
for _, user := range sysUser6 {
fmt.Println(user)
}
// 内联检索
var sysUser2 SysUser
db.First(&sysUser2, "id = ?", 1)
fmt.Println(sysUser2)
db.Find(&sysUser2, "username = ?", "lixingyun")
var sysUser7 []SysUser
db.Find(&sysUser7, "username <> ? AND age > ?", "lixingyun", 22)
// 结构体
db.Find(&sysUser7, SysUser{Age: 22})
// Map
db.Find(&sysUser7, map[string]interface{}{"age": 22})
// 更新
// Save会保存所有的字段，即使字段是零值
var sysUser3 SysUser
sysUser3.Username = "hello"
sysUser3.Age = 38
sysUser3.Createtime = "2023-01-01 18:00:00"
db.Save(&sysUser3)
// 更新指定字段
var sysUser4 SysUser
db.Find(&sysUser4, "username = ?", "hello")
// 每次更新单个字段，零值字段一样会被更新
var pass = "654321"
db.Model(&sysUser4).Update("Nickname", "world12")
// 一次更新多个字段，零值字段一样会被更新
// Select里面都是数据库字段名，不是结构体字段名
db.Model(&sysUser4).Select("email", "password", "desc").Updates(SysUser{Email: "987@qq.com", Password: &pass, Desc: sql.NullString{String: "描述12", Valid: true}})
// 使用原生SQL查询
var userCart []Usercart
results7 := db.Raw("SELECT userid, cartid, createtime FROM usercarts WHERE userid = ?", 1).Scan(&userCart)
fmt.Printf("总共检索到 %d 条记录\r\n", results7.RowsAffected)
for _, user := range userCart {
fmt.Println(user)
}
// 软删除
// 如果表结构包含了 gorm.DeletedAt字段（该字段也被包含在gorm.Model中），那么将自动获得软删除的能力
// 例如：
/*
type User struct {
ID int
Deleted gorm.DeletedAt
Name string
}
*/
// 软删除一条记录
var sysUser5 SysUser
results8 := db.Delete(&sysUser5, 10)
fmt.Printf("总共删除了 %d 条记录\r\n", results8.RowsAffected)
// 查找刚才删除的那条记录
results9 := db.Where("username = ?", "xiangwang10").Find(&sysUser5)
fmt.Printf("总共检索到 %d 条记录\r\n", results9.RowsAffected)
fmt.Println(sysUser5)
// 查询全部记录
var sysUser7 []SysUser
results10 := db.Find(&sysUser7)
fmt.Printf("总共检索到 %d 条记录\r\n", results10.RowsAffected)
for _, user := range sysUser7 {
fmt.Println(user)
}
// 查找被软删除的记录
db.Unscoped().Where("username = ?", "xiangwang10").Find(&sysUser5)
fmt.Println("被删除的数据是：")
fmt.Println(sysUser5)
// 永久删除
var sysUser6 SysUser
db.Unscoped().Delete(&sysUser6, 10)
// 查找被永久删除的记录
db.Unscoped().Where("username = ?", "xiangwang10").Find(&sysUser6)
fmt.Println("被删除的数据是：")
fmt.Println(sysUser6)
}关联查询
package main
import (
"database/sql"
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/logger"
"gorm.io/gorm/schema"
"log"
"os"
"time"
)
// 创建用户表
type SysUser struct {
ID uint `gorm:"primaryKey; <-; comment: 主键ID"` // 主键，允许读和写
Companyid uint `gorm:"type:int(11); <-"`
Username string `gorm:"not null; type:varchar(32); <-"` // not null，允许读和写
Nickname string `gorm:"type:varchar(32); <-"`
Email string `gorm:"type:varchar(32); <-"`
Password *string `gorm:"type:varchar(32); <-"`
Desc sql.NullString `gorm:"type:varchar(64); <-"`
Age uint `gorm:"type:int(4); <-"`
Deleted gorm.DeletedAt // 用于软删除字段的
Createtime string `gorm:"type:timestamp"`
}
// 创建公司表
type SysCompany struct {
ID uint `gorm:"primaryKey; <-; comment: 主键ID"` // 主键，允许读和写
Name string `gorm:"not null; type:varchar(32); <-"` // not null，允许读和写
Deleted gorm.DeletedAt // 用于软删除字段的
Createtime string `gorm:"type:timestamp"`
}
type SysUserInfo struct {
ID uint `gorm:"primaryKey; <-; comment: 主键ID"` // 主键，允许读和写
Companyid uint `gorm:"type:int(11); <-"`
Username string `gorm:"not null; type:varchar(32); <-"` // not null，允许读和写
Nickname string `gorm:"type:varchar(32); <-"`
Name string `gorm:"not null; type:varchar(32); <-"` // not null，允许读和写
}
type User struct {
gorm.Model
ID uint
Name string
}
// 自定义表名
//func (User) TableName() string {
// return "user"
//}
/*
关联查询
*/
func main() {
dsn := "root:123456@tcp(172.16.185.168:3306)/javabook?charset=utf8mb4&parseTime=True&loc=Local"
// 设置全局的logger，打印每一行sql
newLogger := logger.New(
log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
logger.Config{
SlowThreshold: time.Second, // Slow SQL threshold
LogLevel: logger.Warn, // Log level
IgnoreRecordNotFoundError: true, // Ignore ErrRecordNotFound error for logger
ParameterizedQueries: true, // Don't include params in the SQL log
Colorful: true, // Disable color
},
)
// Globally mode
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
NamingStrategy: schema.NamingStrategy{
// 给所有表名增加前缀，但和TableName()不能同时配置
TablePrefix: "t_",
},
Logger: newLogger,
})
if err != nil {
panic("failed to connect database")
}
//// 创建表
//err = db.AutoMigrate(&SysUser{})
//err = db.AutoMigrate(&SysCompany{})
err = db.AutoMigrate(&User{})
// 两表关联查询
var userInfo []SysUserInfo
results := db.Raw("SELECT su.id, su.companyid, su.username, su.nickname, sc.name FROM sys_users AS su, sys_companies AS sc WHERE su.companyid = sc.id AND sc.id = ?", 1).Scan(&userInfo)
fmt.Printf("总共检索到 %d 条记录\r\n", results.RowsAffected)
for _, user := range userInfo {
fmt.Println(user)
}
}感谢支持
更多内容，请移步《超级个体》。