使用SQL语句进行查询

在实际生产中，各种业务逻辑，model不能很好的满足需求，所以就可以使用原生的sql。当然，如果你对sql比较熟悉，你会发现orm有时候没有原生sql来的灵活，下面介绍beego的原生sql。

使用 Raw SQL 查询，无需使用 ORM 表定义
多数据库，都可直接使用占位符号 ?，自动转换，可以防止sql注入
查询时的参数，支持使用 Model Struct 和 Slice, Array

ids := []int{1, 2, 3}
p.Raw("SELECT name FROM user WHERE id IN (?, ?, ?)", ids)

创建一个 RawSeter

o := orm.NewOrm()
var r RawSeter
r = o.Raw("UPDATE user SET name = ? WHERE name = ?", "testing", "slene")

我们来看看源代码接口定义：

type RawSeter interface {
    //execute sql and get result
    Exec() (sql.Result, error)
    //query data and map to container
    //for example:
    //  var name string
    //  var id int
    //  rs.QueryRow(&id,&name) // id==2 name=="slene"
    QueryRow(containers ...interface{}) error

    // query data rows and map to container
    //  var ids []int
    //  var names []int
    //  query = fmt.Sprintf("SELECT 'id','name' FROM %suser%s", Q, Q)
    //  num, err = dORM.Raw(query).QueryRows(&ids,&names) // ids=>{1,2},names=>{"nobody","slene"}
    QueryRows(containers ...interface{}) (int64, error)
    SetArgs(...interface{}) RawSeter
    // query data to []map[string]interface
    // see QuerySeter's Values
    Values(container *[]Params, cols ...string) (int64, error)
    // query data to [][]interface
    // see QuerySeter's ValuesList
    ValuesList(container *[]ParamsList, cols ...string) (int64, error)
    // query data to []interface
    // see QuerySeter's ValuesFlat
    ValuesFlat(container *ParamsList, cols ...string) (int64, error)
    // query all rows into map[string]interface with specify key and value column name.
    // keyCol = "name", valueCol = "value"
    // table data
    // name  | value
    // total | 100
    // found | 200
    // to map[string]interface{}{
    //  "total": 100,
    //  "found": 200,
    // }
    RowsToMap(result *Params, keyCol, valueCol string) (int64, error)
    // query all rows into struct with specify key and value column name.
    // keyCol = "name", valueCol = "value"
    // table data
    // name  | value
    // total | 100
    // found | 200
    // to struct {
    //  Total int
    //  Found int
    // }
    RowsToStruct(ptrStruct interface{}, keyCol, valueCol string) (int64, error)

    // return prepared raw statement for used in times.
    // for example:
    //  pre, err := dORM.Raw("INSERT INTO tag (name) VALUES (?)").Prepare()
    //  r, err := pre.Exec("name1") // INSERT INTO tag (name) VALUES (`name1`)
    Prepare() (RawPreparer, error)
}

下面具体介绍一下接口：

Exec

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

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

QueryRow

QueryRow 和 QueryRows 提供高级 sql mapper 功能

支持 struct

type User struct {
    Id       int
    UserName string
}

var user User
err := o.Raw("SELECT id, user_name FROM user WHERE id = ?", 1).QueryRow(&user)

QueryRows

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

type User struct {
    Id       int
    UserName string
}

var users []User
num, err := o.Raw("SELECT id, user_name FROM user WHERE id = ?", 1).QueryRows(&users)
if err == nil {
    fmt.Println("user nums: ", num)
}

SetArgs

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

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

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

Values / ValuesList / ValuesFlat

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

from beego 1.1.0
Values, ValuesList, ValuesFlat 的参数，可以指定返回哪些 Columns 的数据
通常情况下，是无需指定的，因为 sql 语句中你可以自行设置 SELECT 的字段

Values

返回结果集的 key => value 值

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

ValuesList

返回结果集 slice

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

ValuesFlat

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

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

RowsToMap

SQL 查询结果是这样

name	value
total	100
found	200

查询结果匹配到 map 里

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

RowsToStruct

SQL 查询结果是这样

name	value
total	100
found	200

查询结果匹配到 struct 里

type Options struct {
    Total int
    Found int
}

res := new(Options)
nums, err := o.Raw("SELECT name, value FROM options_table").RowsToStruct(res, "name", "value")
fmt.Println(res.Total) // 100
fmt.Println(res.Found) // 200

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

Prepare

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

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

构造查询

QueryBuilder 提供了一个简便，流畅的 SQL 查询构造器。在不影响代码可读性的前提下用来快速的建立 SQL 语句。

QueryBuilder 在功能上与 ORM 重合，但是各有利弊。ORM 更适用于简单的 CRUD 操作，而 QueryBuilder 则更适用于复杂的查询，例如查询中包含子查询和多重联结。

使用方法:

// User 包装了下面的查询结果
type User struct {
    Name string
    Age  int
}
var users []User

// 获取 QueryBuilder 对象. 需要指定数据库驱动参数。
// 第二个返回值是错误对象，在这里略过
qb, _ := orm.NewQueryBuilder("mysql")

// 构建查询对象
qb.Select("user.name",
    "profile.age").
    From("user").
    InnerJoin("profile").On("user.id_user = profile.fk_user").
    Where("age > ?").
    OrderBy("name").Desc().
    Limit(10).Offset(0)

// 导出 SQL 语句
sql := qb.String()

// 执行 SQL 语句
o := orm.NewOrm()
o.Raw(sql, 20).QueryRows(&users)

完整 API 接口:

// QueryBuilder is the Query builder interface
type QueryBuilder interface {
    Select(fields ...string) QueryBuilder
    ForUpdate() QueryBuilder
    From(tables ...string) QueryBuilder
    InnerJoin(table string) QueryBuilder
    LeftJoin(table string) QueryBuilder
    RightJoin(table string) QueryBuilder
    On(cond string) QueryBuilder
    Where(cond string) QueryBuilder
    And(cond string) QueryBuilder
    Or(cond string) QueryBuilder
    In(vals ...string) QueryBuilder
    OrderBy(fields ...string) QueryBuilder
    Asc() QueryBuilder
    Desc() QueryBuilder
    Limit(limit int) QueryBuilder
    Offset(offset int) QueryBuilder
    GroupBy(fields ...string) QueryBuilder
    Having(cond string) QueryBuilder
    Update(tables ...string) QueryBuilder
    Set(kv ...string) QueryBuilder
    Delete(tables ...string) QueryBuilder
    InsertInto(table string, fields ...string) QueryBuilder
    Values(vals ...string) QueryBuilder
    Subquery(sub string, alias string) string
    String() string
}

Beego models之三