Go sql从扫描返回多行

Question

我正在使用 Postgresql 数据库。这是我要返回多行的代码块：type NewsPaper struct {    language string    logo_url string    slug string    ranking string    title string    search_term string}func GetNewsPapers()  []byte{    db := GetDB()    newspaper := NewsPaper{}    var json = jsoniter.ConfigCompatibleWithStandardLibrary    rows, err := db.Query(`SELECT title, language, ranking, slug, search_term, logo_url FROM public.news_newspaper`)    if err != nil {        log.Println(err)    }    defer rows.Close()    //results := []NewsPaper  // error : type []NewsPaper is not an expression    for rows.Next() {        err = rows.Scan(&newspaper.title, &newspaper.language, &newspaper.ranking, &newspaper.slug, &newspaper.search_term, &newspaper.logo_url)        if err != nil {            log.Println(err)        }        //results = append(results, newspaper)    }    fmt.Println(newspaper)  // printing last row    err = rows.Err()    if err != nil {        panic(err)    }    values, _ := json.Marshal(newspaper)    //fmt.Println(values)    return values}问题是它从表中返回最后一行。我也尝试将结果存储在数组中，但我没有成功。

Answer 1

您为每一行设置相同的结构变量，这就是为什么您只获取最后一行信息。

用于results := []NewsPaper{}创建空切片并为每一行创建新结构。这应该可行，我添加了一些评论。

rows, err := db.Query(`SELECT title, language, ranking, slug, search_term, logo_url FROM public.news_newspaper`)

if err != nil {

    log.Println(err)

}

results := []NewsPaper{} // creating empty slice

defer rows.Close()

for rows.Next() {

    newspaper := NewsPaper{} // creating new struct for every row

    err = rows.Scan(&newspaper.title, &newspaper.language, &newspaper.ranking, &newspaper.slug, &newspaper.search_term, &newspaper.logo_url)

    if err != nil {

        log.Println(err)

    }

    results = append(results, newspaper) // add new row information

}

fmt.Println(results)  // printing result

Answer 2

基本 CRUD 的另一种方法

import (

    "github.com/jmoiron/sqlx"

    "context"

)

type NewsPaper struct {

    Language   string `json:"language" db:"language"`

    LogoURL    string `json:"logo_url" db:"logo_url"`

    Slug       string `json:"slug" db:"slug"`

    Ranking    string `json:"ranking" db:"ranking"`

    Title      string `json:"title" db:"title"`

    SearchTerm string `json:"search_term" db:"search_term"`

}

type NewsPaperRepository interface {

    List(ctx context.Context) ([]NewsPaper, error)

}

type newsPaperRepository struct {

    db *sqlx.DB

}

func (r *newsPaperRepository) List(ctx context.Context) ([]NewsPaper, error) {

    const selectQuery = `SELECT title, language, ranking, slug, search_term, logo_url FROM public.news_newspaper`

    var newsPapers []NewsPaper

    if err := r.db.SelectContext(ctx, &newsPapers, selectQuery); err != nil {

        return nil, fmt.Errorf("news paper repository/problem while trying to retrieve news papers from database: %w", err)

    }

    return newsPapers, nil

}