如何在SQL扫描功能中传递对结构属性的引用列表以使过程自动化？

Question

I have a struct of this form which I add methods too.

type User struct {
    Id           int64
    Email        string
    Username     string
    GeonameId    int64
    Birthdate    string
    Hash         string
    ActiveImage  string
    About        string
    Verified     bool
    Notification bool
    Joined       int64
    LastActivity int64
    Ipv4         int64
    Deleted      bool
}

But everytime I do a query I am manually assigning the results of that query to these properties which is not very DRY. Every time I add a new column in the database I have to change alot of lines of code manually, which is not very desireable.

The kind of methods I have:

func ByEmail(email string) (*User, error) {

    query := `
        SELECT *
        FROM user
        WHERE
            email = ? AND
            deleted = 0
        LIMIT 1;
    `

    user := &User{}
    err := sql.DB.QueryRow(query, email).Scan(
        &user.Id,
        &user.Email,
        &user.Username,
        &user.GeonameId,
        &user.Birthdate,
        &user.Hash,
        &user.ActiveImage,
        &user.About,
        &user.Verified,
        &user.Notification,
        &user.Joined,
        &user.LastActivity,
        &user.Ipv4,
        &user.Deleted,
    )
    if err != nil {
        return nil, err
    }

    return user, nil
}

When creating a new row in the database:

func (u *User) Create() error {

    if u.Id != 0 {
        return fmt.Errorf("User.Create: Struct already has an Id.")
    }

    query := `
        INSERT INTO user
        SET
            email = ?,
            username = ?,
            geonameid = ?,
            birthdate = ?,
            hash = ?,
            active_image = ?,
            about = ?,
            verified = ?,
            notification = ?,           
            joined = ?,
            lastactivity = ?,
            ipv4 = ?,
            deleted = ?;
    `

    stmt, err := sql.DB.Prepare(query)
    if err != nil {
        return err
    }

    res, err := stmt.Exec(
        u.Email,
        u.Username,
        u.GeonameId,
        u.Birthdate,
        u.Hash,
        u.ActiveImage,
        u.About,
        u.Verified,
        u.Notification,
        u.Joined,
        u.LastActivity,
        u.Ipv4,
        u.Deleted,
    )
    if err != nil {
        return err
    }

    lastId, err := res.LastInsertId()
    if err != nil {
        return err
    }

    u.Id = lastId

    return nil
}

I have looked at some examples of reflection usage but I still don't quite grasp it. And I don't know how to do it in a simple way.

How can I automate the process of this by sending in a slice of pointers of reference to the struct properties or something similar in the Scan() and Exec() functions below to automate this process?

EDIT:

I tried to get reference to the structs properties by doing this:

user, _ := user.ById(1)
val := reflect.ValueOf(user).Elem()

fmt.Println(&user.Id)
fmt.Println(val.Field(0).Pointer())

But it panicced and gave me this error:

panic: reflect: call of reflect.Value.Pointer on int64 Value

Answer 1

My suggestion, to use a ORM package.

For your panic, refer to golang document:

It panics if v's Kind is not Chan, Func, Map, Ptr, Slice, or UnsafePointer.

Here is a properties copier example using reflection.

func CopyField(dst interface{}, src interface{}) {
dstVal := reflect.ValueOf(dst).Elem()
srcVal := reflect.ValueOf(src).Elem()

for i := 0; i < srcVal.NumField(); i++ {
    n := srcVal.Type().Field(i).Name
    dstField := dstVal.FieldByName(n)
    if dstField.IsValid() {
        dstField.Set(srcVal.Field(i))
    }
}