doulizhi1247
2017-03-13 22:19
浏览 275

如何在Golang中将* sql.Rows转换为键入的JSON

Essentially, I am trying to run a query on a MySQL database, get the data made converted into JSON and sent back to the client. I have tried several methods and all of the "easy" ones result in sending back all of the JSON as a string. I need this to be send back as a key (string) with []float64 value. This way I have an array of data associated with a key. Also, this needs to have a type. The best method I've found so far to accomplish this was to build put all of the data into a struct, encode it and send that back to the ResponseWriter.

I have seen several questions on making JSON from a database but I haven't found anything utilizing the struct method. I wrote the below code into a single function to illustrate my question. This is VERY limited in that it will only handle two fields and it MUST be a float64.

Therefore, my question is: How do I create this JSON from a query response that has the correct type before sending this back to the client and is there a way to do this dynamically (ie, can accept a variable number of columns and unknown types)?:

{ "Values":[12.54, 76.98, 34.90], "Dates": ["2017-02-03", "2017-02-04:, "2017-02-05"]}


    type DbDao struct{
      db *sql.DB
    }

    type JSONData struct {
      Values []float64
      Dates []string
    }


    func (d *DbDao) SendJSON(sqlString string, w http.ResponseWriter) (error) {

      stmt, err := d.db.Prepare(sqlString)
      if err != nil {
        return err
      }
      defer stmt.Close()

      rows, err := stmt.Query()
      if err != nil {
        return err
      }
      defer rows.Close()

      values := make([]interface{}, 2)
      scanArgs := make([]interface{}, 2)
      for i := range values {
        scanArgs[i] = &values[i]
      }

      for rows.Next() {
        err := rows.Scan(scanArgs...)
        if err != nil {
          return err
        }

        var tempDate string 
        var tempValue float64
        var myjson JSONData

        d, dok := values[0].([]byte)
        v, vok := values[1].(float64)

        if dok {
          tempDate = string(d)
          if err != nil {
            return err
          }
          myjson.Dates = append(myjson.Dates, tempDate)
        }

        if vok {      
          tempValue = v 
          myjson.Values = append(myjson.Values, tempValue)
          fmt.Println(v)
          fmt.Println(tempValue)

        }    

        err = json.NewEncoder(w).Encode(&myjson)
        if err != nil {
          return err
        }
      }

      return nil 
    }

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dqm74406 2017-03-15 00:13
    已采纳

    This is the best implementation that I was able to come up with that would make it dynamic. It is also significantly shorter than my original. As I've seen this type of question quite a bit, I hope this helps others. I am open to other answers that have a better implementation of this:

    func (d *DbDao) makeStructJSON(queryText string, w http.ResponseWriter) error {
    
        // returns rows *sql.Rows
        rows, err := d.db.Query(queryText)
        if err != nil {
            return err
        }
        columns, err := rows.Columns()
        if err != nil {
            return err
        }
    
        count := len(columns)
        values := make([]interface{}, count)
        scanArgs := make([]interface{}, count)
        for i := range values {
            scanArgs[i] = &values[i]
        }
    
        masterData := make(map[string][]interface{})
    
        for rows.Next() {
            err := rows.Scan(scanArgs...)
            if err != nil {
                return err
            }
            for i, v := range values {
    
                x := v.([]byte)
    
                //NOTE: FROM THE GO BLOG: JSON and GO - 25 Jan 2011:
                // The json package uses map[string]interface{} and []interface{} values to store arbitrary JSON objects and arrays; it will happily unmarshal any valid JSON blob into a plain interface{} value. The default concrete Go types are:
                //
                // bool for JSON booleans,
                // float64 for JSON numbers,
                // string for JSON strings, and
                // nil for JSON null.
    
                if nx, ok := strconv.ParseFloat(string(x), 64); ok == nil {
                    masterData[columns[i]] = append(masterData[columns[i]], nx)
                } else if b, ok := strconv.ParseBool(string(x)); ok == nil {
                    masterData[columns[i]] = append(masterData[columns[i]], b)
                } else if "string" == fmt.Sprintf("%T", string(x)) {
                    masterData[columns[i]] = append(masterData[columns[i]], string(x))
                } else {
                    fmt.Printf("Failed on if for type %T of %v
    ", x, x)
                }
    
            }
        }
    
        w.Header().Set("Content-Type", "application/json")
    
        err = json.NewEncoder(w).Encode(masterData)
    
        if err != nil {
            return err
        }
        return err
    }
    
    点赞 评论
  • dstwfcz1377 2017-03-13 22:43

    I think the best option you have is to use json library from golang

    import "encoding/json"
    
    type JSONData struct {
      Values []float64 `json:"Values"`
      Dates []string `json:"Dates"`
    }
    

    I don't think there is a good way to do this dynamically, since golang has no way of matching up the database column name and the output'd json Also as a side note I usually write the db querying code by sending the type directly to the db library

    var tempDate string 
    var tempValue float64
    err := rows.Scan(&tempDate, &tempValue)
    if err != nil {
           return err
    }
    

    If you really want to do this automatically you can look into golang code generation.

    点赞 评论
  • douxun2018 2018-09-29 20:08

    Here is a better solution, using reflection. It handles types correctly (e.g. a string value of true won't erroneously be turned into a bool and so on.

    It also handles possibly-null types (only tested with MySQL - you will probably need to modify it for other drivers).

    package main
    
    import (
        "database/sql"
        "encoding/json"
        "fmt"
        "reflect"
    
        "github.com/go-sql-driver/mysql"
    )
    
    // Additional scan types returned by the MySQL driver. I haven't looked at
    // what PostgreSQL does.
    
    type jsonNullInt64 struct {
        sql.NullInt64
    }
    
    func (v jsonNullInt64) MarshalJSON() ([]byte, error) {
        if !v.Valid {
            return json.Marshal(nil)
        }
        return json.Marshal(v.Int64)
    }
    
    type jsonNullFloat64 struct {
        sql.NullFloat64
    }
    
    func (v jsonNullFloat64) MarshalJSON() ([]byte, error) {
        if !v.Valid {
            return json.Marshal(nil)
        }
        return json.Marshal(v.Float64)
    }
    
    type jsonNullTime struct {
        mysql.NullTime
    }
    
    func (v jsonNullTime) MarshalJSON() ([]byte, error) {
        if !v.Valid {
            return json.Marshal(nil)
        }
        return json.Marshal(v.Time)
    }
    
    var jsonNullInt64Type = reflect.TypeOf(jsonNullInt64{})
    var jsonNullFloat64Type = reflect.TypeOf(jsonNullFloat64{})
    var jsonNullTimeType = reflect.TypeOf(jsonNullTime{})
    var nullInt64Type = reflect.TypeOf(sql.NullInt64{})
    var nullFloat64Type = reflect.TypeOf(sql.NullFloat64{})
    var nullTimeType = reflect.TypeOf(mysql.NullTime{})
    
    // SQLToJSON takes an SQL result and converts it to a nice JSON form. It also
    // handles possibly-null values nicely. See https://stackoverflow.com/a/52572145/265521
    func SQLToJSON(rows *sql.Rows) ([]byte, error) {
        columns, err := rows.Columns()
        if err != nil {
            return nil, fmt.Errorf("Column error: %v", err)
        }
    
        tt, err := rows.ColumnTypes()
        if err != nil {
            return nil, fmt.Errorf("Column type error: %v", err)
        }
    
        types := make([]reflect.Type, len(tt))
        for i, tp := range tt {
            st := tp.ScanType()
            if st == nil {
                return nil, fmt.Errorf("Scantype is null for column: %v", err)
            }
            switch st {
            case nullInt64Type:
                types[i] = jsonNullInt64Type
            case nullFloat64Type:
                types[i] = jsonNullFloat64Type
            case nullTimeType:
                types[i] = jsonNullTimeType
            default:
                types[i] = st
            }
        }
    
        values := make([]interface{}, len(tt))
        data := make(map[string][]interface{})
    
        for rows.Next() {
            for i := range values {
                values[i] = reflect.New(types[i]).Interface()
            }
            err = rows.Scan(values...)
            if err != nil {
                return nil, fmt.Errorf("Failed to scan values: %v", err)
            }
            for i, v := range values {
                data[columns[i]] = append(data[columns[i]], v)
            }
        }
    
        return json.Marshal(data)
    }
    
    点赞 评论

相关推荐 更多相似问题