golang+gin+mysql构建RESTful API
gin是一个简单的golang http框架，其性能比较好。mysql是比较常见的数据库，两都结合起来可以快速构建一个http api server。
一、安装依赖
安装gin和golang mysql driver，如下：
1$ go get "github.com/go-sql-driver/mysql"
2$ go get "github.com/gin-gonic/gin"
二、创建测试用的数据库
安装完mysql-server包后，启动并配置用户名密码后，使用 CREATE DATABASE gotest 创建测试数据库。数据库下新建person 表，该表可以通过命令行进行创建，也可以使用golang程序连接测试创建，代码如下：
1package main
2import (
3 "database/sql"
4 "fmt"
5 _ "github.com/go-sql-driver/mysql"
6)
7func main() {
8 db, err := sql.Open("mysql", "root:passapp@tcp(127.0.0.1:3306)/gotest")
9 if err != nil {
10 fmt.Print(err.Error())
11 }
12 defer db.Close()
13 // make sure connection is available
14 err = db.Ping()
15 if err != nil {
16 fmt.Print(err.Error())
17 }
18 stmt, err := db.Prepare("CREATE TABLE person (id int NOT NULL AUTO_INCREMENT, first_name varchar(40), last_name varchar(40), PRIMARY KEY (id));")
19 if err != nil {
20 fmt.Println(err.Error())
21 }
22 _, err = stmt.Exec()
23 if err != nil {
24 fmt.Print(err.Error())
25 } else {
26 fmt.Printf("Person Table successfully migrated....")
27 }
28}
上面使用了db.Ping()函数进行测试数据库能否正常连接。
三、CURL实现
一个基础的API一般涉及如下几个类型的APi操作模式：
1GET
2POST
3PUT
4DELETE
使用gin.Default()可以创建一个默认路由。接下实现一个handle对象，其有两个参数，第一个参数是URL，第二个是gin.Context对象。具体代码如下：
1package main
2import (
3 "bytes"
4 "database/sql"
5 "fmt"
6 "net/http"
7 "github.com/gin-gonic/gin"
8 _ "github.com/go-sql-driver/mysql"
9)
10func main() {
11 db, err := sql.Open("mysql", "root:passapp@tcp(127.0.0.1:3306)/gotest")
12 if err != nil {
13 fmt.Print(err.Error())
14 }
15 defer db.Close()
16 // make sure connection is available
17 err = db.Ping()
18 if err != nil {
19 fmt.Print(err.Error())
20 }
21 type Person struct {
22 Id int
23 First_Name string
24 Last_Name string
25 }
26 router := gin.Default()
27 // GET a person detail
28 router.GET("/person/:id", func(c *gin.Context) {
29 var (
30 person Person
31 result gin.H
32 )
33 id := c.Param("id")
34 row := db.QueryRow("select id, first_name, last_name from person where id = ?;", id)
35 err = row.Scan(&person.Id, &person.First_Name, &person.Last_Name)
36 if err != nil {
37 // If no results send null
38 result = gin.H{
39 "result": nil,
40 "count": 0,
41 }
42 } else {
43 result = gin.H{
44 "result": person,
45 "count": 1,
46 }
47 }
48 c.JSON(http.StatusOK, result)
49 })
50 // GET all persons
51 router.GET("/persons", func(c *gin.Context) {
52 var (
53 person Person
54 persons []Person
55 )
56 rows, err := db.Query("select id, first_name, last_name from person;")
57 if err != nil {
58 fmt.Print(err.Error())
59 }
60 for rows.Next() {
61 err = rows.Scan(&person.Id, &person.First_Name, &person.Last_Name)
62 persons = append(persons, person)
63 if err != nil {
64 fmt.Print(err.Error())
65 }
66 }
67 defer rows.Close()
68 c.JSON(http.StatusOK, gin.H{
69 "result": persons,
70 "count": len(persons),
71 })
72 })
73 // POST new person details
74 router.POST("/person", func(c *gin.Context) {
75 var buffer bytes.Buffer
76 first_name := c.PostForm("first_name")
77 last_name := c.PostForm("last_name")
78 stmt, err := db.Prepare("insert into person (first_name, last_name) values(?,?);")
79 if err != nil {
80 fmt.Print(err.Error())
81 }
82 _, err = stmt.Exec(first_name, last_name)
83 if err != nil {
84 fmt.Print(err.Error())
85 }
86 // Fastest way to append strings
87 buffer.WriteString(first_name)
88 buffer.WriteString(" ")
89 buffer.WriteString(last_name)
90 defer stmt.Close()
91 name := buffer.String()
92 c.JSON(http.StatusOK, gin.H{
93 "message": fmt.Sprintf(" %s successfully created", name),
94 })
95 })
96 // PUT - update a person details
97 router.PUT("/person", func(c *gin.Context) {
98 var buffer bytes.Buffer
99 id := c.Query("id")
100 first_name := c.PostForm("first_name")
101 last_name := c.PostForm("last_name")
102 stmt, err := db.Prepare("update person set first_name= ?, last_name= ? where id= ?;")
103 if err != nil {
104 fmt.Print(err.Error())
105 }
106 _, err = stmt.Exec(first_name, last_name, id)
107 if err != nil {
108 fmt.Print(err.Error())
109 }
110 // Fastest way to append strings
111 buffer.WriteString(first_name)
112 buffer.WriteString(" ")
113 buffer.WriteString(last_name)
114 defer stmt.Close()
115 name := buffer.String()
116 c.JSON(http.StatusOK, gin.H{
117 "message": fmt.Sprintf("Successfully updated to %s", name),
118 })
119 })
120 // Delete resources
121 router.DELETE("/person", func(c *gin.Context) {
122 id := c.Query("id")
123 stmt, err := db.Prepare("delete from person where id= ?;")
124 if err != nil {
125 fmt.Print(err.Error())
126 }
127 _, err = stmt.Exec(id)
128 if err != nil {
129 fmt.Print(err.Error())
130 }
131 c.JSON(http.StatusOK, gin.H{
132 "message": fmt.Sprintf("Successfully deleted user: %s", id),
133 })
134 })
135 router.Run(":3000")
136}
四、测试
使用postman插件进行测试，
1、post数据
效果如下： 
1curl -d "first_name=361way&last_name=com" http://127.0.0.1:3000/person
2、get数据
1# curl -s http://127.0.0.1:3000/person/3|jq
2{
3 "count": 1,
4 "result": {
5 "Id": 3,
6 "First_Name": "mysite",
7 "Last_Name": "361way.com"
8 }
9}
3、update和delete数据
PUT和DELETE方法使用的是c.Query(“id”)，这和c.PostForm数据不同，这里以PUT为例 
1curl -X DELETE http://127.0.0.1:3000/person?id=4
捐赠本站(Donate)
如您感觉文章有用，可扫码捐赠本站！(If the article useful, you can scan the QR code to donate))
- Author: shisekong
- Link: https://blog.361way.com/gin-mysql-restful-api/6052.html
- License: This work is under a 知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议. Kindly fulfill the requirements of the aforementioned License when adapting or creating a derivative of this work.