· 8 years ago · Jun 05, 2018, 12:38 PM
1// Date: 2018-06-01
2// Author: TripleZ<me@triplez.cn>
3
4package main
5
6import (
7 _ "github.com/go-sql-driver/mysql"
8 "database/sql"
9 "fmt"
10 "log"
11)
12
13type record struct {
14 id int
15 number sql.NullString
16 name sql.NullString
17}
18
19
20func main() {
21 db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/pdbs_course")
22
23 if err != nil {
24 panic(err.Error())
25 }
26
27 defer db.Close()
28
29 db.SetMaxIdleConns(20)
30 db.SetMaxOpenConns(20)
31
32 if err := db.Ping(); err != nil{
33 log.Fatalln(err)
34 } else {
35 fmt.Println("Connection successful!")
36 }
37
38 checkTable(db)
39 Create(db, 2, "161540121", "TripleZ")
40 Retrive(db, 2)
41 Update(db, 2, "161520201", "UncleBlue")
42 Retrive(db, 2)
43 Delete(db, 2)
44 Retrive(db, 2)
45
46}
47
48func checkTable(db *sql.DB) {
49 db.Exec("create table if not exists test_go_sql(id int primary key, number char(9), name char(10))")
50 fmt.Println("Table created !")
51}
52
53func Create (db *sql.DB, id int, number string, name string) int {
54 statement, err := db.Prepare("insert into test_go_sql(id, number, name) values (?, ?, ?)")
55 if err != nil {
56 log.Fatal(err.Error())
57 }
58 res, err := statement.Exec(id, number, name)
59 if err != nil {
60 log.Fatal(err.Error())
61 }
62 cur_id, err := res.LastInsertId()
63 if err != nil {
64 log.Fatal(err.Error())
65 }
66 return int(cur_id)
67}
68
69
70func Retrive (db *sql.DB, id int) {
71
72 rows, err := db.Query("select * from test_go_sql where id = ? ", id)
73 if err != nil {
74 panic(err.Error())
75 }
76 defer rows.Close()
77
78 var rec record
79
80 for rows.Next() {
81 err = rows.Scan(&rec.id, &rec.number, &rec.name)
82 if err != nil {
83 log.Fatal(err)
84 }
85 fmt.Println(rec.id, rec.number.String, rec.name.String)
86 }
87 if err != nil {
88 log.Fatal(err)
89 }
90
91}
92
93func Update (db *sql.DB, id int, number string, name string) int {
94
95 stmt, err := db.Prepare("update test_go_sql set number = ?, name = ? where id = ?")
96 if err != nil {
97 log.Fatal(err)
98 }
99 res, err := stmt.Exec(number, name, id)
100 if err != nil {
101 log.Fatal(err)
102 }
103 num, err := res.RowsAffected()
104 if err != nil {
105 log.Fatal(err)
106 }
107 return int(num)
108
109}
110
111func Delete (db *sql.DB, id int) int {
112 stmt, err := db.Prepare("delete from test_go_sql where id = ?")
113 if err != nil {
114 log.Fatal(err)
115 }
116 res, err := stmt.Exec(id)
117 if err != nil {
118 log.Fatal(err)
119 }
120 num, err := res.RowsAffected()
121 if err != nil {
122 log.Fatal(err)
123 }
124 return int(num)
125}