go gin 学习笔记（二）

1 gin 配合 sqlx 写一个简单 crud 接口

1.1代码 demo

package main

import (
	"log"
	"net/http"
	"strconv"

	//导入驱动
	_ "github.com/go-sql-driver/mysql"

	"github.com/gin-gonic/gin"
	"github.com/jmoiron/sqlx"
)

//书
type book struct {
	ID    int64   `json:"id"`
	Name  string  `json:"name"`
	Price float64 `json:"price"`
}

//获取连接
func getDBConn() *sqlx.DB {
	dsn := "root:123456@tcp(127.0.0.1:3306)/golang"
	conn, _ := sqlx.Open("mysql", dsn)
	return conn
}

//根据ID获取信息
func getBookByID(id int64) *book {
	db := getDBConn()
	defer db.Close()
	b := new(book)
	err := db.Get(b, "select id,name,price from book where id =?", id)
	log.Printf("-------err:%v\n", err)
	return b
}

//获取列表
func getBookList() *[]book {
	db := getDBConn()
	defer db.Close()
	books := make([]book, 0)
	err := db.Select(&books, "select id,name,price from book")
	log.Printf("-------err:%v\n", err)
	return &books
}

//添加
func addBook(b *book) int64 {
	db := getDBConn()
	defer db.Close()
	result, err := db.Exec("insert into book(name,price) values(?,?)", b.Name, b.Price)
	log.Printf("-------err:%v\n", err)
	id, _ := result.LastInsertId()
	return id

}

//修改
func modifyBook(b *book) int64 {
	db := getDBConn()
	defer db.Close()
	r, err := db.Exec("update book set name=?,price=? where id =?", b.Name, b.Price, b.ID)
	log.Printf("-------err:%v\n", err)
	rows, _ := r.RowsAffected()
	return rows
}

//删除
func removeBook(id int64) int64 {
	db := getDBConn()
	defer db.Close()
	r, err := db.Exec("delete from book where id =?", id)
	log.Printf("-------err:%v\n", err)
	rows, _ := r.RowsAffected()
	return rows
}

func selectByID(c *gin.Context) {
	id := c.Query("id")
	log.Printf("-------id:%v\n", id)
	i, _ := strconv.ParseInt(id, 10, 64)
	b := getBookByID(i)
	c.JSON(http.StatusOK, gin.H{
		"book": b,
	})
}

func selectBooks(c *gin.Context) {
	books := getBookList()
	c.JSON(http.StatusOK, gin.H{
		"books": *books,
	})
}

func insertBook(c *gin.Context) {
	b := new(book)
	c.ShouldBindJSON(b)
	log.Printf("b:%v", b)
	id := addBook(b)
	c.JSON(http.StatusOK, gin.H{
		"id": id,
	})
}

func updateBook(c *gin.Context) {
	b := new(book)
	c.ShouldBindJSON(b)
	log.Printf("b:%v", b)
	rows := modifyBook(b)
	c.JSON(http.StatusOK, gin.H{
		"modify_rows": rows,
	})
}

func deleteBook(c *gin.Context) {
	id := c.Query("id")
	log.Printf("-------id:%v\n", id)
	i, _ := strconv.ParseInt(id, 10, 64)
	rows := removeBook(i)
	c.JSON(http.StatusOK, gin.H{
		"delete_rows": rows,
	})

}

func main() {
	router := gin.Default()
	router.GET("/selectByID", selectByID)
	router.POST("/selectBooks", selectBooks)
	router.POST("/insertBook", insertBook)
	router.POST("/updateBook", updateBook)
	router.GET("/deleteBook", deleteBook)
	router.Run()

}

1.2 运行

[GIN-debug] GET    /selectByID               --> main.selectByID (3 handlers)
[GIN-debug] POST   /selectBooks              --> main.selectBooks (3 handlers)
[GIN-debug] POST   /insertBook               --> main.insertBook (3 handlers)
[GIN-debug] POST   /updateBook               --> main.updateBook (3 handlers)
[GIN-debug] GET    /deleteBook               --> main.deleteBook (3 handlers)
[GIN-debug] Environment variable PORT is undefined. Using port :8080 by default
[GIN-debug] Listening and serving HTTP on :8080

1.3 调用结果

使用 postMan 测试

get 127.0.0.1:8080/selectByID?id=2
返回：
{
    "book": {
        "id": 2,
        "name": "python",
        "price": 22.22
    }
}
post 127.0.0.1:8080/selectBooks
返回：
{
    "books": [
        {
            "id": 2,
            "name": "python",
            "price": 22.22
        },
        {
            "id": 3,
            "name": "java",
            "price": 23.33
        },
        {
            "id": 4,
            "name": "sql",
            "price": 10.3
        },
        {
            "id": 5,
            "name": "ql",
            "price": 10.3
        },
        {
            "id": 13,
            "name": "ruby",
            "price": 0
        }
    ]
}

post 127.0.0.1:8080/updateBook
参数：
{
	"id":5,
	"name":"golang",
	"price":56.33
}
返回：
{
    "modify_rows": 1
}

get 127.0.0.1:8080/deleteBook?id=2
返回：
{
    "delete_rows": 1
}