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的设置方法，这里有一个很好的教程 //www.jb51.net/article/202470.htm

补充：golang xorm MSSQL where查询案例

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

先初始化连接等...

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

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