golang中xorm的基本使用说明

脚本专栏 2024/12/26 佚名

3 2 1

简单的用法

package main 
import (
 _ "github.com/go-sql-driver/mysql"
 "github.com/go-xorm/xorm"
 "log"
)
 
//定义结构体(xorm支持双向映射)
type User struct {
 User_id int64 `xorm:"pk autoincr"` //指定主键并自增
 Name string `xorm:"unique"` //唯一的
 Balance float64
 Time int64 `xorm:"updated"` //修改后自动更新时间
 Creat_time int64 `xorm:"created"` //创建时间
 //Version int `xorm:"version"` //乐观锁
}
 
//定义orm引擎
var x *xorm.Engine 
//创建orm引擎
func init() {
 var err error
 x, err = xorm.NewEngine("mysql", "root:root@tcp(127.0.0.1:3306)/xorm")
 if err != nil {
 log.Fatal("数据库连接失败:", err)
 }
 if err := x.Sync(new(User)); err != nil {
 log.Fatal("数据表同步失败:", err)
 }
}
 
//增
func Insert(name string, balance float64) (int64, bool) {
 user := new(User)
 user.Name = name
 user.Balance = balance
 affected, err := x.Insert(user)
 if err != nil {
 return affected, false
 }
 return affected, true
}
 
//删
func Del(id int64) {
 user := new(User)
 x.Id(id).Delete(user)
}
 
//改
func update(id int64, user *User) bool {
 affected, err := x.ID(id).Update(user)
 if err != nil {
 log.Fatal("错误:", err)
 }
 if affected == 0 {
 return false
 }
 return true
}
 
//查
func getinfo(id int64) *User {
 user := &User{User_id: id}
 is, _ := x.Get(user)
 if !is {
 log.Fatal("搜索结果不存在!")
 }
 return user
}

在gin中的用法

package main 
import (
 "github.com/gin-gonic/gin"
 "./models"
 "strconv"
)
 
//添加操作
func insert(c *gin.Context) {
 name := c.Query("name")
 if name == "" {
 c.JSON(200, gin.H{"msg": "name不得为空!"})
 return
 }
 
 money := c.Query("money")
 if money == "" {
 c.JSON(200, gin.H{"msg": "money不得为空!"})
 return
 }
 
 Balance, _ := strconv.ParseFloat(money, 64)
 //添加
 user := models.User{}
 user.Name = name
 user.Balance = Balance
 rel, err := models.X.Insert(user) 
 if rel == 0 || err != nil {
 c.JSON(200, gin.H{"msg": "添加错误", "err": err, "rel": rel})
 } else {
 c.JSON(200, gin.H{"msg": "添加成功"})
 }
}
 
//查询单个操作
func get(c *gin.Context) {
 id := c.Query("id")
 if id == "" {
 c.JSON(200, gin.H{"msg": "id不得为空!"})
 return
 }
 //string转int64
 ids, _ := strconv.ParseInt(id, 10, 64)
 //查询1
 //user := &User{User_id: ids}
 //rel, err := models.X.Get(user)
 //查询2
 user := &models.User{}
 rel, err := models.X.Where("user_id = ", ids).Get(user)
 
 if !rel || err != nil {
 c.JSON(200, gin.H{"msg": "查询错误"})
 } else {
 c.JSON(200, gin.H{"user": user})
 }
}
 
//查询多条操作
func find(c *gin.Context) {
 users := make(map[int64]models.User)
 err := models.X.Find(&users)
 if err != nil {
 c.JSON(200, gin.H{"msg": err})
 }
 c.JSON(200, gin.H{"msg": users})
}
 
//修改操作
func updates(c *gin.Context) {
 id := c.Query("id")
 if id == "" {
 c.JSON(200, gin.H{"msg": "id1不得为空!", "id": id})
 return
 }
 ids, _ := strconv.ParseInt(id, 10, 64) 
 name := c.Query("name")
 if name == "" {
 c.JSON(200, gin.H{"msg": "name不得为空!"})
 return
 }
 
 balance := c.Query("balance")
 if balance == "" {
 c.JSON(200, gin.H{"msg": "balance不得为空!"})
 return
 }
 money, _ := strconv.ParseFloat(balance, 64)
 //修改
 user := models.User{}
 user.Name = name
 user.Balance = money
 rel, err := models.X.Id(ids).Update(user)
 
 if rel == 0 || err != nil {
 c.JSON(200, gin.H{"msg": "修改错误!", "rel": rel, "err": err, "user": user})
 } else {
 c.JSON(200, gin.H{"mag": "修改成功"})
 }
}
 
