【Go Web开发】查询数据列表

接着上一篇文章的内容，实现GET /v1/movies接口返回真实数据。

现在，我们先忽略客户端提供的任何查询字符串值，并返回按电影ID排序的所有电影记录。这将为我们后面功能开发打下基础，可以在此基础上开发过滤、排序和分页等特定功能。

本节的目标是让接口返回一个包含所有电影列表的JSON响应，类似如下:

{
    "movies":
    [
        {
            "id": 1,
            "title": "Moana",
            "year": 2015,
            "runtime": "107 mins",
            "genres":
            [
                "animation",
                "adventure"
            ],
            "version": 1
        },
        {
            "id": 2,
            "title": "Black Panther",
            "year": 2018,
            "runtime": "134 mins",
            "genres":
            [
                "sci-fi",
                "action",
                "adventure"
            ],
            "version": 2
        }
        ...etc
    ]
}

更新应用程序

为了从PostgreSQL数据库中查询这些数据，让我们在数据库模型中创建一个新的GetAll()方法，它执行以下SQL查询:

SELECT id, created_at, title, year, runtime, genres, version 
FROM movies
ORDER BY id

因为我们期望这个SQL查询返回多条记录，所以需要使用Go的QueryContext()方法来运行它。如下所示:

File：internal/data/movies.go

// GetAll() 返回movies切片
func (m MovieModel)GetAll(title string, genres []string, filters Filters) ([]*Movie, error)  {
    query := `
        SELECT id, create_at, title, year, runtime, genres, version
        FROM movies
        ORDER BY id`
    //创建3s超时上下文实例
    ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
    defer cancel()
    //使用QueryContext()来执行查询
    rows, err := m.DB.QueryContext(ctx, query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

        //初始化切片，用户存储查询结果
    movies := []*Movie{}
    for rows.Next(){
        var movie Movie
        err := rows.Scan(
            &movie.ID,
            &movie.CreateAt,
            &movie.Title,
            &movie.Year,
            &movie.Runtime,
            pq.Array(&movie.Genres),
            &movie.Version,
            )
        if err != nil {
            return nil, err
        }
        movies = append(movies, &movie)
    }
        //rows.Next()循环结束，调rows.Err()查看是否迭代过程中发送错误
    if err = rows.Err(); err != nil {
        return nil, err
    }
    return movies, nil
}

接下来，我们需要调整listMoviesHandler，以便它调用新的GetAll()方法来检索电影数据，然后将该数据作为JSON响应写入。

更新接口处理程序如下所示：

File：cmd/api/movies.go

package main

...

func (app *application) listMoviesHandler(w http.ResponseWriter, r *http.Request) {
    var input struct {
        Title  string
        Genres []string
        data.Filters
    }

    v := validator.New()
    qs := r.URL.Query()

    input.Title = app.readString(qs, "title", "")
    input.Genres = app.readCSV(qs, "genres", []string{})

    input.Filters.Page = app.readInt(qs, "page", 1, v)
    input.Filters.PageSize = app.readInt(qs, "page_size", 20, v)

    input.Filters.Sort = app.readString(qs, "sort", "id")
    //检查校验是否通过
    if data.ValidateFilters(v, input.Filters); !v.Valid(){
        app.failedValidationResponse(w, r, v.Errors)
        return
    }

    //调用 GetAll()方法从数据库中查询movie记录
    movies, err := app.models.Movies.GetAll(input.Title, input.Genres, input.Filters)
    if err != nil {
        app.serverErrorResponse(w, r, err)
        return
    }
    err = app.writeJSON(w, http.StatusOK, envelope{"movies": movies}, nil)
    if err != nil {
        app.serverErrorResponse(w, r, err)
    }
}

现在我们应该准备好试一下效果了。重启服务，然后发送GET /v1/movies请求，你将看到GetAll()函数返回的movie列表。如下所示：

$  curl localhost:4000/v1/movies

返回结果如下：

{
        "movies": [
                {
                        "id": 1,
                        "title": "Moana",
                        "year": 2016,
                        "runtime": "107 mins",
                        "genres": [
                                "animation",
                                "adventure"
                        ],
                        "Version": 1
                },
                {
                        "id": 2,
                        "title": "Black Panther",
                        "year": 2018,
                        "runtime": "134 mins",
                        "genres": [
                                "sci-fi",
                                "action",
                                "adventure"
                        ],
                        "Version": 2
                },
                {
                        "id": 4,
                        "title": "The Breakfast Club",
                        "year": 1985,
                        "runtime": "97 mins",
                        "genres": [
                                "comedy",
                                "drama"
                        ],
                        "Version": 15
                }
        ]
}