package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/go-xorm/core"
"github.com/go-xorm/xorm"
"time"
)
//xorm基本操作和高级操作
func main() {
//1. 创建数据库引擎对象
//engine,err := xorm.NewEngine("mysql","%s:%s@(%s:%s)/%s?charset=utf8", username, password, host, port, database)
//engine,err := xorm.NewEngine("mysql","root:1234@(localhost:3306)/test?charset=utf8")
engine, err := xorm.NewEngine("mysql", "root:6746@/test?charset=utf8")
if err != nil {
panic(err.Error())
}
//2.设置映射规则
engine.SetMapper(core.SnakeMapper{})
//3、同步数据库表格(建表)
err = engine.Sync2(new(PersonTable))
if err != nil {
fmt.Println("error in create table person_table",err)
}
//4.判断person表格是否存在
personExist, err := engine.IsTableExist(new(PersonTable))
if err != nil {
panic(err.Error())
}
if personExist {
fmt.Println("人员表存在")
} else {
fmt.Println("人员表不存在")
}
//5.判断person表格是否为空
personEmpty, err := engine.IsTableEmpty(new(PersonTable))
if err != nil {
panic(err.Error())
}
if personEmpty {
fmt.Println(" 人员表是空的,无数据 ")
} else {
fmt.Println(" 人员表不为空,有数据 ")
}
//二、条件查询
//1.ID查询
var person PersonTable
// select * from person_table where id = 1
engine.Id(1).Get(&person) //返回两个值,布尔类型的结果和err(没有错误是nil)
fmt.Println("person.PersonName: ", person.PersonName)
//2.where多条件查询
var person1 PersonTable
// select * from person_table where person_age = 26 and person_sex = 2
//Get只会返回一条数据,如下,查询到age为26的就不查了
engine.Where(" person_age = ? and person_sex = ?", 26, 2).Get(&person1) //?占位
//Find返回所有符合条件的结果
var person2 []PersonTable
//可以使用And拆开写26和2,看下面
engine.Where(" person_age = ? and person_sex = ?", 26, 2).Find(&person2) //?占位
fmt.Println("person1.PersonName: ",person1.PersonName)
//3.And条件查询
var persons []PersonTable
//select * from person_table where person_age = 26 and person_sex = 2
err = engine.Where(" person_age = ? ", 26).And("person_sex = ? ", 2).Find(&persons)
if err != nil {
panic(err.Error())
}
fmt.Println("persons: ",persons)
var persontest []PersonTable
//select * from person_table where person_name = "yang" and person_age > 10
err = engine.Table("person_table").Where("person_name = ?","yang").And("person_age > 10").Find(&persontest)
fmt.Println("persontest: ",persontest)
//4、Or条件查询
var personArr []PersonTable
//select * from person_table where person_age = 26 or person_sex = 1
err = engine.Where(" person_age = ? ", 26).Or("person_sex = ? ", 1).Find(&personArr)
if err != nil {
panic(err.Error())
}
fmt.Println("personArr: ",personArr)
fmt.Println("----------------------------------------")
//5、原生SQL语句查询支持 like语法
var personsNative []PersonTable
err = engine.SQL(" select * from person_table where person_name like 't%' ").Find(&personsNative)
if err != nil {
panic(err.Error())
}
fmt.Println("personNative: ",personsNative)
fmt.Println()
//6、排序条件查询
var personsOrderBy []PersonTable
//select * from person_table orderby person_age 升序排列
//engine.OrderBy(" person_age ").Find(&personsOrderBy)
engine.OrderBy(" person_age desc ").Find(&personsOrderBy)
fmt.Println("personOrderBy: ",personsOrderBy)
fmt.Println("------------------------------------------")
//7、查询特定字段
var personsCols []PersonTable
engine.Cols("person_name", "person_age").Find(&personsCols)
for i, col := range personsCols {
fmt.Printf("%d ---> %v \n",i,col)
}
fmt.Println("=========================================")
//8、查询多行结果,并遍历输出
//Query(返回数据类型为[]map[string][]byte)、
//QueryString(返回数据类型为[]map[string]string)、
//QueryInterface(返回数据类型为[]map[string]interface{})
results,err := engine.QueryString("select * from person_table")
for k,v := range results {
fmt.Println(k)
for m,n := range v {
fmt.Println(m,"-->",n)
}
fmt.Println("----------------")
}
//三、增加记录操作
personInsert := PersonTable{
//字段 //值
PersonName: "Hello",
PersonAge: 18,
PersonSex: 1,
}
rowNum, err := engine.Insert(&personInsert)
fmt.Println("rowNum: ",rowNum) //rowNum:受影响的记录条数,插入了几条数据,就返回几
//四、删除操作
var person4 PersonTable
rowNum2, err := engine.Delete(&personInsert)
fmt.Println("rowNum2: ",rowNum2) //rowNum2:受影响的记录条数
affected, err := engine.ID(2).Delete(&person4) // 删除id为2的一行
fmt.Println(affected)
//五、更新操作
rowNum3, err := engine.Id(7).Update(&personInsert) //修改成personInsert对象对应的数据
fmt.Println("rowNum3: ",rowNum3) //rowNum3:受影响的记录条数
mm,err := engine.Exec("update person4 set name = ? where id = ?","yang",2) //执行更新sql语句
fmt.Println(mm)
//六、统计功能count
count, err := engine.Count(new(PersonTable)) //一个表中有多少条数据,分页中使用
fmt.Println("PersonTable表总记录条数:", count)
//七、事务操作(用于批量执行,不能只执行了一部分,而一部分没有被执行,这种情况使用回滚)
// mysql 中,数据引擎为innodb 事务才有效,myisam引擎是不支持事务的
personsArray := []PersonTable{ //初始化结构体切片
PersonTable{
PersonName: "Jack",
PersonAge: 28,
PersonSex: 1,
},
PersonTable{
PersonName: "Mali",
PersonAge: 28,
PersonSex: 1,
},
PersonTable{
PersonName: "Ruby",
PersonAge: 28,
PersonSex: 1,
},
}
//创建事务
session := engine.NewSession()
//defer session.Close() // 写成这样更合适
//事务开始: add Begin() before any action
session.Begin()
//for循环插入数据
for i := 0; i < len(personsArray); i++ {
_, err = session.Insert(personsArray[i])
if err != nil { //处理错误
//事务回滚
session.Rollback()
session.Close()
}
}
// 更新数据
person3 := PersonTable{PersonName:"yang"}
_,err = session.Where("id = ?",2).Update(&person3)
if err != nil {
session.Rollback()
session.Close()
}
//执行sql语句的删除操作
_,err = session.Exec("delete form test where name = ?",person3.PersonName)
if err != nil {
session.Rollback()
session.Close()
}
//事务提交
err = session.Commit()
session.Close()
if err != nil {
panic(err.Error())
}
}
//人员结构表
type PersonTable struct {
Id int64 `xorm:"pk autoincr"` //主键自增
PersonName string `xorm:"varchar(24)"` //可变字符
PersonAge int `xorm:"int default 0"` //默认值
PersonSex int `xorm:"notnull"` //不能为空
City CityTable `xorm:"-"` //不映射该字段
}
type CityTable struct {
CityName string
CityLongitude float32
CityLatitude float32
}
//创建事务的2 种方法
//---------------------------------------------------------------
type User struct {
Id int64
Name string
Age int
Salt string
Passwd string `xorm:"varchar(200)"`
Created time.Time `xorm:"created"`
Updated time.Time `xorm:"updated"`
}
//创建事务的2 种方法
//一种,新建session,并使用session方法实现,在main函数中调用如下方法:
func myTransaction(engine *xorm.Engine) error{
session := engine.NewSession()
defer session.Close()
if err := session.Begin(); err != nil {
// if returned then will rollback automatically
return err
}
user := User{Name: "xiaoxiao", Salt: "dev", Age: 18, Passwd: "111"}
if _, err := session.Insert(&user); err != nil {
return err
}
if _, err := session.Exec("update user set name = ? where id = ?", 70, "8"); err != nil {
return err
}
return session.Commit()
}
//第二种,重写Transaction方法,在main函数中添加如下代码
func main2() {
engine, err := xorm.NewEngine("mysql", "%s:%s@(%s:%s)/%s?charset=utf8")
res, err := engine.Transaction(func(session *xorm.Session) (interface{}, error) {
user := User{Name: "xiaoxiao", Salt: "dev", Age: 18, Passwd: "111"}
if _, err := session.Insert(&user); err != nil {
return nil, err
}
if _, err := session.Exec("update user set name = ? where id = ?", 70, "id"); err != nil {
return nil, err
}
return nil, nil
})
if err != nil{
fmt.Println(err)
}
fmt.Println(res)
}
结果:
人员表存在
人员表不为空,有数据
person.PersonName:
person1.PersonName:
persons: []
persontest: []
personArr: [{3 Mali 28 1 { 0 0}} {4 Ruby 28 1 { 0 0}} {6 Jack 28 1 { 0 0}} {7 Hello 18 1 { 0 0}} {8 Ruby 28 1 { 0 0}} {10 Jack 28 1 { 0 0}} {11 Mali 28 1 { 0 0}} {12 Ruby 28 1 { 0 0}} {14 Jack 28 1 { 0 0}} {15 Mali 28 1 { 0 0}} {16 Ruby 28 1 { 0 0}} {18 Jack 28 1 { 0 0}} {19 Mali 28 1 { 0 0}} {20 Ruby 28 1 { 0 0}} {22 Jack 28 1 { 0 0}} {23 Mali 28 1 { 0 0}} {24 Ruby 28 1 { 0 0}} {26 Jack 28 1 { 0 0}} {27 Mali 28 1 { 0 0}} {28 Ruby 28 1 { 0 0}} {30 Jack 28 1 { 0 0}} {31 Mali 28 1 { 0 0}} {32 Ruby 28 1 { 0 0}} {34 Jack 28 1 { 0 0}} {35 Mali 28 1 { 0 0}} {36 Ruby 28 1 { 0 0}} {38 Jack 28 1 { 0 0}} {39 Mali 28 1 { 0 0}} {40 Ruby 28 1 { 0 0}} {42 Jack 28 1 { 0 0}} {43 Mali 28 1 { 0 0}} {44 Ruby 28 1 { 0 0}} {46 Jack 28 1 { 0 0}} {47 Mali 28 1 { 0 0}} {48 Ruby 28 1 { 0 0}} {50 Jack 28 1 { 0 0}} {51 Mali 28 1 { 0 0}} {52 Ruby 28 1 { 0 0}} {54 Jack 28 1 { 0 0}} {55 Mali 28 1 { 0 0}} {56 Ruby 28 1 { 0 0}} {58 Jack 28 1 { 0 0}} {59 Mali 28 1 { 0 0}} {60 Ruby 28 1 { 0 0}} {62 Jack 28 1 { 0 0}} {63 Mali 28 1 { 0 0}} {64 Ruby 28 1 { 0 0}} {66 Jack 28 1 { 0 0}} {67 Mali 28 1 { 0 0}} {68 Ruby 28 1 { 0 0}} {70 Jack 28 1 { 0 0}} {71 Mali 28 1 { 0 0}} {72 Ruby 28 1 { 0 0}} {74 Jack 28 1 { 0 0}} {75 Mali 28 1 { 0 0}} {76 Ruby 28 1 { 0 0}} {78 Jack 28 1 { 0 0}} {79 Mali 28 1 { 0 0}} {80 Ruby 28 1 { 0 0}} {82 Jack 28 1 { 0 0}} {83 Mali 28 1 { 0 0}} {84 Ruby 28 1 { 0 0}}]
----------------------------------------
personNative: []
personOrderBy: [{3 Mali 28 1 { 0 0}} {4 Ruby 28 1 { 0 0}} {6 Jack 28 1 { 0 0}} {84 Ruby 28 1 { 0 0}} {8 Ruby 28 1 { 0 0}} {10 Jack 28 1 { 0 0}} {11 Mali 28 1 { 0 0}} {12 Ruby 28 1 { 0 0}} {14 Jack 28 1 { 0 0}} {15 Mali 28 1 { 0 0}} {16 Ruby 28 1 { 0 0}} {18 Jack 28 1 { 0 0}} {19 Mali 28 1 { 0 0}} {20 Ruby 28 1 { 0 0}} {22 Jack 28 1 { 0 0}} {23 Mali 28 1 { 0 0}} {24 Ruby 28 1 { 0 0}} {26 Jack 28 1 { 0 0}} {27 Mali 28 1 { 0 0}} {28 Ruby 28 1 { 0 0}} {30 Jack 28 1 { 0 0}} {31 Mali 28 1 { 0 0}} {32 Ruby 28 1 { 0 0}} {34 Jack 28 1 { 0 0}} {35 Mali 28 1 { 0 0}} {36 Ruby 28 1 { 0 0}} {38 Jack 28 1 { 0 0}} {39 Mali 28 1 { 0 0}} {40 Ruby 28 1 { 0 0}} {42 Jack 28 1 { 0 0}} {43 Mali 28 1 { 0 0}} {44 Ruby 28 1 { 0 0}} {46 Jack 28 1 { 0 0}} {47 Mali 28 1 { 0 0}} {48 Ruby 28 1 { 0 0}} {50 Jack 28 1 { 0 0}} {51 Mali 28 1 { 0 0}} {52 Ruby 28 1 { 0 0}} {54 Jack 28 1 { 0 0}} {55 Mali 28 1 { 0 0}} {56 Ruby 28 1 { 0 0}} {58 Jack 28 1 { 0 0}} {59 Mali 28 1 { 0 0}} {60 Ruby 28 1 { 0 0}} {62 Jack 28 1 { 0 0}} {63 Mali 28 1 { 0 0}} {64 Ruby 28 1 { 0 0}} {66 Jack 28 1 { 0 0}} {67 Mali 28 1 { 0 0}} {68 Ruby 28 1 { 0 0}} {70 Jack 28 1 { 0 0}} {71 Mali 28 1 { 0 0}} {72 Ruby 28 1 { 0 0}} {74 Jack 28 1 { 0 0}} {75 Mali 28 1 { 0 0}} {76 Ruby 28 1 { 0 0}} {78 Jack 28 1 { 0 0}} {79 Mali 28 1 { 0 0}} {80 Ruby 28 1 { 0 0}} {82 Jack 28 1 { 0 0}} {83 Mali 28 1 { 0 0}} {7 Hello 18 1 { 0 0}}]
------------------------------------------
0 ---> {0 Mali 28 0 { 0 0}}
1 ---> {0 Ruby 28 0 { 0 0}}
2 ---> {0 Jack 28 0 { 0 0}}
3 ---> {0 Hello 18 0 { 0 0}}
4 ---> {0 Ruby 28 0 { 0 0}}
5 ---> {0 Jack 28 0 { 0 0}}
6 ---> {0 Mali 28 0 { 0 0}}
7 ---> {0 Ruby 28 0 { 0 0}}
8 ---> {0 Jack 28 0 { 0 0}}
9 ---> {0 Mali 28 0 { 0 0}}
10 ---> {0 Ruby 28 0 { 0 0}}
11 ---> {0 Jack 28 0 { 0 0}}
12 ---> {0 Mali 28 0 { 0 0}}
13 ---> {0 Ruby 28 0 { 0 0}}
14 ---> {0 Jack 28 0 { 0 0}}
15 ---> {0 Mali 28 0 { 0 0}}
16 ---> {0 Ruby 28 0 { 0 0}}
17 ---> {0 Jack 28 0 { 0 0}}
18 ---> {0 Mali 28 0 { 0 0}}
19 ---> {0 Ruby 28 0 { 0 0}}
20 ---> {0 Jack 28 0 { 0 0}}
21 ---> {0 Mali 28 0 { 0 0}}
22 ---> {0 Ruby 28 0 { 0 0}}
23 ---> {0 Jack 28 0 { 0 0}}
24 ---> {0 Mali 28 0 { 0 0}}
25 ---> {0 Ruby 28 0 { 0 0}}
26 ---> {0 Jack 28 0 { 0 0}}
27 ---> {0 Mali 28 0 { 0 0}}
28 ---> {0 Ruby 28 0 { 0 0}}
29 ---> {0 Jack 28 0 { 0 0}}
30 ---> {0 Mali 28 0 { 0 0}}
31 ---> {0 Ruby 28 0 { 0 0}}
32 ---> {0 Jack 28 0 { 0 0}}
33 ---> {0 Mali 28 0 { 0 0}}
34 ---> {0 Ruby 28 0 { 0 0}}
35 ---> {0 Jack 28 0 { 0 0}}
36 ---> {0 Mali 28 0 { 0 0}}
37 ---> {0 Ruby 28 0 { 0 0}}
38 ---> {0 Jack 28 0 { 0 0}}
39 ---> {0 Mali 28 0 { 0 0}}
40 ---> {0 Ruby 28 0 { 0 0}}
41 ---> {0 Jack 28 0 { 0 0}}
42 ---> {0 Mali 28 0 { 0 0}}
43 ---> {0 Ruby 28 0 { 0 0}}
44 ---> {0 Jack 28 0 { 0 0}}
45 ---> {0 Mali 28 0 { 0 0}}
46 ---> {0 Ruby 28 0 { 0 0}}
47 ---> {0 Jack 28 0 { 0 0}}
48 ---> {0 Mali 28 0 { 0 0}}
49 ---> {0 Ruby 28 0 { 0 0}}
50 ---> {0 Jack 28 0 { 0 0}}
51 ---> {0 Mali 28 0 { 0 0}}
52 ---> {0 Ruby 28 0 { 0 0}}
53 ---> {0 Jack 28 0 { 0 0}}
54 ---> {0 Mali 28 0 { 0 0}}
55 ---> {0 Ruby 28 0 { 0 0}}
56 ---> {0 Jack 28 0 { 0 0}}
57 ---> {0 Mali 28 0 { 0 0}}
58 ---> {0 Ruby 28 0 { 0 0}}
59 ---> {0 Jack 28 0 { 0 0}}
60 ---> {0 Mali 28 0 { 0 0}}
61 ---> {0 Ruby 28 0 { 0 0}}
=========================================
0
id --> 3
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
1
id --> 4
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
2
id --> 6
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
3
person_age --> 18
person_sex --> 1
id --> 7
person_name --> Hello
----------------
4
id --> 8
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
5
person_name --> Jack
person_age --> 28
person_sex --> 1
id --> 10
----------------
6
id --> 11
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
7
id --> 12
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
8
id --> 14
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
9
id --> 15
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
10
id --> 16
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
11
person_age --> 28
person_sex --> 1
id --> 18
person_name --> Jack
----------------
12
id --> 19
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
13
id --> 20
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
14
person_sex --> 1
id --> 22
person_name --> Jack
person_age --> 28
----------------
15
id --> 23
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
16
id --> 24
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
17
person_age --> 28
person_sex --> 1
id --> 26
person_name --> Jack
----------------
18
id --> 27
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
19
person_sex --> 1
id --> 28
person_name --> Ruby
person_age --> 28
----------------
20
id --> 30
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
21
id --> 31
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
22
id --> 32
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
23
id --> 34
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
24
id --> 35
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
25
id --> 36
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
26
id --> 38
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
27
person_age --> 28
person_sex --> 1
id --> 39
person_name --> Mali
----------------
28
id --> 40
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
29
id --> 42
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
30
person_name --> Mali
person_age --> 28
person_sex --> 1
id --> 43
----------------
31
id --> 44
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
32
id --> 46
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
33
id --> 47
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
34
person_name --> Ruby
person_age --> 28
person_sex --> 1
id --> 48
----------------
35
id --> 50
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
36
id --> 51
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
37
person_age --> 28
person_sex --> 1
id --> 52
person_name --> Ruby
----------------
38
id --> 54
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
39
id --> 55
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
40
id --> 56
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
41
id --> 58
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
42
id --> 59
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
43
id --> 60
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
44
id --> 62
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
45
id --> 63
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
46
id --> 64
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
47
id --> 66
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
48
id --> 67
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
49
id --> 68
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
50
person_age --> 28
person_sex --> 1
id --> 70
person_name --> Jack
----------------
51
id --> 71
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
52
id --> 72
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
53
id --> 74
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
54
person_name --> Mali
person_age --> 28
person_sex --> 1
id --> 75
----------------
55
id --> 76
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
56
id --> 78
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
57
person_name --> Mali
person_age --> 28
person_sex --> 1
id --> 79
----------------
58
person_name --> Ruby
person_age --> 28
person_sex --> 1
id --> 80
----------------
59
id --> 82
person_name --> Jack
person_age --> 28
person_sex --> 1
----------------
60
id --> 83
person_name --> Mali
person_age --> 28
person_sex --> 1
----------------
61
id --> 84
person_name --> Ruby
person_age --> 28
person_sex --> 1
----------------
rowNum: 1
rowNum2: 1
0
rowNum3: 0
<nil>
PersonTable表总记录条数: 62