XORM快速入门及注意点

灰太狼 2024-03-22 16:33 58阅读 0赞

## XORM快速入门及注意点 ##

### 1 介绍及环境搭建 ###

*  介绍：

>  *  XORM:是一个Go操作数据库的框架
>  *  官网地址：https://xorm.io/zh/
>  *  文档地址：https://gitea.com/xorm/xorm/src/branch/master/README\_CN.md

*  环境安装

go get xorm.io/xorm

### 2 同步结构体与数据表 ###

![在这里插入图片描述][37cb78694d3847a7b166fc4463c573aa.png]

### 3 数据操作 ###

#### 3.1 插入 ####

代码：

package main
    
    import (
    	"fmt"
    	//一定要导入mysql驱动，否则会报错
    	_ "github.com/go-sql-driver/mysql"
    	"time"
    	"xorm.io/xorm"
    )
    
    func main() {
        
    	//数据库连接基本信息
    	var (
    		userName  string = "root"
    		password  string = "200151"
    		ipAddress string = "127.0.0.1"
    		port      int    = 3306
    		dbName    string = "go_test"
    		charset   string = "utf8mb4"
    	)
    	//构建数据库连接信息
    	//root:200151@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4
    	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)
    	//fmt.Println("dataSourceName=", dataSourceName)
    
    	//结构体字段与数据库中表的字段对应【底层会做大小写转换处理】
    	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"`
    	}
    
    	//"mysql":使用什么驱动
    	engine, _ := xorm.NewEngine("mysql", dataSourceName)
    
    	//1. 插入单行数据
    	//engine.Insert()插入对象，返回值：受影响的行数
    	user := User{
        Id: 1000, Name: "jack", Age: 18, Passwd: "123456"}
    	//Insert这里应该传入一个地址
    	n, _ := engine.Insert(&user)
    	if n >= 1 {
        
    		fmt.Println("数据插入成功")
    	}
    
    	//2. 插入多行数据
    	//①直接传入多个地址
    	user1 := User{
        Id: 1001, Name: "tom", Age: 18, Passwd: "123456"}
    	user2 := User{
        Id: 1002, Name: "mom", Age: 18, Passwd: "123456"}
    	n, _ = engine.Insert(&user1, &user2)
    	if n >= 2 {
        
    		fmt.Println("插入多行数据成功")
    	}
    	//②通过切片传入
    	var users []User
    	users = append(users, User{
        Id: 1003, Name: "mom", Age: 18, Passwd: "123456"})
    	users = append(users, User{
        Id: 1004, Name: "mom", Age: 18, Passwd: "123456"})
    	n, _ = engine.Insert(&users)
    	if n >= 2 {
        
    		fmt.Println("通过切片插入多行数据成功")
    	}
    }

结果：  
![在这里插入图片描述][2eceb0db12034168af7ec6c7371d93d7.png]

#### 3.2 更新 ####

package main
    
    import (
    	"fmt"
    	"time"
    
    	//一定要导入mysql驱动，否则会报错
    	_ "github.com/go-sql-driver/mysql"
    	"xorm.io/xorm"
    )
    
    func main() {
        
    	//数据库连接基本信息
    	var (
    		userName  string = "root"
    		password  string = "200151"
    		ipAddress string = "127.0.0.1"
    		port      int    = 3306
    		dbName    string = "go_test"
    		charset   string = "utf8mb4"
    	)
    	//构建数据库连接信息
    	//root:200151@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4
    	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)
    
    	engine, _ := xorm.NewEngine("mysql", dataSourceName)
    
    	/*
    		User结构体：与user表对应
    	*/
    	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 := User{
        Name: "欧文", Age: 24}
    	//1. 根据id更新【需要将id字段设置为pk主键】
    	n, _ := engine.ID(1000).Update(&user)
    	if n >= 1 {
        
    		fmt.Println("更新数据成功...")
    	}
    
    	//2. 根据条件更新 【更新age=18并且name=tom的数据】
    	n, _ = engine.Update(&user, &User{
        Age: 18, Name: "tom"})
    	if n >= 1 {
        
    		fmt.Println("更新数据成功...")
    	}
    }

结果：  
![在这里插入图片描述][97df11cc7afa40b2b46a98dbb281460e.png]

#### 3.3 删除 ####

