Gorm的初步使用(使用频率排序)
在实习这段时间接触到了Grom框架的使用,很好用,有必要整理下其用法:
一、CRUD相关
1. 查询
// 获取第一条记录,按主键排序 db.First(&user) //// SELECT * FROM users ORDER BY id LIMIT 1; // 获取一条记录,不指定排序 db.Take(&user) //// SELECT * FROM users LIMIT 1; // 获取最后一条记录,按主键排序 db.Last(&user) //// SELECT * FROM users ORDER BY id DESC LIMIT 1; // 获取所有的记录 db.Find(&users) //// SELECT * FROM users; // 通过主键进行查询 (仅适用于主键是数字类型) db.First(&user, 10) //// SELECT * FROM users WHERE id = 10;
2. 使用Where添加查询条件
// 获取第一条匹配的记录 db.Where("name = ?", "jinzhu").First(&user) //// SELECT * FROM users WHERE name = 'jinzhu' limit 1; // 获取所有匹配的记录 db.Where("name = ?", "jinzhu").Find(&users) //// SELECT * FROM users WHERE name = 'jinzhu'; // <> db.Where("name <> ?", "jinzhu").Find(&users) // IN db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users) // LIKE db.Where("name LIKE ?", "%jin%").Find(&users) // AND db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users) // Time db.Where("updated_at > ?", lastWeek).Find(&users) // BETWEEN db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
3. 结构体相关
// Struct db.Where(&User{Name: "jinzhu", Age: 20}).First(&user) //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1; // Map db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users) //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20; // 多主键 slice 查询 db.Where([]int64{20, 21, 22}).Find(&users) //// SELECT * FROM users WHERE id IN (20, 21, 22); // NOTE 当通过 struct 进行查询的时候,GORM 将会查询这些字段的非零值, 意味着你的字段包含 0, '', false 或者其他 零值 , 将不会出现在查询语句中. // 解决方案:使用指针或者scanner/valuer // 使用指针类型 type User struct { gorm.Model Name string Age *int } // 使用 scanner/valuer type User struct { gorm.Model Name string Age sql.NullInt64 }
4. Not条件
db.Not("name", "jinzhu").First(&user) //// SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1; // 不包含 db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users) //// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2"); //不在主键 slice 中 db.Not([]int64{1,2,3}).First(&user) //// SELECT * FROM users WHERE id NOT IN (1,2,3); db.Not([]int64{}).First(&user) //// SELECT * FROM users; // 原生 SQL db.Not("name = ?", "jinzhu").First(&user) //// SELECT * FROM users WHERE NOT(name = "jinzhu"); // Struct db.Not(User{Name: "jinzhu"}).First(&user) //// SELECT * FROM users WHERE name <> "jinzhu";
5. Or
db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users) //// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin'; // Struct db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users) //// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; // Map db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users) //// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';
6. 行内查询,把查询写入一条中
// 通过主键进行查询 (仅适用于主键是数字类型) db.First(&user, 23) //// SELECT * FROM users WHERE id = 23 LIMIT 1; // 非数字类型的主键查询 db.First(&user, "id = ?", "string_primary_key") //// SELECT * FROM users WHERE id = 'string_primary_key' LIMIT 1;
7. Attr 如果未找到记录,则使用参数初始化 struct
// 未查询到 db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = 'non_existing'; //// user -> User{Name: "non_existing", Age: 20} db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = 'non_existing'; //// user -> User{Name: "non_existing", Age: 20} // 查询到 db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = jinzhu'; //// user -> User{Id: 111, Name: "Jinzhu", Age: 20}
8. Assingn 无论是否查询到数据,都将参数赋值给 struct
// 未查询到 db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user) //// user -> User{Name: "non_existing", Age: 20} // 查询到 db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = jinzhu'; //// user -> User{Id: 111, Name: "Jinzhu", Age: 30}
9. 子查询
使用*grom.expr 进行子查询
db.Where("amount > ?", DB.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").QueryExpr()).Find(&orders) // SELECT * FROM "orders" WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders" WHERE (state = 'paid')));
查询
db.Select("name, age").Find(&users) //// SELECT name, age FROM users; db.Select([]string{"name", "age"}).Find(&users) //// SELECT name, age FROM users; db.Table("users").Select("COALESCE(age,?)", 42).Rows() //// SELECT COALESCE(age,'42') FROM users;
10. Order
// 当第二个参数设置为true
时,将会覆盖之前的定义条件。
db.Order("age desc, name").Find(&users) //// SELECT * FROM users ORDER BY age desc, name; // 多个排序条件 db.Order("age desc").Order("name").Find(&users) //// SELECT * FROM users ORDER BY age desc, name; // 重新排序 db.Order("age desc").Find(&users1).Order("age", true).Find(&users2) //// SELECT * FROM users ORDER BY age desc; (users1) //// SELECT * FROM users ORDER BY age; (users2)
11. Limit 指定查询的最大记录数
12. Offset 指定在开始返回记录之前要跳过的记录数。
13. Count 获取记录数
db.Limit(10).Find(&users1).Limit(-1).Find(&users2) //// SELECT * FROM users LIMIT 10; (users1) //// SELECT * FROM users; (users2) // 用 -1 取消 OFFSET 限制条件 db.Offset(10).Find(&users1).Offset(-1).Find(&users2) //// SELECT * FROM users OFFSET 10; (users1) //// SELECT * FROM users; (users2) db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count) //// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count) db.Table("deleted_users").Count(&count) //// SELECT count(*) FROM deleted_users;
14. 熟悉的Group和Having
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows() for rows.Next() { ... } rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows() for rows.Next() { ... } type Result struct { Date time.Time Total int64 } db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)
15. Joins
rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows() for rows.Next() { ... } db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results) // 多个关联查询 db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)
二、 更新操作
1. 更新所有字段 Save
// 更新所有字段 Save db.First(&user) user.Name = "jinzhu 2" user.Age = 100 db.Save(&user) //// UPDATE users SET name='jinzhu 2', age=100, birthday='2016-01-01', updated_at = '2013-11-17 21:34:10' WHERE id=111; // 更新已经修改了的字段,可以使用 Update、Updates // 如果单个属性被更改了,更新它 db.Model(&user).Update("name", "hello") //// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111; // 使用组合条件更新单个属性 db.Model(&user).Where("active = ?", true).Update("name", "hello") //// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true; // 使用 `map` 更新多个属性,只会更新那些被更改了的字段 db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false}) //// UPDATE users SET name='hello', age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111; // 使用 `struct` 更新多个属性,只会更新那些被修改了的和非空的字段 db.Model(&user).Updates(User{Name: "hello", Age: 18}) //// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111; // 警告: 当使用结构体更新的时候, GORM 只会更新那些非空的字段 // 例如下面的更新,没有东西会被更新,因为像 "", 0, false 是这些字段类型的空值 db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})
2. 带有表达式的SQL更新
DB.Model(&product).Update("price", gorm.Expr("price * ? + ?", 2, 100)) //// UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '2013-11-17 21:34:10' WHERE "id" = '2'; DB.Model(&product).Updates(map[string]interface{}{"price": gorm.Expr("price * ? + ?", 2, 100)}) //// UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '2013-11-17 21:34:10' WHERE "id" = '2'; DB.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?", 1)) //// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2'; DB.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1)) //// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2' AND quantity > 1;
三、删除
1. 删除一条记录
// 当删除一条记录的时候,你需要确定这条记录的主键有值,GORM 会使用主键来删除这条记录。如果主键字段为空,GORM 会删除模型中所有的记录。 // 删除一条存在的记录 db.Delete(&email) //// DELETE from emails where id=10; // 为删除 SQL 语句添加额外选项 db.Set("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&email) //// DELETE from emails where id=10 OPTION (OPTIMIZE FOR UNKNOWN);
2. 批量删除
db.Where("email LIKE ?", "%jinzhu%").Delete(Email{}) //// DELETE from emails where email LIKE "%jinzhu%"; db.Delete(Email{}, "email LIKE ?", "%jinzhu%") //// DELETE from emails where email LIKE "%jinzhu%";
3. 软删除
如果模型中有 DeletedAt
字段,它将自动拥有软删除的能力!当执行删除操作时,数据并不会永久的从数据库中删除,而是将 DeletedAt
的值更新为当前时间。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
db.Delete(&user) //// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111; // 批量删除 db.Where("age = ?", 20).Delete(&User{}) //// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20; // 在查询记录时,软删除记录会被忽略 db.Where("age = 20").Find(&user) //// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL; // 使用 Unscoped 方法查找软删除记录 db.Unscoped().Where("age = 20").Find(&users) //// SELECT * FROM users WHERE age = 20; // 使用 Unscoped 方法永久删除记录 db.Unscoped().Delete(&order) //// DELETE FROM orders WHERE id=10;
三、链式操作
Gorm中支持链式操作,在调用方法之前都不会进行产生查询
创建方法就是那些会产生 SQL 查询并且发送到数据库,通常它就是一些 CRUD 方法, 就像:
Create, First, Find, Take, Save, UpdateXXX, Delete, Scan, Row, Rows...
db, err := gorm.Open("postgres", "user=gorm dbname=gorm sslmode=disable") // 创建一个新的关系 tx := db.Where("name = ?", "jinzhu") // 新增更多的筛选条件 if someCondition { tx = tx.Where("age = ?", 20) } else { tx = tx.Where("age = ?", 30) } if yetAnotherCondition { tx = tx.Where("active = ?", 1) } // 创建方法 tx.Find(&user) // 生成 SELECT * FROM users where name = 'jinzhu' AND age = 30 AND active = 1;
Scopes方法 提取一些通用的逻辑
func AmountGreaterThan1000(db *gorm.DB) *gorm.DB { return db.Where("amount > ?", 1000) } func PaidWithCreditCard(db *gorm.DB) *gorm.DB { return db.Where("pay_mode_sign = ?", "C") } func PaidWithCod(db *gorm.DB) *gorm.DB { return db.Where("pay_mode_sign = ?", "C") } func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB { return func (db *gorm.DB) *gorm.DB { return db.Scopes(AmountGreaterThan1000).Where("status in (?)", status) } } db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders) // 查找所有大于1000的信用卡订单和金额 db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders) // 查找所有大于1000的 COD 订单和金额 db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders) // 查找大于1000的所有付费和运单
多个创建方法使用
db.Where("name LIKE ?", "jinzhu%").Find(&users, "id IN (?)", []int{1, 2, 3}).Count(&count) // 生成 SELECT * FROM users WHERE name LIKE 'jinzhu%' AND id IN (1, 2, 3) SELECT count(*) FROM users WHERE name LIKE 'jinzhu%'
注:所有的链式操作都将会克隆并创建一个新的数据库对象(共享一个连接池),GORM 对于多个 goroutines 的并发使用是安全的。
四、错误处理
1. check
if err := db.Where("name = ?", "jinzhu").First(&user).Error; err != nil { // error handling... } if result := db.Where("name = ?", "jinzhu").First(&user); result.Error != nil { // error handling... }
2. 多个错误的处理
// 如果有多个错误产生,`GetErrors` 返回一个 `[]error`的切片 db.First(&user).Limit(10).Find(&users).GetErrors() fmt.Println(len(errors)) for _, err := range errors { fmt.Println(err) }
3. GORM 提供了一个处理 RecordNotFound
错误的快捷方式,如果发生了多个错误,它将检查每个错误,如果它们中的任何一个是 RecordNotFound
错误
//检查是否返回 RecordNotFound 错误 db.Where("name = ?", "hello world").First(&user).RecordNotFound() if db.Model(&user).Related(&credit_card).RecordNotFound() { // 数据没有找到 } if err := db.Where("name = ?", "jinzhu").First(&user).Error; gorm.IsRecordNotFoundError(err) { // 数据没有找到 }