当前位置: 首页 > 工具软件 > go-xorm > 使用案例 >

Go XORM学习

单于煌
2023-12-01

XORM

一个简答强大的Go语言ORM库。
操作数据库的东西。
官网:https://xorm.io/

文档:https://gitea.com/xorm/xorm/src/branch/master/README_CN.md

安装

请确保你的 Go 版本在 1.8+ 以上可以如下安装。
三选一

go get xorm.io/xorm
go get -u github.com/xormplus/xorm
go get github.com/go-xorm/xorm

下载mysql的驱动:

go get -u github.com/go-sql-driver/mysql

同步结构体到数据库

package main  
  
import (  
   "fmt"  
   _ "github.com/go-sql-driver/mysql"  
   "time"   "xorm.io/xorm")  
  
func main() {  
   var (  
      username = "root"  
      password = "root"  
      ip       = "127.0.0.1"  
      potr     = 3306  
      dbName   = "go_test"  
      charset  = "utf8mb4"  
   )  
   //1.创建数据库引擎对象  
   dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", username, password, ip, potr, dbName, charset)  
   engine, err := xorm.NewEngine("mysql", dataSourceName)  
  
   if err != nil {  
      panic(err.Error())  
   }  
  
   //2.数据库引擎关闭  
   defer engine.Close()  
  
   type User struct {  
      Id      int64  
      Name    string  
      Salt    string  
      Age     int  
      Passwd  string    `xorm:"varchar(200)"`  
      Created time.Time `xorm:"created"`  
      Updated time.Time `xorm:"updated"`  
   }  
   err1 := engine.Sync(new(User))  
   if err1 != nil {  
      panic(err1.Error())  
   }  
}

数据插入

  • insert 插入数据
package main  
  
import (  
   "fmt"  
   _ "github.com/go-sql-driver/mysql"  
   "time"   "xorm.io/xorm")  
  
func main() {  
   var (  
      username = "root"  
      password = "root"  
      ip       = "127.0.0.1"  
      potr     = 3306  
      dbName   = "go_test"  
      charset  = "utf8mb4"  
   )  
   //1.创建数据库引擎对象  
   dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", username, password, ip, potr, dbName, charset)  
   engine, err := xorm.NewEngine("mysql", dataSourceName)  
  
   if err != nil {  
      panic(err.Error())  
   }  
  
   //2.数据库引擎关闭  
   defer engine.Close()  
  
   type User struct {  
      Id      int64  
      Name    string  
      Salt    string  
      Age     int  
      Passwd  string    `xorm:"varchar(200)"`  
      Created time.Time `xorm:"created"`  
      Updated time.Time `xorm:"updated"`  
   }  
   err1 := engine.Sync(new(User))  
   if err1 != nil {  
      panic(err1.Error())  
   }  
  
   // 插入数据  返回值:受影响的行数  下面是插入单条数据的  
   user := User{  
      Id:     10,  
      Name:   "simple",  
      Age:    10,  
      Passwd: "simple",  
   }  
   n, _ := engine.Insert(&user)  
  
   if n >= 1 {  
      fmt.Println("数据插入成功")  
   }  
  
   //插入多个数据  
  
   user1 := User{  
      Id:     2,  
      Name:   "simple",  
      Age:    10,  
      Passwd: "simple",  
   }  
   user2 := User{  
      Id:     3,  
      Name:   "simple",  
      Age:    10,  
      Passwd: "simple",  
   }  
   user3 := User{  
      Id:     4,  
      Name:   "simple",  
      Age:    10,  
      Passwd: "simple",  
   }  
   n, _ = engine.Insert(&user1, &user2, &user3)  
   fmt.Println(n)  
  
   //User切片的添加。  
   var users []User  
   users = append(users, User{  
      Id:     5,  
      Name:   "simple",  
      Age:    10,  
      Passwd: "simple",  
   })  
   users = append(users, User{  
      Id:     6,  
      Name:   "simple",  
      Age:    10,  
      Passwd: "simple",  
   })  
   users = append(users, User{  
      Id:     7,  
      Name:   "simple",  
      Age:    10,  
      Passwd: "simple",  
   })  
   users = append(users, User{  
      Id:     8,  
      Name:   "simple",  
      Age:    10,  
      Passwd: "simple",  
   })  
  
   n, _ = engine.Insert(&users)  
  
   fmt.Println(n)  
  
}

更新与删除

  • update更新数据,默认只更新非空和非0的字段
  • Delete删除记录 , 需要注意,删除必须至少有一个条件,否则会报错。
  • Exec执行一个SQL语句。
package main  
  
import (  
   "fmt"  
   _ "github.com/go-sql-driver/mysql"  
   "time"   "xorm.io/xorm")  
  
func main() {  
   var (  
      username = "root"  
      password = "root"  
      ip       = "127.0.0.1"  
      potr     = 3306  
      dbName   = "go_test"  
      charset  = "utf8mb4"  
   )  
   //1.创建数据库引擎对象  
   dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", username, password, ip, potr, dbName, charset)  
   engine, err := xorm.NewEngine("mysql", dataSourceName)  
  
   if err != nil {  
      panic(err.Error())  
   }  
  
   //2.数据库引擎关闭  
   defer engine.Close()  
  
   type User struct {  
      Id      int64  
      Name    string  
      Salt    string  
      Age     int  
      Passwd  string    `xorm:"varchar(200)"`  
      Created time.Time `xorm:"created"`  
      Updated time.Time `xorm:"updated"`  
   }  
   err1 := engine.Sync(new(User))  
   if err1 != nil {  
      panic(err1.Error())  
   }  
   //更新  
   user := User{Name: "complex"}  
   n, _ := engine.ID(1).Update(&user)  
   //把id为1的Name变成complex  
   fmt.Println(n)  
   //删除  
   user = User{Name: "simple"}  
   n, _ = engine.ID(10).Delete(&user)  
   //把id为10的且名字是simple的删除。  
   fmt.Println(n)  
  
   //     Exec() 执行SQL语句  
   engine.Exec("update user set age = ? where id = ?", 11, 1)  
}

