• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

sagacity-sqltoy: java真正最强大的ORM框架,支持mysql、oracle、postgresql、sqlserv ...

原作者: [db:作者] 来自: 网络 收藏 邀请

开源软件名称:

sagacity-sqltoy

开源软件地址:

https://gitee.com/sagacity/sagacity-sqltoy

开源软件介绍:

在线文档

sqltoy-online-doc 网友海贝(hugo)提供

xml中sql完整配置

github地址

WORD版详细文档(完整)

请见:docs/睿智平台SqlToy5.1 使用手册.doc

范例演示项目

快速集成演示项目

快速上手项目

POJO和DTO 严格分层演示项目

sharding分库分表演示

dynamic-datasource多数据源范例

nosql演示(mongodb和elasticsearch)

sqltoy基于xml配置演示

QQ 交流群:531812227

最新版本号:

  • 5.1.30.jre11 发版日期: 2022-3-22
  • 5.1.30 发版日期: 2022-3-22
  • 4.19.23 发版日期: 2022-3-22

更新内容:

  • 1、修复updateByQuery条件参数为in (?) 模式导致参数类型长度跟实际参数长度不一致的缺陷

4.x 升级5.x 项目影响点

  • 分页查询PaginationModel 类改为Page
  • org.sagacity.sqltoy.executor.QueryExecutor 改为 org.sagacity.sqltoy.model.QueryExecutor
  • findAll(pojo.class) 改为findEntity(pojo.class,null)

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
  • StarRocks(原dorisdb)
  • oceanBase
  • guassdb
  • tidb
  • impala(kudu)
  • kingbase
  • mongodb (只支持查询)
  • 其他数据库支持基于jdbc的sql执行(查询和自定义sql的执行)

1.2 jdk版本要求1.8+

2. 快速特点说明

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

  • 通过quickvo工具从数据库生成对应的POJO,引入sqlltoy自带的SqlToyLazyDao即可完成全部操作
   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<StaffInfoVO> staffList = new ArrayList<StaffInfoVO>();   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 <T> * @param sqlOrNamedSql 可以是具体sql也可以是对应xml中的sqlId * @param entity        通过对象传参数,并按对象类型返回结果 */ public <T extends Serializable> List<T> findBySql(final String sqlOrNamedSql, final T entity);
  • 基于对象单表查询,并带缓存翻译
