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]
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 为什么视频算法现在全是动作识别?
  • ¥15 编写一段matlab代码
  • ¥15 用Python做岩石类别鉴定软件
  • ¥15 关于调取、提交更新数据库记录的问题
  • ¥15 之前删了盘从下vs2022遇见这个问题 搞了一整天了
  • ¥15 从Freecad中宏下载的DesignSPHysics,出现如下问题是什么原因导致的(语言-python)
  • ¥30 notepad++ 自定义代码补全提示
  • ¥15 MATLAB有限差分法解一维边值问题
  • ¥200 内网渗透测试 横向渗透 Windows漏洞 Windows权限维持
  • ¥15 数据结构图的相关代码实现