1. 前言

1.1 sqltoy-orm是什么

sqltoy-orm是比hibernate+myBatis(plus)更加贴合项目的orm框架(依赖spring)，具有jpa式的对象CRUD的同时具有比myBatis(plus)更直观简洁性能强大的查询功能。 支持以下数据库:

• 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
• dorisdb
• oceanBase
• guassdb
• tidb
• kingbase
• mongodb (只支持查询)
• sybase_iq 支持15.4以上版本，建议使用16版本

1.2 jdk版本要求1.8+

2. 快速特点说明

2.1 对象操作跟jpa类似并有针对性加强(包括级联)

• 无需写任何sql，通过quickvo工具从数据库生成对应的POJO

   StaffInfoVO staffInfo = new StaffInfoVO(); 
   //保存
   sqlToyLazyDao.save(staffInfo);
   //删除
   sqlToyLazyDao.delete(new StaffInfoVO("S2007"));

   //public Long update(Serializable entity, String... forceUpdateProps);
   // 这里对photo 属性进行强制修改，其他为null自动会跳过
   sqlToyLazyDao.update(staffInfo, "photo");

   //深度修改,不管是否null全部字段修改
   sqlToyLazyDao.updateDeeply(staffInfo);

   List staffList = new ArrayList();
   StaffInfoVO staffInfo = new StaffInfoVO();
   StaffInfoVO staffInfo1 = new StaffInfoVO();
   staffList.add(staffInfo);
   staffList.add(staffInfo1);
   //批量保存或修改
   sqlToyLazyDao.saveOrUpdateAll(staffList);
   //批量保存
   sqlToyLazyDao.saveAll(staffList);
   ...............
   sqlToyLazyDao.loadByIds(StaffInfoVO.class,"S2007")
   //唯一性验证
   sqlToyLazyDao.isUnique(staffInfo, "staffCode");

2.2 支持代码中对象查询

• sqltoy 中统一的规则是代码中可以直接传sql也可以是对应xml文件中的sqlId

/**
 * @todo 通过对象传参数,简化paramName[],paramValue[] 模式传参
 * @param 
 * @param sqlOrNamedSql 可以是具体sql也可以是对应xml中的sqlId
 * @param entity        通过对象传参数,并按对象类型返回结果
 */
 public  List findBySql(final String sqlOrNamedSql, final T entity);

• 基于对象单表查询，并带缓存翻译

public PaginationModel findStaff(PaginationModel pageModel, StaffInfoVO staffInfoVO) {
     // sql可以直接在代码中编写,复杂sql建议在xml中定义
     // 单表entity查询场景下sql字段可以写成java类的属性名称
     return findEntity(StaffInfoVO.class, pageModel, EntityQuery.create()
	.where("#[staffName like :staffName]#[and createTime>=:beginDate]#[and createTime<=:endDate]")
	.values(staffInfoVO)
	// 字典缓存必须要设置cacheType
	// 单表对象查询需设置keyColumn构成select keyColumn as column模式
	.translates(new Translate("dictKeyName").setColumn("sexTypeName").setCacheType("SEX_TYPE")
         		.setKeyColumn("sexType"))
	.translates(new Translate("organIdName").setColumn("organName").setKeyColumn("organId")));
}

• 对象式查询后修改或删除

//演示代码中非直接sql模式设置条件模式进行记录修改
public Long updateByQuery() {
     return sqlToyLazyDao.updateByQuery(StaffInfoVO.class,
		EntityUpdate.create().set("createBy", "S0001")
                     .where("staffName like ?").values("张"));
}

//代码中非直接sql模式设置条件模式进行记录删除
sqlToyLazyDao.deleteByQuery(StaffInfoVO.class, EntityQuery.create().where("status=?").values(0));

2.3 极致朴素的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()高度灵活模式,为特殊复杂场景下提供便利

//1、 条件值处理跟具体sql分离
//2、 将条件值前置通过filters 定义的通用方法加工规整(大多数是不需要额外处理的)


   
   

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

2.4 天然防止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) 设置条件值

2.5 最为极致的分页

2.5.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支持并行查询：parallel="true"，同时查询总记录数和单页数据,大幅提升性能
• 5、在极特殊情况下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.5.2 分页sql示例

2.5.3 分页java代码调用

/**
 *  基于对象传参数模式
 */
public void findPageByEntity() {
	PaginationModel pageModel = new PaginationModel();
	StaffInfoVO staffVO = new StaffInfoVO();
	// 作为查询条件传参数
	staffVO.setStaffName("陈");
	// 使用了分页优化器
	// 第一次调用:执行count 和 取记录两次查询
        // 第二次调用:在特定时效范围内count将从缓存获取，只会执行取单页记录查询
	PaginationModel result = sqlToyLazyDao.findPageBySql(pageModel, "sqltoy_fastPage", staffVO);
}