package main
    
    import (
    	"fmt"
    	"time"
    
    	//一定要导入mysql驱动，否则会报错
    	_ "github.com/go-sql-driver/mysql"
    	"xorm.io/xorm"
    )
    
    func main() {
        
    	//数据库连接基本信息
    	var (
    		userName  string = "root"
    		password  string = "200151"
    		ipAddress string = "127.0.0.1"
    		port      int    = 3306
    		dbName    string = "go_test"
    		charset   string = "utf8mb4"
    	)
    	//构建数据库连接信息
    	//root:200151@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4
    	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)
    
    	engine, _ := xorm.NewEngine("mysql", dataSourceName)
    
    	/*
    		User结构体：与user表对应
    	*/
    	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"`
    	}
    	//删除操作
    	//1. 根据id删除
    	n, _ := engine.ID(1000).Delete(&User{
        })
    	if n >= 1 {
        
    		fmt.Println("根据id删除数据成功")
    	}
    	
    	//2. 根据条件删除
    	n, _ = engine.Delete(&User{
        Name: "mom", Age: 14})
    	if n >= 1 {
        
    		fmt.Println("根据条件删除数据成功")
    	}
    	
    	//3. 根据SQL语句直接删除【更新、删除等同理】
    	engine.Exec("delete from user where id = ?", 1002)
    
    	//通过执行SQL插入数据
    	//exec, err := engine.Exec("insert into user (id, name, age, passwd )values (?, 'bob', 19, '7890')", 1005)
    }

#### 3.4 查询与遍历输出 ####

##### ① 查询 #####

package main
    
    import (
    	"fmt"
    	"time"
    
    	//一定要导入mysql驱动，否则会报错
    	_ "github.com/go-sql-driver/mysql"
    	"xorm.io/xorm"
    )
    
    func main() {
        
    	//数据库连接基本信息
    	var (
    		userName  string = "root"
    		password  string = "200151"
    		ipAddress string = "127.0.0.1"
    		port      int    = 3306
    		dbName    string = "go_test"
    		charset   string = "utf8mb4"
    	)
    	//构建数据库连接信息
    	//root:200151@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4
    	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)
    
    	engine, _ := xorm.NewEngine("mysql", dataSourceName)
    
    	/*
    		User结构体：与user表对应
    	*/
    	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"`
    	}
    	//查询
    	//1. 直接将查询出来的结果转为字符【常用】 查询出来的结果是放在了一个map中
    	result, _ := engine.QueryString("select * from user")
    	fmt.Println(result)
    
    	user1 := &User{
        }
    	//2. 指定条件来查询用户 //select from user where name = "bob" order by id asc
    	engine.Where("name=?", "bob").Asc("id").Get(&user1)
    	fmt.Println(*user1)
    
    	//3. 查询并获取获取指定字段的值
    	var pwd string
    	user := User{
        }
    	engine.Table(&user).Where("name=?", "jack").Cols("passwd").Get(&pwd)
    	fmt.Println("pwd=", pwd)
    
    	//4. 用Find查询多条记录
    	var users []User
    	engine.Where("created is not null").And("id >= 1001").Limit(10, 0).Find(&users)
    	fmt.Println("users=", users)
    
    }

![在这里插入图片描述][b3e8f41d20d34313b29c4043bb9801d9.png]

##### ②遍历输出 #####

package main
    
    import (
    	"fmt"
    	"time"
    
    	//一定要导入mysql驱动，否则会报错
    	_ "github.com/go-sql-driver/mysql"
    	"xorm.io/xorm"
    )
    
    func main() {
        
    	//数据库连接基本信息
    	var (
    		userName  string = "root"
    		password  string = "200151"
    		ipAddress string = "127.0.0.1"
    		port      int    = 3306
    		dbName    string = "go_test"
    		charset   string = "utf8mb4"
    	)
    	//构建数据库连接信息
    	//root:200151@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4
    	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)
    
    	engine, _ := xorm.NewEngine("mysql", dataSourceName)
    
    	/*
    		User结构体：与user表对应
    	*/
    	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"`
    	}
    	//`Count` 获取记录条数
    	user := User{
        }
    	counts, _ := engine.Count(&user)
    	fmt.Println("数据条数=", counts)
    
    	//Iterate 和 Row 根据条件遍历数据
    	engine.Iterate(&User{
        Passwd: "123456"}, func(idx int, bean interface{
        }) error {
        
    		//类型断言
    		user, ok := bean.(User)
    		if ok {
        
    			fmt.Println(user)
    		}
    		return nil
    	})
    	rows, _ := engine.Rows(&User{
        Passwd: "123456"})
    	//延迟关闭
    	defer rows.Close()
    	//&{} 结构体
    	userBean := new(User)
    	for rows.Next() {
        
    		rows.Scan(userBean)
    		//根据* 取具体的值
    		fmt.Println(*userBean)
    		//fmt.Println(userBean)
    	}
    }

![在这里插入图片描述][9ecf4f760d554906a14def5b5d426cc6.png]

