sqltoy-orm 在github上的开源项目地址

在gitee的开源地址：

说明文档地址：

致谢：

sqltoy只从4月3日发布发版公告以来，得到了大量的关注，从质疑逐步到认同，也得到了不少的反馈，通过QQ群大家彼此交流，此版本正是因为吸收了大家在使用过程中一些特殊用法的反馈得以改进，在此表示感受！

说远比mybatis优雅强大，很多人感觉哗众取宠，实为不然，这是经的起比较的。因为之前已经写过一篇介绍的文章，这里就不再赘述。

sqltoy发展的宗旨

希望可以借助社会力量一道打造一套可以让大家抛开ORM的ORM框架，是希望好到一定高度做到可以抛弃，让大家腾出精力安心的做其他更有价值的领域！而mybatis做不到这一点，就连一个分页都要找插件搞探索式，感觉很有意思一样，将大家的精力耗进去！跟饥饿营销一样!

sqltoy要做的就是用了sqltoy，好，你就不要操心数据库层交互了，玩微服务、大数据、AI去吧、玩业务去吧，或者干脆安心休息吧！而不是当你其他方向忙的要命时说：快不行了，你得分点精力过来好好折腾数据库交互！

更新内容：

1、增加不取总记录数的分页功能(特例功能,为特殊场景提供)，PaginationModel 条件中增加了参数skipTotalCount默认为false，当参数为true时分页跳过取总记录数

2、优化对BigInteger类型的适配处理

3、quickvo优化了jdbc type类似Integer unsigned类型匹配处理,排除unsigned干扰。

4、放开sqltoy中pojo对象set方法返回类型必须是void的限制，便于对象属性可以进行链式赋值，如vo.setName(xxx).setType(1),quickvo 增加了相应的开关，可生成返回类本身的set方法:public VO setName(String name){this.name=name;return this;}

关于sqltoy和mybatis的对比请参见这篇文章：

概括的说有以下主要几点：

sqltoy提供了类似于hibernate的基于对象的crudsqltoy提供了类似于hibernate-tools的quickvo从数据库生产POJOsqltoy极简极为直观的sql编写方式最符合sql编写的套路极为巧妙的缓存翻译、缓存条件检索功能，大幅减少表关联，简化sql、优化性能天然防止sql注入，用sqltoy大家就没有sql注入这个话题了最高等级的分页优化功能支持分库分表支持跨数据库函数转换功能sql文件自动更新加载，开发过程中无需重启应用支持树结构表进行标准化处理支持行列数据旋转，一句配置让sql大牛淡化用sql进行旋转的话题支持多级分组汇总、求平均，简单的一级汇总你可以，2级、3级呢？换一个数据库语法还一样吗？支持同比环比计算支持数据格式化，日期格式、数字格式（可以格式化为大写中文，也可以格式化为大写的金额）支持数据脱敏功能，将电话号码、地址、姓名进行脱敏，如：139****4832 张*敏支持超时sql记录，帮你分析哪些慢sql直接提供了findTop、getRandomResult、isUnique、updateFetch 等非常规功能支持mysql、postgresql、db2、oracle、sqlserver、clickhouse、elasticsearch、sqlite、sybase iq等数据库sqltoy-orm是什么

sqltoy-orm是比hibernate+myBatis更加贴合项目的orm框架，具有hibernate增删改的便捷性同时也具有比myBatis更加灵活优雅的自定义sql查询功能。 支持以下数据库:

oracle 从oracle11g到19cdb2 9.5+,建议从10.5 开始mysql 支持5.6、5.7、8.0 版本postgresql 支持9.5 以及以上版本sqlserver 支持2008到2019版本，建议使用2012或以上版本sqlitesybase_iq 支持15.4以上版本，建议使用16版本elasticsearch 只支持查询,版本支持5.7+版本，建议使用7.3以上版本clickhousemongodb (只支持查询)1.2 是否重复造轮子，我只想首先说五个特性：根本上杜绝了sql注入问题，sql支持写注释、sql文件动态更新检测，开发时sql变更会自动重载最直观的sql编写模式，当查询条件稍微复杂一点的时候就会体现价值，后期变更维护的时候尤为凸显极为强大的缓存翻译查询：巧妙的结合缓存减少查询语句表关联，极大简化sql和提升性能。最强大的分页查询：很多人第一次了解到何为快速分页、分页优化这种极为巧妙的处理，还有在count语句上的极度优化。跨数据库函数方言替换，如：isnull/ifnull/nvl、substr/substring 等不同数据库

