简单的用法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
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中的用法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
|
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为例
先初始化连接等...
|
1
2
3
4
5
6
7
|
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() |
一、查询案例
|
1
2
3
4
5
6
|
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
|
1
2
|
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方法 ，很妥
|
1
2
|
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&结果
|
1
2
3
4
|
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
