讲述最值得你了解的sqltoy-orm最佳查询实践

zhongxuchen

浏览: 33437 次
性别:
来自: 上海

最近访客更多访客>>

cqh520llr

kejiangwei

fan0128

leimingchao

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

技术交流
SAGACITY(睿智)框架讲坛

数据库 sql优化动态sql 动态查询

开发的起因是因为一次非常偶然的机会发现了数据库查询的奥秘，因为这个发现在我负责的项目中，几乎所有人都不必太care数据库交互。

很多时候大家发现项目卡慢的时候总是考虑用更强大的机器、更多的集群规模来解决问题，但忽视了很多问题都是因为数据库交互性能导致的。一谈到解决数据库查询性能，很多人是言必称mybatis，因为他名气大可以理解！

但我今天在此要分享给大家的则是另辟蹊径非常独特的orm框架“sqltoy-orm”：

首先我们带着几个问题展开：

1、数据库的动态查询大家是怎么做的？myBatis那种xml中<if test=""></if> 优雅吗？条件多的时候直观吗？后期维护清晰便捷吗？

2、大家的分页是怎么弄的？如果查询员工表，同时需要关联员工的机构名称，你是查询出10条再关联机构表还是直接关联机构表然后取10条记录呢？

3、大家分页是不是每次做2次查询，一次查询总记录数，然后再查10条记录？能不能再优化？

4、大家做查询的时候需要多次关联数据字典表，有没有更好的办法提升性能？

5、大家的动态查询会不会存在sql注入问题？

当然还有很多类似的实战性问题，比如树形结构的查询、数据旋转等等。

但是我这里可以比较肯定的说，上述5条myBatis做的都不好，而sqltoy则绝对的惊艳，让人感到匪夷所思！

首先我们对比一下

mybatis的动态查询（sql要是复杂一点看起来够累）

sqltoy-orm的动态查询编写方式

两者之间谁更容易看的懂？谁更有利于后期的维护？毫无疑问，sqltoy的编写模式保留了从客户端编写调试后完整sql的风格，这还是一个2个条件的查询，要是十个八个的，

尤其mybatis in处理还整出一个foreach；还有choose等，而sqltoy针对in 自动构造（？，？，？），针对choose 则可以通过 #[ #[] ] 形式的嵌套就可以解决。

让框架苦逼，让开发者爽，这才是框架的真谛！

谈及sql查询编写的直观性，很多人说是开发者不会用mybatis所以写的难以理解，又有人说查询一般就2到4个条件！但sqltoy针对这些问题的思路是这样的：

1、不给你写出看不懂的sql机会，统一的sql写法：从客户端调试完直接copy进来，写出了之后就是统一的风格。修改的时候copy出去放入客户端再调试修改。这种模式是否最贴切项目实践过程？mybatis你就不停剔除或增加<if>吧！

2、不给你sql注入的机会，所有参数都转成预编译模式。

3、sqltoy给你留下足够的手段和措施，不需要的时候不影响你，万一需要的时候呢？

那为什么sqltoy可以这么书写？大家有没有发现一个规律:你的参数为null或者为空白99.9%表示在页面上用户没有做选择，即不用参与查询，#[] 就等于if(xx!=null){}，相当于一个约定。

当然会有极少的例外(什么是框架？框架就应该较方便的解决85%以上的普遍问题，针对特殊问题提供解决手段)。

展示一下完整的sqltoy动态查询以及分页、缓存翻译、快速分页、分页优化的配置

第一：运用了快速分页，先分页再关联，提升效率。

第二：运用了缓存翻译(可以配置多个translate)，巧妙自然的将缓存跟数据库查询框架集成（很多人会说，我自己写代码从缓存里面循环翻译，my God! 你不嫌累你去写吧）

第三：配置了分页优化器，利用缓存机制将对应查询条件的总记录数缓存下来（设置缓存数量和缓存有效时长），从而避免了每次分页查询都执行两次查询。

当然sqltoy的普通的分页查询内部机制不是简单的根据不同数据库类型进行sql包裹，尤其对count语句的处理进行了优化，比如去除order by 、智能判断是否直接去除from前的语句用select count(1) 代替等。

sqltoy通过filters来规范和处理查询参数

如上图，通过filters中的约定的一些标准逻辑处理，规范参数的值和格式，达到确保#[] 符合逻辑抽象的合法性。

sqltoy的sql简单查询范例：sqlToyLazyDao是sqltoy默认提供dao实现，可以让开发者不必要针对简单的逻辑写一个dao类。sqltoy默认提供了分页、取top、取随机记录等等的实现机制，不需要开发者

针对这种非常基础的功能去寻找插件（个人认为框架就必须要提供这种常规的功能，针对相对偏僻的功能提供默认实现，同时提供好扩展的口子）。

简单范例如下：

sqltoy实现行列转换

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

sqltoy实现分组汇总求平均

<!-- 汇总计算 (场景是sql先汇总，页面上还需要对已有汇总再汇总的情况,如果用sql实现在跨数据库的时候就存在问题)-->
	<sql id="sys_summarySearch">
		<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>
		<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>

sqltoy实现分库分表查询:具体分库分表策略可以由开发者自行定义，sqltoy只提供几种默认的策略

分库分表策略定义(spring中定义bean):

<!-- 所有分库分表策略开发者可以自行实现接口进行拓展 -->
	<!-- 按照周期范围进行分表查询 -->
	<bean id="historyTableStrategy" name="dateHisTable"
		class="org.sagacity.sqltoy.plugin.sharding.DefaultShardingStrategy"
		init-method="initialize">
		<!-- 多少天内查询实时表,可以用逗号分隔,如:value="360,35,1" -->
		<property name="days" value="14" />
		<property name="dateParams"
			value="createTime,beginDate,bizDate,beginTime,bizTime" />
		<!-- 实时表和历史表对照 -->
		<property name="tableNamesMap">
			<map>
				<!-- value可用逗号分隔,跟days对应 ,如:value="a,b,c" -->
				<entry key="SYS_SHARDING_REAL" value="SYS_SHARDING_HIS" />
			</map>
		</property>
	</bean>

	<!-- 按照权重进行分库查询分流策略-->
	<bean id="dataSourceStrategy" name="multiDataSource"
		class="org.sagacity.sqltoy.plugin.sharding.DefaultShardingStrategy"
		init-method="initialize">
		<!-- 不同数据库的分配权重 -->
		<property name="dataSourceWeight">
			<map>
				<entry key="dataSource" value="30" />
				<entry key="dataSourceView" value="70" />
			</map>
		</property>
                <!-- 数据库有效性检测时间间隔秒数,小于等于0表示不自动检测数据库 -->
		<property name="checkSeconds" value="180"/>
	</bean>

	<!-- 按照hash取模进行分库和分表策略 -->
	<bean id="hashDataSourceSharding" name="hashDataSourceSharding"
		class="org.sagacity.sqltoy.plugin.sharding.HashShardingStrategy"
		init-method="initialize">
		<!-- 不同数据库的分配权重 -->
		<property name="dataSourceMap">
			<map>
				<entry key="0" value="dataSource" />
				<entry key="1" value="sharding1" />
				<entry key="2" value="sharding2" />
			</map>
		</property>
		<!-- 分表-->
		<property name="tableMap">
			<map>
				<entry key="0" value="SYS_STAFF_INFO" />
				<entry key="1" value="SYS_STAFF_INFO_1" />
			</map>
		</property>	
	</bean>