##### ③分页查询 #####

func (d *activityDetailDao) GetActivityListByPage(engine xorm.Interface, startPage, pageSize int) (detailList []*model.ActivityDetail, err error) {
        
    	err = engine.Limit(pageSize, (startPage-1)*pageSize).Find(&detailList)
    	return
    }

##### ④不插入某个字段、忽略字段为空 #####

//不插入某个字段
    func (d *activityDetailDao) Insert(engine xorm.Interface, activityDetail *model.ActivityDetail) (int64, error) {
        
    	return engine.Omit("id").Insert(activityDetail)
    }
    
    //忽略某个字段为空
    type Data struct {
        
    	Endpoint        string `json:"endpoint,omitempty"`
    	Region          string `json:"region,omitempty"`
    }

### 4 事务处理 ###

package main
    
    import (
    	"fmt"
    	"time"
    
    	//一定要导入mysql驱动，否则会报错
    	_ "github.com/go-sql-driver/mysql"
    	"xorm.io/xorm"
    )
    
    func main() {
        
    	//数据库连接基本信息
    	var (
    		userName  string = "root"
    		password  string = "200151"
    		ipAddress string = "127.0.0.1"
    		port      int    = 3306
    		dbName    string = "go_test"
    		charset   string = "utf8mb4"
    	)
    	//构建数据库连接信息
    	//root:200151@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4
    	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)
    
    	engine, _ := xorm.NewEngine("mysql", dataSourceName)
    
    	/*
    		User结构体：与user表对应
    	*/
    	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"`
    	}
    	session := engine.NewSession()
    	defer session.Close()
    
    	session.Begin()
    	defer func() {
        
    		//捕获错误
    		err := recover()
    		if err != nil {
        
    			//回滚
    			session.Rollback()
    			fmt.Println("事务回滚...err=", err)
    		} else {
        
    			session.Commit()
    			fmt.Println("事务提交....")
    		}
    	}()
    
    	//事务回滚...err= Error 1062 (23000): Duplicate entry '1005' for key 'PRIMARY'
    	//user1 := User{Id: 1005, Name: "PLA", Age: 18, Passwd: "111222"}
    	
    	//事务提交....
    	user1 := User{
        Id: 1007, Name: "PLA", Age: 18, Passwd: "111222"}
    	if _, err := engine.Insert(&user1); err != nil {
        
    		panic(err)
    	}
    
    	user2 := User{
        Name: "柳小山", Age: 25}
    	if _, err := session.Where("id=1001").Update(&user2); err != nil {
        
    		panic(err)
    	}
    
    	if _, err := session.Exec("delete from user where name = 'jack'"); err != nil {
        
    		panic(err)
    	}
    }

![在这里插入图片描述][d43e54edc2d94d4b9624464b2f948187.png]

### 5 实战：操作postgresql ###

package main
    
    import (
    	"fmt"
    	"time"
    
    	"github.com/go-xorm/xorm"
    	_ "github.com/lib/pq"
    )
    
    //xorm : 自动下划线转驼峰
    //user_detail
    type UserDetail struct {
        
    	Id          int64     `xorm:"id pk autoincr INTEGER"`
    	Profile     string    `xorm:"profile  varchar(20) unique"`
    	Name        string    `xorm:"name"`
    	CreatedTime time.Time `xorm:"created_time timestampz created"`
    	ModifyTime  time.Time `json:"modify_time" xorm:"modify_time timestampz updated"`
    }
    
    //此方法仅用于orm查询时，查询表认定[如：通过queryString查询时候，表名会参考]
    func (UserDetail) TableName() string {
        
    	return "public.user_detail"
    }
    
    func main() {
        
    	// 连接数据库
    	engine, err := xorm.NewEngine("postgres", "user=postgres password=postgres dbname=postgres host=localhost port=5432 sslmode=disable")
    	if err != nil {
        
    		fmt.Println("连接数据库失败：", err)
    		return
    	}
    
    	// 同步表结构[sync2相比sync会多一些表字段的校验等]
    	err = engine.Sync2(new(UserDetail))
    	if err != nil {
        
    		fmt.Println("同步表结构失败：", err)
    		return
    	}
    	fmt.Println("同步表结构成功...")
    
    	//①插入数据
    	user1 := UserDetail{
        
    		Id:          1,
    		Profile:     "user1",
    		Name:        "user1-name",
    		CreatedTime: time.Now(),
    	}
    	user2 := UserDetail{
        
    		Id:          2,
    		Profile:     "user2",
    		Name:        "user2-name",
    		CreatedTime: time.Now(),
    	}
    	//Insert函数中传入多个就是插入多行，传入一个就是插入一行
    	n, err := engine.Insert(&user1, &user2)
    	if n != 2 {
        
    		fmt.Errorf("【①插入数据】插入多行数据失败：%v", err)
    	} else {
        
    		fmt.Println("【①插入数据】插入多行数据成功...")
    	}
    	user3 := UserDetail{
        
    		Id:          3,
    		Profile:     "user3",
    		Name:        "user3-name",
    		CreatedTime: time.Now(),
    	}
    	n, err = engine.Insert(&user3)
    	if n != 1 {
        
    		fmt.Println("【①插入数据】插入单行数据失败, err=", err)
    	} else {
        
    		fmt.Println("【①插入数据】插入单行数据成功...")
    	}
    
    	//②更新数据
    	//根据id更新
    	newUser1 := UserDetail{
        
    		Name: "newUser1Name",
    	}
    	//主键必须是id,假如主键字段名为uid则该方法失效
    	n, err = engine.Id(1).Update(&newUser1)
    	if n >= 1 {
        
    		fmt.Println("【②更新数据】根据id更新数据成功...")
    	} else {
        
    		fmt.Println("【②更新数据】根据id更新数据失败..., err=", err)
    	}
    	//根据条件更新
    	user2.Name = "user2-newName"
    	n, _ = engine.Update(&user2, &UserDetail{
        Id: 2})
    	if n >= 1 {
        
    		fmt.Println("【②更新数据】根据条件更新数据成功...")
    	} else {
        
    		fmt.Println("【②更新数据】根据条件更新数据失败...")
    	}
    
    	//③查询数据
    	//queryString,将结果转为字符，最终是放在了map里
    	result, _ := engine.QueryString("select id, profile, name, created_time from user_detail")
    	fmt.Println("【③查询数据】queryString查询到的结果=", result)
    	//指定条件来查询用户
    	userTmp := &UserDetail{
        }
    	engine.Where("name=?", "user10").Asc("id").Get(&userTmp)
    	fmt.Println("【③查询数据】根据指定条件来查询用户=", *userTmp) //前面userTmp是一个地址，因此这里需要通过*来具体结构体中的值
    	//获取指定字段的值
    	profileTmp := ""
    	user := UserDetail{
        }
    	engine.Table(&user).Where("id=?", 1).Cols("profile").Get(&profileTmp)
    	fmt.Println("【③查询数据】获取指定字段的值, profile=", profileTmp)
    	//find查询多条记录
    	var users []UserDetail
    	engine.Where("id is not null").And("name like ?", "user%").Limit(2, 0).Find(&users)
    	fmt.Println("【③查询数据】find查询多条记录,users=", users)
    
    	//④删除数据
    	//根据id删除
    	n, _ = engine.Id(1).Delete(&UserDetail{
        })
    	if n >= 1 {
        
    		fmt.Println("【④删除数据】根据id删除数据成功...")
    	} else {
        
    		fmt.Println("【④删除数据】根据id删除数据失败...")
    	}
    	//根据条件删除
    	n, _ = engine.Delete(&UserDetail{
        Profile: "user3"})
    	if n >= 1 {
        
    		fmt.Println("【④删除数据】根据条件删除数据成功...")
    	} else {
        
    		fmt.Println("【④删除数据】根据条件删除数据失败...")
    	}
    	//根据SQL语句直接删除[通过原生SQL语句还可以进行更新等]
    	_, err = engine.Exec("delete from user_detail where id = ?", 2)
    	if err == nil {
        
    		fmt.Println("【④删除数据】根据SQL语句删除成功...")
    	} else {
        
    		fmt.Println("【④删除数据】根据SQL语句删除失败,err=", err)
    	}
    
    }

结果：  
![在这里插入图片描述][6031c11a4781456ea525bd98dcab8d0f.png]

[37cb78694d3847a7b166fc4463c573aa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/86a44b5c519d4afa95d9d0289cbf2640.png
[2eceb0db12034168af7ec6c7371d93d7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/83397a50f50543ba89af2e5375a41422.png
[97df11cc7afa40b2b46a98dbb281460e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/98762634660b4033a5c8a0bcccf1cd51.png
[b3e8f41d20d34313b29c4043bb9801d9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/8fa1e3f3072149ac947e97a711a849ca.png
[9ecf4f760d554906a14def5b5d426cc6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/af5337798ac74ea2b7070f158bae5665.png
[d43e54edc2d94d4b9624464b2f948187.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/279866063ee6417e8e092164c108ccb6.png
[6031c11a4781456ea525bd98dcab8d0f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/ae7a63e5b5db4620bee724d3d36d172c.png