Java转Go日记（三十九）：Gorm查询

1.1.1. 查询

    // 获取第一条记录，按主键排序db.First(&user)SELECT * FROM users ORDER BY id 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;

1.1.2. Where查询条件 (简单SQL)

    // 获取第一个匹配记录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)// INdb.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)// LIKEdb.Where("name LIKE ?", "%jin%").Find(&users)// ANDdb.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)// Timedb.Where("updated_at > ?", lastWeek).Find(&users)db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

1.1.3. Where查询条件 (Struct & Map)

注意：当使用struct查询时，GORM将只查询那些具有值的字段

    // Structdb.Where(&User{Name: "jinzhu", Age: 20}).First(&user)SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;// Mapdb.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)SELECT * FROM users WHERE name = "jinzhu" AND age = 20;// 主键的Slicedb.Where([]int64{20, 21, 22}).Find(&users)SELECT * FROM users WHERE id IN (20, 21, 22);

1.1.4. Not条件查询

    db.Not("name", "jinzhu").First(&user)SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;// Not Indb.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users)SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");// Not In slice of primary keysdb.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;// Plain SQLdb.Not("name = ?", "jinzhu").First(&user)SELECT * FROM users WHERE NOT(name = "jinzhu");// Structdb.Not(User{Name: "jinzhu"}).First(&user)SELECT * FROM users WHERE name <> "jinzhu";

1.1.5. 带内联条件的查询

注意：使用主键查询时，应仔细检查所传递的值是否为有效主键，以避免SQL注入

    // 按主键获取db.First(&user, 23)SELECT * FROM users WHERE id = 23 LIMIT 1;// 简单SQLdb.Find(&user, "name = ?", "jinzhu")SELECT * FROM users WHERE name = "jinzhu";db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;// Structdb.Find(&users, User{Age: 20})SELECT * FROM users WHERE age = 20;// Mapdb.Find(&users, map[string]interface{}{"age": 20})SELECT * FROM users WHERE age = 20;

1.1.6. Or条件查询

    db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';// Structdb.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users)SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';// Mapdb.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)

1.1.7. 查询链

Gorm有一个可链接的API，你可以这样使用它

    db.Where("name <> ?","jinzhu").Where("age >= ? and role <> ?",20,"admin").Find(&users)SELECT * FROM users WHERE name <> 'jinzhu' AND age >= 20 AND role <> 'admin';db.Where("role = ?", "admin").Or("role = ?", "super_admin").Not("name = ?", "jinzhu").Find(&users)

1.1.8. 扩展查询选项

    // 为Select语句添加扩展SQL选项db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10)SELECT * FROM users WHERE id = 10 FOR UPDATE;

1.1.9. FirstOrInit

获取第一个匹配的记录，或者使用给定的条件初始化一个新的记录（仅适用于struct，map条件）

    // Unfounddb.FirstOrInit(&user, User{Name: "non_existing"})user -> User{Name: "non_existing"}// Founddb.Where(User{Name: "Jinzhu"}).FirstOrInit(&user)user -> User{Id: 111, Name: "Jinzhu", Age: 20}db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})user -> User{Id: 111, Name: "Jinzhu", Age: 20}

1.1.10. Attrs

如果未找到记录，则使用参数初始化结构

    // Unfounddb.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}// Founddb.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user)SELECT * FROM USERS WHERE name = jinzhu';user -> User{Id: 111, Name: "Jinzhu", Age: 20}

1.1.11. Assign

将参数分配给结果，不管它是否被找到

    db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)user -> User{Name: "non_existing", Age: 20}// Founddb.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user)SELECT * FROM USERS WHERE name = jinzhu';user -> User{Id: 111, Name: "Jinzhu", Age: 30}

1.1.12. FirstOrCreate

获取第一个匹配的记录，或创建一个具有给定条件的新记录（仅适用于struct, map条件）

    // Unfounddb.FirstOrCreate(&user, User{Name: "non_existing"})INSERT INTO "users" (name) VALUES ("non_existing");user -> User{Id: 112, Name: "non_existing"}// Founddb.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user)user -> User{Id: 111, Name: "Jinzhu"}