2.6 极为巧妙的缓存翻译，将多表关联查询尽量变成单表

• 1、 通过缓存翻译: 将代码转化为名称，避免关联查询，极大简化sql并提升查询效率
• 2、 通过缓存名称模糊匹配: 获取精准的编码作为条件，避免关联like 模糊查询

2.7 并行查询

• 接口规范

// parallQuery 面向查询(不要用于事务操作过程中),sqltoy提供强大的方法，但是否恰当使用需要使用者做合理的判断
/**
  * @TODO 并行查询并返回一维List，有几个查询List中就包含几个结果对象，paramNames和paramValues是全部sql的条件参数的合集
  * @param parallQueryList
  * @param paramNames
  * @param paramValues
  */
public  List> parallQuery(List parallQueryList, String[] paramNames,
			Object[] paramValues);

• 使用范例

//定义参数
String[] paramNames = new String[] { "userId", "defaultRoles", "deployId", "authObjType" };
Object[] paramValues = new Object[] { userId, defaultRoles, GlobalConstants.DEPLOY_ID,
		SagacityConstants.TempAuthObjType.GROUP };
// 使用并行查询同时执行2个sql,条件参数是2个查询的合集
List> list = super.parallQuery(
		Arrays.asList(
		        ParallQuery.create().sql("webframe_searchAllModuleMenus").resultType(TreeModel.class),
				ParallQuery.create().sql("webframe_searchAllUserReports").resultType(TreeModel.class)),
		paramNames, paramValues);
		

2.8 跨数据库支持

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

• 6、通过sqlId+dialect模式，可针对特定数据库写sql,sqltoy根据数据库类型获取实际执行sql,顺序为: dialect_sqlId->sqlId_dialect->sqlId， 如数据库为mysql,调用sqlId:sqltoy_showcase,则实际执行:sqltoy_showcase_mysql

2.9 提供行列转换、分组汇总、同比环比等

• 水果销售记录表

2.9.1 行转列(列转行也支持)

• 效果

2.9.2 分组汇总、求平均(可任意层级)

• 效果

2.9.3 先行转列再环比计算

• 效果

2.10 分库分表

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

   sql参见quickstart项目:com/sqltoy/quickstart/sqltoy-quickstart.sql.xml 文件
   
	
		
		
			=:beginDate]
			#[and t.log_date<=:endDate]
				]]>
		
	

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

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

@Sharding 在对象上通过注解来实现分库分表的策略配置

参见:com.sqltoy.quickstart.ShardingSearchTest 进行演示

package com.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;

/*
 * 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();
	}
}

2.11 五种非数据库相关主键生成策略

主键策略除了数据库自带的 sequence\identity 外包含以下数据库无关的主键策略。通过quickvo配置，自动生成在VO对象中。

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

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

2.11.3 uuid:32 位uuid

2.11.4 SnowflakeId 雪花算法ID

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

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

2.12 elastic原生查询支持

2.13 elasticsearch-sql 插件模式sql模式支持

2.14 sql文件变更自动重载，方便开发和调试

2.15 公共字段统一赋值,针对创建人、创建时间、修改人、修改时间等

2.16 提供了查询结果日期、数字格式化、安全脱敏处理，让复杂的事情变得简单

3.集成说明

3.1 参见trunk 下面的quickstart,并阅读readme.md进行上手

package com.sqltoy.quickstart;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * 
 * @project sqltoy-quickstart
 * @description quickstart 主程序入口
 * @author zhongxuchen
 * @version v1.0, Date:2020年7月17日
 * @modify 2020年7月17日,修改说明
 */
@SpringBootApplication
@ComponentScan(basePackages = { "com.sqltoy.config", "com.sqltoy.quickstart" })
@EnableTransactionManagement
public class SqlToyApplication {
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		SpringApplication.run(SqlToyApplication.class, args);
	}
}

3.2 application.properties sqltoy部分配置

# sqltoy config
spring.sqltoy.sqlResourcesDir=classpath:com/sqltoy/quickstart
spring.sqltoy.translateConfig=classpath:sqltoy-translate.xml
spring.sqltoy.debug=true
#spring.sqltoy.reservedWords=status,sex_type
#obtainDataSource: org.sagacity.sqltoy.plugins.datasource.impl.DefaultObtainDataSourc
#spring.sqltoy.defaultDataSource=dataSource
# 提供统一公共字段赋值(源码参见quickstart)
spring.sqltoy.unifyFieldsHandler=com.sqltoy.plugins.SqlToyUnifyFieldsHandler
#spring.sqltoy.printSqlTimeoutMillis=200000

3.3 缓存翻译的配置文件sqltoy-translate.xml

	
	
		
		
			
			
			
		

		
		
			
			
			
		
		
		
			
			
			
		
	

	
	
		
		
			=:lastUpdateTime
			]]>
		

		
		
			=:lastUpdateTime
			]]>
		

		
		
			=:lastUpdateTime
			]]>
		
	

