# je-ibatis

**Repository Path**: wangscript/je-ibatis

## Basic Information

- **Project Name**: je-ibatis
- **Description**: JECloud和JEPaaS特色mybatis中间件
- **Primary Language**: Java
- **License**: MIT
- **Default Branch**: jecloud
- **Homepage**: https://jecloud.net
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 37
- **Created**: 2023-07-27
- **Last Updated**: 2023-07-27

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README


# JEIbatis项目

## 项目简介

JEIbatis项目，用于为其他应用服务提供基础持久层统一处理能力，使开发人员摆脱繁杂的实体和Mapper编写工作，提高服务的开发速度。

## 环境依赖

* jdk1.8
* maven
* 请使用官方仓库(http://maven.jepaas.com)，暂不同步jar到中央仓库。


> Maven安装 (http://maven.apache.org/download.cgi)

> Maven学习，请参考[maven-基础](docs/mannual/maven-基础.md)

# 主要目标
- 支持平台元数据：提供MetaDataParse接口，默认使用JdbcTemplate查询数据库获取资源表和功能信息，支持自定义元数据解析器(详见配置)
- 主键策略拓展：在mybatis现有自增主键、查询主键的基础上，增加路由主键，插入数据时动态路由到资源表对应的主键策略。
- 元数据标签：提供<meta/>标签，可在mybatis的xml中使用，通过方法参数中的TableCode与标签type属性(update|insert|select|columns|pk-col|load|load-columns)，动态生成sql。
- 条件解析器：增加ConditionsWrapper实现条件封装，支持自定义sql与复杂条件语句嵌套。
- 元数据CRUD：基于上述拓展，封装MetaBaseMapper，使用MetaStatementBuilder解析。继承MetaBaseMapper即可使用基础方法
- 分页插件：拓展MyBatis插件，支持多数据库方言，支持拓展。

## 项目依赖

- mybatis
- mybatis-spring
- spring-beans
- spring-context
- spring-jdbc

## 开源协议

- [MIT](./LICENSE)
- [平台证书补充协议](./SUPPLEMENTAL_LICENSE.md)

## JECloud主目录
[JECloud 微服务架构低代码平台（点击了解更多）](https://gitee.com/ketr/jecloud.git)

## Spring-MVC 配置

 ```xml
<!-- Mybatis SessionFactory-->
<bean id="sqlSessionFactory" class="com.je.ibatis.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="configurationProperties" >
        <bean class="org.springframework.beans.factory.config.PropertiesFactoryBean">
            <!-- mybatis配置文件 -->
            <property name="locations" value="classpath*:mybatis.properties"/>
        </bean>
    </property>
    <!-- 元数据解析器 实现 MetaDataParse 接口 -->
    <property name="metaDataParse">
        <bean class="com.je.ibatis.extension.parse.DefaultMetaDataParse"/>
    </property>
    <!-- 元数据缓存管理 实现 MetaDataCacheManager 接口-->
    <property name="cacheManager">
        <bean class="com.je.cache.service.ibatis.SaaSMetaDataCacheManager"/>
    </property>
    <property name="plugins">
        <array>
            <!-- 分页插件配置 -->
            <bean id="paginationInterceptor" class="com.je.ibatis.extension.plugins.PaginationInterceptor"/>
        </array>
    </property>
</bean>
<!-- MapperScanner 扫描Mapper 创建代理 -->
<bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.je.**.mapper" />
    <property name="annotationClass" value="org.apache.ibatis.annotations.Mapper" />
</bean>
 ```
 
## ConditionsWrapper 条件构造器
说明:
- 以下方法须按照正常sql语法顺序调用!，列如 orderBy 一定要最后调用
- 以下出现的第一个入参`boolean condition`表示该条件**是否**加入sql中
- 以下代码块内的多个方法均为从上往下补全个别`boolean`类型的入参,默认为`true`
- 以下出现的泛型`Param`均为`ConditionsWrapper`的实例
- 以下方法在入参中出现的`R`为泛型,在ConditionsWrapper中是`String`
- 以下方法入参中的`R column`均表示数据库字段
- 以下举例均为使用普通wrapper,入参为`Map`和`List`的均以`json`形式表现!


### allEq
``` java{3}
allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)
```
- 全部[eq](#eq)(或个别[isNull](#isnull))
 个别参数说明:
`params` : `key`为数据库字段名,`value`为字段值  
`null2IsNull` : 为`true`则在`map`的`value`为`null`时调用 [isNull](#isnull) 方法,为`false`时则忽略`value`为`null`的

- 例1: `allEq({id:1,name:"老王",age:null})`--->`id = 1 and name = '老王' and age is null`
- 例2: `allEq({id:1,name:"老王",age:null}, false)`--->`id = 1 and name = '老王'`

``` java{3}
allEq(BiPredicate<R, V> filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull) 
```
个别参数说明:
`filter` : 过滤函数,是否允许字段传入比对条件中  
`params` 与 `null2IsNull` : 同上

- 例1: `allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null})`--->`name = '老王' and age is null`
- 例2: `allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null}, false)`--->`name = '老王'`

### eq
``` java{2}
eq(R column, Object val)
eq(boolean condition, R column, Object val)
```
- 等于 =
- 例: `eq("name", "老王")`--->`name = '老王'`

### ne
``` java{2}
ne(R column, Object val)
ne(boolean condition, R column, Object val)
```
- 不等于 <>
- 例: `ne("name", "老王")`--->`name <> '老王'`

### gt
``` java{2}
gt(R column, Object val)
gt(boolean condition, R column, Object val)
```
- 大于 >
- 例: `gt("age", 18)`--->`age > 18`

### ge
``` java{2}
ge(R column, Object val)
ge(boolean condition, R column, Object val)
```
- 大于等于 >=
- 例: `ge("age", 18)`--->`age >= 18`

### lt
``` java{2}
lt(R column, Object val)
lt(boolean condition, R column, Object val)
```
- 小于 <
- 例: `lt("age", 18)`--->`age < 18`

### le
``` java{2}
le(R column, Object val)
le(boolean condition, R column, Object val)
```
- 小于等于 <=
- 例: `le("age", 18)`--->`age <= 18`

### between
``` java{2}
between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)
```
- BETWEEN 值1 AND 值2
- 例: `between("age", 18, 30)`--->`age between 18 and 30`

### notBetween
``` java{2}
notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)
```
- NOT BETWEEN 值1 AND 值2
- 例: `notBetween("age", 18, 30)`--->`age not between 18 and 30`

### like
``` java{2}
like(R column, Object val)
like(boolean condition, R column, Object val)
```
- LIKE '%值%'
- 例: `like("name", "王")`--->`name like '%王%'`

### notLike
``` java{2}
notLike(R column, Object val)
notLike(boolean condition, R column, Object val)
```
- NOT LIKE '%值%'
- 例: `notLike("name", "王")`--->`name not like '%王%'`

### likeLeft
``` java{2}
likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)
```
- LIKE '%值'
- 例: `likeLeft("name", "王")`--->`name like '%王'`

### likeRight
``` java{2}
likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)
```
- LIKE '值%'
- 例: `likeRight("name", "王")`--->`name like '王%'`

### isNull
``` java{2}
isNull(R column)
isNull(boolean condition, R column)
```
- 字段 IS NULL
- 例: `isNull("name")`--->`name is null`

### isNotNull
``` java{2}
isNotNull(R column)
isNotNull(boolean condition, R column)
```
- 字段 IS NOT NULL
- 例: `isNotNull("name")`--->`name is not null`

### in
``` java{2}
in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)
```
- 字段 IN (value.get(0), value.get(1), ...)
- 例: `in("age",[1,2,3])`--->`age in (1,2,3)`
- 例: `in("age",[])`--->`age in (null)`

``` java{2}
in(R column, Object... values)
in(boolean condition, R column, Object... values)
```
- 字段 IN (v0, v1, ...)
- 例: `in("age", 1, 2, 3)`--->`age in (1,2,3)`

### notIn
``` java{2}
notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)
```
- 字段 IN (value.get(0), value.get(1), ...)
- 例: `notIn("age",[1,2,3])`--->`age not in (1,2,3)`

``` java{2}
notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)
```
- 字段 NOT IN (v0, v1, ...)
- 例: `notIn("age", 1, 2, 3)`--->`age not in (1,2,3)`

### inSql
``` java{2}
inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)
```
- 字段 IN ( sql语句 )
- 例: `inSql("age", "1,2,3,4,5,6")`--->`age in (1,2,3,4,5,6)`
- 例: `inSql("id", "select id from table where id < 3")`--->`id in (select id from table where id < 3)`

### notInSql
``` java{2}
notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)
```
- 字段 NOT IN ( sql语句 )
- 例: `notInSql("age", "1,2,3,4,5,6")`--->`age not in (1,2,3,4,5,6)`
- 例: `notInSql("id", "select id from table where id < 3")`--->`age not in (select id from table where id < 3)`

### groupBy
``` java{2}
groupBy(R... columns)
groupBy(boolean condition, R... columns)
```
- 分组：GROUP BY 字段, ...
- 例: `groupBy("id", "name")`--->`group by id,name`

### orderByAsc
``` java{2}
orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)
```
- 排序：ORDER BY 字段, ... ASC
- 例: `orderByAsc("id", "name")`--->`order by id ASC,name ASC`

### orderByDesc
``` java{2}
orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)
```
- 排序：ORDER BY 字段, ... DESC
- 例: `orderByDesc("id", "name")`--->`order by id DESC,name DESC`

### orderBy
``` java{1}
orderBy(boolean condition, boolean isAsc, R... columns)
```
- 排序：ORDER BY 字段, ...
- 例: `orderBy(true, true, "id", "name")`--->`order by id ASC,name ASC`

### or
``` java{2}
or()
or(boolean condition)
```
- 拼接 OR
注意事项:
主动调用`or`表示紧接着下一个**方法**不是用`and`连接!(不调用`or`则默认为使用`and`连接)

- 例: `eq("id",1).or().eq("name","老王")`--->`id = 1 or name = '老王'`

``` java{2}
or(Consumer<Param> consumer)
or(boolean condition, Consumer<Param> consumer)
```
- OR 嵌套
- 例: `or(i -> i.eq("name", "李白").ne("status", "活着"))`--->`or (name = '李白' and status <> '活着')`

### and
``` java{2}
and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)
```
- AND 嵌套
- 例: `and(i -> i.eq("name", "李白").ne("status", "活着"))`--->`and (name = '李白' and status <> '活着')`

### nested
``` java{2}
nested(Consumer<Param> consumer)
nested(boolean condition, Consumer<Param> consumer)
```
- 正常嵌套 不带 AND 或者 OR
- 例: `nested(i -> i.eq("name", "李白").ne("status", "活着"))`--->`(name = '李白' and status <> '活着')`

### exists
``` java{2}
exists(String existsSql)
exists(boolean condition, String existsSql)
```
- 拼接 EXISTS ( sql语句 )
- 例: `exists("select id from table where age = 1")`--->`exists (select id from table where age = 1)`

### notExists
``` java{2}
notExists(String notExistsSql)
notExists(boolean condition, String notExistsSql)
```
- 拼接 NOT EXISTS ( sql语句 )
- 例: `notExists("select id from table where age = 1")`--->`not exists (select id from table where age = 1)`

### apply
``` java{2}
apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)
```
- 拼接 sql
 注意事项:
- 动态入参`params`对应前面`applySql`内部的`{index}`部分.使用预处理方式，不会有sql注入风险的!
- 此方法会保留原有sql,不会对sql字符串进行增删操作。
- 可变参中不可包含数组,如有in语句预处理参数应使用List或Set(见示例)。

######
- 例: `apply("id = 1")`--->`id = 1`
- 例: `apply("id in ({0})", Lists.newArrayList(1,2,3))`--->`and id in(1,2,3)`
- 例: `apply("name like {0}", "jay%")`--->`name like 'jay%'`
- 例: `apply("name like {0}", "%jay%")`--->`name like '%jay%'`
- 例: `apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")`--->`date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")`
- 例: `apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")`--->`date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")`


# MetaBaseMapper 平台基础CRUD封装

说明:
- 以下出现的变量`mapper`均为`MetaBaseMapper`子接口的Mapper实例
- 以下出现的变量`tableCode`均为平台资源表名称
- 以下出现的变量`pkValue`均为主键
- 以下出现的变量`wrapper`均为`ConditionsWrapper`实例

### 继承 MetaBaseMapper
``` java
@Mapper
public interface MetaMapper extends MetaBaseMapper {
}
```

### 注入 MetaBaseMapper 子接口实例
``` java
@Autowired
private MetaMapper baseDataMapper;
```

### insertMap
- 平台资源表基础插入方法,须指定 tableCode
``` java
int insertMap(Map<String, Object> beanMap)
```
- 示例
``` java
Map<String, Object> beanMap = new HashMap<>();
beanMap.put(Constants.KEY_TABLE_CODE, tableCode);
// 如果插入前未指定主键值,则会使用自动生成主键策略
// beanMap.put("ID", "1");
beanMap.put("CODE", "编号");
beanMap.put("NAME", "名称");
// 插入数据
mapper.insertMap(beanMap);
// 主键为自动生成时,插入后会将主键值放入Map
Object pkValue = beanMap.get("ID");
```

### updateMap
- 平台资源表基础修改方法,须指定 tableCode
``` java
int updateMap(@Param(Constants.MAP_ALIAS) Map<String, Object> beanMap, @Param(Constants.WRAPPER_ALIAS) ConditionsWrapper wrapper)
```
- 示例
``` java
Map<String, Object> beanMap = new HashMap<>();
beanMap.put(Constants.KEY_TABLE_CODE, tableCode);
beanMap.put("CODE", "编号01");
beanMap.put("NAME", "名称01");
// 如果wrapper为空时,使用主键作为where条件
beanMap.put(Constants.KEY_PK_VALUE, pkValue);
//update语句的where条件
ConditionsWrapper wrapper = ConditionsWrapper.builder().apply("CODE = '编号' and NAME = '名称'");
mapper.updateMap(beanMap, wrapper);
```

### select
- 平台资源表基础查询方法,查询资源表全部字段,须指定 tableCode
``` java
List<Map<String, Object>> select(@Param(Constants.PAGE_ALIAS) Page page, @Param(Constants.WRAPPER_ALIAS) ConditionsWrapper wrapper);
```
- 示例
``` java
//页编码 current = -1 时不分页
int current = 1;
//每页数量
int size = 30;
Page page = new Page(current, size);
//select语句的where条件
ConditionsWrapper wrapper = ConditionsWrapper.builder()
    //指定tableCode
    .table(tableCode)
    //添加where条件
    .apply("CODE = '编号' and NAME = '名称'")
    .apply(" ORDER BY CODE ");
//wrapper作为where条件语句时不能以AND开头, 可调用 wrapper.getSql()进行调试。
List<Map<String, Object>> list = mapper.select(page, wrapper);
```

### load
- 平台功能基础查询方法,只查询功能列表加载字段,须指定 funcCode 功能编码
``` java
List<Map<String, Object>> load(@Param(Constants.PAGE_ALIAS) Page page, @Param(Constants.WRAPPER_ALIAS) ConditionsWrapper wrapper);
```
- 示例
``` java
//页编码 current = -1 时不分页
int current = 1;
//每页数量
int size = 30;
Page page = new Page(current, size);
//select语句的where条件
ConditionsWrapper wrapper = ConditionsWrapper.builder()
    //指定funcCode
    .function(funcCode)
    //添加where条件
    .apply("CODE = '编号' and NAME = '名称'")
    .apply(" ORDER BY CODE ");
//wrapper作为where条件语句时不能以AND开头, 可调用 wrapper.getSql()进行调试。
List<Map<String, Object>> list = mapper.load(page, wrapper);
```

### selectOneByPk
- 根据主键查询,须指定 tableCode pkValue
``` java
Map<String, Object> selectOneByPk(@Param(Constants.KEY_TABLE_CODE) String tableCode, @Param(Constants.KEY_PK_VALUE) String pkValue);
```
- 示例
``` java
//使用表名与主键查询，查询资源表所有列
Map<String, Object> map = mapper.selectOneByPk(tableCode, pkValue);
```

### delete
- 平台资源表基础删除方法
``` java
int delete(@Param(Constants.WRAPPER_ALIAS) ConditionsWrapper wrapper);
```
- 示例
``` java
//delete语句的where条件
ConditionsWrapper wrapper = ConditionsWrapper.builder()
    //指定tableCode
    .table(tableCode)
    //添加where条件
    .apply("CODE = '编号' and NAME = '名称'")
    .apply(" ORDER BY CODE ");
//wrapper作为where条件语句时不能以AND开头, 可调用 wrapper.getSql()进行调试。
int num = mapper.delete(wrapper);
```

### selectSql
- 自定义Sql查询
``` java
List<Map<String, Object>> selectSql(@Param(Constants.PAGE_ALIAS) Page page, @Param(Constants.WRAPPER_ALIAS) ConditionsWrapper wrapper);
```
- 示例
``` java
//页编码 current = -1 时不分页
int current = 1;
//每页数量
int size = 30;
Page page = new Page(current, size);
//wrapper为完整select语句
ConditionsWrapper wrapper = ConditionsWrapper.builder()
    .apply("select * from USER where ")
    //添加where条件
    .eq("CODE", "编号")
    .apply(" and NAME = {0}", "名称")
    .apply(" ORDER BY CODE ");
List<Map<String, Object>> list = mapper.selectSql(page, wrapper);
```

### insertSql
- 执行新增sql
``` java
int insertSql(@Param(Constants.WRAPPER_ALIAS) ConditionsWrapper wrapper);
```
- 示例
``` java
//wrapper为完整语句
ConditionsWrapper wrapper = ConditionsWrapper.builder()
    .apply("INSERT INTO USER (CODE, NAME) VALUES ({0}, {1})", "编号", "名称");
int num = mapper.insertSql(wrapper);
```

### updateSql
- 执行更新sql
``` java
int updateSql(@Param(Constants.WRAPPER_ALIAS) ConditionsWrapper wrapper);
```
- 示例
``` java
//wrapper为完整语句
ConditionsWrapper wrapper = ConditionsWrapper.builder()
    .apply("UPDATE USER SET AGE = {0} WHERE CODE = {1} ", 18, "Wang");
int num = mapper.updateSql(wrapper);
```

### deleteSql
- 执行删除sql
``` java
int deleteSql(@Param(Constants.WRAPPER_ALIAS) ConditionsWrapper wrapper);
```
- 示例
``` java
//wrapper为完整语句
ConditionsWrapper wrapper = ConditionsWrapper.builder()
    .apply("DELETE FROM USER WHERE CODE = {0} ", "Wilson");
int num = mapper.deleteSql(wrapper);
```