sqltoy开源项目地址： https://github.com/chenrenfei/sagacity-sqltoy

sqltoy文档地址： https://chenrenfei.github.io/sqltoy/#/

先回答2个问题：

1、为什么对标mybatis？

我觉得作为开源orm项目如果不比mybatis强大的多(至少30%以上)，就必须克制住那份show的冲动，不要给开发者添麻烦！996已经不易!

2、我个人觉得sqltoy跟mybatis比如何？

好太多了，单从sql写法上就无法对比。好吧，看过罗永浩跟王自如的pk视频吧，有一句说的好谁质疑谁举证，下面开始一一举证模式(如有质疑也请举证)

下面开始证明式：

1. sqltoy是支持对象增删改和load的，类似于hibernate和jpa，有个别地方有些优势，如update操作规避了hibernate先load再update两次交互的缺陷。同时sqltoy也提供了quickvo工具，可以实现从数据库生产POJO。但这里我相信大家的差距不大，今天不作为重点讲解。其中有一个updateFetch方法是用于实现一次数据库交互完成锁记录、修改、并返回结果的操作，用于一些诸如库存台账、资金台账、订单台账、抢单等场景。

2.sqltoy的具有极其优雅的编写方式(说明一下sqltoy的sql是可以写在代码里面不一定只是写在xml中，简单的可以写代码中)

• mybatis的sql编写：这是一个工程化的写法，说的委婉点全是硬功夫！

select *
 from sqltoy_device_order_info t 
 <where>
    <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>

• sqltoy的sql编写：简单直观、便于维护，开发和运维过程极为自然。

select 	*
from sqltoy_device_order_info t 
where #[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]  

3、sqltoy拥有独特的缓存翻译功能，

感受sqltoy之美--缓存翻译、缓存条件检索

• 缓存翻译和缓存检索化繁为简---查询订单表(简化为单商品订单便于演示)

• 要求查询日期在2020年1月10日至3月20日、客户名称中含<<星云科技>>字符的全部订单信息，要求显示商品名称、客户名称、业务员姓名、部门名称、订单状态中文

• 往常的做法:硬功夫硬碰硬系列

关联客户表做like

关联商品表查询品名

关联员工信息表显示员工名字

关联机构表显示机构名称

关联数据字典翻译状态

• 你是这么做的吗?看一下sqltoy怎么做吧！是不是变成了单表查询，效率毫无疑问秒杀多表关联无数倍!

  <sql id="order_showcase">
    <!-- 通过缓存对最终结果代码进行翻译，显示名称 -->
    <translate cache="organIdName" columns="ORGAN_NAME" />
    <translate cache="staffIdName" columns="STAFF_NAME" />
    <translate cache="goodsIdName" columns="GOODS_NAME" />
    <translate cache="customIdName" columns="CUSTOM_NAME" />
    <translate cache="dictKeyName" cache-type="ORDER_STATUS" columns="STATUS_NAME" />
    <filters>
        <!-- 将查询参数customName通过缓存进行类似like检索获取匹配的customId数组作为查询条件 -->
        <cache-arg cache-name="customIdName" param="customName"    alias-name="customIds" />
    </filters>
    <value>
    <![CDATA[
    select
        ORDER_ID ,
        TOTAL_QUANTITY,
        TOTAL_AMT,
        ORGAN_ID ,
        ORGAN_ID ORGAN_NAME,-- 缓存翻译
        STAFF_ID ,
        STAFF_ID STAFF_NAME,
        SIGN_TIME,
        CUSTOM_ID,
        CUSTOM_ID CUSTOM_NAME,
        GOODS_ID ,
        GOODS_ID GOODS_NAME,
        STATUS,
        STATUS STATUS_NAME
    from od_order_info t1
    where #[SIGN_TIME>=:beginTime]
          #[and SIGN_TIME <=:endTime]
          -- 这里就是缓存条件检索
          #[and CUSTOM_ID in (:customIds)]
    ]]>
    </value>
  </sql>

4、sqltoy具有最高层级的分页优化（不提取count记录的另做讨论，绝大多数分页都是要提供用户符合条件记录总数预期的），sql分页的五个优化等级

• 第一级：将分页用不同数据库方言来封装。
• 第二级：考虑count查询的性能优化，如合理规避select 和from之间函数，order by 等跟count无关的sql执行，提升性能。
• 第三级：考虑join关联查询时，优先分页，将关联的数据范围缩小，从而提升关联查询的效率。
• 第四级：利用缓存机制避免每次count查询，从而提升分页的整体性能。
• 第五级：考虑极为特殊场景下分页的优化，第一：count语句可以自行定义；第二、考虑with table1 as (),table2 as() 等复杂场景下优化和适用。

<!-- 快速分页和分页优化演示 -->
<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>

5、sqltoy是根本上避免sql注入的。

• 假设sql语句如下

select 	*
from sqltoy_device_order_info t 
where #[t.ORGAN_ID in (:authedOrganIds)]
      #[and t.TRANS_DATE>=:beginDate]
      #[and t.TRANS_DATE<:endDate] 

• java调用过程

sqlToyLazyDao.findBySql(sql, new String[] { "authedOrganIds","beginDate", "endDate"},
				new Object[] { authedOrganIdAry,beginDate,null}, DeviceOrderInfoVO.class);

• 最终执行的sql是这样的:

select 	*
from sqltoy_device_order_info t 
where t.ORDER_ID=?
      and t.ORGAN_ID in (?,?,?)
      and t.TRANS_DATE>=?	

• 然后通过: pst.set(index,value) 设置条件值，不存在将条件直接作为字符串拼接为sql的一部分

6、sqltoy 提供了非常符合开发者的算法集成，如无限级的分组汇总求平均、行转列、列转行、同比环比计算，而这些往往都是普通开发者的弱点（靠sql写不出或者换了数据库又不通用）

• 感受sqltoy之美--数据旋转

• 水果销售记录表

• sqltoy 实现行转列(category-columns 支持多列,即多维度分组如:

  月份下面:5月(上旬、中旬、下旬)、4月(上旬、中旬、下旬)

电子产品例如: 手机(华为、三星、小米)、笔记本(苹果、联想、戴尔))

<!-- 列转行 -->
<sql id="pivot_case">
    <value>
    <![CDATA[
    select t.fruit_name,t.order_month,t.sale_count,t.sale_quantity,t.total_amt 
    from sqltoy_fruit_order t
    order by t.fruit_name ,t.order_month
    ]]>
    </value>
    <!-- 行转列,将order_month作为分类横向标题，从sale_count列到total_amt 三个指标旋转成行 -->
    <pivot start-column="sale_count" end-column="total_amt"    group-columns="fruit_name" category-columns="order_month" />
</sql>

• 效果:简单一个pivot代替了复杂的sql，而且不用考虑不同数据库的实现差异

感受sqltoy之美--分组汇总

• 还是上面的苹果销售表格

  <!-- 演示分组汇总 -->
  <sql id="group_summary_case">
    <value>
        <![CDATA[
        select t.fruit_name,t.order_month,t.sale_count,t.sale_quantity,t.total_amt 
        from sqltoy_fruit_order t
        order by t.fruit_name ,t.order_month
        ]]>
    </value>
    <!-- reverse 是否反向 -->    
    <summary columns="sale_count,sale_quantity,total_amt" reverse="true">
        <!-- 层级顺序保持从高到低 -->
        <global sum-label="总计" label-column="fruit_name" />
        <group group-column="fruit_name" sum-label="小计" label-column="fruit_name" />
    </summary>
  </sql>

• 效果

感受sqltoy之美--环比计算

• 演示先行转列再环比计算

  <!-- 列与列环比演示 -->
  <sql id="cols_relative_case">
    <value>
    <![CDATA[
        select t.fruit_name,t.order_month,t.sale_count,t.sale_amt,t.total_amt 
        from sqltoy_fruit_order t
        order by t.fruit_name ,t.order_month
    ]]>
    </value>
    <!-- 数据旋转,行转列,将order_month 按列显示，每个月份下面有三个指标 -->
    <pivot start-column="sale_count" end-column="total_amt"    group-columns="fruit_name" category-columns="order_month" />
    <!-- 列与列之间进行环比计算 -->
    <cols-chain-relative group-size="3" relative-indexs="1,2" start-column="1" format="#.00%" />
  </sql>

• 效果

7、 sqltoy提供了常规的直接调用：如取随机记录、取top记录、唯一性校验（isUnique）、取总记录数等

