使用SQL语句进行查询

  • 使用 Raw SQL 查询,无需使用 ORM 表定义
  • 多数据库,都可直接使用占位符号 ?,自动转换
  • 查询时的参数,支持使用 Model Struct 和 Slice, Array
  1. o := orm.NewOrm()
  2. ids := []int{1, 2, 3}
  3. var r RawSter
  4. r = o.Raw("SELECT name FROM user WHERE id IN (?, ?, ?)", ids)

创建一个 RawSeter

  1. o := orm.NewOrm()
  2. var r RawSeter
  3. r = o.Raw("UPDATE user SET name = ? WHERE name = ?", "testing", "slene")
  1. // RawSeter raw query seter
  2. // create From Ormer.Raw
  3. // for example:
  4. // sql := fmt.Sprintf("SELECT %sid%s,%sname%s FROM %suser%s WHERE id = ?",Q,Q,Q,Q,Q,Q)
  5. // rs := Ormer.Raw(sql, 1)
  6. type RawSeter interface {
  7. // execute sql and get result
  8. Exec() (sql.Result, error)
  9. // query data and map to container
  10. // for example:
  11. // var name string
  12. // var id int
  13. // rs.QueryRow(&id,&name) // id==2 name=="slene"
  14. QueryRow(containers ...interface{}) error
  15. // query data rows and map to container
  16. // var ids []int
  17. // var names []int
  18. // query = fmt.Sprintf("SELECT 'id','name' FROM %suser%s", Q, Q)
  19. // num, err = dORM.Raw(query).QueryRows(&ids,&names) // ids=>{1,2},names=>{"nobody","slene"}
  20. QueryRows(containers ...interface{}) (int64, error)
  21. SetArgs(...interface{}) RawSeter
  22. // query data to []map[string]interface
  23. // see QuerySeter's Values
  24. Values(container *[]Params, cols ...string) (int64, error)
  25. // query data to [][]interface
  26. // see QuerySeter's ValuesList
  27. ValuesList(container *[]ParamsList, cols ...string) (int64, error)
  28. // query data to []interface
  29. // see QuerySeter's ValuesFlat
  30. ValuesFlat(container *ParamsList, cols ...string) (int64, error)
  31. // query all rows into map[string]interface with specify key and value column name.
  32. // keyCol = "name", valueCol = "value"
  33. // table data
  34. // name | value
  35. // total | 100
  36. // found | 200
  37. // to map[string]interface{}{
  38. // "total": 100,
  39. // "found": 200,
  40. // }
  41. RowsToMap(result *Params, keyCol, valueCol string) (int64, error)
  42. // query all rows into struct with specify key and value column name.
  43. // keyCol = "name", valueCol = "value"
  44. // table data
  45. // name | value
  46. // total | 100
  47. // found | 200
  48. // to struct {
  49. // Total int
  50. // Found int
  51. // }
  52. RowsToStruct(ptrStruct interface{}, keyCol, valueCol string) (int64, error)
  53. // return prepared raw statement for used in times.
  54. // for example:
  55. // pre, err := dORM.Raw("INSERT INTO tag (name) VALUES (?)").Prepare()
  56. // r, err := pre.Exec("name1") // INSERT INTO tag (name) VALUES (`name1`)
  57. Prepare() (RawPreparer, error)
  58. }

Exec

执行 sql 语句,返回 sql.Result 对象

  1. res, err := o.Raw("UPDATE user SET name = ?", "your").Exec()
  2. if err == nil {
  3. num, _ := res.RowsAffected()
  4. fmt.Println("mysql row affected nums: ", num)
  5. }

QueryRow

QueryRow 和 QueryRows 提供高级 sql mapper 功能

支持 struct

  1. type User struct {
  2. Id int
  3. UserName string
  4. }
  5. var user User
  6. err := o.Raw("SELECT id, user_name FROM user WHERE id = ?", 1).QueryRow(&user)

QueryRows

QueryRows 支持的对象还有 map 规则是和 QueryRow 一样的,但都是 slice

  1. type User struct {
  2. Id int
  3. UserName string
  4. }
  5. var users []User
  6. num, err := o.Raw("SELECT id, user_name FROM user WHERE id = ?", 1).QueryRows(&users)
  7. if err == nil {
  8. fmt.Println("user nums: ", num)
  9. }

SetArgs

改变 Raw(sql, args…) 中的 args 参数,返回一个新的 RawSeter

用于单条 sql 语句,重复利用,替换参数然后执行。

  1. res, err := r.SetArgs("arg1", "arg2").Exec()
  2. res, err := r.SetArgs("arg1", "arg2").Exec()
  3. ...

Values / ValuesList / ValuesFlat

Raw SQL 查询获得的结果集 Value 为 string 类型,NULL 字段的值为空 ``

Values

返回结果集的 key => value 值

  1. var maps []orm.Params
  2. num, err := o.Raw("SELECT user_name FROM user WHERE status = ?", 1).Values(&maps)
  3. if err == nil && num > 0 {
  4. fmt.Println(maps[0]["user_name"]) // slene
  5. }

ValuesList

返回结果集 slice

  1. var lists []orm.ParamsList
  2. num, err := o.Raw("SELECT user_name FROM user WHERE status = ?", 1).ValuesList(&lists)
  3. if err == nil && num > 0 {
  4. fmt.Println(lists[0][0]) // slene
  5. }

ValuesFlat

返回单一字段的平铺 slice 数据

  1. var list orm.ParamsList
  2. num, err := o.Raw("SELECT id FROM user WHERE id < ?", 10).ValuesFlat(&list)
  3. if err == nil && num > 0 {
  4. fmt.Println(list) // []{"1","2","3",...}
  5. }

RowsToMap

SQL 查询结果是这样

name value
total 100
found 200

查询结果匹配到 map 里

  1. res := make(orm.Params)
  2. nums, err := o.Raw("SELECT name, value FROM options_table").RowsToMap(&res, "name", "value")
  3. // res is a map[string]interface{}{
  4. // "total": 100,
  5. // "found": 200,
  6. // }

RowsToStruct

SQL 查询结果是这样

name value
total 100
found 200

查询结果匹配到 struct 里

  1. type Options struct {
  2. Total int
  3. Found int
  4. }
  5. res := new(Options)
  6. nums, err := o.Raw("SELECT name, value FROM options_table").RowsToStruct(res, "name", "value")
  7. fmt.Println(res.Total) // 100
  8. fmt.Println(res.Found) // 200

匹配支持的名称转换为 snake -> camel, eg: SELECT user_name … 需要你的 struct 中定义有 UserName

Prepare

用于一次 prepare 多次 exec,以提高批量执行的速度。

  1. p, err := o.Raw("UPDATE user SET name = ? WHERE name = ?").Prepare()
  2. res, err := p.Exec("testing", "slene")
  3. res, err = p.Exec("testing", "astaxie")
  4. ...
  5. ...
  6. p.Close() // 别忘记关闭 statement