• 实际业务开发使用，直接利用SqlToyCRUDService 就可以进行常规的操作，避免简单的对象操作自己写service， 另外针对复杂逻辑则自己写service直接通过调用sqltoy提供的：SqlToyLazyDao 完成数据库交互操作！

@RunWith(SpringRunner.class)
@SpringBootTest(classes = SqlToyApplication.class)
public class CrudCaseServiceTest {
	@Autowired
	private SqlToyCRUDService sqlToyCRUDService;

	/**
	 * 创建一条员工记录
	 */
	@Test
	public void saveStaffInfo() {
		StaffInfoVO staffInfo = new StaffInfoVO();
		staffInfo.setStaffId("S190715005");
		staffInfo.setStaffCode("S190715005");
		staffInfo.setStaffName("测试员工4");
		staffInfo.setSexType("M");
		staffInfo.setEmail("test3@aliyun.com");
		staffInfo.setEntryDate(LocalDate.now());
		staffInfo.setStatus(1);
		staffInfo.setOrganId("C0001");
		staffInfo.setPhoto(FileUtil.readAsBytes("classpath:/mock/staff_photo.jpg"));
		staffInfo.setCountry("86");
		sqlToyCRUDService.save(staffInfo);
	}
 }

4. sqltoy sql关键说明

4.1 sqltoy sql最简单规则#[] 对称符号

• #[] 等于if(中间语句参数是否有null)? true: 剔除#[] 整块代码，false：拿掉#[ 和 ] ,将中间的sql作为执行的一部分。
• #[] 支持嵌套，如#[t.status=:status #[and t.createDate>=:createDate]] 会先从内而外执行if(null)逻辑
• 利用filters条件值预处理实现判断null的统一,下面是sqltoy完整提供的条件过滤器和其他函数 不要被大段的说明吓一跳，99%都用不上，正常filters里面只会用到eq 和 to-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
	]]>	
	

	
	
	
	

		
		
	
	
	
	
	
	
	

5. sqltoy关键代码说明

5.1 sqltoy-orm 主要分以下几个部分：

• SqlToyDaoSupport:提供给开发者Dao继承的基本Dao,集成了所有对数据库操作的方法。
• SqlToyLazyDao:提供给开发者快捷使用的Dao,等同于开发者自己写的Dao，用于在简单场景下开发者可以不用写Dao，而直接写Service。
• SqltoyCRUDService:简单Service的封装，一些简单的对象增删改开发者写Service也是简单的调用Dao,针对这种场景提供一个简单功能的Service调用，开发者自己的Service用于封装相对复杂的业务逻辑。
• DialectFactory:数据库方言工厂类，sqltoy根据当前连接的方言调用不同数据库的实现封装。
• SqlToyContext:sqltoy上下文配置,是整个框架的核心配置和交换区，spring配置主要是配置sqltoyContext。
• EntityManager:封装于SqlToyContext，用于托管POJO对象，建立对象跟数据库表的关系。sqltoy通过SqlToyEntity注解扫描加载对象。
• ScriptLoader:sql配置文件加载解析器,封装于SqlToyContext中。sql文件严格按照*.sql.xml规则命名。
• TranslateManager:缓存翻译管理器,用于加载缓存翻译的xml配置文件和缓存实现类，sqltoy提供了接口并提供了默认基于ehcache的实现，缓存翻译最好是使用ehcache本地缓存(或ehcache rmi模式的分布式缓存)，这样效率是最高的，而redis这种分布式缓存IO开销太大，缓存翻译是一个高频度的调用，一般会缓存注入员工、机构、数据字典、产品品类、地区等相对变化不频繁的稳定数据。
• ShardingStragety:分库分表策略管理器，4.x版本之后策略管理器并不需要显式定义，只有通过spring定义，sqltoy会在使用时动态管理。

5.2 快速阅读理解sqltoy:

• 从BaseDaoSupport(或SqlToyDaoSupport)作为入口,你会看到sqltoy的所有提供的功能，通过LinkDaoSupport则可以按照不同分类视角看到sqltoy的功能组织形式。
• 从DialectFactory会进入不同数据库方言的实现入口。可以跟踪看到具体数据库的实现逻辑。你会看到oracle、mysql等分页、取随机记录、快速分页的封装等。
• EntityManager:你会找到如何扫描POJO并构造成模型，知道通过POJO操作数据库实质会变成相应的sql进行交互。
• ParallelUtils:对象分库分表并行执行器，通过这个类你会看到分库分表批量操作时如何将集合分组到不同的库不同的表并进行并行调度的。
• SqlToyContext:sqltoy配置的上下文,通过这个类可以看到sqltoy全貌。
• PageOptimizeUtils:可以看到分页优化默认实现原理。