golang使用gin框架实现restful风格对数据库的增删查改操作

依赖包的下载:
  go get github.com/jmoiron/sqlx
  go get github.com/gin-gonic/gin
  
1.目录结构:
restfulServer
Jackm1688sMacBook-Air:restfulServer JackMeng$ tree .
.
├── db
│   ├── IUser.go //数据库CURD接口设计
│   ├── User.go //用户数据结构对应user_tb表
│   ├── conn 
│   │   └── conn.go //创建数据库连接池
│   └── dao
│       └── userDao.go //对IUser接口实现类
├── go.mod
├── go.sum
├── httpServer
│   └── httpServer.go //处理http请求的类
├── main
│   └── main.go //主函数，程序入口
└── redisUtil 

2.主要实现功能：
  2.1 添加用户
  2.2 更新用户
  2.3 删除用户
  2.4 查询用户

3.创建用户表
  CREATE TABLE `user_tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT '',
  `password` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(2) DEFAULT '男',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8；



4.实现代码:

IUser.go //数据库CURD接口设计
  package db

/**
 @func 用户操作接口
 @author:柠檬191104
 @date:2020/04/26
 */


type IUser interface {
	/**
	 *添加用户
	 */
	AddUser(u *User) (int64,error)
	/**
	 *更新用户
	 */
	UpdateUser(u *User) (int64,error)
	/**
	 根据ID删除用户
	 */
	DelUserById(id int) (int64,error)

	/**
	根据ID查询用户
	 */
	QueryUserById(id int) (*User,error)

	/**
	根据用户名查询用户
	@return 用户对象集
	 */
	QueryUserByName(username string)([]*User,error)
}



User.go //用户数据结构对应user_tb表
package db

/**
@func 对应数据库user_tb 表
@author:柠檬191104
@date:2020/04/26
*/

/*
 * CREATE TABLE user_tb(id INT PRIMARY KEY AUTO_INCREMENT
  ,username VARCHAR(30) DEFAULT ""
  ,password VARCHAR(255) NOT NULL
  ,age int
  , sex varchar(2) default "男"
 );
*/
type User struct{
	Id int `db:"id" json:"id"`
	Username string `db:"username" json:"username"`
	Password string `db:"password" json:"password"`
	Age int `db:"age" json:"age"`
	Sex string `db:"sex" json:"sex"`
}


conn.go //创建数据库连接池
  package conn

import(
	"database/sql"
	"fmt"
	_"github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

/**
@func 创建db连接池
@author:柠檬191104
@date:2020/04/26
*/

var dbx *sqlx.DB

func init(){
	var err error
	dsn := "root:abc@198920@tcp(localhost:3306)/golang"
	dbx,err = sqlx.Open("mysql",dsn)
	if err != nil {
		fmt.Printf("init db conntection instalce failed:%v\n",err)
		return
	}
}

func NewDb() *sqlx.DB{
	if dbx != nil {
		return dbx
	}
	return  dbx
}

func CloseStmt(stmt *sql.Stmt){
	if stmt != nil {
		fmt.Println("close stmt connection")
		stmt.Close()
	}
}

userDao.go //对IUser接口实现类
package dao

import (
	"github.com/jmoiron/sqlx"
	"restfulServer/db"
	"restfulServer/db/conn"
)

/**
@func IUser接口的实现
@author:柠檬191104
@date:2020/04/26
*/
var dbConn *sqlx.DB

func init()  {
	dbConn  = conn.NewDb()
}
type UserDao struct {
}

func NewUserDao() *UserDao{
	return &UserDao{}
}



/**
 *添加用户
 */
func (user *UserDao) AddUser(u *db.User) (int64,error) {
	insert := "INSERT INTO user_tb(username,password,age,sex) VALUES(?,?,?,?)"
	stmt,err := dbConn.Prepare(insert)
	if err != nil {
		return 0,err
	}
	defer conn.CloseStmt(stmt)
	result,err := stmt.Exec(u.Username,u.Password,u.Age,u.Sex)
	if err != nil {
		return 0,err
	}
	rowId,err := result.LastInsertId()
	if err != nil {
		return 0,err
	}

	return rowId,nil
}

/**
 *更新用户
 */
func (user *UserDao) UpdateUser(u *db.User) (int64,error) {
	update := "UPDATE user_tb SET username=?,password=?,age=?,sex=? WHERE id=?"
	stmt,err := dbConn.Prepare(update)
	if err != nil {
		return  0,err
	}
	defer conn.CloseStmt(stmt)
	result, err := stmt.Exec(u.Username,u.Password,u.Age,u.Sex,u.Id)
	if err != nil {
		return 0,err
	}
	affecteRow,err := result.RowsAffected()
	if err != nil {
		return 0,err
	}
	return affecteRow,nil
}

/**
根据ID删除用户
*/
func (user *UserDao) DelUserById(id int) (int64,error) {

	delete := "DELETE FROM user_tb where id=?"
	result,err := dbConn.Exec(delete,id)
	if err != nil {
		return  0,err
	}
	affectedRow, err:= result.RowsAffected()
	if err !=nil {
		return 0,err
	}
	return affectedRow,nil
}

/**
根据ID查询用户
*/
func (user *UserDao) QueryUserById(id int)(*db.User,error) {

	selectSql := "SELECT * FROM user_tb where id=?"
		var us *db.User = &db.User{}
		err := dbConn.Get(us,selectSql,id)
		if err != nil {
			return  nil,err
		}
		return  us,nil

}

/**
根据用户名查询用户
@return 用户对象集
*/
func (user *UserDao) QueryUserByName(username string) ([]*db.User,error) {

	selectMultiSql := "select * from user_tb where username=?"
	var users []*db.User =make([]*db.User,0)
	err := dbConn.Select(&users,selectMultiSql,username)
	if err != nil {
		return  nil,err
	}
	return users,nil
}
 


httpServer.go //处理http请求的类
  package httpServer

import (
	"fmt"
	"github.com/gin-gonic/gin"
	"restfulServer/db"
	"restfulServer/db/dao"
	"strconv"
)

/**
@func 处理http请求
@author:柠檬191104
@date:2020/04/26
*/

type userParams struct{
	User db.User `json:"user"`
}

type myHttpServer struct {
}

func NewMyHttpServer() *myHttpServer {
	return &myHttpServer{}
}

var userDao db.IUser = dao.NewUserDao()

func (h *myHttpServer) StartServer(server string) {


	engine := gin.Default()
	//查询用户,可以根据用户名或id来查询
	engine.GET("/user/dao", func(c *gin.Context) {

		id := c.Query("id")
		if id != "" {
			idValue, err := strconv.Atoi(id)
			if err != nil {
				idValue = 0
			}
			var users []*db.User
			us, err := userDao.QueryUserById(idValue)
			if err != nil {
				users = append(users, nil)
			} else {
				users = append(users, us)
			}
			c.JSON(200, gin.H{
				"status": "ok",
				"users":  users,
				"error":  err,
			})
		} else {
			username := c.Query("username")
			us, err := userDao.QueryUserByName(username)
			c.JSON(200, gin.H{
				"status": "ok",
				"users":  us,
				"error":  err,
			})
		}
	})

	//处理添加用户请求
	engine.POST("/user/dao", func(c *gin.Context) {
		c.Header("content-Type","application/json")
		var user *db.User = &db.User{}
		err := c.BindJSON(user)
		fmt.Printf("get user data:%#v\n",user)
		if err != nil {
			c.JSON(200,gin.H{
				"status":"failed",
				"error":err,
			})
			return
		}
		rowId,err := userDao.AddUser(user)
		c.JSON(200,gin.H{
			"status":"ok",
			"error":err,
			"rowId":rowId,
		})
	})

	//处理更新用户请求
	engine.PUT("/user/dao", func(c *gin.Context) {
		c.Header("content-Type","application/json")
		var user *db.User = &db.User{}
		err := c.BindJSON(user)
		if err != nil {
			c.JSON(200,gin.H{
				"status":"failed",
				"error":err,
			})
			return
		}
		rowId,err := userDao.UpdateUser(user)
		c.JSON(200,gin.H{
			"status":"ok",
			"error":err,
			"affectedRow":rowId,
		})
	})

	engine.DELETE("/user/dao", func(c *gin.Context) {
		idStr := c.Query("id")
		if idStr == ""{
			idStr = "0"
		}
		id,err := strconv.Atoi(idStr)
		if err != nil {
			id = 0
		}
		afftecRow,err := userDao.DelUserById(id)
		c.JSON(200,gin.H{
			"status":"ok",
			"error":err,
			"affectedRow":afftecRow,
		})
	})

	engine.Run(server) //启动监听程序
}

main.go //主函数，程序入口
  package main

import (
	"restfulServer/httpServer"
)

func main(){

	myHttpServer := httpServer.NewMyHttpServer()

	myHttpServer.StartServer(":8080")
}

5.验证结果
通过get方法来请求
http://127.0.0.1:8080/user/dao?id=2

验证添加用户:
使用POST方法请求

验证更新方法
使用PUT方法请求

验证删除操作
使用DELETE请求方法

以上通过GET,POST,PUT,DELETE等方法完成了restful api的请求操作