条件查询和遍历输出

  • Query最原始的与支持SQL语句查询,返回的结构类型为[]map[string][]byte。QueryString返回[]map[string]string。QueryInterface返回[]map[string]interface{}
  • GET 查询单条记录
  • Find 查询多条记录
  • Count 获取记录的条数
  • Iterate和Rows根据条件遍历数据。
package main  
  
import (  
   "fmt"  
   _ "github.com/go-sql-driver/mysql"  
   "time"   "xorm.io/xorm")  
  
func main() {  
   var (  
      username = "root"  
      password = "root"  
      ip       = "127.0.0.1"  
      potr     = 3306  
      dbName   = "go_test"  
      charset  = "utf8mb4"  
   )  
   //1.创建数据库引擎对象  
   dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", username, password, ip, potr, dbName, charset)  
   engine, err := xorm.NewEngine("mysql", dataSourceName)  
  
   if err != nil {  
      panic(err.Error())  
   }  
  
   //2.数据库引擎关闭  
   defer engine.Close()  
  
   type User struct {  
      Id      int64  
      Name    string  
      Salt    string  
      Age     int  
      Passwd  string    `xorm:"varchar(200)"`  
      Created time.Time `xorm:"created"`  
      Updated time.Time `xorm:"updated"`  
   }  
   err1 := engine.Sync(new(User))  
   if err1 != nil {  
      panic(err1.Error())  
   }  
  
   //查询  
   //results1, _ := engine.Query("select * from user")  
   //fmt.Println(results1)   //results2, _ := engine.QueryString("select * from user")   //fmt.Println(results2)   //results3, _ := engine.QueryInterface("select * from user")   //fmt.Println(results3)  
   // Get 只能查询单条。  
   user := User{}  
   engine.Get(&user)  
   //select * from user limit 1  
   fmt.Println(user)  
  
   // 指定条件来查询用户  
   user1 := User{  
      Name: "complex",  
   }  
   engine.Where("name=?", user1.Name).Desc("id").Get(&user1)  
   fmt.Println(user1)  
  
   // 只查询某个字段  
   var name string  
   engine.Table(&user).Where("id = 1").Cols("name").Get(&name)  
   fmt.Println(name)  
  
   // Find查询多条记录  
   var users []User  
   err = engine.Where("age > 1").Find(&users)  
   if err != nil {  
      panic(err.Error())  
   }  
   fmt.Println(users)  
   // count 获取记录条数  
   user = User{  
      Passwd: "simple",  
   }  
   count, err := engine.Count(&user)  
   if err != nil {  
      panic(err.Error())  
   }  
   fmt.Println(count)  
   // 遍历数据表中的信息  
   engine.Iterate(&User{Passwd: "simple"}, func(idx int, bean interface{}) error {  
      user := bean.(*User)  
      fmt.Println(user)  
      return nil  
   })  
  
   rows, err := engine.Rows(&User{Passwd: "simple"})  
   defer rows.Close()  
   userBean := new(User)  
   for rows.Next() {  
      rows.Scan(userBean)  
      fmt.Println(userBean)  
   }  
}

事务处理

package main  
  
import (  
   "fmt"  
   _ "github.com/go-sql-driver/mysql"  
   "time"   "xorm.io/xorm")  
  
func main() {  
   var (  
      username = "root"  
      password = "root"  
      ip       = "127.0.0.1"  
      potr     = 3306  
      dbName   = "go_test"  
      charset  = "utf8mb4"  
   )  
   //1.创建数据库引擎对象  
   dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", username, password, ip, potr, dbName, charset)  
   engine, err := xorm.NewEngine("mysql", dataSourceName)  
  
   if err != nil {  
      panic(err.Error())  
   }  
  
   //2.数据库引擎关闭  
   defer engine.Close()  
  
   type User struct {  
      Id      int64  
      Name    string  
      Salt    string  
      Age     int  
      Passwd  string    `xorm:"varchar(200)"`  
      Created time.Time `xorm:"created"`  
      Updated time.Time `xorm:"updated"`  
   }  
   err1 := engine.Sync(new(User))  
   if err1 != nil {  
      panic(err1.Error())  
   }  
   // 事务  
   session := engine.NewSession()  
   defer session.Close()  
   //开启事务  
   session.Begin()  
   user1 := User{  
      Id:      9,  
      Name:    "simple9",  
      Salt:    "s",  
      Age:     12,  
      Passwd:  "simple",  
      Created: time.Time{},  
      Updated: time.Time{},  
   }  
   defer func() {  
      err := recover()  
      if err != nil {  
         println(err)  
         fmt.Println("Rollback")  
         session.Rollback()  
      } else {  
         session.Commit()  
      }  
   }()  
   _, err = session.Insert(&user1)  
   if err != nil {  
      panic(err)  
   }  
   user2 := User{  
      Name: "complexxxx",  
   }  
   _, err = session.Where("id = 3").Update(&user2)  
   if err != nil {  
      panic(err)  
   }  
  
}
 类似资料: