快速开始

我们将使用一个简单的例子来阐述gobatis的CRUD操作。

假设现在有一张表为test_table 的表

CREATE TABLE `test_table`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `createTime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 65 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

数据为：

INSERT INTO `test_table` VALUES (1, 'user1', '123456', '2022-07-24 02:32:29');
INSERT INTO `test_table` VALUES (2, 'user2', '123456', '2022-07-24 02:21:41');
INSERT INTO `test_table` VALUES (3, 'user3', '123456', '2022-07-24 02:22:07');

从github上获取gobatis-example项目

1. 执行命令下载依赖

go mod tidy

1. 连接数据库

   打开quick_start目录下的 test_model_test.go 文件 配置数据库地址

func connect() factory.Factory {
  return gobatis.NewFactory(
    gobatis.SetMaxConn(100),
    gobatis.SetMaxIdleConn(50),
    gobatis.SetDataSource(&datasource.MysqlDataSource{
      Host:     "localhost", // 数据库IP
      Port:     3306,        // 数据库端口
      DBName:   "test",      // 数据库名
      Username: "root",      // 数据库用户名
      Password: "123456",    // 数据库密码
      Charset:  "utf8",      // 编码格式
    }))
}

1. 运行测试例子

func TestTestTable_Insert(t *testing.T) {
  testTable := &TestTable{
    CreateTime: time.Now(),
    Id:         1,
    Username:   "user1",
    Password:   "123456",
  }
  result, id, err := testTable.Insert(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  fmt.Println(result)
  fmt.Println(id)
}

func TestTestTable_Select(t *testing.T) {
  table := &TestTable{}
  tables, err := table.Select(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  marshal, _ := json.Marshal(tables)
  fmt.Println(string(marshal))
}

func TestTestTable_Count(t *testing.T) {
  table := &TestTable{}
  count, err := table.Count(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  fmt.Println(count)
}

func TestTestTable_Update(t *testing.T) {
  table := &TestTable{
    Id:       1,
    Password: "654321",
  }
  result, err := table.Update(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  fmt.Println(result)
}

func TestTestTable_Delete(t *testing.T) {
  table := &TestTable{
    Username: "user1",
  }
  result, err := table.Delete(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  fmt.Println(result)
}

通过上面的例子，我们非常方便地完成了CRUD操作，下面我们来详细说明一下例子项目是如何跑起来的。

详细介绍

在XML中，我们存放的是动态的SQL语句。

在test_model 中，我们存放数据库对应的实体和调用基础的CRUD方法。

在test_table_proxy中封装了基础的CRUD的实现方法，通过这里调用XML编写的动态SQL语句。

在test_mode_test 中连接数据库和调用test_model 的CRUD方法。

test_table_mapper.xml

<!--This file was generated by acmestack/gobatis-cmd at -->
<!--2022-07-24 10:06:28.3273422 +0800 CST m=+0.029213301-->

<mapper namespace="test">
    <sql id="columns_id">createTime,id,password,username</sql>

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

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

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

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

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

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

test_model.go

package test

import (
  "github.com/acmestack/gobatis"
  "time"
)

type TestTable struct {
  //TableName gobatis.TableName `test_table`
  CreateTime time.Time `column:"createTime"`
  Id         int       `column:"id"`
  Password   string    `column:"password"`
  Username   string    `column:"username"`
}

func (m *TestTable) Select(sess *gobatis.Session) ([]TestTable, error) {
  return SelectTestTable(sess, *m)
}

func (m *TestTable) Count(sess *gobatis.Session) (int64, error) {
  return SelectTestTableCount(sess, *m)
}

func (m *TestTable) Insert(sess *gobatis.Session) (int64, int64, error) {
  return InsertTestTable(sess, *m)
}

func (m *TestTable) Update(sess *gobatis.Session) (int64, error) {
  return UpdateTestTable(sess, *m)
}

func (m *TestTable) Delete(sess *gobatis.Session) (int64, error) {
  return DeleteTestTable(sess, *m)
}

test_table_proxy.go

package test

import (
  "github.com/acmestack/gobatis"
)

func init() {
  modelV := TestTable{}
  gobatis.RegisterModel(&modelV)
}

func SelectTestTable(sess *gobatis.Session, model TestTable) ([]TestTable, error) {
  var dataList []TestTable
  err := sess.Select("test.selectTestTable").Param(model).Result(&dataList)
  return dataList, err
}

func SelectTestTableCount(sess *gobatis.Session, model TestTable) (int64, error) {
  var ret int64
  err := sess.Select("test.selectTestTableCount").Param(model).Result(&ret)
  return ret, err
}

func InsertTestTable(sess *gobatis.Session, model TestTable) (int64, int64, error) {
  var ret int64
  runner := sess.Insert("test.insertTestTable").Param(model)
  err := runner.Result(&ret)
  id := runner.LastInsertId()
  return ret, id, err
}

func InsertBatchTestTable(sess *gobatis.Session, models []TestTable) (int64, int64, error) {
  var ret int64
  runner := sess.Insert("test.insertBatchTestTable").Param(models)
  err := runner.Result(&ret)
  id := runner.LastInsertId()
  return ret, id, err
}

func UpdateTestTable(sess *gobatis.Session, model TestTable) (int64, error) {
  var ret int64
  err := sess.Update("test.updateTestTable").Param(model).Result(&ret)
  return ret, err
}

func DeleteTestTable(sess *gobatis.Session, model TestTable) (int64, error) {
  var ret int64
  err := sess.Delete("test.deleteTestTable").Param(model).Result(&ret)
  return ret, err
}

test_model_test.go

package test

import (
  "encoding/json"
  "fmt"
  "github.com/acmestack/gobatis"
  "github.com/acmestack/gobatis/datasource"
  "github.com/acmestack/gobatis/factory"
  _ "github.com/go-sql-driver/mysql"
  "testing"
  "time"
)

func connect() factory.Factory {
  return gobatis.NewFactory(
    gobatis.SetMaxConn(100),
    gobatis.SetMaxIdleConn(50),
    gobatis.SetDataSource(&datasource.MysqlDataSource{
      Host:     "localhost", // 数据库IP
      Port:     3306,        // 数据库端口
      DBName:   "test",      // 数据库名
      Username: "root",      // 数据库用户名
      Password: "123456",    // 数据库密码
      Charset:  "utf8",      // 编码格式
    }))
}

var sessionManager *gobatis.SessionManager

func init() {
  err := gobatis.RegisterMapperFile("./xml/test_table_mapper.xml")
  if err != nil {
    fmt.Println("parse xml is error:", err.Error())
  }
  sessionManager = gobatis.NewSessionManager(connect())
}

func TestTestTable_Insert(t *testing.T) {
  testTable := &TestTable{
    CreateTime: time.Now(),
    Username:   "user",
    Password:   "123456",
  }
  result, id, err := testTable.Insert(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  fmt.Println(result)
  fmt.Println(id)
}

func TestTestTable_Select(t *testing.T) {
  table := &TestTable{}
  tables, err := table.Select(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  marshal, _ := json.Marshal(tables)
  fmt.Println(string(marshal))
}

func TestTestTable_Count(t *testing.T) {
  table := &TestTable{}
  count, err := table.Count(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  fmt.Println(count)
}

func TestTestTable_Update(t *testing.T) {
  table := &TestTable{
    Id:       1,
    Password: "654321",
  }
  result, err := table.Update(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  fmt.Println(result)
}

func TestTestTable_Delete(t *testing.T) {
  table := &TestTable{
    Username: "user1",
  }
  result, err := table.Delete(sessionManager.NewSession())
  if err != nil {
    fmt.Println(err)
  }
  fmt.Println(result)
}

执行流程