golang中xorm的基本使用说明

简单的用法

 

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?charset=utf8")
 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的设置方法，这里有一个很好的教程

补充：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

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

原文链接：https://blog.csdn.net/feiwutudou/article/details/81317558