博客

XORM快速入门及注意点

  • Post author:
  • Post category:其他




XORM快速入门及注意点



1 介绍及环境搭建

  • 介绍：
  • XORM:是一个Go操作数据库的框架
  • 官网地址：https://xorm.io/zh/
  • 文档地址：https://gitea.com/xorm/xorm/src/branch/master/README_CN.md
  • 环境安装 
go get xorm.io/xorm

package main

import (
	"fmt"
	//一定要导入mysql驱动，否则会报错
	_ "github.com/go-sql-driver/mysql"
	"time"
	"xorm.io/xorm"
)

func main() {
	//数据库连接基本信息
	var (
		userName  string = "root"
		password  string = "123456"
		ipAddress string = "127.0.0.1"
		port      int    = 3306
		dbName    string = "go_test"
		charset   string = "utf8mb4"
	)
	//构建数据库连接信息
	//root:123456@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)
	engine, err := xorm.NewEngine("mysql", dataSourceName)
	if err != nil {
		fmt.Println("数据库连接失败")
	}

}



2 同步结构体与数据表

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, err := xorm.NewEngine("mysql", dataSourceName)
	if err != nil {
		fmt.Println("数据库连接失败")
	}

	//直接通过结构体，在数据库中创建对应的表【同步结构体与数据表】
	err = engine.Sync(new(User))
	if err != nil {
		fmt.Println("表结构同步失败")
	}
}

在这里插入图片描述



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("通过切片插入多行数据成功")
	}
}

结果：

在这里插入图片描述



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("更新数据成功...")
	}
}

结果：

在这里插入图片描述



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)

}

在这里插入图片描述



②遍历输出

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)
	}
}

在这里插入图片描述



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)
	}
}

在这里插入图片描述



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)
	}

}

结果：

在这里插入图片描述


版权声明：本文为weixin_45565886原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。