douzhan2027
2016-05-28 16:54
浏览 523

创建一个数组/切片以在Golang中存储数据库查询结果

I'm just getting started with golang and I'm attempting to read several rows from a Postgres users table and store the result as an array of User structs that model the row.

type User struct {
    Id  int
    Title string
}

func Find_users(db *sql.DB) {
    // Query the DB
    rows, err := db.Query(`SELECT u.id, u.title FROM users u;`)
    if err != nil { log.Fatal(err) }

    // Initialize array slice of all users. What size do I use here? 
    // I don't know the number of results beforehand
    var users = make([]User, ????)

    // Loop through each result record, creating a User struct for each row
    defer rows.Close()
    for i := 0; rows.Next(); i++ {
        err := rows.Scan(&id, &title)
        if err != nil { log.Fatal(err) }

        log.Println(id, title)
        users[i] = User{Id: id, Title: title}
    }

    // .... do stuff
}

As you can see, my problem is that I want to initialize an array or slice beforehand to store all the DB records, but I don't know ahead of time how many records there are going to be.

I was weighing a few different approaches, and wanted to find out which of the following was most used in the golang community -

  1. Create a really large array beforehand (e.g. 10,000 elements). Seems wasteful

  2. Count the rows explicitly beforehand. This could work, but I need to run 2 queries - one to count and one to get the results. If my query is complex (not shown here), that's duplicating that logic in 2 places. Alternatively I can run the same query twice, but first loop through it and count the rows. All this would work, but it just seems unclean.

  3. I've seen examples of expanding slices. I don't quite understand slices well enough to see how it could be adapted here. Also if I'm constantly expanding a slice 10k times, it definitely seems wasteful.

Thanks!

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duandazhen7306 2016-05-28 17:46
    已采纳

    Go has a built-in append function for exactly this purpose. It takes a slice and one or more elements and appends those elements to the slice, returning the new slice. Additionally, the zero value of a slice (nil) is a slice of length zero, so if you append to a nil slice, it will work. Thus, you can do:

    type User struct {
        Id    int
        Title string
    }
    
    func Find_users(db *sql.DB) {
        // Query the DB
        rows, err := db.Query(`SELECT u.id, u.title FROM users u;`)
        if err != nil {
            log.Fatal(err)
        }
        defer rows.Close()
    
        var users []User
        for rows.Next() {
            err := rows.Scan(&id, &title)
            if err != nil {
                log.Fatal(err)
            }
    
            log.Println(id, title)
            users = append(users, User{Id: id, Title: title})
        }
        if err := rows.Err(); err != nil {
            log.Fatal(err)
        }
    
        // ...
    }
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dtwye28880 2018-08-21 16:22

    User appending to a slice:

    type DeviceInfo struct {
         DeviceName     string
         DeviceID       string
         DeviceUsername string
         Token          string
    }
    
    func QueryMultiple(db *sql.DB){
        var device DeviceInfo
        sqlStatement := `SELECT "deviceName", "deviceID", "deviceUsername", 
                     token FROM devices LIMIT 10`
        rows, err := db.Query(sqlStatement)
        if err != nil {
           panic(err)
        }
        defer rows.Close()
        var deviceSlice []DeviceInfo
        for rows.Next(){
            rows.Scan(&device.DeviceID, &device.DeviceUsername, &device.Token, 
                      &device.DeviceName)
             deviceSlice = append(deviceSlice, device)
        }
        fmt.Println(deviceSlice)
    }
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题