Go语言 Gin框架使用Xorm

jeikerxiao
649 阅读2分钟

官方介绍：xorm是一个简单而强大的Go语言ORM库. 通过它可以使数据库操作非常简便。xorm的目标并不是让你完全不去学习SQL，我们认为SQL并不会为ORM所替代，但是ORM将可以解决绝大部分的简单SQL需求。xorm支持两种风格的混用。

下面在Gin框架中使用Xorm实现crud操作。

0. 初始化数据库

func init() {
	sqlStr := "root:123456@tcp(127.0.0.1:3306)/mydb?charset=utf8&parseTime=true&loc=Local" //mydb代表数据库名称
	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)
	}
}

1. 插入操作

func insertData(c *gin.Context) {
	// 插入信息
	var s Stu
	err := c.Bind(&s)
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "参数错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	affected, err := x.Insert(s)
	if err != nil || affected <= 0 {
		fmt.Printf("insert failed, err:%v\n", err)
		resp.Code = http.StatusBadRequest
		resp.Message = "写入失败"
		resp.Data = err
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "写入成功"
	resp.Data = "OK"
	c.JSON(http.StatusOK, resp)
	fmt.Println(affected) //打印结果

}

29-add.png

2. 查询操作

1）查询单条数据

func getData(c *gin.Context) {
	// 获取单条数据
	stuNum := c.Query("stu_num")
	var s Stu
	err := x.Where("stu_num=?", stuNum).Find(&s)
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "查询错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "查询成功"
	resp.Data = s
	c.JSON(http.StatusOK, resp)
}

29-get.png

  1. 查询多条数据
func getAllData(c *gin.Context) {
	// 查询列表
	stuList := make([]Stu, 0)
	err := x.Where("stu_num > ? ", 1).Find(&stuList)
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "查询错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "查询成功"
	resp.Data = stuList
	c.JSON(http.StatusOK, resp)
}

29-all.png

3. 删除操作

func deleteData(c *gin.Context) {
	// 删除数据
	stuNum := c.Query("stu_num")
	affected, err := x.Delete(&Stu{StuNum: stuNum})
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "删除错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "删除成功"
	resp.Data = affected
	c.JSON(http.StatusOK, resp)

}

29-del.png

4. 修改操作

func updateData(c *gin.Context) {
	// 根据id更新用户信息
	var s Stu
	err := c.Bind(&s)
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "参数错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	affected, err := x.ID(&s.Id).Update(&s)
	// UPDATE stu SET ... Where id = ?
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "更新错误"
		resp.Data = err
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "更新成功"
	resp.Data = affected
	c.JSON(http.StatusOK, resp)
}

29-update.png

5. 整体示例

package main

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

var x *xorm.Engine
var resp Resp

// 定义结构体(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 Resp struct {
	Code    int         `json:"code"`
	Message string      `json:"msg"`
	Data    interface{} `json:"data"`
}

// 初始化数据库
func init() {
	sqlStr := "root:123456@tcp(127.0.0.1:3306)/mydb?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("/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 s Stu
	err := c.Bind(&s)
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "参数错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	affected, err := x.Insert(s)
	if err != nil || affected <= 0 {
		fmt.Printf("insert failed, err:%v\n", err)
		resp.Code = http.StatusBadRequest
		resp.Message = "写入失败"
		resp.Data = err
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "写入成功"
	resp.Data = "OK"
	c.JSON(http.StatusOK, resp)
	fmt.Println(affected) //打印结果

}

func getData(c *gin.Context) {
	// 获取单条数据
	stuNum := c.Query("stu_num")
	var s Stu
	err := x.Where("stu_num=?", stuNum).Find(&s)
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "查询错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "查询成功"
	resp.Data = s
	c.JSON(http.StatusOK, resp)
}

func getAllData(c *gin.Context) {
	// 查询列表
	stuList := make([]Stu, 0)
	err := x.Where("stu_num > ? ", 1).Find(&stuList)
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "查询错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "查询成功"
	resp.Data = stuList
	c.JSON(http.StatusOK, resp)
}

func deleteData(c *gin.Context) {
	// 删除数据
	stuNum := c.Query("stu_num")
	affected, err := x.Delete(&Stu{StuNum: stuNum})
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "删除错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "删除成功"
	resp.Data = affected
	c.JSON(http.StatusOK, resp)

}

func updateData(c *gin.Context) {
	// 根据id更新用户信息
	var s Stu
	err := c.Bind(&s)
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "参数错误"
		resp.Data = "error"
		c.JSON(http.StatusOK, resp)
		return
	}
	affected, err := x.ID(&s.Id).Update(&s)
	// UPDATE stu SET ... Where id = ?
	if err != nil {
		resp.Code = http.StatusBadRequest
		resp.Message = "更新错误"
		resp.Data = err
		c.JSON(http.StatusOK, resp)
		return
	}
	resp.Code = http.StatusOK
	resp.Message = "更新成功"
	resp.Data = affected
	c.JSON(http.StatusOK, resp)
}
avatar
文章
阅读
粉丝