Go语言 Gin框架使用原生数据库

Gin框架使用原生数据库，操作数据库，编写crud接口。

1.初始化数据库

func initDB() {
	// 1.打开数据库
	sqlStr := "root:123456@tcp(127.0.0.1:3306)/mydb"
	var err error
	sqlDb, err = sql.Open("mysql", sqlStr)
	if err != nil {
		fmt.Println("数据库打开出错：", err)
		return
	}
	// 2.测试与数据库建立的连接（校验连接是否正确）
	err = sqlDb.Ping()
	if err != nil {
		fmt.Println("数据库连接出错：", err)
		return
	}
}

2.插入数据

func insertData(c *gin.Context) {
	var u SqlUser
	err := c.Bind(&u)
	if err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "参数错误"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlStr := "insert into person(name, age) values(?,?)"
	_, err = sqlDb.Exec(sqlStr, u.Name, u.Age)
	if err != nil {
		fmt.Printf("insert failed, error: %v\n", err)
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "插入数据失败"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "插入数据成功"
	sqlResponse.Data = "ok"
	c.JSON(http.StatusOK, sqlResponse)
	return
}

3.查询数据

1）查询单条数据

func getData(c *gin.Context) {
	name := c.Query("name")
	sqlStr := "select name,age from person where name=?"
	var u SqlUser
	err := sqlDb.QueryRow(sqlStr, name).Scan(&u.Name, &u.Age)
	if err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "查询错误"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	u.Name = name
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "读取成功"
	sqlResponse.Data = u
	c.JSON(http.StatusOK, sqlResponse)

}

2）查询多条数据

func getAllData(c *gin.Context) {
	name := c.Query("name")
	sqlStr := "select name,age from person where name=?"
	rows, err := sqlDb.Query(sqlStr, name)
	if err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "查询错误"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	defer rows.Close()
	resUser := make([]SqlUser, 0)
	for rows.Next() {
		var userTemp SqlUser
		rows.Scan(&userTemp.Name, &userTemp.Age)
		resUser = append(resUser, userTemp)
	}
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "读取成功"
	sqlResponse.Data = resUser
	c.JSON(http.StatusOK, sqlResponse)

}

4.更新数据

func updateData(c *gin.Context) {
	var u SqlUser
	err := c.Bind(&u)
	if err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "参数错误"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlStr := "update person set age=? where name=?"
	ret, err := sqlDb.Exec(sqlStr, u.Age, u.Name)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "更新失败"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "更新成功"
	sqlResponse.Data = "OK"
	c.JSON(http.StatusOK, sqlResponse)
	fmt.Println(ret.LastInsertId()) //打印结果

}

5.删除数据

func deleteData(c *gin.Context) {
	name := c.Query("name")
	var count int
	//1、先查询
	sqlStr := "select count(*) from person where name=?"
	err := sqlDb.QueryRow(sqlStr, name).Scan(&count)
	if count <= 0 || err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "删除的数据不存在"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	//2、再删除
	delStr := "delete from person where name=?"
	ret, err := sqlDb.Exec(delStr, name)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "删除失败"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "删除成功"
	sqlResponse.Data = "OK"
	c.JSON(http.StatusOK, sqlResponse)
	fmt.Println(ret.LastInsertId()) //打印结果
}

6.整体示例

package main

import (
	"database/sql"
	"fmt"
	"github.com/gin-gonic/gin"
	_ "github.com/go-sql-driver/mysql"
	"net/http"
)

var sqlDb *sql.DB           // 数据库连接db
var sqlResponse SqlResponse // 响应client的数据

// 初始化数据库
func initDB() {
	// 1.打开数据库
	sqlStr := "root:123456@tcp(127.0.0.1:3306)/mydb"
	var err error
	sqlDb, err = sql.Open("mysql", sqlStr)
	if err != nil {
		fmt.Println("数据库打开出错：", err)
		return
	}
	// 2.测试与数据库建立的连接（校验连接是否正确）
	err = sqlDb.Ping()
	if err != nil {
		fmt.Println("数据库连接出错：", err)
		return
	}
}

// Client 提交的数据结构
type SqlUser struct {
	Name string `json:"name"`
	Age  int    `json:"age"`
}

// 接口响应体（响应client的请求）
type SqlResponse struct {
	Code    int         `json:"code"`
	Message string      `json:"message"`
	Data    interface{} `json:"data"`
}

func main() {
	initDB()

	r := gin.Default()
	r.POST("/add", insertData)      //插入数据
	r.GET("/get", getData)          //查询单条数据
	r.GET("/all", getAllData)       //查询多条数据
	r.PUT("/update", updateData)    //更新数据
	r.DELETE("/delete", deleteData) //删除数据
	r.Run(":8080")
}

// 插入数据
func insertData(c *gin.Context) {
	var u SqlUser
	err := c.Bind(&u)
	if err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "参数错误"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlStr := "insert into person(name, age) values(?,?)"
	_, err = sqlDb.Exec(sqlStr, u.Name, u.Age)
	if err != nil {
		fmt.Printf("insert failed, error: %v\n", err)
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "插入数据失败"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "插入数据成功"
	sqlResponse.Data = "ok"
	c.JSON(http.StatusOK, sqlResponse)
	return
}

func getData(c *gin.Context) {
	name := c.Query("name")
	sqlStr := "select name,age from person where name=?"
	var u SqlUser
	err := sqlDb.QueryRow(sqlStr, name).Scan(&u.Name, &u.Age)
	if err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "查询错误"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	u.Name = name
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "读取成功"
	sqlResponse.Data = u
	c.JSON(http.StatusOK, sqlResponse)

}

func getAllData(c *gin.Context) {
	name := c.Query("name")
	sqlStr := "select name,age from person where name=?"
	rows, err := sqlDb.Query(sqlStr, name)
	if err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "查询错误"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	defer rows.Close()
	resUser := make([]SqlUser, 0)
	for rows.Next() {
		var userTemp SqlUser
		rows.Scan(&userTemp.Name, &userTemp.Age)
		resUser = append(resUser, userTemp)
	}
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "读取成功"
	sqlResponse.Data = resUser
	c.JSON(http.StatusOK, sqlResponse)

}

func updateData(c *gin.Context) {
	var u SqlUser
	err := c.Bind(&u)
	if err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "参数错误"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlStr := "update person set age=? where name=?"
	ret, err := sqlDb.Exec(sqlStr, u.Age, u.Name)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "更新失败"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "更新成功"
	sqlResponse.Data = "OK"
	c.JSON(http.StatusOK, sqlResponse)
	fmt.Println(ret.LastInsertId()) //打印结果

}

func deleteData(c *gin.Context) {
	name := c.Query("name")
	var count int
	//1、先查询
	sqlStr := "select count(*) from person where name=?"
	err := sqlDb.QueryRow(sqlStr, name).Scan(&count)
	if count <= 0 || err != nil {
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "删除的数据不存在"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	//2、再删除
	delStr := "delete from person where name=?"
	ret, err := sqlDb.Exec(delStr, name)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		sqlResponse.Code = http.StatusBadRequest
		sqlResponse.Message = "删除失败"
		sqlResponse.Data = "error"
		c.JSON(http.StatusOK, sqlResponse)
		return
	}
	sqlResponse.Code = http.StatusOK
	sqlResponse.Message = "删除成功"
	sqlResponse.Data = "OK"
	c.JSON(http.StatusOK, sqlResponse)
	fmt.Println(ret.LastInsertId()) //打印结果
}