<!-- 分表分库查询 -->
	<sql id="sys_findByShardingTime">
		<!-- 分库查询,将查询压力分担到多个数据库上 -->
		<sharding-datasource strategy="multiDataSource" />
		
		<!-- 分表查询:按数据热度分表,如：将最近30天的数据查询30天的表，其他查询历史表，提升查询效率 -->
		<sharding-table tables="sys_sharding_real" strategy="dateHisTable"
			params="beginTime" />
			
		<!-- 优化分页查询,核心是避免相同的查询条件每次查询总记录数量 -->
		<page-optimize alive-max="100" alive-seconds="600" />
		<value>
		<![CDATA[
		SELECT ID, STAFF_ID, POST_TYPE, CREATE_TIME, COMMENTS
		FROM sqltoy.sys_sharding_real t
		where t.CREATE_TIME between :beginTime and :endTime
		]]>
		</value>
	</sql>

sqltoy实现对象操作的分库分表

@SqlToyEntity
/*
 * db则是分库策略配置,table 则是分表策略配置，可以同时配置也可以独立配置
 * 策略name要跟spring中的bean定义name一致,fields表示要以对象的哪几个字段值作为判断依据,可以一个或多个字段
 * maxConcurrents:可选配置，表示最大并行数
 * maxWaitSeconds:可选配置，表示最大等待秒数
 */
@Sharding(db = @Strategy(name = "hashDataSourceSharding", fields = {
		"staffId" }), table = @Strategy(name = "hashDataSourceSharding", fields = {
				"staffId" }), maxConcurrents = 10, maxWaitSeconds = 3600)
public class StaffInfoVO extends AbstractStaffInfoVO {

sqltoy 快速分页查询:@fast()实现先分页再关联，同时此功能适用于取随机记录和top记录，如果非分页查询@fast会自动剔除，不影响查询。

<sql id="sys_findStaff">
<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]
                          #[and t.TECH_GROUP in (:techGroups)]
	         ) t1,sys_organ_info t2
where t1.ORGAN_ID=t2.ORGAN_ID
]]>
</value>
</sql>

sqltoy 实现跨数据库函数替换：开发者可以扩展IFunction接口实现其他函数的替换，从而实现软件产品的跨数据库能力。

<!-- 配置辅助sql处理工具用于sql查询条件的处理 -->
	<bean id="sqlToyContext" name="sqlToyContext" class="org.sagacity.sqltoy.SqlToyContext"
		init-method="initialize">
		<!-- 指定sql.xml 文件的路径实现目录的递归查找,非必须属性 -->
		<property name="sqlResourcesDir" value="classpath:sqltoy/showcase/" />
		<!-- 针对不同数据库函数进行转换,非必须属性 -->
		<property name="functionConverts">
			<list>
				<value>org.sagacity.sqltoy.plugin.function.SubStr</value>
				<value>org.sagacity.sqltoy.plugin.function.Trim</value>
				<value>org.sagacity.sqltoy.plugin.function.Instr</value>
				<value>org.sagacity.sqltoy.plugin.function.Concat</value>
				<value>org.sagacity.sqltoy.plugin.function.Nvl</value>
			</list>
		</property>
	</bean>

极端情况下动态sql后门@if()逻辑，@if支持简单的逻辑处理，包含：@if(:paramName>=xxx) 单逻辑和@if(:paramName1>=xxx2 || :paramName2<=xxx2) 双逻辑判断，对比符号：==、！=、<>、>=、>、<、<=，逻辑符号：|| 和 &&

<sql id="sag_if_test">
<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>

查看图片附件

分享到：

sqltoy-orm 增删改和load操作介绍 | 正式开源sqltoy-orm框架

2017-05-19 00:08
浏览 2735
评论(8)
分类:开源软件
查看更多

8 楼 zhongxuchen 2017-12-18

感谢大家的评价，sqltoy在大家刚接触的时候多少会存在一些心理上的障碍:不知名,也没有大厂有专门的人进行维护和写文档。

但真正用起来之后才会发现真的很实用，所有设计都是很贴切项目实践过程的。

另外sqltoy会保持更新和维护的。另外目前sqltoy4.0.5版本功能已经比较完善。

7 楼 chenbaixing8 2017-12-14

从事5年多得开发了，自从项目用了sqltoy后感觉效率快得不行，集成了mybaits和heb得所有优点，比mybaits写法简单，提供得一些工具类对开发者工作效率快，希望作者把一些工具类得使用方法有时间也讲讲

6 楼 hong_zhiliang 2017-12-14

使用sqlToy已经有一段时间了，真正解决实际项目中碰到的一些棘手的问题，发现版本也在持续地更新维护，感觉上已经非常成熟了
个人使用感觉
1.上手比较快,集成步骤比较简单，学习成本比较低
2.sql可读性比较强,维护比较方便，动态sql简洁明了
3.扩展插件功能，缓存翻译，快速分页，查询结果的处理
4.高级特性，分库分表，多数据源的使用支持
这个只有使用了才有深刻体会，其它的特性正常逐步使用中，打算在spring boot集成一些功能特性，感谢楼主的付出

5 楼 luckywang168 2017-12-14

接触这个orm框架有段时间了，新人上手很快，省去了mybatis这种框架的大量配置，已及简化类似mybatis在做复杂操作时，不断拼接的过程，可读性高，调试简单快捷，写好的sql直接拷如代码即可用，而且提供简单的标签对sql入参处理很灵活，同时支持级联操作，和快速分页，提供的行列转换很适合报表功能的快速开发，translate（字段翻译）功能，也很强大，省去了为了翻译一个字段而去多关联一张或多个表的过程，性能很高，很值得去深入学习，身边的朋友也很喜欢这个框架，一直在用，开发效率高，客户也很满意

4 楼 chenbingbing 2017-12-14

目前公司的报表系统就是采用的楼主的sqlToy,不得不说楼主的想法很赞，解决了目前mybatis书写SQL繁琐的缺点，同时保证了数据查询的速度。实现了报表的快速开发，节省了大量的人力，使得新手可以快速上手（会SQL就可以进行开发）

。同时希望楼主可以持续更新迭代

3 楼 zhongxuchen 2017-12-05

wangxin_toy 写道

作为一个从业二十多年的数据库开发人员，一直在寻找一个能让框架和开发人员和谐共处，新手上手既能开发的模式。看了楼主的sqltoy简直茅塞顿开！！！

开发人员再也不用面对繁琐的数据库连接语句，正如楼主所说的，开发人员，爽！！！楼主对问题的理解相当透彻，能够深入到问题的最底层，也就是每个程序员每天都要做的数据库交互进行优化，看出了楼主对代码的热爱和对各个关系型数据库的理解，期待楼主的更新！！

谢谢评价，sqltoy一定会坚持开源和发展下去的，目前正在写4.x版本的文档。并给予大家以支持。

2 楼 ralfsqual 2017-11-15

    试用了一段时间，感觉这是我遇到的最好用的orm及jdbc工具了，感谢楼主的分享！
    说实在的，早厌倦了mybatis不停追加xml条件的写法了。主要是每次检查sql的时候很不直观，楼主的工具写法只要忽略掉#[] 就是完整的sql语句。
    还有个是，完全不用自己去写分页，分页与否完全是同一个sql，楼主的工具自动根据调用的方法来自动将其解析成分页语句，而且不用关心不同数据库的分页方式，这个工具基本都是使用的对应数据库的最佳分页方式实现的，太强大了。
那个内嵌的translate功能，可以对查出来的字段值进行翻译，对于名称翻译类的语句可以不用自己一个个表去关联了，省力。当然，耗掉一定的内存用于缓存这些翻译。呃，这个要看数据量，一般能缓存的都不是大量的数据啦。
    还有不少的后续扩展功能也很实用，比如查询出的list可以根据一定列旋转及分组汇总，嗯，最新版本还有分库分表功能，这个还要多研究一下！希望楼主持续更新。

1 楼 wangxin_toy 2017-11-14

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论