Convert SQL rows to JSON format in Go

问题: In the REST API that I'm developing, there is a /courses endpoint which returns some data queried from a SQL table in JSON format.However, I couldn't find a way to convert...

问题:

In the REST API that I'm developing, there is a /courses endpoint which returns some data queried from a SQL table in JSON format.However, I couldn't find a way to convert queried data (Rows) to JSON.

func GetCoursesEndpoint(w http.ResponseWriter, req *http.Request) {
  db, err := sql.Open("mysql", "root:@/academy")
  checkErr(err)

  rows, err := db.Query("SELECT course_name,price FROM course;")
  checkErr(err)

  //how to convert returned rows to JSON?
  msg, err := json.Marshal(rows)
  checkErr(err)

  json.NewEncoder(w).Encode(msg)
  return  
}

回答1:

An instance of the *sql.Rows type is not something that can be marshaled into json directly. It does not implement the json.Marshaler interface and all of its fields are unexported and therefore inaccessible to the encoding/json package.

What you need to do is to scan the contents of the rows into an intermediary object, one that can be marshaled, and then marshal that object into json.

So first, start by declaring a type that will represent this "intermediary" object, for example:

type Course struct {
    Name  string
    Price int
}

Then, since you're selecting multiple records, you'll need to iterate over the rows object using its Next method and on each iteration scan the contents of the record into an instance of the Course type.

var courses []*Course // declare a slice of courses that will hold all of the Course instances scanned from the rows object
for rows.Next() { // this stops when there are no more rows
    c := new(Course) // initialize a new instance
    err := rows.Scan(&c.Name, &c.Price) // scan contents of the current row into the instance
    if err != nil {
        return err
    }

    courses = append(courses, c) // add each instance to the slice
}
if err := rows.Err(); err != nil { // make sure that there was no issue during the process
    return err
}

And finally you can turn the courses slice into json by passing it to the encoder.

if err := json.NewEncoder(w).Encode(courses); err != nil {
    log.Println(err)
}

If you apply the above suggestions to your handler you should start seeing the result that you expect, or something similar to it... However your handler has a couple other issues that you need to resolve if you don't want your application to crash.

First:

db, err := sql.Open("mysql", "root:@/academy")

It is unnecessary to open a connection every time the handler is executed and so it would be much better if you move the open-db-connection code outside of the handler and just make the db variable accessible to the handler. If, however, you want to keep openning the connection every time you need to make sure to also close it every time, otherwise you'll run out of available connections.

Second:

rows, err := db.Query("SELECT course_name,price FROM course;")

The returned rows object needs to be closed for the same reasons that you need to close the db handle if you keep opening it every time, i.e. you'll run out of available connections and your app will crash.

So a more complete version of the code that should work correctly would look like this:

var db *sql.DB // declare a global variable that will be used by all handlers

func init() {
    var err error
    db, err = sql.Open("mysql", "root:@/academy") // initialize the global connection
    if err != nil {
        panic(err)
    }
}

type Course struct {
    Name  string
    Price int
}

func GetCoursesEndpoint(w http.ResponseWriter, req *http.Request) {
    rows, err := db.Query("SELECT course_name,price FROM course;")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer rows.Close() // make sure rows is closed when the handler exits

    var courses []*Course
    for rows.Next() {
        c := new(Course)
        err := rows.Scan(&c.Name, &c.Price)
        if err != nil {
            fmt.Println(err)
            return
        }

        courses = append(courses, c)
    }
    if err := rows.Err(); err != nil {
        fmt.Println(err)
        return
    }

    if err := json.NewEncoder(w).Encode(courses); err != nil {
        fmt.Println(err)
    }
    return
}
  • 发表于 2019-03-21 05:10
  • 阅读 ( 152 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除