【GoWeb框架初探————XORM篇】

1. XORM

xorm 是一个简单而强大的Go语言ORM库. 通过它可以使数据库操作非常简便。

1.1 特性

• 支持 Struct 和数据库表之间的灵活映射，并支持自动同步
• 事务支持
• 同时支持原始SQL语句和ORM操作的混合执行
• 使用连写来简化调用
• 支持使用ID, In, Where, Limit, Join, Having, Table, SQL, Cols等函数和结构体等方式作为条件
• 支持级联加载Struct
• Schema支持（仅Postgres）
• 支持缓存
• 通过 xorm.io/reverse 支持根据数据库自动生成 xorm 结构体
• 支持记录版本（即乐观锁）
• 通过 xorm.io/builder 内置 SQL Builder 支持
• 上下文缓存支持
• 支持日志上下文

1.2 驱动支持

目前支持的Go数据库驱动和对应的数据库如下：

• Mysql5.* / Mysql8.* / Mariadb / Tidb
• Postgres / Cockroach
• SQLite
• MsSql
• Oracle

1.3 安装

go get xorm.io/xorm
1

2. 特性功能

2.1 Struct和数据库表映射

func main() {
	// 数据库连接基本信息
	var (
		userName  string = "root"
		password  string = "password"
		ipAddress string = "127.0.0.1"
		port      int    = 3306
		dbName    string = "go_test"
		charset   string = "utf8mb4"
	)
	// 构建数据库连接信息
	conn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)

	engine, err := xorm.NewEngine("mysql", conn)
	if err != nil {
		fmt.Println("数据库连接失败", err)
	}

	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"`
	}
	// 表同步，以后修改User的结构后只需再执行一遍即可
	err = engine.Sync(new(User))
	if err != nil {
		fmt.Println("表结构同步失败", err)
	}
}
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

2.2 数据库的基本操作

2.3.1 数据插入

在数据插入的时候，我们应该要使用地址类型，这样方便数据库进行一些回写操作。

user := pojo.User{
		Id:     123456,
		Name:   "gzh",
		Age:    23,
		Passwd: "123456",
	}
// 插入一个对象
// INSERT INTO struct () values ()
n, err := engine.Insert(&user)
// 一次性插入多个
// INSERT INTO struct1 () values ()
// INSERT INTO struct2 () values ()
n, err := engine.Insert(&user1，&user2)
// 插入一个切片
// INSERT INTO struct () values (),(),()
var users []User
users = append(users,user1)
users = append(users,user2)
n, err := engine.Insert(&users)
// 通过map插入数据
// INSERT INTO user (name, age) values (?,?)
affected, err := engine.Table("user").Insert(map[string]interface{}{
    "name": "lunny",
    "age": 18,
})
// 插入列表map数据
// INSERT INTO user (name, age) values (?,?),(?,?)
affected, err := engine.Table("user").Insert([]map[string]interface{}{
    {
        "name": "lunny",
        "age": 18,
    },
    {
        "name": "lunny2",
        "age": 19,
    },
})
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

2.3.2 数据查找

2.3.2.1 Get查询单条记录

has, err := engine.Get(&user)
// SELECT * FROM user LIMIT 1

has, err := engine.Where("name = ?", name).Desc("id").Get(&user)
// SELECT * FROM user WHERE name = ? ORDER BY id DESC LIMIT 1

// 查询一部分/全部属性
var name string
has, err := engine.Table(&user).Where("id = ?", id).Cols("name").Get(&name)
// SELECT name FROM user WHERE id = ?

var id int64
has, err := engine.Table(&user).Where("name = ?", name).Cols("id").Get(&id)
has, err := engine.SQL("select id from user").Get(&id)
// SELECT id FROM user WHERE name = ?

var id int64
var name string
has, err := engine.Table(&user).Cols("id", "name").Get(&id, &name)
// SELECT id, name FROM user LIMIT 1

var valuesMap = make(map[string]string)
has, err := engine.Table(&user).Where("id = ?", id).Get(&valuesMap)
// SELECT * FROM user WHERE id = ?

var valuesSlice = make([]interface{}, len(cols))
has, err := engine.Table(&user).Where("id = ?", id).Cols(cols...).Get(&valuesSlice)
// SELECT col1, col2, col3 FROM user WHERE id = ?
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

2.3.2.2 检测记录是否存在

// 检验数据表中是否存在数据
has, err := testEngine.Exist(new(RecordExist))
// SELECT * FROM record_exist LIMIT 1

// 检查数据表中是否存在满足指定条件的数据
has, err = testEngine.Exist(&RecordExist{
		Name: "test1",
	})
// SELECT * FROM record_exist WHERE name = ? LIMIT 1

has, err = testEngine.Where("name = ?", "test1").Exist(&RecordExist{})
// SELECT * FROM record_exist WHERE name = ? LIMIT 1

has, err = testEngine.SQL("select * from record_exist where name = ?", "test1").Exist()
// select * from record_exist where name = ?

has, err = testEngine.Table("record_exist").Exist()
// SELECT * FROM record_exist LIMIT 1

has, err = testEngine.Table("record_exist").Where("name = ?", "test1").Exist()
// SELECT * FROM record_exist WHERE name = ? LIMIT 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2.3.2.3 Find 查询多条记录

var users []User
err := engine.Where("name = ?", name).And("age > 10").Limit(10, 0).Find(&users)
// SELECT * FROM user WHERE name = ? AND age > 10 limit 10 offset 0

type Detail struct {
    Id int64
    UserId int64 `xorm:"index"`
}

type UserDetail struct {
    User `xorm:"extends"`
    Detail `xorm:"extends"`
}

var users []UserDetail
err := engine.Table("user").Select("user.*, detail.*").
    Join("INNER", "detail", "detail.user_id = user.id").
    Where("user.name = ?", name).Limit(10, 0).
    Find(&users)
// SELECT user.*, detail.* FROM user INNER JOIN detail WHERE user.name = ? limit 10 offset 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.3.2.4 Iterate 和 Rows 根据条件遍历数据库

Iterate

err := engine.Iterate(&User{Name:name}, func(idx int, bean interface{}) error {
    user := bean.(*User)
    return nil
})
// SELECT * FROM user

err := engine.BufferSize(100).Iterate(&User{Name:name}, func(idx int, bean interface{}) error {
    user := bean.(*User)
    return nil
})
// SELECT * FROM user Limit 0, 100
// SELECT * FROM user Limit 101, 100
1
2
3
4
5
6
7
8
9
10
11
12

Rows

rows, err := engine.Rows(&User{Name:name})
// SELECT * FROM user
defer rows.Close()
bean := new(Struct)
for rows.Next() {
    err = rows.Scan(bean)
}
//===============或者==================
rows, err := engine.Cols("name", "age").Rows(&User{Name:name})
// SELECT * FROM user
defer rows.Close()
for rows.Next() {
    var name string
    var age int
    err = rows.Scan(&name, &age)
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.3.3 数据更新

Update 更新数据，除非使用Cols,AllCols函数指明，默认只更新非空和非0的字段

affected, err := engine.ID(1).Update(&user)
// UPDATE user SET ... Where id = ?

affected, err := engine.Update(&user, &User{Name:name})
// UPDATE user SET ... Where name = ?

var ids = []int64{1, 2, 3}
affected, err := engine.In(ids).Update(&user)
// UPDATE user SET ... Where id IN (?, ?, ?)

// force update indicated columns by Cols
affected, err := engine.ID(1).Cols("age").Update(&User{Name:name, Age: 12})
// UPDATE user SET age = ?, updated=? Where id = ?

// force NOT update indicated columns by Omit
affected, err := engine.ID(1).Omit("name").Update(&User{Name:name, Age: 12})
// UPDATE user SET age = ?, updated=? Where id = ?

affected, err := engine.ID(1).AllCols().Update(&user)
// UPDATE user SET name=?,age=?,salt=?,passwd=?,updated=? Where id = ?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.3.4 数据删除

Delete 删除记录，需要注意，删除必须至少有一个条件，否则会报错。要清空数据库可以用EmptyTable

affected, err := engine.Where(...).Delete(&user)
// DELETE FROM user Where ...

affected, err := engine.ID(2).Delete(&user)
// DELETE FROM user Where id = ?

affected, err := engine.Table("user").Where(...).Delete()
// DELETE FROM user WHERE ...
1
2
3
4
5
6
7
8

2.3.5 其他

2.3.5.1 Count 获取记录条数

counts, err := engine.Count(&user)
// SELECT count(*) AS total FROM user
1
2

2.3.5.2 Sum 求和函数

agesFloat64, err := engine.Sum(&user, "age")
// SELECT sum(age) AS total FROM user

agesInt64, err := engine.SumInt(&user, "age")
// SELECT sum(age) AS total FROM user

sumFloat64Slice, err := engine.Sums(&user, "age", "score")
// SELECT sum(age), sum(score) FROM user

sumInt64Slice, err := engine.SumsInt(&user, "age", "score")
// SELECT sum(age), sum(score) FROM user

1
2
3
4
5
6
7
8
9
10
11
12

2.3.5.3 条件编辑器

err := engine.Where(builder.NotIn("a", 1, 2).And(builder.In("b", "c", "d", "e"))).Find(&users)
// SELECT id, name ... FROM user WHERE a NOT IN (?, ?) AND b IN (?, ?, ?)
1
2

2.3 事务支持

2.3.1 事务的使用

使用事务可以使得多个sql语句具有原子性。

session := engine.NewSession()
defer session.Close()

// 开启事务
if err := session.Begin(); err != nil {
    // if returned then will rollback automatically
    // 如果发生错误会自动回滚
    return err
}

user1 := Userinfo{Username: "xiaoxiao", Departname: "dev", Alias: "lunny", Created: time.Now()}
if _, err := session.Insert(&user1); err != nil {
    return err
}

user2 := Userinfo{Username: "yyy"}
if _, err := session.Where("id = ?", 2).Update(&user2); err != nil {
    return err
}

if _, err := session.Exec("delete from userinfo where username = ?", user2.Username); err != nil {
    return err
}

// 提交事务
return session.Commit()
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

2.3.2 事务的简洁写法

res, err := engine.Transaction(func(session *xorm.Session) (interface{}, error) {
    user1 := Userinfo{Username: "xiaoxiao", Departname: "dev", Alias: "lunny", Created: time.Now()}
    if _, err := session.Insert(&user1); err != nil {
        return nil, err
    }

    user2 := Userinfo{Username: "yyy"}
    if _, err := session.Where("id = ?", 2).Update(&user2); err != nil {
        return nil, err
    }

    if _, err := session.Exec("delete from userinfo where username = ?", user2.Username); err != nil {
        return nil, err
    }
    return nil, nil
})
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16