您的位置:首页技术文章

golang中xorm的基本使用说明

【字号: 日期:2023-08-22 13:31:10浏览:6作者:馨心
简单的用法

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')}

golang中xorm的基本使用说明

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

而关于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或者直接自己写SQLengine.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
相关文章: