CODEWITHSUNDEEP
go
Blankman
Blankman

Reputation: 266940

Mapping struct to mysql table, and binding row to struct

This is my first script using go-sql-driver.

My mysql table (PRODUCT) looks like:

id int
name varchar(255)
IsMatch tinyint(1)
created datetime

I want to simply load a row from a table, and bind it to a struct.

I have this so far:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

type Product struct {
    Id    int64
  Name  string
  IsMatch ??????????
  Created ?????
}

func main() {
    fmt.Printf("hello, world!\n")

    db, err := sql.Open("mysql", "root:@/product_development")
    defer db.Close()

    err = db.Ping()
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }

    rows, err := db.Query("SELECT * FROM products where id=1")
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }

}

Now I need to:

1. What datatype in Go do I use for tinyint and datetime?
2. How to I map the rows to a Product struct?

Upvotes: 4

Views: 12536

Answers (2)

arthas29
arthas29

Reputation: 1

How to I map the rows to a Product struct? You can use reflect to bind table rows in db to a struct, and automatically match values without long Hard-Code sql string which is easy to make mistakes. this is a light demo: sqlmapper

Upvotes: -1

Kyle Lemons
Kyle Lemons

Reputation: 4756

What datatype in Go do I use for tinyint and datetime?

For a hint as to the types that the database/sql package will be using, have a look at the documentation for database/sql.Scanner, which lists the Go types used within database/sql itself:

int64
float64
bool
[]byte
string
time.Time
nil - for NULL values

This would lead you to try int64 for IsMatch and time.Time for Created. I believe in reality you can use pretty much any sized int (maybe even bool, you'd have to check the source) for IsMatch because it can be stored "without loss of precision." The documentation for go-mysql-driver explains that you will need to add parseTime=true to your DSN in order for it to parse into a time.Time automatically or use NullTime.

How to I map the rows to a Product struct?

It should be something pretty strightforward, using Rows.Scan, like:

var products []*Product
for rows.Next() {
    p := new(Product)
    if err := rows.Scan(&p.ID, &p.Name, &p.IsMatch, &p.Created); err != nil { ... }
    products = append(products, p)
}
if err := rows.Err() { ... }

This scans the columns into the fields of a struct and accumulates them into a slice. (Don't forget to Close the rows!)

Upvotes: 6

Related Questions