当然这只是sqltoy其中的五个特点，还有行列转换(俗称数据旋转)、多级分组汇总、统一树结构表(如机构)查询、分库分表sharding、取随机记录、取top记录、修改并返回记录、慢sql提醒等这些贴合项目应用的功能， 当你真正了解上述特点带来的巨大优势之后，您就会对中国人创造的sqltoy-orm有了信心！

sqltoy-orm 来源于个人亲身经历的无数个项目的总结和思考，尤其是性能优化上不断的挖掘，至于是不是重复的轮子并不重要，希望能够帮到大家！

2. 快速特点说明2.1 最优雅直观的sql编写模式sqltoy 的写法(一眼就看明白sql的本意,后面变更调整也非常便捷,copy到数据库客户端里稍做出来即可执行)sqltoy条件组织原理很简单: 如 #[order_id=:orderId] 等于if(:orderId<>null) sql.append(order_id=:orderId);#[]内只要有一个参数为null即剔除支持多层嵌套:如 #[and t.order_id=:orderId #[and t.order_type=:orderType]]条件判断保留#[@if(:param>=xx ||:param<=xx1) sql语句] 这种@if()高度灵活模式,为特殊复杂场景下提供万能钥匙

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]  

mybatis的写法(一板一眼很工程化)

 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>

2.2 天然防止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的一部分2.3 最强大的分页查询2.3.1 分页特点说明1、快速分页:@fast() 实现先取单页数据然后再关联查询，极大提升速度。2、分页优化器:page-optimize 让分页查询由两次变成1.3~1.5次(用缓存实现相同查询条件的总记录数量在一定周期内无需重复查询)3、sqltoy的分页取总记录的过程不是简单的select count(1) from (原始sql)；而是智能判断是否变成:select count(1) from 'from后语句'， 并自动剔除最外层的order by4、在极特殊情况下sqltoy分页考虑是最优化的，如:with t1 as (),t2 as @fast(select * from table1) select * from xxx 这种复杂查询的分页的处理，sqltoy的count查询会是:with t1 as () select count(1) from table1, 如果是:with t1 as @fast(select * from table1) select * from t1 ,count sql 就是：select count(1) from table12.3.1 分页sql示例

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

2.3.3 分页java代码调用

/** *  基于对象传参数模式 */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));}	

2.4 最巧妙的缓存应用，将多表关联查询尽量变成单表(看下面的sql,如果不用缓存翻译需要关联多少张表?sql要有多长?多难以维护?)1、 通过缓存翻译: 将代码转化为名称，避免关联查询，极大简化sql并提升查询效率2、 通过缓存名称模糊匹配: 获取精准的编码作为条件，避免关联like 模糊查询

<sql id="sqltoy_order_search">	<!-- 缓存翻译设备类型        cache:具体的缓存定义的名称，        cache-type:一般针对数据字典，提供一个分类条件过滤	columns:sql中的查询字段名称，可以逗号分隔对多个字段进行翻译	cache-indexs:缓存数据名称对应的列,不填则默认为第二列(从0开始,1则表示第二列)，	      例如缓存的数据结构是:key、name、fullName,则第三列表示全称	-->	<translate cache="dictKeyNameCache" cache-type="DEVICE_TYPE" columns="deviceTypeName" cache-indexs="1"/>	<!-- 员工名称翻译,如果同一个缓存则可以同时对几个字段进行翻译 -->	<translate cache="staffIdNameCache" 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>

最跨数据库1、提供类似hibernate性质的对象操作，自动生成相应数据库的方言。2、提供了最常用的:分页、取top、取随机记录等查询，避免了各自不同数据库不同的写法。3、提供了树形结构表的标准钻取查询方式，代替以往的递归查询，一种方式适配所有数据库。4、sqltoy提供了大量基于算法的辅助实现，最大程度上用算法代替了以往的sql，实现了跨数据库5、sqltoy提供了函数替换功能，比如可以让oracle的语句在mysql或sqlserver上执行(sql加载时将函数替换成了mysql的函数),最大程度上实现了代码的产品化。 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)

        <!-- 列转行测试 -->	<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>

2.6 提供分组汇总求平均算法(用算法代替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>

2.7 分库分表2.7.1 查询分库分表（分库和分表策略可以同时使用）

        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>        

2.7.2 操作分库分表(vo对象由quickvo工具自动根据数据库生成，且自定义的注解不会被覆盖)

@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)@SqlToyEntitypublic class UserLogVO extends AbstractUserLogVO {	/**	 * 	 */	private static final long serialVersionUID = 1296922598783858512L;	/** default constructor */	public UserLogVO() {		super();	}}