避免反射-如何最好地重构此代码？

Question

I started experimenting with Go, and so far it's been a blast. I decided to make a small app which will help a friend organize information business-related information in his (small) company, and I thought I would use Go to implement it.

I haven't (exactly) run into a problem, it's more of a question, when should I consider using reflection? For example, I have 3 related types: Company, Project and Staff. They all have several fields in common (such as id,name) so as you can imagine, the functions that load them from the database (I'm using MySQL) are all very similar.

Look at LoadCompany(), LoadStaff(), and LoadProject() :

// Loads the company from the database with the given id.
func LoadCompany(id int) (Company, error) {
    db := tools.OpenDB()
    defer db.Close()
    stmt, err := db.Prepare("SELECT * FROM companies WHERE id = ?")
    if err != nil {
        log.Panic(err)
    }   
    var c Company 
    err = stmt.QueryRow(id).Scan(&c.id, &c.FullName, &c.Name, &c.History, &c.Overview, &c.Est, &c.Phone, &c.Website, &c.Email)
    if err != nil {
        return Company{}, err 
    }   
    return c, nil 
}

// Loads the staff from the database with the given id.
func LoadStaff(id int) (Staff, error) {
    db := tools.OpenDB()
    defer db.Close()
    stmt, err := db.Prepare("SELECT * FROM staff WHERE id = ?")
    if err != nil {
        log.Panic(err)
    }
    var s Staff
    err = stmt.QueryRow(id).Scan(&s.id, &s.FullName, &s.Name, &s.Email, &s.Joined, &s.Left, &s.History, &s.Phone, &s.Position)
    if err != nil {
        return Staff{}, err
    }
    return s, nil
}

// Loads the project from the database with the given id.
func LoadProject(id int) (Project, error) {
    db := tools.OpenDB()
    defer db.Close()
    stmt, err := db.Prepare("SELECT * FROM projects WHERE id = ?")
    if err != nil {
        log.Panic(err)
    }
    var p Project
    err = stmt.QueryRow(id).Scan(&p.id, &p.Title, &p.Overview, &p.Value, &p.Started, &p.Finished, &p.Client, &p.Architect, &p.Status)
    if err != nil {
        return Project{}, err
    }
    return p, nil
}

When I wrote LoadCompany(), I was feeling pretty good about myself (ahem as a beginner/intermediate programmer) because it seemed minimal and clean. But as I wrote LoadStaff() and LoadProject(), all I was doing is copying and tweaking. I'm certain there's a better way to do this, but I'm weary of jumping into reflection, after reading Pike's post on it:

[Reflection is] a powerful tool that should be used with care and avoided unless strictly necessary.

So my question is, should I use reflection, and if so, can you give me some pointers on the best technique for something like this? This is only the tip of the iceberg, because I feel as though the rest of the functions and methods relating to these types are all similarly repetitive (and don't get me started on the tests!).

Thanks!

Answer 1

Something like:

func LoadObject(sql string, id int, dest ...interface{}) error {
    db := tools.OpenDB()
    defer db.Close()
    stmt, err := db.Prepare(sql)
    if err != nil {
        log.Panic(err)
    }   
    defer stmt.Close()
    return stmt.QueryRow(id).Scan(dest)
}

// Loads the company from the database with the given id.
func LoadCompany(id int) (c Company, err error) {
    err = LoadObject("SELECT * FROM companies WHERE id = ?", &c.id,
        &c.FullName, &c.Name, &c.History, &c.Overview, &c.Est, &c.Phone, &c.Website, &c.Email)
    return
}

Note that I haven't compiled this code, but hopefully it is good enough to give you an idea.

A few suggestions:

• Read through the docs: https://golang.org/pkg/database/sql/
• create sql.DB instance once on program start up
• specify column order explicitly in the SQL statements (select full_name, history, .... from companies ....)