f559facb-f001-463f-97ab-1a9884f3dada
xorm
xorm是一个简单而强大的Go语言ORM库通过它可以使数据库操作非常简便。
官网: https://xorm.io/
中文文档: https://gitea.com/xorm/xorm/src/branch/master/README_CN.md
特性
- 支持 Struct 和数据库表之间的灵活映射,并支持自动同步
- 事务支持
- 同时支持原始SQL语句和ORM操作的混合执行
- 使用连写来简化调用。
- 支持使用ID,In,Where, Limit,Join,Having,Table, SQL,Cols等函数和结构体等方式作为条件
- …
安装
go get xorm.io/xorm
同步结构体到数据库
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
"xorm.io/xorm"
)
func main() {
//初始化数据库连接信息
var (
userName = "root"
passWord = "xxxxx"
ipAddress = "127.0.0.1"
port = 3306
dbName = "poem_noun"
charset = "utf8mb4"
)
dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, passWord, ipAddress, port, dbName, charset)
//创建引擎,driverName, dataSourceName 和 database/sql 接口相同
engine, err := xorm.NewEngine("mysql", dataSourceName)
if err != nil {
fmt.Println("数据库连接失败")
}
//定义一个和表同步的结构体,并且自动同步结构体到数据库
type User struct {
Id int64
Name string
Age int
Passwd string `xorm:"varchar(200)"`
Created time.Time `xorm:"created"`
Updated time.Time `xorm:"updated"`
}
err = engine.Sync(new(User))
if err != nil {
println("表结构同步失败")
}
}
注:后续需要增加表的字段只需要在结构体中新增对应字段,然后执行改代码,即可把字段同步至数据库。
数据插入
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
"xorm.io/xorm"
)
func main() {
//初始化数据库连接信息
var (
userName = "root"
passWord = "xxxxx"
ipAddress = "127.0.0.1"
port = 3306
dbName = "poem_noun"
charset = "utf8mb4"
)
dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, passWord, ipAddress, port, dbName, charset)
//创建引擎,driverName, dataSourceName 和 database/sql 接口相同
engine, err := xorm.NewEngine("mysql", dataSourceName)
if err != nil {
fmt.Println("数据库连接失败")
}
type User struct {
Id int64
Name string
Age int
Passwd string `xorm:"varchar(200)"`
Created time.Time `xorm:"created"`
Updated time.Time `xorm:"updated"`
}
user := User{
Id: 1,
Name: "wk",
Age: 18,
Passwd: "wwwwwwww",
}
//插入单挑
affected, err := engine.Insert(&user)
if affected >= 1 {
println("数据插入成功")
}
//定义切片
var users []User
users = append(users, User{
Id: 2,
Name: "wk",
Age: 18,
Passwd: "wwwwwwww",
})
users = append(users, User{
Id: 3,
Name: "wk",
Age: 18,
Passwd: "wwwwwwww",
})
//插入切片
affected, err = engine.Insert(&users)
}
更新和删除
- 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 = "xxxxx"
ipAddress = "127.0.0.1"
port = 3306
dbName = "poem_noun"
charset = "utf8mb4"
)
dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, passWord, ipAddress, port, dbName, charset)
//创建引擎,driverName, dataSourceName 和 database/sql 接口相同
engine, err := xorm.NewEngine("mysql", dataSourceName)
if err != nil {
fmt.Println("数据库连接失败")
}
type User struct {
Id int64
Name string
Age int
Passwd string `xorm:"varchar(200)"`
Created time.Time `xorm:"created"`
Updated time.Time `xorm:"updated"`
}
user := User{
Name: "cs",
}
//更新一条数据
_, err = engine.ID(1).Update(&user)
user = User{
Name: "wk",
}
//删除一条数据
_, err = engine.ID(3).Delete(&user)
//直接执行sql
engine.Exec("update user set name = ? where id = ?", "ddddddd", 2)
}
数据查询
- 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 = "xxxxxx"
ipAddress = "127.0.0.1"
port = 3306
dbName = "poem_noun"
charset = "utf8mb4"
)
dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, passWord, ipAddress, port, dbName, charset)
//创建引擎,driverName, dataSourceName 和 database/sql 接口相同
engine, err := xorm.NewEngine("mysql", dataSourceName)
if err != nil {
fmt.Println("数据库连接失败")
}
type User struct {
Id int64
Name string
Age int
Passwd string `xorm:"varchar(200)"`
Created time.Time `xorm:"created"`
Updated time.Time `xorm:"updated"`
}
//查询
result, err := engine.Query("select * from user")
fmt.Println(result)
result2, err := engine.QueryString("select * from user")
fmt.Println(result2)
result3, err := engine.QueryInterface("select * from user")
fmt.Println(result3)
//Get查询方法
user := User{}
engine.Get(&user)
fmt.Println(user)
//指定条件来查询用户
user1 := User{Name: "cs"}
engine.Where("name=?", user1.Name).Asc("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
engine.Where("passwd='wwwwwwww'").Limit(10, 0).Find(&users)
fmt.Println(users)
//根据条件查询总条数
userCount := User{}
total, err := engine.Count(&userCount)
fmt.Println(total)
//Iterate 和 Rows 根据条件遍历数据
engine.Iterate(&userCount, func(idx int, bean interface{}) error {
user := bean.(*User)
fmt.Println(user)
return nil
})
rows, err := engine.Rows(&user)
defer rows.Close()
for rows.Next() {
rows.Scan(user)
fmt.Println(user)
}
}
事务
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
"xorm.io/xorm"
)
func main() {
//初始化数据库连接信息
var (
userName = "root"
passWord = "xxxxxx"
ipAddress = "127.0.0.1"
port = 3306
dbName = "poem_noun"
charset = "utf8mb4"
)
dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, passWord, ipAddress, port, dbName, charset)
//创建引擎,driverName, dataSourceName 和 database/sql 接口相同
engine, err := xorm.NewEngine("mysql", dataSourceName)
if err != nil {
fmt.Println("数据库连接失败")
}
type User struct {
Id int64
Name string
Age int
Passwd string `xorm:"varchar(200)"`
Created time.Time `xorm:"created"`
Updated time.Time `xorm:"updated"`
}
//创建事务并开启
session := engine.NewSession()
defer session.Close()
session.Begin()
defer func() {
err := recover()
if err != nil {
session.Rollback()
} else {
session.Commit()
}
}()
user := User{Id: 3, Name: "RRRR", Age: 20, Passwd: "54644"}
if _, err := session.Insert(&user); err != nil {
panic(err)
}
}