public Page<StaffInfoVO> findStaff(Page<StaffInfoVO> pageModel, StaffInfoVO staffInfoVO) {     // sql可以直接在代码中编写,复杂sql建议在xml中定义     // 单表entity查询场景下sql字段可以写成java类的属性名称     return findPageEntity(pageModel, StaffInfoVO.class, 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 定义的通用方法加工规整(大多数是不需要额外处理的)<sql id="show_case"><filters>   <!-- 参数statusAry只要包含-1(代表全部)则将statusAry设置为null不参与条件检索 -->   <eq params="statusAry" value="-1" /></filters><value><![CDATA[	select 	*	from sqltoy_device_order_info t 	where #[t.status in (:statusAry)]		  #[and 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]    	]]></value></sql>

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, MapKit.keys("authedOrganIds","beginDate", "endDate").values(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示例

<!-- 快速分页和分页优化演示 --><sql id="sqltoy_fastPage">	<!-- 分页优化器,通过缓存实现查询条件一致的情况下在一定时间周期内缓存总记录数量,从而无需每次查询总记录数量 -->	<!-- parallel:是否并行查询总记录数和单页数据,当alive-max=1 时关闭缓存优化 -->	<!-- 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>

2.5.3 分页java代码调用

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

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

  • 1、 通过缓存翻译: 将代码转化为名称,避免关联查询,极大简化sql并提升查询效率
  • 2、 通过缓存名称模糊匹配: 获取精准的编码作为条件,避免关联like 模糊查询
//支持对象属性注解模式进行缓存翻译@Translate(cacheName = "dictKeyName", cacheType = "DEVICE_TYPE", keyField = "deviceType")private String deviceTypeName;@Translate(cacheName = "staffIdName", keyField = "staffId")private String staffName;
<sql id="sqltoy_order_search">	<!-- 缓存翻译设备类型        cache:具体的缓存定义的名称,        cache-type:一般针对数据字典,提供一个分类条件过滤	columns:sql中的查询字段名称,可以逗号分隔对多个字段进行翻译	cache-indexs:缓存数据名称对应的列,不填则默认为第二列(从0开始,1则表示第二列),	      例如缓存的数据结构是:key、name、fullName,则第三列表示全称	-->	<translate cache="dictKeyName" cache-type="DEVICE_TYPE" columns="deviceTypeName" cache-indexs="1"/>	<!-- 员工名称翻译,如果同一个缓存则可以同时对几个字段进行翻译 -->	<translate cache="staffIdName" 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>

2.7 并行查询

  • 接口规范
// parallQuery 面向查询(不要用于事务操作过程中),sqltoy提供强大的方法,但是否恰当使用需要使用者做合理的判断/**  * @TODO 并行查询并返回一维List,有几个查询List中就包含几个结果对象,paramNames和paramValues是全部sql的条件参数的合集  * @param parallQueryList  * @param paramNames  * @param paramValues  */public <T> List<QueryResult<T>> parallQuery(List<ParallQuery> 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<QueryResult<TreeModel>> 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 代码实现
   <!-- 跨数据库函数自动替换(非必须项),适用于跨数据库软件产品,如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> -->
  • 6、通过sqlId+dialect模式,可针对特定数据库写sql,sqltoy根据数据库类型获取实际执行sql,顺序为:dialect_sqlId->sqlId_dialect->sqlId,如数据库为mysql,调用sqlId:sqltoy_showcase,则实际执行:sqltoy_showcase_mysql
	<sql id="sqltoy_showcase">		<value>			<![CDATA[			select * from sqltoy_user_log t 			where t.user_id=:userId 				]]>		</value>	</sql>        <!-- sqlId_数据库方言(小写) -->	<sql id="sqltoy_showcase_mysql">		<value>			<![CDATA[			select * from sqltoy_user_log t 			where t.user_id=:userId 				]]>		</value>	</sql>

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

  • 水果销售记录表
品类销售月份销售笔数销售数量(吨)销售金额(万元)
苹果2019年5月1220002400
苹果2019年4月1119002600
苹果2019年3月1320002500
香蕉2019年5月1020002000
香蕉2019年4月1224002700
香蕉2019年3月1323002700

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

<!-- 行转列 --><sql id="pivot_case">	<value>	<![CDATA[	select t.fruit_name,t.order_month,t.sale_count,t.sale_quantity,t.total_amt 	from sqltoy_fruit_order t	order by t.fruit_name ,t.order_month	]]>	</value>	<!-- 行转列,将order_month作为分类横向标题,从sale_count列到total_amt 三个指标旋转成行 -->	<pivot start-column="sale_count" end-column="total_amt"	group-columns="fruit_name" category-columns="order_month" /></sql>
  • 效果
品类 2019年3月 2019年4月 2019年5月
笔数数量总金额 笔数数量总金额 笔数数量总金额
香蕉 13 2300 2700 12 2400 2700 10 2000 2000
苹果 13 2000 2500 11 1900 2600 12 2000 2400

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

<sql id="group_summary_case">	<value>		<![CDATA[		select t.fruit_name,t.order_month,t.sale_count,t.sale_quantity,t.total_amt 		from sqltoy_fruit_order t		order by t.fruit_name ,t.order_month		]]>	</value>	<!-- reverse 是否反向 -->		<summary columns="sale_count,sale_quantity,total_amt" reverse="true">		<!-- 层级顺序保持从高到低 -->		<global sum-label="总计" label-column="fruit_name" />		<group group-column="fruit_name" sum-label="小计" label-column="fruit_name" />	</summary></sql>
  • 效果
品类销售月份销售笔数销售数量(吨)销售金额(万元)
总计711260014900
小计3659007500
苹果2019年5月1220002400
苹果2019年4月1119002600
苹果2019年3月1320002500
小计3567007400
香蕉2019年5月1020002000
香蕉2019年4月1224002700
香蕉2019年3月1323002700

2.9.3 先行转列再环比计算

<!-- 列与列环比演示 --><sql id="cols_relative_case">	<value>	<![CDATA[		select t.fruit_name,t.order_month,t.sale_count,t.sale_amt,t.total_amt 		from sqltoy_fruit_order t		order by t.fruit_name ,t.order_month	]]>	</value>	<!-- 数据旋转,行转列,将order_month 按列显示,每个月份下面有三个指标 -->	<pivot start-column="sale_count" end-column="total_amt"	group-columns="fruit_name" category-columns="order_month" />	<!-- 列与列之间进行环比计算 -->	<cols-chain-relative group-size="3" relative-indexs="1,2" start-column="1" format="#.00%" /></sql>
  • 效果
品类 2019年3月 2019年4月 2019年5月
笔数数量比上月总金额比上月 笔数数量比上月总金额比上月 笔数数量比上月总金额比上月
香蕉 13 2300 2700 12 2400 4.30% 2700 0.00% 10 2000 -16.70% 2000 -26.00%
苹果 13 2000 2500 11 1900 -5.10% 2600 4.00% 12 2000 5.20% 2400 -7.70%

2.10 分库分表

2.10.1 查询分库分表(分库和分表策略可以同时使用)

   sql参见quickstart项目:com/sqltoy/quickstart/sqltoy-quickstart.sql.xml 文件   <!-- 演示分库 -->	<sql id="qstart_db_sharding_case">		<sharding-datasource strategy="hashDataSource"			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="qstart_sharding_table_case">		<sharding-table tables="sqltoy_trans_info_15d"			strategy="realHisTable" params="beginDate" />		<value>			<![CDATA[			select * from sqltoy_trans_info_15d t 			where t.trans_date>=:beginDate			#[and t.trans_date<=:endDate]				]]>		</value>	</sql>        

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)@SqlToyEntitypublic class UserLogVO extends AbstractUserLogVO {		private static final long serialVersionUID = 1296922598783858512L;	/** default constructor */	public UserLogVO() {		super();	}}


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap