如何一次使用GORM读取一行SQLite数据库

Question

I have the following code

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func getDatabaseHandle(dbpath string) (*sql.DB, error) {
    database, err := sql.Open("sqlite3", dbpath)
    if err != nil {
        log.Printf("Failed to create the handle")
        return nil, err
    }
    if err = database.Ping(); err != nil {
        fmt.Printf("Failed to keep connection alive")
        return nil, err
    }
    return database, nil
}

func getAllRows(database *sql.DB, table string) {
    query := fmt.Sprintf("SELECT User, AppName FROM %s LIMIT 10", table)
    rows, err := database.Query(query)
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var app string
        rows.Scan(&id, &app)
        fmt.Println(id, app)
    }
}

func main() {
    db, err := getDatabaseHandle("./gozo.db")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    getAllRows(db, "timesheet")
}

This is against a SQLite database which has following columns

id, User, Matter, AppName, AppDesc, Duration, Type, Timestamp

The above code word perfectly. But there are two problems

1. I need to declare variables for each of the column that I intend to use inside the for loop that does for rows.Next() which is annoying and can't be made modular easily.

2. I need to use an ORM tool for database portability and all.

Hence I tied GORM and here is my code

type Timesheet struct {
    id        int
    User      int
    Matter    int
    AppName   string
    AppDesc   string
    Duration  int64
    Type      string
    Timestamp string
}

// TableName -- Sets the table name
func (ts Timesheet) TableName() string {
    return "timesheet"
}

func main() {
    db, err := gorm.Open("sqlite3", "./gozo.db")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    var ts []Timesheet
    db.Find(&ts).Limit(5)
    fmt.Println(ts)
}

But this doesn't give me data properly instead it gave me all values 0. Moreover this is not using each row scanning iteration so that I can wrap this go concurrency while performing some other related operation. This seems to be pulling all the data and that is also wrong. Please let me know how get scan each row in a loop and get the right data using GORM.

Answer 1

I have found an answer. The GORM documentation was not so verbose but the flowing could be the answer.

package main

import (
    "fmt"

    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

// Product -- Represents a product
type Product struct {
    gorm.Model
    Code  string
    Price uint
}

// TableName setting the table name
func (Product) TableName() string {
    return "allProducts"
}

func main() {
    db, err := gorm.Open("sqlite3", "test.db")
    if err != nil {
        panic("failed to connect database")
    }
    defer db.Close()
    var product Product
    rows, err := db.Model(&Product{}).Rows()
    defer rows.Close()
    if err != nil {
        panic(err)
    }
    for rows.Next() {
        db.ScanRows(rows, &product)
        fmt.Println(product)
    }
}