簡單的用法
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"` //創(chuàng)建時間
//Version int `xorm:"version"` //樂觀鎖
}
//定義orm引擎
var x *xorm.Engine
//創(chuàng)建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("數(shù)據(jù)庫連接失敗:", err)
}
if err := x.Sync(new(User)); err != nil {
log.Fatal("數(shù)據(jù)表同步失敗:", 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) {
//創(chuàng)建session
session := models.X.NewSession()
defer session.Close()
//創(chuàng)建事務
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")
}
需要填坑的是:這里面我使用事務一直實現(xiàn)不了回滾,再次細致閱讀文檔才發(fā)現(xiàn),
而關于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=要取的條數(shù), j=開始的位置
MSSQL 雖然執(zhí)行的結果正確,可以看到生成的分頁SQL很亂,建議直接MSSQL分頁直接用方式二寫在SQL里。其他數(shù)據(jù)庫應該是沒有問題, 如:mysql
其實本文用數(shù)據(jù)庫的版本SQL2014 是支持:OFFSET 2 ROW FETCH NEXT 10 ROW ONLY的寫法的,xorm并未識別數(shù)據(jù)庫的版本調(diào)整分頁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
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方,望不吝賜教。
您可能感興趣的文章:- 在golang xorm中使用postgresql的json,array類型的操作
- golang xorm及time.Time自定義解決json日期格式的問題
- golang xorm日志寫入文件中的操作
- go語言 xorm框架 postgresql 的用法及詳細注解
- 解決goxorm無法更新值為默認值的問題
- xorm根據(jù)數(shù)據(jù)庫生成go model文件的操作
- 使用go xorm來操作mysql的方法實例
- go xorm框架的使用