//删除操作
func delte(c *gin.Context) {
 id := c.Query("id")
 if id == "" {
 c.JSON(200, gin.H{"msg": "id1不得为空!", "id": id})
 return
 }
 //string转化int64
 ids, _ := strconv.ParseInt(id, 10, 64)
 //删除
 user := models.User{}
 rel, err := models.X.Id(ids).Delete(user)
 
 if rel == 0 || err != nil {
 c.JSON(200, gin.H{"msg": "删除错误!", "rel": rel, "err": err, "user": user})
 } else {
 c.JSON(200, gin.H{"mag": "删除成功"})
 }
}
 
//事务的提交以及回滚
func gun(c *gin.Context) {
 //创建session
 session := models.X.NewSession()
 defer session.Close()
 //创建事务
 err := session.Begin()
 if err != nil {
 c.JSON(200, gin.H{"err": err})
 return
 }
 //操作事务,失败并回滚(模拟购物车结算情景)
 car_id := c.Query("car_id")
 if car_id == "" {
 c.JSON(200, gin.H{"msg": "car_id1不得为空!", "car_id": car_id})
 return
 }
 
 //查找购物车中的商品id
 ids, _ := strconv.ParseInt(car_id, 10, 64)
 car := &models.Car{Car_id: ids}
 models.X.Get(car)
 
 /**
 * goods表库存减去销量
 */
 //查询商品
 goods := &models.Goods{Goods_id: car.Goods_id}
 models.X.Get(goods)
 //更新库存
 good := models.Goods{}
 good.Stock = goods.Stock - car.Num
 rel4, err4 := session.ID(car.Goods_id).Update(good)
 if rel4 == 0 || err4 != nil {
 session.Rollback()
 c.JSON(200, gin.H{"err4": err4, "rel4": rel4, "carid": car.Goods_id, "goodsid": goods.Goods_id, "Stock": good.Stock})
 return
 }
 
 /**
 * 用户扣费
 */
 //查询用户
 user := &models.User{User_id: car.User_id}
 models.X.Get(user)
 //更新价格
 user_up := models.User{}
 user_up.Balance = user.Balance - car.Total_price
 rel1, err1 := session.ID(car.User_id).Update(user_up)
 if err1 != nil || rel1 == 0 {
 session.Rollback()
 c.JSON(200, gin.H{"err1": err1, "rel1": rel1})
 return
 }
 
 /**
 * 删除用户的购物车信息
 */
 rel2, err2 := session.Delete(car)
 if err2 != nil || rel2 == 0 {
 session.Rollback()
 c.JSON(200, gin.H{"err2": err2, "rel2": rel2})
 return
 }
 if user_up.Balance <= 0 {
 session.Rollback()
 c.JSON(200, gin.H{"msg": "余额不足"})
 return
 }
 
 err3 := session.Commit()
 if err3 != nil {
 c.JSON(200, gin.H{"err3": err3})
 return
 }
 c.JSON(200, gin.H{"msg": "用户扣费成功"})
}
 
func update_goods(c *gin.Context) {
 id := c.Query("id")
 if id == "" {
 c.JSON(200, gin.H{"msg": "id1不得为空!", "id": id})
 return
 }
 //string转换int64
 ids, err := strconv.ParseInt(id, 10, 64)
 
 goods_name := c.Query("goods_name")
 if goods_name == "" {
 c.JSON(200, gin.H{"msg": "goods_name不得为空!", "goods_name": goods_name})
 return
 }
 
 price := c.Query("price")
 if price == "" {
 c.JSON(200, gin.H{"msg": "price不得为空!", "price": price})
 return
 }
 prices, _ := strconv.ParseFloat(price, 64)
 
 stock := c.Query("stock")
 if stock == "" {
 c.JSON(200, gin.H{"msg": "stock不得为空!", "stock": stock})
 return
 }
 stocks, _ := strconv.ParseInt(stock, 10, 64)
 
 //修改
 goods := models.Goods{}
 goods.Stock = stocks
 goods.Goods_name = goods_name
 goods.Price = prices
 rel, err := models.X.ID(ids).Update(goods)
 if rel == 0 || err != nil {
 c.JSON(200, gin.H{"msg": "修改失败", "err": err, "stocks": stocks, "goods_name": goods_name, "prices": prices, "id": id})
 } else {
 c.JSON(200, gin.H{"msg": "修改成功"})
 }
}
 
