gobatis

package module
v0.2.6 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 11, 2020 License: Apache-2.0 Imports: 16 Imported by: 0

README

gobatis

Appveyor Build status

介绍

gobatis是一个golang的ORM框架，类似Java的Mybatis。支持直接执行sql语句以及动态sql。

建议配合gobatis-cmd自动代码、sql生成工具使用。

支持的动态sql标签：

标签 说明
if 动态 SQL 通常要做的事情是根据条件包含 where 子句的一部分。
where where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。
set set 元素会动态前置 SET 关键字，同时也会删掉无关的逗号。
include 使用sql标签定义的语句替换。
choose
when
otherwise		 有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，gobatis 提供了 choose 元素，它有点像switch 语句。
foreach foreach 允许指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。

除了xml之外，gobatis也支持使用go template的mapper格式。

待完成项

  • 继续完善动态sql支持（trim）
  • 性能优化：增加动态sql缓存 (已经实现，但测试发现性能提升很小，目前该功能被关闭)

使用

1、配置数据库，获得SessionManager
func InitDB() *gobatis.SessionManager {
    fac := gobatis.NewFactory(
    		gobatis.SetMaxConn(100),
    		gobatis.SetMaxIdleConn(50),
    		gobatis.SetDataSource(&datasource.MysqlDataSource{
    			Host:     "localhost",
    			Port:     3306,
    			DBName:   "test",
    			Username: "root",
    			Password: "123",
    			Charset:  "utf8",
    		}))
    return gobatis.NewSessionManager(&fac)
}

注意：

gobatis.NewFactory当连接数据库失败时会返回nil，如果需要知道具体的失败原因请使用：

fac, err := gobatis.CreateFactory(
    		gobatis.SetMaxConn(100),
    		gobatis.SetMaxIdleConn(50),
    		gobatis.SetDataSource(&datasource.MysqlDataSource{
    			Host:     "localhost",
    			Port:     3306,
    			DBName:   "test",
    			Username: "root",
    			Password: "123",
    			Charset:  "utf8",
    		}))
if err != nil {
    t.Log(err)
}
2、定义Model

使用tag（"xfield"）定义struct，tag指定数据库表中的column name。

type TestTable struct {
    //指定table name
    TestTable gobatis.ModelName "test_table"
    //指定表字段id
    Id        int64             `xfield:"id"`
    //指定表字段username
    Username  string            `xfield:"username"`
    //指定表字段password
    Password  string            `xfield:"password"`
}
3、注册Model

作用是提高执行速度，已变为非必要步骤，现在gobatis会自动缓存。

func init() {
    var model TestTable
    gobatis.RegisterModel(&model)
}
4、调用
func Run() {
    //初始化db并获得Session Manager
    mgr := InitDB()
    
    //获得session
    session := mgr.NewSession()
    
    ret := TestTable{}
    
    //使用session查询
    session.Select("select * from test_table where id = ${0}").Param(100).Result(&ret)
    
    fmt.printf("%v\n", ret)
}
5、解析说明
5.1、内置动态解析

内置动态解析是gobatis类Mybatis的解析方案（目前是xml mapper文件和直接执行sql的默认解析方式）：

  1. ${}表示直接替换，#{}防止sql注入
  2. 与Mybatis类似，语句中${0}、${1}、${2}...${n} 对应的是Param方法中对应的不定参数，最终替换和调用底层Driver
  3. Param方法接受简单类型的不定参数（string、int、time、float等）、struct、map，底层自动解析获得参数，用法为：
param := TestTable{Username:"test_user"}
ret := TestTable{}
session.Select("select * from test_table where username = #{TestTable.username}").Param(param).Result(&ret)
  1. Param解析的参数规则（请务必按此规则对应SQL语句的占位参数）：

  • 简单类型

    对应sql参数中的#{0}、#{1}...

  • map类型

    对应sql参数中的#{key1}、#{key2}...

  • struct类型

    对应sql参数中的#{StructName.Field1}、#{StructName.Field2}...

5.2、go template解析

使用go template解析，遵循template解析规则，是template mapper文件的解析方式。

如要要修改直接执行sql的默认解析方式，可通过：

    sessionManager.SetParserFactory(gobatis.TemplateParserFactory)

或者

    session.SetParserFactory(gobatis.TemplateParserFactory)

调用后可使用template的方式直接解析执行sql：

session.Select("SELECT * FROM test_table WHERE id = {{.}}").Param(2).Result(&ret)

gobatis内置where、set、arg自定义函数，用于智能生成动态sql

arg用于将对象动态转换为占位符，并保存为sql参数，如:

SELECT * FROM TABLE_NAME WHERE name = {{arg .Name}}

以mysql为例，将解析为：

SELECT * FROM TABLE_NAME WHERE name = ?

同时Name的值将自动保存为SQL参数，自动传入，起到类似内置动态解析中#{MODEL.Name}的效果。

6、事务

使用

    mgr.NewSession().Tx(func(session *gobatis.Session) error {
        ret := 0
        session.Insert("insert_id").Param(testV).Result(&ret)
        
        t.Logf("ret %d\n", ret)
        
        session.Select("select_id").Param().Result(&testList)
        
        for _, v := range  testList {
            t.Logf("data: %v", v)
        }
        //commit
        return nil
    })
  1. 当参数的func返回nil，则提交
  2. 当参数的func返回非nil的错误，则回滚
  3. 当参数的func内抛出panic，则回滚
7、扫描mapper文件
err := gobatis.ScanMapperFile(${MAPPER_FILE_DIR})
if err != nil {
    t.Fatal(err)
}
8、xml

gobatis支持xml的sql解析及动态sql

  1. 直接注册xml
gobatis.RegisterMapperData([]byte(main_xml))

或

gobatis.RegisterMapperFile(filePath)
  1. xml示例
<mapper namespace="test">
    <sql id="columns_id">id,username,password,createtime</sql>

    <select id="selectTestTable">
        SELECT <include refid="columns_id"> </include> FROM test_table
        <where>
            <if test="{TestTable.id} != nil and {TestTable.id} != 0">AND id = #{TestTable.id} </if>
            <if test="{TestTable.username} != nil">AND username = #{TestTable.username} </if>
            <if test="{TestTable.password} != nil">AND password = #{TestTable.password} </if>
            <if test="{TestTable.createtime} != nil">AND createtime = #{TestTable.createtime} </if>
        </where>
    </select>

    <select id="selectTestTableCount">
        SELECT COUNT(*) FROM test_table
        <where>
            <if test="{TestTable.id} != nil and {TestTable.id} != 0">AND id = #{TestTable.id} </if>
            <if test="{TestTable.username} != nil">AND username = #{TestTable.username} </if>
            <if test="{TestTable.password} != nil">AND password = #{TestTable.password} </if>
            <if test="{TestTable.createtime} != nil">AND createtime = #{TestTable.createtime} </if>
        </where>
    </select>

    <insert id="insertTestTable">
        INSERT INTO test_table (id,username,password,createtime)
        VALUES(
        #{TestTable.id},
        #{TestTable.username},
        #{TestTable.password},
        #{TestTable.createtime}
        )
    </insert>

    <insert id="insertBatchTestTable">
        INSERT INTO test_table (id,username,password,createtime)
        VALUES
        <foreach item="item" index="index" collection="{0}" open="" separator="," close="">
            (#{item.TestTable.id},#{item.TestTable.username},#{item.TestTable.password},#{item.TestTable.createtime})
        </foreach>
    </insert>

    <update id="updateTestTable">
        UPDATE test_table
        <set>
            <if test="{TestTable.username} != nil"> username = #{TestTable.username} </if>
            <if test="{TestTable.password} != nil"> password = #{TestTable.password} </if>
            <if test="{TestTable.createtime} != nil"> createtime = #{TestTable.createtime} </if>
        </set>
        WHERE id = #{TestTable.id}
    </update>

    <delete id="deleteTestTable">
        DELETE FROM test_table
        <where>
            <if test="{TestTable.id} != nil and {TestTable.id} != 0">AND id = #{TestTable.id} </if>
            <if test="{TestTable.username} != nil">AND username = #{TestTable.username} </if>
            <if test="{TestTable.password} != nil">AND password = #{TestTable.password} </if>
            <if test="{TestTable.createtime} != nil">AND createtime = #{TestTable.createtime} </if>
        </where>
    </delete>
</mapper>
  1. namespace

xml数据或文件注册之后，session参数sqlid与xml action对应关系为：${NAMESPACE}+"."+${ACTION_ID}

以2中的xml为例，调用select的方式为：

sess.Select("test.selectTestTable").Param(model).Result(&dataList)
9、template

gobatis也支持go template的sql解析及动态sql

  1. 直接注册template
gobatis.RegisterTemplateData([]byte(main_xml))

或

gobatis.RegisterTemplateFile(filePath)
  1. template示例
{{define "namespace"}}test{{end}}

{{define "selectTestTable"}}
SELECT id,username,password,createtime FROM test_table
{{where .Id "AND" "id = " (arg .Id) "" | where .Username "AND" "username = " (arg .Username) | where .Password "AND" "password = " (arg .Password) | where .Createtime "AND" "createtime = " (arg .Createtime)}}
{{end}}

{{define "selectTestTableCount"}}
SELECT COUNT(*) FROM test_table
{{where .Id "AND" "id = " (arg .Id) "" | where .Username "AND" "username = " (arg .Username) | where .Password "AND" "password = " (arg .Password) | where .Createtime "AND" "createtime = " (arg .Createtime)}}
{{end}}

{{define "insertTestTable"}}
INSERT INTO test_table(id,username,password,createtime)
VALUES(
{{arg .Id}}, {{arg .Username}}, {{arg .Password}}, {{arg .Createtime}})
{{end}}

{{define "insertBatchTestTable"}}
{{$size := len . | add -1}}
INSERT INTO test_table(id,username,password,createtime)
VALUES {{range $i, $v := .}}
({{arg $v.Id}}, {{arg $v.Username}}, {{arg $v.Password}}, {{arg $v.Createtime}}){{if lt $i $size}},{{end}}
{{end}}
{{end}}

{{define "updateTestTable"}}
UPDATE test_table
{{set .Id "id = " (arg .Id) "" | set .Username "username = " (arg .Username) | set .Password "password = " (arg .Password) | set .Createtime "createtime = " (arg .Createtime)}}
{{where .Id "AND" "id = " (arg .Id) ""}}
{{end}}

{{define "deleteTestTable"}}
DELETE FROM test_table
{{where .Id "AND" "id = " (arg .Id) "" | where .Username "AND" "username = " (arg .Username) | where .Password "AND" "password = " (arg .Password) | where .Createtime "AND" "createtime = " (arg .Createtime)}}
{{end}}
  1. namespace

template数据或文件可定义一个名称为namespace的子模版，用以定义namespace。

template数据或文件注册之后，session参数sql id与模板对应关系为：${NAMESPACE}+"."+${ACTION_ID}

以2中的template为例，调用select的方式为：

sess.Select("test.selectTestTable").Param(model).Result(&dataList)
10、gobatis-cmd生成文件使用示例

参考cmd_test

11、 SQL语句构建器

gobatis xml特性有非常强大的动态SQL生成方案，当需要在代码中嵌入SQL语句时，也可使用SQL语句构建器：

import "github.com/xfali/gobatis/builder"

    str := builder.Select("A.test1", "B.test2").
            Select("B.test3").
            From("test_a AS A").
            From("test_b AS B").
            Where("id = 1").
            And().
            Where("name=2").
            GroupBy("name").
            OrderBy("name").
            Desc().
            Limit(5, 10).
            String()
    t.Log(str)

其他

1、分页

使用pagehelper: gobatis的配套分页工具

go get github.com/xfali/pagehelper
2、模糊查询支持

使用LIKE CONCAT('%',#{field},'%')

举例：

 SELECT <include refid="columns_id"> </include> FROM `TEST_TABLE`
        <where>
            <if test="{TestTable.username} != nil">AND `username` LIKE CONCAT('%',#{TestTable.username},'%') </if>
        </where>

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func CreateFactory added in v0.0.5 

func CreateFactory(opts ...FacOpt) (factory.Factory, error)

func DynamicParserFactory added in v0.2.3 

func DynamicParserFactory(sql string) (sqlparser.SqlParser, error)

func FindDynamicSqlParser added in v0.2.1 

func FindDynamicSqlParser(sqlId string) (sqlparser.SqlParser, bool)

func FindTemplateSqlParser added in v0.2.1 

func FindTemplateSqlParser(sqlId string) (sqlparser.SqlParser, bool)

func NewFactory added in v0.0.5 

func NewFactory(opts ...FacOpt) factory.Factory

func ParseObject 

func ParseObject(bean interface{}) (reflection.Object, error)

func RegisterMapperData 

func RegisterMapperData(data []byte) error

func RegisterMapperFile 

func RegisterMapperFile(file string) error

func RegisterModel 

func RegisterModel(model interface{})

注册struct模型，模型描述了column和field之间的关联关系； 目前已非必要条件

func RegisterModelWithName 

func RegisterModelWithName(name string, model interface{}) error

func RegisterSql 

func RegisterSql(sqlId string, sql string) error

func RegisterTemplateData added in v0.2.1 

func RegisterTemplateData(data []byte) error

func RegisterTemplateFile added in v0.2.1 

func RegisterTemplateFile(file string) error

func ScanMapperFile added in v0.2.5 

func ScanMapperFile(dir string) error

func TemplateParserFactory added in v0.2.3 

func TemplateParserFactory(sql string) (sqlparser.SqlParser, error)

func UnregisterSql 

func UnregisterSql(sqlId string)

Types

type BaseRunner 

type BaseRunner struct {
	// contains filtered or unexported fields
}

func (*BaseRunner) Context 

func (this *BaseRunner) Context(ctx context.Context) Runner

Context 设置执行的context

func (*BaseRunner) LastInsertId 

func (this *BaseRunner) LastInsertId() int64

func (*BaseRunner) Param 

func (this *BaseRunner) Param(params ...interface{}) Runner

func (*BaseRunner) Result 

func (this *BaseRunner) Result(bean interface{}) error

type DeleteRunner 

type DeleteRunner struct {
	BaseRunner
}

func (*DeleteRunner) Result 

func (this *DeleteRunner) Result(bean interface{}) error

type ExecRunner added in v0.2.3 

type ExecRunner struct {
	BaseRunner
}

func (*ExecRunner) Result added in v0.2.3 

func (this *ExecRunner) Result(bean interface{}) error

type FacOpt added in v0.0.5 

type FacOpt func(f *factory.DefaultFactory)

func SetConnMaxLifetime added in v0.0.5 

func SetConnMaxLifetime(v time.Duration) FacOpt

func SetDataSource added in v0.0.5 

func SetDataSource(v datasource.DataSource) FacOpt

func SetLog added in v0.0.5 

func SetLog(v logging.LogFunc) FacOpt

func SetMaxConn added in v0.0.5 

func SetMaxConn(v int) FacOpt

func SetMaxIdleConn added in v0.0.5 

func SetMaxIdleConn(v int) FacOpt

type InsertRunner 

type InsertRunner struct {
	BaseRunner
	// contains filtered or unexported fields
}

func (*InsertRunner) LastInsertId 

func (this *InsertRunner) LastInsertId() int64

func (*InsertRunner) Result 

func (this *InsertRunner) Result(bean interface{}) error

type ModelName 

type ModelName string

type ObjectCache 

type ObjectCache struct {
	// contains filtered or unexported fields
}

type ParserFactory added in v0.2.3 

type ParserFactory func(sql string) (sqlparser.SqlParser, error)

type Runner 

type Runner interface {
	//参数
	//注意：如果没有参数也必须调用
	//如果参数个数为1并且为struct，将解析struct获得参数
	//如果参数个数大于1并且全部为简单类型，或则个数为1且为简单类型，则使用这些参数
	Param(params ...interface{}) Runner
	//获得结果
	Result(bean interface{}) error
	//最后插入的自增id
	LastInsertId() int64
	//设置Context
	Context(ctx context.Context) Runner
}

type SelectRunner 

type SelectRunner struct {
	BaseRunner
	// contains filtered or unexported fields
}

func (*SelectRunner) Result 

func (this *SelectRunner) Result(bean interface{}) error

type Session 

type Session struct {
	ParserFactory ParserFactory
	// contains filtered or unexported fields
}

func (*Session) Delete 

func (this *Session) Delete(sql string) Runner

func (*Session) Exec added in v0.2.3 

func (this *Session) Exec(sql string) Runner

func (*Session) GetContext 

func (this *Session) GetContext() context.Context

func (*Session) Insert 

func (this *Session) Insert(sql string) Runner

func (*Session) Select 

func (this *Session) Select(sql string) Runner

func (*Session) SetContext 

func (this *Session) SetContext(ctx context.Context) *Session

func (*Session) SetParserFactory added in v0.2.3 

func (this *Session) SetParserFactory(fac ParserFactory)

修改sql解析器创建者

func (*Session) Tx 

func (this *Session) Tx(txFunc func(session *Session) error) error

开启事务执行语句 返回nil则提交，返回error回滚 抛出异常错误触发回滚

func (*Session) Update 

func (this *Session) Update(sql string) Runner

type SessionManager 

type SessionManager struct {
	ParserFactory ParserFactory
	// contains filtered or unexported fields
}

func NewSessionManager 

func NewSessionManager(factory factory.Factory) *SessionManager

func (*SessionManager) Close added in v0.2.5 

func (this *SessionManager) Close() error

func (*SessionManager) NewSession 

func (this *SessionManager) NewSession() *Session

使用一个session操作数据库

func (*SessionManager) SetParserFactory added in v0.2.3 

func (this *SessionManager) SetParserFactory(fac ParserFactory)

修改sql解析器创建者

type UpdateRunner 

type UpdateRunner struct {
	BaseRunner
}

func (*UpdateRunner) Result 

func (this *UpdateRunner) Result(bean interface{}) error

Source Files

View all Source files

Directories

Path Synopsis
sqlparser
template
xml
cmd

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.