golang原生数据库的使用

golang原生数据库的使用

废话不多说。直接上代码

package main

/**
 * @Description
 * @Author 
 * @Date 2022/8/28 14:39
 **/
import (
	"database/sql"
	"fmt"
	"github.com/gin-gonic/gin"
	_"github.com/go-sql-driver/mysql"
	"net/http"
)
var slqDb *sql.DB
var sqlResoponse SqlResponse
func init(){
	//1打开数据库
	//loc=Local：Mysql的时区设置
	DB_TYPE:="mysql"
	DB_HOST:="localhost"
	DB_PORT:="3306"
	DB_USER:="root"
	DB_NAME:="go_db"
	DB_PASSWORD:="123456"
	sqlStr:=DB_USER+":"+DB_PASSWORD+"@tcp("+DB_HOST+":"+DB_PORT+")/"+DB_NAME+"?charset=utf8&parseTime=true&loc=Local"
	var err error
	slqDb, err = sql.Open(DB_TYPE, sqlStr)
	if err!=nil{
		fmt.Println("数据库打开出现了问题:",err)
		return

	}
	//2测试与数据库建立的连接（检验连接是否正确）
	err =slqDb.Ping()
	if err!=nil{
		fmt.Println("数据库连接出现了问题:",err)
		return
	}
}


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

}

//应答体（响应客户端的请求 ）
type SqlResponse struct {
	Code int `json:"code"`
	Message string `json:"message"`
	Data interface{} `json:"data"`
}

func main() {
	r := gin.Default()
	r.POST("sql/insert",insertData)//添加数据
	r.GET("sql/get",getData)//单条记录
	r.GET("sql/mulget",getMulData)//单条记录
	r.PUT("sql/update",updateData)//更新数据
	r.DELETE("sql/delete",deleteData)

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

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

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

}
func getData(c*gin.Context){
	name:=c.Query("name")
	sqlStr:="select age,address from user where name=?"
	var u SqlUser

	err := slqDb.QueryRow(sqlStr, name).Scan(&u.Age, &u.Address)
	if err!=nil{
		sqlResoponse.Code=http.StatusBadRequest
		sqlResoponse.Message="查询错误"
		sqlResoponse.Data="error"
		c.JSON(http.StatusOK,sqlResoponse)
		return
	}
	u.Name=name
	sqlResoponse.Code=http.StatusOK
	sqlResoponse.Message="读取成功"
	sqlResoponse.Data=u
	c.JSON(http.StatusOK,sqlResoponse)
}
func insertData(c *gin.Context){
	var u SqlUser
	//绑定
	err:=c.Bind(&u)
	if err!=nil{
		sqlResoponse.Code=http.StatusBadRequest
		sqlResoponse.Message="参数错误"
		sqlResoponse.Data="error"
		c.JSON(http.StatusBadRequest,sqlResoponse)
		return
	}
	sqlStr:="insert into user(name,age,address) values (?,?,?)"
	result ,err := slqDb.Exec(sqlStr, u.Name, u.Age, u.Address)
	if err!=nil {
		fmt.Printf("insert failed,err:%v\n",err)
		sqlResoponse.Code=http.StatusBadRequest
		sqlResoponse.Message="写入失败"
		sqlResoponse.Data="error"
		c.JSON(http.StatusBadRequest,sqlResoponse)
		return


	}
	sqlResoponse.Code=http.StatusOK
	sqlResoponse.Message="写入成功"
	sqlResoponse.Data="Ok"
	c.JSON(http.StatusOK,sqlResoponse)
	fmt.Println(result.LastInsertId())//打印结果

}