数据库是业务利用的外围,本节次要解说 gin 框架(Go 语言)操作原生数据库、go 语言数据库 ORM 框架(gorm 和 xorm)。
1. 原生数据库
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 init() {
//1、关上数据库
//parseTime: 工夫格局转换 (查问后果为工夫时,是否主动解析为工夫);
// loc=Local:MySQL 的时区设置
sqlStr := "root:123456@tcp(127.0.0.1:3306)/testdb?charset=utf8&parseTime=true&loc=Local"
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"`
Address string `json:"address"`
}
// 应答体(响应 client 的申请)type SqlResponse struct {
Code int `json:"code"`
Message string `json:"message"`
Data interface{} `json:"data"`}
func main() {r := gin.Default()
// 数据库的 CRUD--->gin 的 post、get、put、delete 办法
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 := 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 user 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()) // 打印后果
}
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 user set age=? ,address=? where name=?"
ret, err := sqlDb.Exec(sqlStr, u.Age, u.Address, 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 getMulData(c *gin.Context) {address:=c.Query("address")
sqlStr:="select name,age from user where address=?"
rows, err := sqlDb.Query(sqlStr, address)
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)
userTemp.Address=address
resUser=append(resUser,userTemp)
}
sqlResponse.Code = http.StatusOK
sqlResponse.Message = "读取胜利"
sqlResponse.Data=resUser
c.JSON(http.StatusOK, sqlResponse)
}
func getData(c *gin.Context) {name:=c.Query("name")
sqlStr:="select age,address from user where name=?"
var u SqlUser
err := sqlDb.QueryRow(sqlStr, name).Scan(&u.Age, &u.Address)
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 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 user(name, age, address) values (?,?,?)"
ret, err := sqlDb.Exec(sqlStr, u.Name, u.Age, u.Address)
if err != nil {fmt.Printf("insert 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()) // 打印后果
}
//todo: go-sql-driver 地址:https://github.com/go-sql-driver/mysql
2.xorm
xorm 是一个简略而弱小的 Go 语言 ORM 库,通过它能够使数据库操作十分简便。
外围代码如下:
var x *xorm.Engine
var xormResponse XormResponse
// 定义构造体 (xorm 反对双向映射);没有表,会进行创立
type Stu struct {
Id int64 `xorm:"pk autoincr" json:"id"` // 指定主键并自增
StuNum string `xorm:"unique" json:"stu_num"`
Name string `json:"name"`
Age int `json:"age"`
Created time.Time `xorm:"created" json:"created"`
Updated time.Time `xorm:"updated" json:"updated"`
}
// 应答体
type XormResponse struct {
Code int `json:"code"`
Message string `json:"msg"`
Data interface{} `json:"data"`}
func init() {sqlStr := "root:123456@tcp(127.0.0.1:3306)/xorm?charset=utf8&parseTime=true&loc=Local" //xorm 代表数据库名称
var err error
x, err = xorm.NewEngine("mysql", sqlStr) //1、创立数据库引擎
if err != nil {fmt.Println("数据库连贯失败:", err)
}
//2、创立或者同步表(名称为 Stu)err = x.Sync(new(Stu))
if err != nil {fmt.Println("数据表同步失败:", err)
}
}
func main() {r := gin.Default()
// 数据库的 CRUD--->gin 的 post、get、put、delete 办法
r.POST("xorm/insert", xormInsetData) // 增加数据
r.GET("xorm/get", xormGetData) // 查问数据(单条记录)r.GET("xorm/mulget", xormGetMulData) // 查问数据(多条记录)r.PUT("xorm/update", xormUpdateData) // 更新数据
r.DELETE("xorm/delete", xormDeleteData) // 删除数据
r.Run(":9090")
}
func xormGetData(c *gin.Context) {stuNum := c.Query("stu_num")
var stus []Stu
err := x.Where("stu_num=?", stuNum).Find(&stus)
if err != nil {
xormResponse.Code = http.StatusBadRequest
xormResponse.Message = "查问谬误"
xormResponse.Data = "error"
c.JSON(http.StatusOK, xormResponse)
return
}
xormResponse.Code = http.StatusOK
xormResponse.Message = "读取胜利"
xormResponse.Data = stus
c.JSON(http.StatusOK, xormResponse)
}
func xormInsetData(c *gin.Context) {
var s Stu
err := c.Bind(&s)
if err != nil {
xormResponse.Code = http.StatusBadRequest
xormResponse.Message = "参数谬误"
xormResponse.Data = "error"
c.JSON(http.StatusOK, xormResponse)
return
}
affected, err := x.Insert(s)
if err != nil || affected <= 0 {fmt.Printf("insert failed, err:%v\n", err)
xormResponse.Code = http.StatusBadRequest
xormResponse.Message = "写入失败"
xormResponse.Data = err
c.JSON(http.StatusOK, xormResponse)
return
}
xormResponse.Code = http.StatusOK
xormResponse.Message = "写入胜利"
xormResponse.Data = "OK"
c.JSON(http.StatusOK, xormResponse)
fmt.Println(affected) // 打印后果
}
//TODO: xom 文档地址:https://github.com/go-xorm/xorm
3.gorm
特地留神:gorm 原来的版本曾经废除,新版本进行了迁徙,地址:https://gorm.io/。新版本与旧版本有很大的区别,本节次要解说新版本。
外围代码如下:
// 特地留神:构造体名称为:Product,创立的表的名称为:Products
type Product struct {
ID int `gorm:"primaryKey;autoIncrement" json:"id"`
Number string `gorm:"unique" json:"number"` // 商品编号(惟一)Category string `gorm:"type:varchar(256);not null" json:"category"` // 商品类别
Name string `gorm:"type:varchar(20);not null" json:"name"` // 商品名称
MadeIn string `gorm:"type:varchar(128);not null" json:"made_in"` // 生产地
ProductionTime time.Time `json:"production_time"` // 生产工夫
}
// 应答体
type GormResponse struct {
Code int `json:"code"`
Message string `json:"msg"`
Data interface{} `json:"data"`}
var gormDB *gorm.DB
var gormResponse GormResponse
func init() {
var err error
sqlStr := "root:123456@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=true&loc=Local"
gormDB, err = gorm.Open(mysql.Open(sqlStr), &gorm.Config{}) // 配置项中预设了连接池 ConnPool
if err != nil {fmt.Println("数据库连贯呈现了问题:", err)
return
}
}
func main() {r := gin.Default()
// 数据库的 CRUD--->gin 的 post、get、put、delete 办法
r.POST("gorm/insert", gormInsertData) // 增加数据
r.GET("gorm/get", gormGetData) // 查问数据(单条记录)r.GET("gorm/mulget", gormGetMulData) // 查问数据(多条记录)r.PUT("gorm/update", gormUpdateData) // 更新数据
r.DELETE("gorm/delete", gormDeleteData) // 删除数据
r.Run(":9090")
}
func gormGetData(c *gin.Context) {
//============= 捕捉异样 ============
defer func() {err := recover()
if err != nil {
gormResponse.Code = http.StatusBadRequest
gormResponse.Message = "谬误"
gormResponse.Data = err
c.JSON(http.StatusBadRequest, gormResponse)
}
}()
//============
number := c.Query("number")
product := Product{}
tx := gormDB.Where("number=?", number).First(&product)
if tx.Error != nil {
gormResponse.Code = http.StatusBadRequest
gormResponse.Message = "查问谬误"
gormResponse.Data = tx.Error
c.JSON(http.StatusOK, gormResponse)
return
}
gormResponse.Code = http.StatusOK
gormResponse.Message = "读取胜利"
gormResponse.Data = product
c.JSON(http.StatusOK, gormResponse)
}
func gormInsertData(c *gin.Context) {
//============= 捕捉异样 ============
defer func() {err := recover()
if err != nil {
gormResponse.Code = http.StatusBadRequest
gormResponse.Message = "谬误"
gormResponse.Data = err
c.JSON(http.StatusBadRequest, gormResponse)
}
}()
//============
var p Product
err := c.Bind(&p)
if err != nil {
gormResponse.Code = http.StatusBadRequest
gormResponse.Message = "参数谬误"
gormResponse.Data = err
c.JSON(http.StatusOK, gormResponse)
return
}
fmt.Println(p)
tx := gormDB.Create(&p)
if tx.RowsAffected > 0 {
gormResponse.Code = http.StatusOK
gormResponse.Message = "写入胜利"
gormResponse.Data = "OK"
c.JSON(http.StatusOK, gormResponse)
return
}
fmt.Printf("insert failed, err:%v\n", err)
gormResponse.Code = http.StatusBadRequest
gormResponse.Message = "写入失败"
gormResponse.Data = tx
c.JSON(http.StatusOK, gormResponse)
fmt.Println(tx) // 打印后果
}
//Todo:GitHub 地址:https://github.com/go-gorm/gorm
// 文档地址:https://gorm.io/