/**
	 * @todo 基于对象传参数模式(内部会根据sql中的参数提取对象对应属性的值)
	 * @param <T>
	 * @param sqlOrNamedSql
	 * @param entity
	 * @param topSize (大于1则取固定数量的记录，小于1，则表示按比例提取)
	 * @return
	 */
	public <T extends Serializable> List<T> findTopBySql(final String sqlOrNamedSql, final T entity,
			final double topSize);

	public QueryResult getRandomResult(final QueryExecutor queryExecutor, final double randomCount);

	/**
	 * @TODO 通过对象传参模式取随机记录
	 * @param <T>
	 * @param sqlOrNamedSql
	 * @param entity
	 * @param randomCount
	 * @return
	 */
	public <T extends Serializable> List<T> getRandomResult(final String sqlOrNamedSql, final T entity,
			final double randomCount);

	public <T> List<T> getRandomResult(final String sqlOrNamedSql, final String[] paramsNamed,
			final Object[] paramsValue, final Class<T> voClass, final double randomCount);

   /**
	 * @todo 判断对象属性在数据库中是否唯一(已经考虑了是自身问题)
	 * @param entity
	 * @param paramsNamed
	 * @return boolean true：唯一；false：不唯一
	 */
	public boolean isUnique(Serializable entity, String[] paramsNamed);

8、 sqltoy提供了对树形结构表的跨数据库解决方案wrapTreeTableRoute，将表结构中统一构建节点路径、节点等级、是否叶子节点。

9、 sqltoy提供了分库分表功能，而且执行过程是并行计算的。

10、 sqltoy提供了统一字段赋值，解决创建时间、创建人、修改时间、修改人等信息的统一赋值。

11、 sqltoy提供了跨数据库函数自动替换功能，比如concat、ws_concat、decode、date_format、if、instr、nvl、sysdate 、length、to-char、to-number、trim 等函数的自动转换适配。有助于软件产品在一种数据库下开发，不同数据库下执行。

<bean id="sqlToyContext" class="org.sagacity.sqltoy.SqlToyContext"
		init-method="initialize" destroy-method="destroy">
		<!-- 非必须属性:跨数据库函数自动替换(非必须项),适用于跨数据库软件产品,如mysql开发，oracle部署 -->
		<property name="functionConverts" value="default" />
		<!-- 可以这样自行根据需要进行定义和扩展 
		<property name="functionConverts"> 
			    <list> 
					<value>org.sagacity.sqltoy.plugins.function.impl.Nvl</value> 
					<value>org.sagacity.sqltoy.plugins.function.impl.SubStr</value> 
					<value>org.sagacity.sqltoy.plugins.function.impl.Now</value> 
					<value>org.sagacity.sqltoy.plugins.function.impl.Length</value> 
		        </list> 
		</property> -->
</bean>

12、 sqltoy提供了sql变更检测自动加载功能，便于开发阶段减少重新加载；以及超时sql执行统计功能，便于找到慢sql进行优化。

13、提供了5种默认的非数据库相关的主键策略

• shortNanoTime 22位有序安全ID，格式: 13位当前毫秒+6位纳秒+3位主机ID

• nanoTimeId 26位有序安全ID,格式:15位:yyMMddHHmmssSSS+6位纳秒+2位(线程Id+随机数)+3位主机ID

• uuid:32 位uuid

• SnowflakeId 雪花算法ID

• redisId 基于redis 来产生规则的ID主键

根据对象属性值,产生规则有序的ID,比如:订单类型为采购:P 销售:S，贸易类型：I内贸;O 外贸; 订单号生成规则为:1位订单类型+1位贸易类型+yyMMdd+3位流水(超过3位自动扩展) 最终会生成单号为:SI191120001

14、sqltoy是便于其他框架进行扩展式集成的

public class SqlToyContext implements ApplicationContextAware {

	/**
	 * @todo 提供可以动态增加解析sql片段配置的接口,并返回具体id,用于第三方平台集成，如报表平台等
	 * @param sqlSegment
	 * @return
	 * @throws Exception
	 */
	public synchronized SqlToyConfig parseSqlSegment(Object sqlSegment) throws Exception {
		return scriptLoader.parseSqlSagment(sqlSegment);
	}

	/**
	 * @todo 将构造好的SqlToyConfig放入缓存
	 * @param sqlToyConfig
	 * @throws Exception
	 */
	public synchronized void putSqlToyConfig(SqlToyConfig sqlToyConfig) throws Exception {
		scriptLoader.putSqlToyConfig(sqlToyConfig);
	}

}

sqltoy-orm已经在多家银行和企业应用，有兴趣我们可以一起来发展完善一个属于中国人的ORM框架！