gin是一个简单的golang http框架,其性能比较好。mysql是比较常见的数据库,两都结合起来可以快速构建一个http api server。
一、安装依赖
安装gin和golang mysql driver,如下:
$ go get "github.com/go-sql-driver/mysql"
$ go get "github.com/gin-gonic/gin"
二、创建测试用的数据库
安装完mysql-server包后,启动并配置用户名密码后,使用 CREATE DATABASE gotest 创建测试数据库。数据库下新建person 表,该表可以通过命令行进行创建,也可以使用golang程序连接测试创建,代码如下:
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root:passapp@tcp(127.0.0.1:3306)/gotest")
if err != nil {
fmt.Print(err.Error())
}
defer db.Close()
// make sure connection is available
err = db.Ping()
if err != nil {
fmt.Print(err.Error())
}
stmt, err := db.Prepare("CREATE TABLE person (id int NOT NULL AUTO_INCREMENT, first_name varchar(40), last_name varchar(40), PRIMARY KEY (id));")
if err != nil {
fmt.Println(err.Error())
}
_, err = stmt.Exec()
if err != nil {
fmt.Print(err.Error())
} else {
fmt.Printf("Person Table successfully migrated....")
}
}
上面使用了db.Ping()函数进行测试数据库能否正常连接。
三、CURL实现
一个基础的API一般涉及如下几个类型的APi操作模式:
GET
POST
PUT
DELETE
使用gin.Default()可以创建一个默认路由。接下实现一个handle对象,其有两个参数,第一个参数是URL,第二个是gin.Context对象。具体代码如下:
package main
import (
"bytes"
"database/sql"
"fmt"
"net/http"
"github.com/gin-gonic/gin"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root:passapp@tcp(127.0.0.1:3306)/gotest")
if err != nil {
fmt.Print(err.Error())
}
defer db.Close()
// make sure connection is available
err = db.Ping()
if err != nil {
fmt.Print(err.Error())
}
type Person struct {
Id int
First_Name string
Last_Name string
}
router := gin.Default()
// GET a person detail
router.GET("/person/:id", func(c *gin.Context) {
var (
person Person
result gin.H
)
id := c.Param("id")
row := db.QueryRow("select id, first_name, last_name from person where id = ?;", id)
err = row.Scan(&person.Id, &person.First_Name, &person.Last_Name)
if err != nil {
// If no results send null
result = gin.H{
"result": nil,
"count": 0,
}
} else {
result = gin.H{
"result": person,
"count": 1,
}
}
c.JSON(http.StatusOK, result)
})
// GET all persons
router.GET("/persons", func(c *gin.Context) {
var (
person Person
persons []Person
)
rows, err := db.Query("select id, first_name, last_name from person;")
if err != nil {
fmt.Print(err.Error())
}
for rows.Next() {
err = rows.Scan(&person.Id, &person.First_Name, &person.Last_Name)
persons = append(persons, person)
if err != nil {
fmt.Print(err.Error())
}
}
defer rows.Close()
c.JSON(http.StatusOK, gin.H{
"result": persons,
"count": len(persons),
})
})
// POST new person details
router.POST("/person", func(c *gin.Context) {
var buffer bytes.Buffer
first_name := c.PostForm("first_name")
last_name := c.PostForm("last_name")
stmt, err := db.Prepare("insert into person (first_name, last_name) values(?,?);")
if err != nil {
fmt.Print(err.Error())
}
_, err = stmt.Exec(first_name, last_name)
if err != nil {
fmt.Print(err.Error())
}
// Fastest way to append strings
buffer.WriteString(first_name)
buffer.WriteString(" ")
buffer.WriteString(last_name)
defer stmt.Close()
name := buffer.String()
c.JSON(http.StatusOK, gin.H{
"message": fmt.Sprintf(" %s successfully created", name),
})
})
// PUT - update a person details
router.PUT("/person", func(c *gin.Context) {
var buffer bytes.Buffer
id := c.Query("id")
first_name := c.PostForm("first_name")
last_name := c.PostForm("last_name")
stmt, err := db.Prepare("update person set first_name= ?, last_name= ? where id= ?;")
if err != nil {
fmt.Print(err.Error())
}
_, err = stmt.Exec(first_name, last_name, id)
if err != nil {
fmt.Print(err.Error())
}
// Fastest way to append strings
buffer.WriteString(first_name)
buffer.WriteString(" ")
buffer.WriteString(last_name)
defer stmt.Close()
name := buffer.String()
c.JSON(http.StatusOK, gin.H{
"message": fmt.Sprintf("Successfully updated to %s", name),
})
})
// Delete resources
router.DELETE("/person", func(c *gin.Context) {
id := c.Query("id")
stmt, err := db.Prepare("delete from person where id= ?;")
if err != nil {
fmt.Print(err.Error())
}
_, err = stmt.Exec(id)
if err != nil {
fmt.Print(err.Error())
}
c.JSON(http.StatusOK, gin.H{
"message": fmt.Sprintf("Successfully deleted user: %s", id),
})
})
router.Run(":3000")
}
四、测试
使用postman插件进行测试,
1、post数据
效果如下: 使用post方法发送相关数据给服务器,发送完毕后,可以通过http://serverip:3000/persons进行查看结果,当然上面是post的formdata,所以也可以使用命令行的方式进行提交,如下:
curl -d "first_name=361way&last_name=com" http://127.0.0.1:3000/person
2、get数据
命令查询如下:
# curl -s http://127.0.0.1:3000/person/3|jq
{
"count": 1,
"result": {
"Id": 3,
"First_Name": "mysite",
"Last_Name": "361way.com"
}
}
3、update和delete数据
PUT和DELETE方法使用的是c.Query(“id”),这和c.PostForm数据不同,这里以PUT为例 同样,DELETE,也可以使用如下方式进行删除:
curl -X DELETE http://127.0.0.1:3000/person?id=4