sqltoy-orm是比hibernate+myBatis更加贴合项目的orm框架,具有hibernate增删改的便捷性同时也具有比myBatis更加灵活优雅的自定义sql查询功能。 支持以下数据库:
- oracle 11g+
- db2 9.5+,建议从10.5 开始
- mysql(mariadb/innosql)支持5.6、5.7、8.0 版本
- postgresql(greenplum) 支持9.5 以及以上版本
- sqlserver 2012+
- sqlite
- DM达梦数据库
- elasticsearch 只支持查询,版本支持5.7+版本,建议使用7.3以上版本
- clickhouse
- StarRocks(原dorisdb)
- oceanBase
- guassdb
- tidb
- impala(kudu)
- kingbase
- mongodb (只支持查询)
- 其他数据库支持基于jdbc的sql执行(查询和自定义sql的执行)
1. 快速特点说明
1.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>
1.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的一部分
1.3 最强大的分页查询
1.3.1 分页特点说明
- 1、快速分页:@fast() 实现先取单页数据然后再关联查询,极大提升速度。
- 2、分页优化器:page-optimize 让分页查询由两次变成1.3~1.5次(用缓存实现相同查询条件的总记录数量在一定周期内无需重复查询)
- 3、sqltoy的分页取总记录的过程不是简单的select count(1) from (原始sql);而是智能判断是否变成:select count(1) from 'from后语句', 并自动剔除最外层的order by
- 4、在极特殊情况下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 table1
1.3.1 分页sql示例
<!-- 快速分页和分页优化演示 --> <sql id="sqltoy_fastPage"> <!-- 分页优化器,通过缓存实现查询条件一致的情况下在一定时间周期内缓存总记录数量,从而无需每次查询总记录数量 --> <!-- alive-max:最大存放多少个不同查询条件的总记录量; alive-seconds:查询条件记录量存活时长(比如120秒,超过阀值则重新查询) --> <page-optimize parallel="true" 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>
1.3.3 分页java代码调用
/** * 基于对象传参数模式 */ public void findPageByEntity() { Page pageModel = new Page(); StaffInfoVO staffVO = new StaffInfoVO(); // 作为查询条件传参数 staffVO.setStaffName("陈"); // 使用了分页优化器 // 第一次调用:执行count 和 取记录两次查询 Page 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() { Page result = sqlToyLazyDao.findPageBySql(new Page(), "sqltoy_fastPage",MapKit.keys("staffName").values("陈"),StaffInfoVO.class); System.err.println(JSON.toJSONString(result)); }
1.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>
1.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>
1.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>
1.7 分库分表
1.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>
1.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) @SqlToyEntity public class UserLogVO extends AbstractUserLogVO { /** * */ private static final long serialVersionUID = 1296922598783858512L; /** default constructor */ public UserLogVO() { super(); } }
评论