1.1 sqltoy-orm是什么

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

• oracle 从oracle11g到19c
• db2 9.5+,建议从10.5 开始
• mysql 支持5.6、5.7、8.0 版本
• postgresql 支持9.5 以及以上版本
• sqlserver 支持2008到2019版本，建议使用2012或以上版本
• sqlite
• sybase_iq 支持15.4以上版本，建议使用16版本
• elasticsearch 只支持查询,版本支持5.7+版本，建议使用7.3以上版本
• clickhouse
• mongodb (只支持查询)

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 
 
    
	and t.ORDER_ID=#{orderId}
    
    
	and t.ORGAN_ID in
	  
            #{order_id}  
 	  
    
    
	and t.STAFF_ID in
	  
            #{staff_id}  
 	  
    
    
	and t.TRANS_DATE>=#{beginDate}
    
    
	and t.TRANS_DATE<#{endDate}
    

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 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

2.3.1 分页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 模糊查询

最跨数据库

• 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 代码实现

2.5 提供行列转换(数据旋转)，避免写复杂的sql或存储过程，用算法来化解对sql的高要求，同时实现数据库无关(不管是mysql还是sqlserver)

2.6 提供分组汇总求平均算法(用算法代替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 配置
        
	
		
		
			=:beginDate]
			#[and t.log_date<=:endDate]
				]]>
		
	

	
	
		
		
			=:beginDate
			#[and t.trans_date<=:endDate]
				]]>
		
	
        

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)
@SqlToyEntity
public class UserLogVO extends AbstractUserLogVO {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1296922598783858512L;

	/** default constructor */
	public UserLogVO() {
		super();
	}
}