sqltoy-orm是比hibernate+myBatis更加贴合项目的orm框架(依赖spring)，具有jpa式的对象CRUD的同时具有比myBatis(plus)更直观简洁性能强大的查询功能。 支持以下数据库:
<sql id="show_case">
<filters>
<!-- 参数statusAry只要包含-1(代表全部)则将statusAry设置为null不参与条件检索 -->
<eq params="statusAry" value="-1" />
</filters>
<value><![CDATA[
select *
from sqltoy_device_order_info t
where #[t.status in (:statusAry)]
#[and t.ORDER_ID=:orderId]
#[and t.ORGAN_ID in (:authedOrganIds)]
#[and t.STAFF_ID in (:staffIds)]
#[and t.TRANS_DATE>=:beginDate]
#[and t.TRANS_DATE<:endDate]
]]></value>
</sql><select id="show_case" resultMap="BaseResultMap">
select *
from sqltoy_device_order_info t
<where>
<if test="statusAry!=null">
and t.status in
<foreach collection="status" item="statusAry" separator="," open="(" close=")">
#{status}
</foreach>
</if>
<if test="orderId!=null">
and t.ORDER_ID=#{orderId}
</if>
<if test="authedOrganIds!=null">
and t.ORGAN_ID in
<foreach collection="authedOrganIds" item="order_id" separator="," open="(" close=")">
#{order_id}
</foreach>
</if>
<if test="staffIds!=null">
and t.STAFF_ID in
<foreach collection="staffIds" item="staff_id" separator="," open="(" close=")">
#{staff_id}
</foreach>
</if>
<if test="beginDate!=null">
and t.TRANS_DATE>=#{beginDate}
</if>
<if test="endDate!=null">
and t.TRANS_DATE<#{endDate}
</if>
</where>
</select>select *
from sqltoy_device_order_info t
where #[t.ORGAN_ID in (:authedOrganIds)]
#[and t.TRANS_DATE>=:beginDate]
#[and t.TRANS_DATE<:endDate] sqlToyLazyDao.findBySql(sql, new String[] { "authedOrganIds","beginDate", "endDate"},
new Object[] { authedOrganIdAry,beginDate,null}, DeviceOrderInfoVO.class);select *
from sqltoy_device_order_info t
where t.ORDER_ID=?
and t.ORGAN_ID in (?,?,?)
and t.TRANS_DATE>=? <!-- 快速分页和分页优化演示 -->
<sql id="sqltoy_fastPage">
<!-- 分页优化器,通过缓存实现查询条件一致的情况下在一定时间周期内缓存总记录数量，从而无需每次查询总记录数量 -->
<!-- alive-max:最大存放多少个不同查询条件的总记录量; alive-seconds:查询条件记录量存活时长(比如120秒,超过阀值则重新查询) -->
<page-optimize alive-max="100" alive-seconds="120" />
<value>
<![CDATA[
select t1.*,t2.ORGAN_NAME
-- @fast() 实现先分页取10条(具体数量由pageSize确定),然后再关联
from @fast(select t.*
from sqltoy_staff_info t
where t.STATUS=1
#[and t.STAFF_NAME like :staffName]
order by t.ENTRY_DATE desc
) t1
left join sqltoy_organ_info t2 on t1.organ_id=t2.ORGAN_ID
]]>
</value>
<!-- 这里为极特殊情况下提供了自定义count-sql来实现极致性能优化 -->
<!-- <count-sql></count-sql> -->
</sql>/**
* 基于对象传参数模式
*/
public void findPageByEntity() {
PaginationModel pageModel = new PaginationModel();
StaffInfoVO staffVO = new StaffInfoVO();
// 作为查询条件传参数
staffVO.setStaffName("陈");
// 使用了分页优化器
// 第一次调用:执行count 和 取记录两次查询
PaginationModel result = sqlToyLazyDao.findPageBySql(pageModel, "sqltoy_fastPage", staffVO);
System.err.println(JSON.toJSONString(result));
// 第二次调用:过滤条件一致，则不会再次执行count查询
//设置为第二页
pageModel.setPageNo(2);
result = sqlToyLazyDao.findPageBySql(pageModel, "sqltoy_fastPage", staffVO);
System.err.println(JSON.toJSONString(result));
}
/**
* 基于参数数组传参数
*/
public void findPageByParams() {
//默认pageSize 为10，pageNo 为1
PaginationModel pageModel = new PaginationModel();
String[] paramNames=new String[]{"staffName"};
Object[] paramValues=new Object[]{"陈"};
PaginationModel result = sqlToyLazyDao.findPageBySql(pageModel, "sqltoy_fastPage",paramNames,paramValues,StaffInfoVO.class);
System.err.println(JSON.toJSONString(result));
}
<sql id="sqltoy_order_search">
<!-- 缓存翻译设备类型
cache:具体的缓存定义的名称，
cache-type:一般针对数据字典，提供一个分类条件过滤
columns:sql中的查询字段名称，可以逗号分隔对多个字段进行翻译
cache-indexs:缓存数据名称对应的列,不填则默认为第二列(从0开始,1则表示第二列)，
例如缓存的数据结构是:key、name、fullName,则第三列表示全称
-->
<translate cache="dictKeyName" cache-type="DEVICE_TYPE" columns="deviceTypeName" cache-indexs="1"/>
<!-- 员工名称翻译,如果同一个缓存则可以同时对几个字段进行翻译 -->
<translate cache="staffIdName" columns="staffName,createName" />
<filters>
<!-- 反向利用缓存通过名称匹配出id用于精确查询 -->
<cache-arg cache-name="staffIdNameCache" param="staffName" alias-name="staffIds"/>
</filters>
<value>
<![CDATA[
select ORDER_ID,
DEVICE_TYPE,
DEVICE_TYPE deviceTypeName,-- 设备分类名称
STAFF_ID,
STAFF_ID staffName, -- 员工姓名
ORGAN_ID,
CREATE_BY,
CREATE_BY createName -- 创建人名称
from sqltoy_device_order_info t
where #[t.ORDER_ID=:orderId]
#[and t.STAFF_ID in (:staffIds)]
]]>
</value>
</sql>default:SubStr\Trim\Instr\Concat\Nvl 函数；可以参见org.sagacity.sqltoy.plugins.function.Nvl 代码实现
<!-- 跨数据库函数自动替换(非必须项),适用于跨数据库软件产品,如mysql开发，oracle部署 -->
<property name="functionConverts" value="default">
<!-- 也可以这样自行根据需要进行定义和扩展
<property name="functionConverts">
<list>
<value>org.sagacity.sqltoy.plugins.function.Nvl</value>
<value>org.sagacity.sqltoy.plugins.function.SubStr</value>
<value>org.sagacity.sqltoy.plugins.function.Now</value>
<value>org.sagacity.sqltoy.plugins.function.Length</value>
</list>
</property> -->
</bean>
## 2.5 提供行列转换(数据旋转)，避免写复杂的sql或存储过程，用算法来化解对sql的高要求，同时实现数据库无关(不管是mysql还是sqlserver)
```xml
<!-- 列转行测试 -->
<sql id="sys_unpvoitSearch">
<value>
<![CDATA[
SELECT TRANS_DATE,
sum(TOTAL_AMOUNT) TOTAL_AMOUNT,
sum(PERSON_AMOUNT) PERSON_AMOUNT,
sum(COMPANY_AMOUNT) COMPANY_AMOUNT
FROM sys_unpivot_data
group by TRANS_DATE
]]>
</value>
<!-- 将指定的列变成行(这里3列变成了3行) -->
<unpivot columns="TOTAL_AMOUNT:总金额,PERSON_AMOUNT:个人金额,COMPANY_AMOUNT:企业金额"
values-as-column="TRANS_AMOUNT" labels-as-column="AMOUNT_TYPE" />
</sql>
<!-- 行转列测试 -->
<sql id="sys_pvoitSearch">
<value>
<![CDATA[
select t.TRANS_DATE,t.TRANS_CHANNEL,TRANS_CODE,sum(t.TRANS_AMT) TRANS_AMT from sys_summary_case t
group by t.TRANS_DATE,t.TRANS_CHANNEL,TRANS_CODE
order by t.TRANS_DATE,t.TRANS_CHANNEL,TRANS_CODE
]]>
</value>
<pivot category-columns="TRANS_CHANNEL,TRANS_CODE" start-column="TRANS_AMT"
default-value="0" default-type="decimal" end-column="TRANS_AMT"
group-columns="TRANS_DATE" />
</sql> <!-- 汇总计算 (场景是sql先汇总，页面上还需要对已有汇总再汇总的情况,如果用sql实现在跨数据库的时候就存在问题)-->
<sql id="sys_summarySearch">
<!-- 数据源sharding，多库将请求压力分摊到多个数据库节点上，支撑更多并发请求 -->
<sharding-datasource strategy="multiDataSource" />
<value>
<![CDATA[
select t.TRANS_CHANNEL,t.TRANS_CODE,sum( t.TRANS_AMT )
from sys_summary_case t
group by t.TRANS_CHANNEL,t.TRANS_CODE
]]>
</value>
<!-- reverse 表示将汇总信息在上面显示(如第1行是汇总值，第2、3、4行为明细，反之，1、2、3行未明细，第4行为汇总) -->
<summary columns="2" reverse="true" sum-site="left" radix-size="2">
<global sum-label="总计" label-column="0" />
<!-- 可以无限层级的分组下去-->
<group sum-label="小计/平均" label-column="0" group-column="0" average-label="平均" />
</summary>
</sql> sql参见showcase项目:com/sagframe/sqltoy/showcase/sqltoy-showcase.sql.xml 文件
sharding策略配置参见:src/main/resources/spring/spring-sqltoy-sharding.xml 配置
<!-- 演示分库 -->
<sql id="sqltoy_db_sharding_case">
<sharding-datasource
strategy="hashBalanceDBSharding" params="userId" />
<value>
<![CDATA[
select * from sqltoy_user_log t
-- userId 作为分库关键字段属于必备条件
where t.user_id=:userId
#[and t.log_date>=:beginDate]
#[and t.log_date<=:endDate]
]]>
</value>
</sql>
<!-- 演示分表 -->
<sql id="sqltoy_15d_table_sharding_case">
<sharding-table tables="sqltoy_trans_info_15d"
strategy="historyTableStrategy" params="beginDate" />
<value>
<![CDATA[
select * from sqltoy_trans_info_15d t
where t.trans_date>=:beginDate
#[and t.trans_date<=:endDate]
]]>
</value>
</sql>
@Sharding 在对象上通过注解来实现分库分表的策略配置
参见:com.sagframe.sqltoy.showcase.ShardingCaseServiceTest 进行演示
package com.sagframe.sqltoy.showcase.vo;
import java.time.LocalDate;
import java.time.LocalDateTime;
import org.sagacity.sqltoy.config.annotation.Sharding;
import org.sagacity.sqltoy.config.annotation.SqlToyEntity;
import org.sagacity.sqltoy.config.annotation.Strategy;
import com.sagframe.sqltoy.showcase.vo.base.AbstractUserLogVO;
/**
* @project sqltoy-showcase
* @author zhongxuchen
* @version 1.0.0 Table: sqltoy_user_log,Remark:用户日志表
*/
/*
* db则是分库策略配置,table 则是分表策略配置，可以同时配置也可以独立配置
* 策略name要跟spring中的bean定义name一致,fields表示要以对象的哪几个字段值作为判断依据,可以一个或多个字段
* maxConcurrents:可选配置，表示最大并行数 maxWaitSeconds:可选配置，表示最大等待秒数
*/
@Sharding(db = @Strategy(name = "hashBalanceDBSharding", fields = { "userId" }),
// table = @Strategy(name = "hashBalanceSharding", fields = {"userId" }),
maxConcurrents = 10, maxWaitSeconds = 1800)
@SqlToyEntity
public class UserLogVO extends AbstractUserLogVO {
/**
*
*/
private static final long serialVersionUID = 1296922598783858512L;
/** default constructor */
public UserLogVO() {
super();
}
}
主键策略除了数据库自带的 sequence\identity 外包含以下数据库无关的主键策略。通过quickvo配置，自动生成在VO对象中。根据对象属性值,产生规则有序的ID,比如:订单类型为采购:P 销售:S，贸易类型：I内贸;O 外贸; 订单号生成规则为:1位订单类型+1位贸易类型+yyMMdd+3位流水(超过3位自动扩展) 最终会生成单号为:SI191120001
sqltoy-starter-showcase：演示无xml配置形式的基于boot-starter模式的集成
package com.sagframe.sqltoy;import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.annotation.ComponentScan; import org.springframework.transaction.annotation.EnableTransactionManagement;
/**
@author zhongxuchen */ @SpringBootApplication @ComponentScan(basePackages = { "com.sagframe.sqltoy" }) @EnableTransactionManagement public class SqlToyApplication {
/**
application.properties sqltoy部分配置
```javascript
## sqltoy 配置
# sql.xml 文件的路径,多个路径用;符合分割(原则上也是可选配置，如果只用对象操作的话,但不建议)
spring.sqltoy.sqlResourcesDir=/com/sagframe/sqltoy/showcase
# 缓存翻译的配置(可选配置)
spring.sqltoy.translateConfig=classpath:sqltoy-translate.xml
# 是否debug模式,debug 模式会打印执行的sql和参数信息(可选配置)
spring.sqltoy.debug=true
# 设置默认使用的datasource(可选配置,不配置会自动注入)
spring.sqltoy.defaultDataSource=dataSource
# 提供统一字段:createBy createTime updateBy updateTime 等字段补漏性(为空时)赋值(可选配置)
spring.sqltoy.unifyFieldsHandler=com.sagframe.sqltoy.plugins.SqlToyUnifyFieldsHandler
# sql执行超过多长时间则进行日志输出(可选配置:默认30秒)，用于监控哪些慢sql
spring.sqltoy.printSqlTimeoutMillis=30000
缓存翻译的配置文件sqltoy-translate.xml
<?xml version="1.0" encoding="UTF-8"?>
<sagacity
xmlns="http://www.sagframe.com/schema/sqltoy-translate"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.sagframe.com/schema/sqltoy-translate http://www.sagframe.com/schema/sqltoy/sqltoy-translate.xsd">
<!-- 缓存有默认失效时间，默认为1小时,因此只有较为频繁的缓存才需要及时检测 -->
<cache-translates
disk-store-path="./sqltoy-showcase/translateCaches">
<!-- 基于sql直接查询的方式获取缓存 -->
<sql-translate cache="dictKeyName"
datasource="dataSource">
<sql>
<![CDATA[
select t.DICT_KEY,t.DICT_NAME,t.STATUS
from SQLTOY_DICT_DETAIL t
where t.DICT_TYPE=:dictType
order by t.SHOW_INDEX
]]>
</sql>
</sql-translate>
<!-- 员工ID和姓名的缓存 -->
<sql-translate cache="staffIdName"
datasource="dataSource">
<sql>
<![CDATA[
select STAFF_ID,STAFF_NAME,STATUS
from SQLTOY_STAFF_INFO
]]>
</sql>
</sql-translate>
</cache-translates>
<!-- 缓存刷新检测,可以提供多个基于sql、service、rest服务检测 -->
<cache-update-checkers>
<!-- 基于sql的缓存更新检测,间隔为秒，可以分段设置，也可以直接设置一个数组如60，表示一分钟检测一次-->
<sql-checker
check-frequency="30"
datasource="dataSource">
<sql><![CDATA[
--#not_debug#--
select distinct 'staffIdName' cacheName,null cache_type
from SQLTOY_STAFF_INFO t1
where t1.UPDATE_TIME >=:lastUpdateTime
-- 数据字典key和name缓存检测
union all
select distinct 'dictKeyName' cacheName,t2.DICT_TYPE cache_type
from SQLTOY_DICT_DETAIL t2
where t2.UPDATE_TIME >=:lastUpdateTime
]]></sql>
</sql-checker>
</cache-update-checkers>
</sagacity>@RunWith(SpringRunner.class)
@SpringBootTest(classes = SqlToyApplication.class)
public class CrudCaseServiceTest {
@Autowired
private SqlToyCRUDService sqlToyCRUDService;
/**
* 创建一条员工记录
*/
@Test
public void saveStaffInfo() {
StaffInfoVO staffInfo = new StaffInfoVO();
staffInfo.setStaffId("S190715005");
staffInfo.setStaffCode("S190715005");
staffInfo.setStaffName("测试员工4");
staffInfo.setSexType("M");
staffInfo.setEmail("[email protected]");
staffInfo.setEntryDate(LocalDate.now());
staffInfo.setStatus(1);
staffInfo.setOrganId("C0001");
staffInfo.setPhoto(ShowCaseUtils.getBytes(ShowCaseUtils.getFileInputStream("classpath:/mock/staff_photo.jpg")));
staffInfo.setCountry("86");
sqlToyCRUDService.save(staffInfo);
}
}<sql id="show_case">
<!-- 通过filters里面的逻辑将查询条件转为null，部分逻辑则对参数进行二次转换
默认条件参数为空白、空集合、空数组都转为null
parmas 表示可以用逗号写多个参数，param 表示只支持单个参数
-->
<filters>
<!-- 等于，如机构类别前端传负一就转为null不参与条件过滤 -->
<eq params="organType" value="-1" />
<!-- 条件值在某个区间则转为null -->
<between params="" start-value="0" end-value="9999" />
<!-- 将参数条件值转换为日期格式,format可以是yyyy-MM-dd这种自定义格式也可以是:
first_day:月的第一天;last_day:月的最后一天,first_year_day:年的第一天,last_year_day年的最后一天 -->
<to-date params="" format="yyyyMMdd" increment-days="1" />
<!-- 将参数转为数字 -->
<to-number params="" data-type="decimal" />
<!-- 将前端传过来的字符串切割成数组 -->
<split data-type="string" params="staffAuthOrgs" split-sign=","/>
<!-- 小于等于 -->
<lte params="" value="" />
<!-- 小于 -->
<lt params="" value="" />
<!-- 大于等于 -->
<gte params="" value="" />
<!-- 大于 -->
<gt params="" value="" />
<!-- 字符替换,默认根据正则表达进行全部替换，is-first为true时只替换首个 -->
<replace params="" regex="" value="" is-first="false" />
<!-- 首要参数，即当某个参数不为null时，excludes是指被排除之外的参数全部为null -->
<primary param="orderId" excludes="organIds" />
<!-- 排他性参数,当某个参数是xxx值时,将其他参数设置为特定值 -->
<exclusive param="" compare-type="eq" compare-values=""
set-params="" set-value="" />
<!-- 通过缓存进行文字模糊匹配获取精确的代码值参与精确查询 -->
<cache-arg cache-name="" cache-type="" param="" cache-mapping-indexes="" alias-name=""/>
<!-- 将数组转化成in 的参数条件并增加单引号 -->
<to-in-arg params=""/>
</filters>
<!-- 缓存翻译,可以多个，uncached-template 是针对未能匹配时显示的补充,${value} 表示显示key值,可以key=[${value}未定义 这种写法 -->
<translate cache="dictKeyName" cache-type="POST_TYPE" columns="POST_TYPE"
cache-indexs="1" uncached-template=""/>
<!-- 安全掩码:tel\姓名\地址\卡号 -->
<!--最简单用法: <secure-mask columns="" type="tel"/> -->
<secure-mask columns="" type="name" head-size="3" tail-size="4"
mask-code="*****" mask-rate="50" />
<!-- 分库策略 -->
<sharding-datasource strategy="" />
<!-- 分表策略 -->
<sharding-table tables="" strategy="" params="" />
<!-- 分页优化,缓存相同查询条件的分页总记录数量, alive-max:表示相同的一个sql保留100个不同条件查询 alive-seconds:相同的查询条件分页总记录数保留时长(单位秒) -->
<page-optimize alive-max="100" alive-seconds="600" />
<!-- 日期格式化 -->
<date-format columns="" format="yyyy-MM-dd HH:mm:ss"/>
<!-- 数字格式 -->
<number-format columns="" format=""/>
<value>
<![CDATA[
select t1.*,t2.ORGAN_NAME from
@fast(select * from sys_staff_info t
where #[t.sexType=:sexType]
#[and t.JOIN_DATE>:beginDate]
#[and t.STAFF_NAME like :staffName]
-- 是否虚拟员工@if()做逻辑判断
#[@if(:isVirtual==true||:isVirtual==0) and t.IS_VIRTUAL=1]
) t1,sys_organ_info t2
where t1.ORGAN_ID=t2.ORGAN_ID
]]>
</value>
<!-- 为极致分页提供自定义写sql -->
<count-sql><![CDATA[]]></count-sql>
<!-- 汇总和求平均，通过算法实现复杂的sql，同时可以变成数据库无关 -->
<summary columns="" radix-size="2" reverse="false" sum-site="left">
<global sum-label="" label-column="" />
<group sum-label="" label-column="" group-column="" />
</summary>
<!-- 拼接某列,mysql中等同于group_concat\oracle 中的WMSYS.WM_CONCAT功能 -->
<link sign="," column="" />
<!-- 行转列 (跟unpivot互斥)，算法实现数据库无关 -->
<pivot category-columns="" group-columns="" start-column="" end-column=""
default-value="0" />
<!-- 列转行 -->
<unpivot columns="" values-as-column="" />
</sql>sqltoy-orm 主要分以下几个部分：
快速阅读理解sqltoy:
