# sqltoy-orm
**Repository Path**: magic-codex/sqltoy-orm
## Basic Information
- **Project Name**: sqltoy-orm
- **Description**: 1.1 sqltoy-orm是什么 sqltoy-orm是比hibernate+myBatis更加贴合项目的orm框架，具有hibernate增删改的便捷性同时也具有比myBati
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: https://www.oschina.net/p/sqltoy-orm
- **GVP Project**: No
## Statistics
- **Stars**: 0
- **Forks**: 41
- **Created**: 2020-04-24
- **Last Updated**: 2020-12-19
## Categories & Tags
**Categories**: Uncategorized
**Tags**: None
## README
# 使用文档(完善进度73%,可以预览)
## [https://chenrenfei.github.io/sqltoy/](https://chenrenfei.github.io/sqltoy/)
# QQ交流群:531812227
# 致谢
## 中化石化销售有限公司
* sqltoy经过化销公司ERP、CRM、壹化网、掌上化销、数据平台等多个项目2年多来的应用，得到了大幅的改进提升，底层代码也得到了充分优化改进。
* sqltoy的开源推广得到了化销公司各级领导、开发团队、测试团队的大力支持，决心打造化销公司践行中化集团<<知性合一、科学发展>>企业发展理念,发展新基建的排头兵,争做中国科技发展的积极参与者、奋斗者、贡献者!
* sqltoy在公司的大力支持下积极推广，希望服务更多的企业和开发者，愿大家一起携手共进:中华有我，不负韶华!
## 拉卡拉支付集团
sqltoy的发展感谢拉卡拉支付有限公司的支持，将sqltoy作为公司orm的主推框架
并于2018年7月收到拉卡拉公司的一笔6千元的创新赞助，激励sqltoy走向开源并不断完善、发展。
- 1、在拉卡拉sqltoy经受了日均1300万笔交易流水的考验。
- 2、通过拉卡拉的业务全场景验证:分库分表；缓存翻译的优势展示；快速分页、分页优化在每一点性能都需要极度优化考虑的场景下价值体现；elasticsearch十亿级别的数据毫秒级查询；mongodb在用户画像标签数据场景下的应用。
# 使用单位或项目:
* 宁波农行
* 山东农信
* 成都银行
* 中国建行上海开发中心
* 合肥农信
* 浦发电销
* 拉卡拉支付集团
* 中化壹化网
* 中化石化销售
* 苏州友达光电
# 疑问解答
* sqltoy会不维护吗? 不要担心sqltoy其实发展至今已经10年多了,因为经历了2018~2019年大规模复杂场景应用非常成熟了才开始推广的,目前公司全部系统都基于此,sqltoy不但要维护更加要深入发展,变得更强更可靠。
* sqltoy难道就是写sql?不是这样的，sqltoy其实是hibernate+mybatis(含plus)的合集，还包括一个quickvo帮助从数据库生成pojo，是一个完整的体系。
* sqltoy学习困难吗? 其实sqltoy学习是极其简单的,参考范例一个晚上绝对可以学会,规则极其统一简单。用学习mybatis五分之一的精力就足够。
* sqltoy的sql对应xml文件难写吗?xml里面有schema,所有配置方式在eclipse或idea中都是可以自动提示,不需要死记硬背。
# 1. 前言
## 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的写法(一板一眼很工程化),sqltoy比这个香多少倍?其实根本就无法比,因为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语句如下
```xml
select *
from sqltoy_device_order_info t
where #[t.ORGAN_ID in (:authedOrganIds)]
#[and t.TRANS_DATE>=:beginDate]
#[and t.TRANS_DATE<:endDate]
```
* java调用过程
```java
sqlToyLazyDao.findBySql(sql, new String[] { "authedOrganIds","beginDate", "endDate"},
new Object[] { authedOrganIdAry,beginDate,null}, DeviceOrderInfoVO.class);
```
* 最终执行的sql是这样的:
```xml
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示例
```xml
```
### 2.3.3 分页java代码调用
```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 模糊查询
```xml
```
## 2.4 最跨数据库
* 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 代码实现
```xml
```
## 2.5 提供行列转换(数据旋转)，避免写复杂的sql或存储过程，用算法来化解对sql的高要求，同时实现数据库无关(不管是mysql还是sqlserver)
```xml
```
## 2.6 提供分组汇总求平均算法(用算法代替sql避免跨数据库语法不一致)
```xml
```
## 2.7 分库分表
### 2.7.1 查询分库分表（分库和分表策略可以同时使用）
```xml
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 进行演示
```java
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();
}
}
```
## 2.8 五种非数据库相关主键生成策略
主键策略除了数据库自带的 sequence\identity 外包含以下数据库无关的主键策略。通过quickvo配置，自动生成在VO对象中。
### 2.8.1 shortNanoTime 22位有序安全ID，格式: 13位当前毫秒+6位纳秒+3位主机ID
### 2.8.2 nanoTimeId 26位有序安全ID,格式:15位:yyMMddHHmmssSSS+6位纳秒+2位(线程Id+随机数)+3位主机ID
### 2.8.3 uuid:32 位uuid
### 2.8.4 SnowflakeId 雪花算法ID
### 2.8.5 redisId 基于redis 来产生规则的ID主键
根据对象属性值,产生规则有序的ID,比如:订单类型为采购:P 销售:S，贸易类型：I内贸;O 外贸;
订单号生成规则为:1位订单类型+1位贸易类型+yyMMdd+3位流水(超过3位自动扩展)
最终会生成单号为:SI191120001
## 2.9 elastic原生查询支持
## 2.10 elasticsearch-sql 插件模式sql模式支持
# 3.集成说明
* 参见trunk 下面的sqltoy-showcase 和 sqltoy-starter-showcase
* sqltoy-showcase 是演示springboot 和sqltoy基于xml配置模式的集成，大多数功能演示在此项目中，其中tools/quickvo 目录是利用数据库生成POJO的配置示例(具体是VO还是其它可根据实际情况修改配置)
* sqltoy-starter-showcase：演示无xml配置形式的基于boot-starter模式的集成
```java
package com.sagframe.sqltoy;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;
/**
* @author zhongxuchen
*/
@SpringBootApplication
@ComponentScan(basePackages = { "com.sagframe.sqltoy" })
@EnableTransactionManagement
public class SqlToyApplication {
/**
* @param args
*/
public static void main(String[] args) {
SpringApplication.run(SqlToyApplication.class, args);
}
}
```
application.properties sqltoy部分配置
```javascript
## sqltoy 配置
# sql.xml 文件的路径,多个路径用;符合分割(原则上也是可选配置，如果只用对象操作的话,但不建议)
spring.sqltoy.sqlResourcesDir=/com/sagframe/sqltoy/showcase
# 缓存翻译的配置(可选配置)
spring.sqltoy.translateConfig=classpath:sqltoy-translate.xml
# 是否debug模式,debug 模式会打印执行的sql和参数信息(可选配置)
spring.sqltoy.debug=true
# 设置默认使用的datasource(可选配置,不配置会自动注入)
spring.sqltoy.defaultDataSource=dataSource
# 提供统一字段:createBy createTime updateBy updateTime 等字段补漏性(为空时)赋值(可选配置)
spring.sqltoy.unifyFieldsHandler=com.sagframe.sqltoy.plugins.SqlToyUnifyFieldsHandler
# sql执行超过多长时间则进行日志输出(可选配置:默认30秒)，用于监控哪些慢sql
spring.sqltoy.printSqlTimeoutMillis=30000
```
缓存翻译的配置文件sqltoy-translate.xml
```xml
=:lastUpdateTime
-- 数据字典key和name缓存检测
union all
select distinct 'dictKeyName' cacheName,t2.DICT_TYPE cache_type
from SQLTOY_DICT_DETAIL t2
where t2.UPDATE_TIME >=:lastUpdateTime
]]>
```
* 实际业务开发使用，直接利用SqlToyCRUDService 就可以进行常规的操作，避免简单的对象操作自己写service，
另外针对复杂逻辑则自己写service直接通过调用sqltoy提供的：SqlToyLazyDao 完成数据库交互操作！
```java
@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(ShowCaseUtils.getBytes(ShowCaseUtils.getFileInputStream("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
```xml
: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关键代码说明
* sqltoy-orm 主要分以下几个部分：
- BaseDaoSupport:提供给开发者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会在使用时动态管理。
* 快速阅读理解sqltoy:
- 从BaseDaoSupport(或SqlToyDaoSupport)作为入口,你会看到sqltoy的所有提供的功能，通过LinkDaoSupport则可以按照不同分类视角看到sqltoy的功能组织形式。
- 从DialectFactory会进入不同数据库方言的实现入口。可以跟踪看到具体数据库的实现逻辑。你会看到oracle、mysql等分页、取随机记录、快速分页的封装等。
- EntityManager:你会找到如何扫描POJO并构造成模型，知道通过POJO操作数据库实质会变成相应的sql进行交互。
- ParallelUtils:对象分库分表并行执行器，通过这个类你会看到分库分表批量操作时如何将集合分组到不同的库不同的表并进行并行调度的。
- SqlToyContext:sqltoy配置的上下文,通过这个类可以看到sqltoy全貌。
- PageOptimizeCacheImpl:可以看到分页优化默认实现原理。