· 9 years ago · Nov 11, 2016, 05:52 PM
1package main
2
3import (
4 "database/sql"
5 _ "github.com/go-sql-driver/mysql"
6 "gopkg.in/gorp.v1"
7 "log"
8 "time"
9)
10
11func main() {
12 // initialize the DbMap
13 dbmap := initDb()
14 defer dbmap.Db.Close()
15
16 // delete any existing rows
17 err := dbmap.TruncateTables()
18 checkErr(err, "TruncateTables failed")
19
20 // create two posts
21 p1 := newPost("Go 1.1 released!", "Lorem ipsum lorem ipsum")
22 p2 := newPost("Go 1.2 released!", "Lorem ipsum lorem ipsum")
23
24 // insert rows - auto increment PKs will be set properly after the insert
25 err = dbmap.Insert(&p1, &p2)
26 checkErr(err, "Insert failed")
27
28 // use convenience SelectInt
29 count, err := dbmap.SelectInt("select count(*) from posts")
30 checkErr(err, "select count(*) failed")
31 log.Println("Rows after inserting:", count)
32
33 // update a row
34 p2.Title = "Go 1.2 is better than ever"
35 count, err = dbmap.Update(&p2)
36 checkErr(err, "Update failed")
37 log.Println("Rows updated:", count)
38
39 // fetch one row - note use of "post_id" instead of "Id" since column is aliased
40 //
41 // Postgres users should use $1 instead of ? placeholders
42 // See 'Known Issues' below
43 //
44 err = dbmap.SelectOne(&p2, "select * from posts where post_id=?", p2.Id)
45 checkErr(err, "SelectOne failed")
46 log.Println("p2 row:", p2)
47
48 // fetch all rows
49 var posts []Post
50 _, err = dbmap.Select(&posts, "select * from posts order by post_id")
51 checkErr(err, "Select failed")
52 log.Println("All rows:")
53 for x, p := range posts {
54 log.Printf(" %d: %v\n", x, p)
55 }
56
57 // delete row by PK
58 count, err = dbmap.Delete(&p1)
59 checkErr(err, "Delete failed")
60 log.Println("Rows deleted:", count)
61
62 // delete row manually via Exec
63 // _, err = dbmap.Exec("delete from posts where post_id=?", p2.Id)
64 // checkErr(err, "Exec failed")
65
66 // confirm count is zero
67 count, err = dbmap.SelectInt("select count(*) from posts")
68 checkErr(err, "select count(*) failed")
69 log.Println("Row count - should be zero:", count)
70
71 log.Println("Done!")
72}
73
74type Post struct {
75 // db tag lets you specify the column name if it differs from the struct field
76 Id int64 `db:"post_id"`
77 Created int64
78 Title string `db:",size:50"` // Column size set to 50
79 Body string `db:"article_body,size:1024"` // Set both column name and size
80}
81
82func newPost(title, body string) Post {
83 return Post{
84 Created: time.Now().UnixNano(),
85 Title: title,
86 Body: body,
87 }
88}
89
90func createAndOpen(name string) (*sql.DB, error) {
91 db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/")
92 if err != nil {
93 // panic(err)
94 // return nil, err
95 }
96 // defer db.Close()
97
98 _, err = db.Exec("CREATE DATABASE IF NOT EXISTS " + name)
99 if err != nil {
100 // panic(err)
101 }
102 // db.Close()
103
104 db, err = sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/"+name)
105 if err != nil {
106 // panic(err)
107 }
108 // defer db.Close()
109 return db, err
110}
111func initDb() *gorp.DbMap {
112 db, err := createAndOpen("gorpdb")
113 checkErr(err, "sql.Open failed")
114
115 // construct a gorp DbMap
116 dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "MyISAM", Encoding: "utf8"}}
117
118 // add a table, setting the table name to 'posts' and
119 // specifying that the Id property is an auto incrementing PK
120 dbmap.AddTableWithName(Post{}, "posts").SetKeys(true, "Id")
121
122 // create the table. in a production system you'd generally
123 // use a migration tool, or create the tables via scripts
124 err = dbmap.CreateTablesIfNotExists()
125 checkErr(err, "Create tables failed")
126
127 return dbmap
128}
129
130func checkErr(err error, msg string) {
131 if err != nil {
132 log.Fatalln(msg, err)
133 }
134}