dqb77047 2019-03-27 00:30
浏览 546
已采纳

Golang中的动态SQL选择查询

I am trying to build API, with database/sql and mysql driver, that will read data based on URL parameters.
Something like this

myapi.com/users?columns=id,first_name,last_name,country&sort=desc&sortColumn=last_name&limit=10&offset=20

I know how to get all columns or just specific columns when it is defined in struct. But I want to know is it possible to get columns from url and instead of predefined struct save it to map and than just scan those columns.
I have working code that will get data from above endpoint only if number of columns is same as in struct. If I remove country for example I get error that Scan expects 4 params but 3 are given.

I don't need specific code, just some directions since I am learning Go and my background is PHP where this is easier to do.

Update

Thanks to answers I have partly working solution.
Here is code:

cols := []string{"id", "first_name", "last_name"}
vals := make([]interface{}, len(cols))
w := map[string]interface{}{"id": 105}

var whereVal []interface{}
var whereCol []string

for k, v := range w {
    whereVal = append(whereVal, v)
    whereCol = append(whereCol, fmt.Sprintf("%s = ?", k))
}

for i := range cols {
    vals[i] = new(interface{})
}
err := db.QueryRow("SELECT "+strings.Join(cols, ",")+" FROM users WHERE "+strings.Join(whereCol, " AND "), whereVal...).Scan(vals...)

if err != nil {
    fmt.Println(err)
}

b, _ := json.Marshal(vals)
fmt.Println(string(b))

This should query SELECT id, first_name, last_name FROM users WHERE id = 105;

But how do I get data out to proper json object? Now it prints out strings encoded in base64 like this.

[105,"Sm9obm55","QnJhdm8="]
  • 写回答

3条回答 默认 最新

  • dsm13698679318 2019-03-28 16:48
    关注

    From what I know (also not much experienced in Go) if you don't assign a real type to value then Scan will return []byte and when it is marshalled it returns base64 encoded string.

    So you have to assign a type to your columns and if you want proper json then assign keys to values.

    In your example it can be done something like this:

    cols := []string{"id", "first_name", "last_name"}
    vals := make([]interface{}, len(cols))
    result := make(map[string]interface{}, len(cols))
    
    for i, key := range cols {
        switch key {
        case "id", "status":
            vals[i] = new(int)
        default:
            vals[i] = new(string)
        }
    
        result[key] = vals[i]
    }
    
    b, _ := json.Marshal(result)
    fmt.Println(string(b))
    

    So, instead of looping over cols and creating new interface for each column, now we are creating key/value pairs and assigning type based on column name.

    Also, if you have nullable columns in table, and you probably have, then you'll get error because nil can't go into string. So I suggest this package gopkg.in/guregu/null.v3 and then assign type like null.String. That way you'll get back null as a value.

    For example:

    for i, key := range cols {
        switch key {
        case "id", "status":
            vals[i] = new(int)
        case "updated_at", "created_at":
            vals[i] = new(null.Time)
        default:
            vals[i] = new(null.String)
        }
    
        result[key] = vals[i]
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
  • doubei5114 2019-03-27 09:07
    关注

    You must first fetch the result columns count and then don't exceed the size.

    If you meant the query fields, you need dynamic create the query string, the params size must be the same.

    评论
  • dongyanju1094 2019-03-27 14:31
    关注

    I would create a query statement with the dynamic fields(use placeholder for avoiding sql injection):

    rows := db.QueryRow("SELECT {{YOUR_FIELDS}} from table_tbl")
    

    Create variable carrier with the same size of columns

    vals := make([]interface{}, len(rows.Columns()))
    

    Use sql.RawBytes for field's type if you don't need type checking or can't know their types, otherwise use the same type of field.

    for i, _ := range cols {
        vals[i] = new(sql.RawBytes)
        //check column name, if it is id, and you know it is integer
        //vals[i] = new(int)
    }
    

    Iterate rows and scan

    for rows.Next() {
        err = rows.Scan(vals...)
    }
    
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 关于#java#的问题,请各位专家解答!(相关搜索:java程序)
  • ¥15 linux tsi721的驱动编译后 insmod 提示 报错
  • ¥20 multisim测数据
  • ¥15 求无向连通网的所有不同构的最小生成树
  • ¥15 模拟器的framebuffer问题
  • ¥15 opencv检测轮廓问题
  • ¥15 单点式登录SSO怎么爬虫获取动态SSO_AUTH_ACCESS_Token
  • ¥30 哈夫曼编码译码器打印树形项目
  • ¥20 求完整顺利登陆QQ邮箱的python代码
  • ¥15 怎么下载MySQL,怎么卸干净原来的MySQL