func shiwu(c *gin.Context) {
 session := models.X.NewSession()
 defer session.Close()
 
 err := session.Begin()
 user1 := models.User{Name: "xiaoxiao1", Balance: 100}
 _, err = session.Insert(&user1)
 if err != nil {
 return
 }
 
 session.Rollback()
 data := make(map[string]interface{})
 data["msg"] = "错误"
 c.JSON(200, session)
 c.JSON(200, data)
 return
 
 //提交
 err = session.Commit()
 if err != nil {
 return
 }
}
 
func main() {
 r := gin.Default()
 r.GET("/insert", insert)
 r.GET("/get", get)
 r.GET("/find", find)
 r.GET("/updates", updates)
 r.GET("/delte", delte)
 r.GET("/update_goods", update_goods)
 r.GET("/gun", gun)
 r.GET("/shiwu", shiwu)
 r.Run(":88")
}

需要填坑的是：这里面我使用事务一直实现不了回滚，再次细致阅读文档才发现,

而关于innodb的设置方法，这里有一个很好的教程 //www.jb51.net/article/202470.htm

补充：golang xorm MSSQL where查询案例

xorm官方中文文档参考 http://xorm.io/docs/

以sqlserver为例

先初始化连接等...

engine, err := xorm.NewEngine("mssql", "server=127.0.0.1;user id=sa;password=123456;database=dbname")
//控制台打印SQL语句
engine.ShowSQL(true)
if err != nil {
 fmt.Println(err)
}
defer engine.Close()

一、查询案例

ids := []model.MsIdcaid{} //实体定义的话自己写
engine.Cols("Id", "Address").Where("id in(2,3,4,5,6)").OrderBy("id desc,address asc").Find(&ids)
//[SQL] SELECT "Id", "Address" FROM "cdsgus" WHERE (id in(2,3,4,5,6)) ORDER BY id desc,address asc
或者直接自己写SQL
engine.SQL("SELECT Address from cdsgus where id in (2,3,4,6) order by id desc ").Find(&ids)
//[SQL] SELECT Address from cdsgus where id in (2,3,4,6) order by id desc

二、分页查询

方式一：用Limit(int i,int j) 方法， i=要取的条数， j=开始的位置

MSSQL 虽然执行的结果正确，可以看到生成的分页SQL很乱，建议直接MSSQL分页直接用方式二写在SQL里。其他数据库应该是没有问题，如：mysql

其实本文用数据库的版本SQL2014 是支持：OFFSET 2 ROW FETCH NEXT 10 ROW ONLY的写法的，xorm并未识别数据库的版本调整分页SQL

engine.Cols("Id", "Name").Where("id in(2,3,4,5,6)").OrderBy("id desc,address asc").Limit(10, 2).Find(&ids)
//[[SQL] SELECT TOP 10 "Id", "Name" FROM "cdsgus" WHERE (id in(2,3,4,5,6)) AND (id NOT IN (SELECT TOP 2 id FROM "cdsgus" WHERE (id in(2,3,4,5,6)) ORDER BY id desc,address asc)) ORDER BY id desc,address asc

方式二：用原生的SQL方法，很妥

engine.SQL("SELECT Id,Name from cdsgus where id in (2,3,4,5,6) order by id desc OFFSET 2 ROW FETCH NEXT 10 ROW ONLY").Find(&ids)
//[SQL] SELECT Id,Name from cdsgus where id in (2,3,4,5,6) order by id desc OFFSET 2 ROW FETCH NEXT 10 ROW ONLY

方式三：用原生的SQL + Limit 方法？？MSSQL居然是错误SQL&结果

engine.Sql("SELECT Id,Name from cdsgus where id in (2,3,4,5,6) ").OrderBy("id").Limit(10, 2).Find(&ids)
//[SQL] SELECT Id,Name from cdsgus where id in (2,3,4,5,6) 
data, _ := engine.Sql("SELECT Id,Name from cdsgus where id in (2,3,4,5,6) ").OrderBy("id").Limit(10, 2).Query()
//[SQL] SELECT Id,Name from cdsgus where id in (2,3,4,5,6)

方式四： github.com/go-xorm/builder

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

golang,xorm使用

标签：

golang,xorm使用

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“golang中xorm的基本使用说明”

golang中xorm的基本使用说明

暂无“golang中xorm的基本使用说明”评论...

golang中xorm的基本使用说明

简单的用法

在gin中的用法

以sqlserver为例

一、查询案例

二、分页查询

golang,xorm使用

golang时间字符串和时间戳转换的案例

golang 实现struct、json、map互相转化

评论“golang中xorm的基本使用说明”

P70系列延期，华为新旗舰将在下月发布

更新动态

友情链接