gobatis: Golang 关系数据库自动映射框架

GoBatis

version

go1.19

GoBatisMyBatisGoBatis

XML 解析规则

GoBatis''

上下文数据

上下文数据是由用户调用时候传递接，仅接受 map 或者结构体如下：

标签详情

demo

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE mapper SYSTEM "http://aurora-engine.com/GoBatis.dtd">

<mapper namespace="user">
    <select id="find">
        select * from student where sss={name}
        <if expr="{arr}!=nil and {len(arr)}>0">
            and
            <for slice="{arr}" item="obj" column="id" open="(" separator="," close=")">
                {obj}
            </for>
        </if>

        <if expr="{name}=='aaa'">
            and abc = 1
            <if expr="1==1">
                and 1=1
                <if expr="1!=1">
                    or 1!=1
                </if>
            </if>
            or cba=1
        </if>
        or name = {name} and 1=1
    </select>
</mapper>

xml解析

第一层

namespacenamespace

第二层

ididnamespace{xx}{xx}

第三层

exprexprtruefalse

定义 Mapper

GoBatismapper

• 上下文参数，只能是结构体，指针结构体或者map
• 至少有一个返回值，一个返回值只能是 error

快速入门

创建 table

创建一张表，用于测试

## 用户设计
create table comm_user(
    user_id varchar(50) primary key         comment '主键',
    user_account varchar(50)                comment '账号',
    user_email varchar(50)                  comment '邮箱',
    user_password varchar(200)              comment '密码',
    user_name varchar(50) not null          comment '昵称',
    user_age int default 0                  comment '年龄',
    user_birthday datetime                  comment '生日',
    user_head_picture varchar(100)          comment '头像',
    user_create_time timestamp              comment '用户创建时间'
) comment '用户设计';

创建 映射模型

更具 数据库表，或者sql查询结果集 创建一个结构体用于接收查询数据

// UserModel 用户模型
type UserModel struct {
	UserId          string `column:"user_id"`
	UserAccount     string `column:"user_account"`
	UserEmail       string `column:"user_email"`
	UserPassword    string `column:"user_password"`
	UserName        string `column:"user_name"`
	UserAge         int    `column:"user_age"`
	UserBirthday    string `column:"user_birthday"`
	UserHeadPicture string `column:"user_head_picture"`
	UserCreateTime  string `column:"user_create_time"`
}

创建 Mapper

Mapper

// UserMapper s
type UserMapper struct {
	FindUser   func(ctx any) (UserModel, error)
	UserSelect func(ctx any) (map[string]any, error)
}

创建 XML

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE mapper
        SYSTEM "http://aurora-engine.com/GoBatis.dtd">

<mapper namespace="UserMapper">
    <select id="FindUser">
        select * from comm_user where user_id={id}
    </select>
    <select id="UserSelect">
        select * from comm_user where user_id={id}
    </select>
</mapper>

namespaceid

创建并使用

package main

import (
	"fmt"
	"gitee.com/aurora-engine/gobatis"
)
func main() {
	ctx := map[string]any{
		"id": "3de784d9a29243cdbe77334135b8a282",
	}
	open, err := sql.Open("mysql", "root:xxx@2022@tcp(82.xx.xx.xx:xx)/xx")
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	build := sgo.New(open)
	build.Source("/")
	mapper := &UserMapper{}
	build.ScanMappers(mapper)
	user, err := mapper.FindUser(ctx)
	if err != nil {
		return
	}
	fmt.Println(user)
}

创建数据库表

创建一个学生表

create table student
(
    id          int         null,
    name        varchar(20) null,
    age         int         null,
    create_time datetime    null
);

创建映射对象

column

type Student struct {
	Id         string `column:"id"`
	Name       string `column:"name"`
	Age        int    `column:"age"`
	CreateTime string `column:"create_time"`
}

创建 Mapper 和 XML

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE mapper SYSTEM "http://aurora-engine.com/GoBatis.dtd">
<mapper namespace="StudentMapper">

</mapper>

http://aurora-engine.com/GoBatis.dtd

type StudentMapper struct {
	
}

前置工作已经准备就绪，xml 和 mapper 结构体里面的内容会在下面的案例中一步一步的添加进去。

Insert

Insert 插入数据

对学生表进行新增数据,我们先从定义 mapper 函数开始。

type StudentMapper struct {
	InsertOne func(any) (int, error)
}

开始定义 xml 元素，insert 中的模板参数，均来自于 mapper 函数的上下文参数中

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE mapper SYSTEM "http://aurora-engine.com/GoBatis.dtd">

<mapper namespace="StudentMapper">
    <insert id="InsertOne">
        insert into student(id,name,age,create_time) value({id},{name},{age},{time})
    </insert>
</mapper>

创建 GoBatis 并调用执行

package main

import (
	"database/sql"
	"fmt"
	"gitee.com/aurora-engine/gobatis"
	_ "github.com/go-sql-driver/mysql"
	"time"
)
type Student struct {
	Id         string `column:"id"`
	Name       string `column:"name"`
	Age        int    `column:"age"`
	CreateTime string `column:"create_time"`
}

type StudentMapper struct {
	InsertOne func(any) (int64, error)
}

func main() {
	ctx := map[string]any{
		"id":   "1",
		"name": "test1",
		"age":  19,
		"time": time.Now().Format("2006-01-02 15:04:05"),
	}
	open, err := sql.Open("mysql", "xxxx")
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	if err != nil {
		return
	}
	build := gobatis.New(open)
	build.Source("/")
	mapper := &StudentMapper{}
	build.ScanMappers(mapper)
	count, err := mapper.InsertOne(ctx)
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	fmt.Println(count)
}

批量插入数据

我们现在继续向 Mapper 结构体中添加定义

type StudentMapper struct {
	InsertOne func(any) (int64, error)
	InsertArr func(any) (int64, error)
}

InsertArr func(any) (int64, error)

<mapper namespace="StudentMapper">
    <insert id="InsertOne">
        insert into student(id,name,age,create_time) value({id},{name},{age},{time})
    </insert>

    <insert id="InsertArr">
        insert into student(id,name,age,create_time) values
        <for slice="{arr}" item="obj">
            ({obj.id},{obj.name},{obj.age},{obj.time})
        </for>
    </insert>

</mapper>

arrobj

package main

import (
	"database/sql"
	"fmt"
	"gitee.com/aurora-engine/gobatis"
	_ "github.com/go-sql-driver/mysql"
	"time"
)
type Student struct {
	Id         string `column:"id"`
	Name       string `column:"name"`
	Age        int    `column:"age"`
	CreateTime string `column:"create_time"`
}

type StudentMapper struct {
	InsertOne func(any) (int64, error)
	InsertArr func(any) (int64, error)
}

func main() {
	ctx := map[string]any{
		"arr": []map[string]any{
			{
				"id":   "1",
				"name": "test1",
				"age":  19,
				"time": time.Now().Format("2006-01-02 15:04:05"),
			},
			{
				"id":   "2",
				"name": "test2",
				"age":  19,
				"time": time.Now().Format("2006-01-02 15:04:05"),
			},
			{
				"id":   "3",
				"name": "test3",
				"age":  19,
				"time": time.Now().Format("2006-01-02 15:04:05"),
			},
		},
	}
	open, err := sql.Open("mysql", "xxxxx")
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	build := gobatis.New(open)
	build.Source("/")
	mapper := &StudentMapper{}
	build.ScanMappers(mapper)
	count, err := mapper.InsertArr(ctx)
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	fmt.Println(count)
}

::: tip Insert,Update,Delete，定义的返回值只能返回数据库处理记录，第一个参数返回类型不正确将会返回错误信息，Insert 相对特殊，第二个参数可以返回，自增长id。 :::

Select

查询一条记录

添加 查询定义如下：

type StudentMapper struct {
	InsertOne func(any) (int64, error)
	InsertArr func(any) (int64, error)

	SelectById func(any) (Student, error)
}

<select id="SelectById">
        select * from student where id={id}
</select>

package main

import (
	"database/sql"
	"fmt"
	"gitee.com/aurora-engine/gobatis"
	_ "github.com/go-sql-driver/mysql"
	"time"
)

type Student struct {
	Id         string `column:"id"`
	Name       string `column:"name"`
	Age        int    `column:"age"`
	CreateTime string `column:"create_time"`
}

type StudentMapper struct {
	InsertOne func(any) (int64, error)
	InsertArr func(any) (int64, error)

	SelectById  func(any) (Student, error)
}

func main() {
	ctx := map[string]any{
		"arr": []map[string]any{
			{
				"id":   "1",
				"name": "test1",
				"age":  19,
				"time": time.Now().Format("2006-01-02 15:04:05"),
			},
			{
				"id":   "2",
				"name": "test2",
				"age":  19,
				"time": time.Now().Format("2006-01-02 15:04:05"),
			},
			{
				"id":   "3",
				"name": "test3",
				"age":  19,
				"time": time.Now().Format("2006-01-02 15:04:05"),
			},
		},
		"id":  "1",
		"ids": []string{"1", "2"},
	}
	open, err := sql.Open("mysql", "xxxx")
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	build := gobatis.New(open)
	build.Source("/")
	mapper := &StudentMapper{}
	build.ScanMappers(mapper)
	stu, err := mapper.SelectById(ctx)
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	fmt.Println(stu)
}

查询多条数据

type StudentMapper struct {
	InsertOne func(any) (int64, error)
	InsertArr func(any) (int64, error)

	SelectById func(any) (Student, error)
	SelectAll   func() ([]Student, error)
}

<select id="SelectAll">
    select * from student
</select>

package main

import (
	"database/sql"
	"fmt"
	"gitee.com/aurora-engine/gobatis"
	_ "github.com/go-sql-driver/mysql"
	"time"
)
type Student struct {
	Id         string `column:"id"`
	Name       string `column:"name"`
	Age        int    `column:"age"`
	CreateTime string `column:"create_time"`
}

type StudentMapper struct {
	InsertOne func(any) (int64, error)
	InsertArr func(any) (int64, error)

	SelectById  func(any) (Student, error)
	SelectAll   func() ([]Student, error)
}

func main() {
	open, err := sql.Open("mysql", "xxxx")
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	build := gobatis.New(open)
	build.Source("/")
	mapper := &StudentMapper{}
	build.ScanMappers(mapper)
	stu, err := mapper.SelectAll()
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	fmt.Println(stu)
}

批量查询

type StudentMapper struct {
	InsertOne func(any) (int64, error)
	InsertArr func(any) (int64, error)

	SelectById func(any) (Student, error)
	SelectAll   func() ([]Student, error)
	SelectByIds func(any) ([]Student, error)
}

<select id="SelectByIds">
    select * from student where id in
    <for slice="{ids}" item="id" open="(" separator="," close=")">
        {id}
    </for>
</select>

package main

import (
	"database/sql"
	"fmt"
	"gitee.com/aurora-engine/gobatis"
	_ "github.com/go-sql-driver/mysql"
	"time"
)
type Student struct {
	Id         string `column:"id"`
	Name       string `column:"name"`
	Age        int    `column:"age"`
	CreateTime string `column:"create_time"`
}

type StudentMapper struct {
	InsertOne func(any) (int64, error)
	InsertArr func(any) (int64, error)

	SelectById  func(any) (Student, error)
	SelectAll   func() ([]Student, error)
	SelectByIds func(any) ([]Student, error)
}

func main() {
	ctx := map[string]any{
		"arr": []map[string]any{
			{
				"id":   "1",
				"name": "test1",
				"age":  19,
				"time": time.Now().Format("2006-01-02 15:04:05"),
			},
			{
				"id":   "2",
				"name": "test2",
				"age":  19,
				"time": time.Now().Format("2006-01-02 15:04:05"),
			},
			{
				"id":   "3",
				"name": "test3",
				"age":  19,
				"time": time.Now().Format("2006-01-02 15:04:05"),
			},
		},
		"id":  "1",
		"ids": []string{"1", "2"},
	}
	open, err := sql.Open("mysql", "xxxxxxx")
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	build := gobatis.New(open)
	build.Source("/")
	mapper := &StudentMapper{}
	build.ScanMappers(mapper)
	stu, err := mapper.SelectByIds(ctx)
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	fmt.Println(stu)
}

Update

同上...

Delete

同上...