1.1.13. Attrs

如果未找到记录，则为参数分配结构

    // Unfounddb.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)SELECT * FROM users WHERE name = 'non_existing';INSERT INTO "users" (name, age) VALUES ("non_existing", 20);user -> User{Id: 112, Name: "non_existing", Age: 20}// Founddb.Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user)SELECT * FROM users WHERE name = 'jinzhu';user -> User{Id: 111, Name: "jinzhu", Age: 20}

1.1.14. Assign

将其分配给记录，而不管它是否被找到，并保存回数据库。

    // Unfounddb.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)SELECT * FROM users WHERE name = 'non_existing';INSERT INTO "users" (name, age) VALUES ("non_existing", 20);user -> User{Id: 112, Name: "non_existing", Age: 20}// Founddb.Where(User{Name: "jinzhu"}).Assign(User{Age: 30}).FirstOrCreate(&user)SELECT * FROM users WHERE name = 'jinzhu';UPDATE users SET age=30 WHERE id = 111;user -> User{Id: 111, Name: "jinzhu", Age: 30}

1.1.15. Select

指定要从数据库检索的字段，默认情况下，将选择所有字段;

    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;

1.1.16. Order

在从数据库检索记录时指定顺序，将重排序设置为true以覆盖定义的条件

    db.Order("age desc, name").Find(&users)SELECT * FROM users ORDER BY age desc, name;// Multiple ordersdb.Order("age desc").Order("name").Find(&users)SELECT * FROM users ORDER BY age desc, name;// ReOrderdb.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)

1.1.17. Limit

指定要检索的记录数

    db.Limit(3).Find(&users)SELECT * FROM users LIMIT 3;// Cancel limit condition with -1db.Limit(10).Find(&users1).Limit(-1).Find(&users2)SELECT * FROM users LIMIT 10; (users1)SELECT * FROM users; (users2)

1.1.18. Offset

指定在开始返回记录之前要跳过的记录数

    db.Offset(3).Find(&users)SELECT * FROM users OFFSET 3;// Cancel offset condition with -1db.Offset(10).Find(&users1).Offset(-1).Find(&users2)SELECT * FROM users OFFSET 10; (users1)SELECT * FROM users; (users2)

1.1.19. 18. Count

获取模型的记录数

    db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users)SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)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;

1.1.20. 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.TimeTotal int64}db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

1.1.21. Join

指定连接条件

    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.1.22. Pluck

将模型中的单个列作为地图查询，如果要查询多个列，可以使用Scan

    var ages []int64db.Find(&users).Pluck("age", &ages)var names []stringdb.Model(&User{}).Pluck("name", &names)db.Table("deleted_users").Pluck("name", &names)// 要返回多个列，做这样：db.Select("name, age").Find(&users)

1.1.23. Scan

将结果扫描到另一个结构中。

    type Result struct {Name stringAge  int}var result Resultdb.Table("users").Select("name, age").Where("name = ?", 3).Scan(&result)// Raw SQLdb.Raw("SELECT name, age FROM users WHERE name = ?", 3).Scan(&result)

1.1.24. Scopes

将当前数据库连接传递到func(*DB) *DB，可以用于动态添加条件

    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)// 查找所有信用卡订单和金额大于1000db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)// 查找所有COD订单和金额大于1000db.Scopes(OrderStatus([]string{"paid", "shipped"})).Find(&orders)// 查找所有付费，发货订单

1.1.25. 指定表名

    // 使用User结构定义创建`deleted_users`表db.Table("deleted_users").CreateTable(&User{})var deleted_users []Userdb.Table("deleted_users").Find(&deleted_users)SELECT * FROM deleted_users;db.Table("deleted_users").Where("name = ?", "jinzhu").Delete()DELETE FROM deleted_users WHERE name = 'jinzhu';