xorm代码

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