在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
开源软件名称:sql-paging开源软件地址:https://gitee.com/tenmg/sql-paging开源软件介绍:sql-paging介绍sql-paging是一个SQL分页查询方言类库,它原来是Sqltool的智能分页组件,后剥离出来作为独立项目,以供更多组件集成其能力。通过调用相关API,可快速将一个普通SQL转换为一个特定数据库的计数( 数据库支持
使用说明以基于Maven项目为例
<!-- https://mvnrepository.com/artifact/cn.tenmg/sql-paging --><dependency> <groupId>cn.tenmg</groupId> <artifactId>sql-paging</artifactId> <version>${sql-paging.version}</version></dependency>
String namedSql = "……";sqlMetaData sqlMetaData = SQLUtils.getSQLMetaData(namedSql);SQLPagingDialect dialect = MySQLPagingDialect.getInstance();String countSql = dialect.countSql(namedSql, sqlMetaData);……
……try { String pageSql = dialect.pageSql(con, namedSql, params, sqlMetaData, 20, 2); ……} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace();}…… API详解countSql用于根据实际查询的SQL自动生成计数SQL,完成对总数的统计,结合页容量可计算出总页数。根据对源SQL的分析和智能决策,生成计数SQL会去除不必要的列或者排序子句(ORDER BY),且不会引入不必要子查询,以达到最优性能。例如如下SQL: SELECT S.STAFF_ID, S.STAFF_NAME, S.DEPARTMENT_ID, S.POSITION, S.STATUSFROM STAFF_INFO SORDER BY S.STAFF_ID 并不是简单包裹子查询实现计数: SELECT COUNT(*)FROM ( SELECT S.STAFF_ID, S.STAFF_NAME, S.DEPARTMENT_ID, S.POSITION, S.STATUS FROM STAFF_INFO S ORDER BY S.STAFF_ID) T 而是,不嵌套不必要的子查询,并去除不必要的排序子句: SELECT COUNT(*)FROM STAFF_INFO S 嗯,这的确是我们想要的样子。但如果情况复杂一点呢?比如,我们需要查询某段时间内用户的订单金额并按金额从大到小排序: SELECT USER_ID, SUM(AMT) AMTFROM ORDER_INFO OWHERE O.CREATE_TIME >= :begin AND O.CREATE_TIME < :endGROUP BY USER_IDORDER BY SUM(AMT) DESC 我们得到的是: SELECT COUNT(*)FROM ( SELECT USER_ID FROM ORDER_INFO O WHERE O.CREATE_TIME >= :begin AND O.CREATE_TIME < :end GROUP BY USER_ID) SQLTOOL 干得漂亮!这完全是我们所期待的。但如果情况再复杂一点呢?比如这样,我们需要查询某段时间内订单金额前一百名的用户: SELECT USER_ID, /*用户编号*/ AMT /*订单金额*/FROM ( SELECT USER_ID, SUM(AMT) AMT FROM ORDER_INFO O WHERE O.CREATE_TIME >= :begin AND O.CREATE_TIME < :end GROUP BY USER_ID) TORDER BY AMT DESCLIMIT 100 我们得到的是: SELECT COUNT(*)FROM ( SELECT USER_ID, /*用户编号*/ AMT /*订单金额*/ FROM ( SELECT USER_ID, SUM(AMT) AMT FROM ORDER_INFO O WHERE O.CREATE_TIME >= :begin AND O.CREATE_TIME < :end ) T ORDER BY AMT DESC LIMIT 100) SQLTOOL sql-paging没有误杀无辜者,不该去掉的当然要保留原样,这时候仅仅做了必要的包装。 pageSql用于根据实际查询的SQL生成分页查询SQL,它也不是简单得对源SQL包裹子查询,同样是按需智能决策。继续上述三个例子: SELECT S.STAFF_ID, S.STAFF_NAME, S.DEPARTMENT_ID, S.POSITION, S.STATUSFROM STAFF_INFO SORDER BY S.STAFF_ID 得到的分页查询SQL(以页容量为10,页码第2页为例): 1.1. MySQL SELECT S.STAFF_ID, S.STAFF_NAME, S.DEPARTMENT_ID, S.POSITION, S.STATUSFROM STAFF_INFO SORDER BY S.STAFF_IDLIMIT 10,10 1.2. Oracle SELECT STAFF_ID, STAFF_NAME, DEPARTMENT_ID, POSITION, STATUSFROM ( SELECT ROWNUM RN__, SQLTOOL.* FROM ( SELECT S.STAFF_ID, S.STAFF_NAME, S.DEPARTMENT_ID, S.POSITION, S.STATUS FROM STAFF_INFO S ORDER BY S.STAFF_ID ) SQLTOOL WHERE RN__ <= 20)WHERE RN__ > 10 1.3. PostgresSQL SELECT S.STAFF_ID, S.STAFF_NAME, S.DEPARTMENT_ID, S.POSITION, S.STATUSFROM STAFF_INFO SORDER BY S.STAFF_IDLIMIT 10 OFFSET 10 SELECT USER_ID, SUM(AMT) AMTFROM ORDER_INFO OWHERE O.CREATE_TIME >= :begin AND O.CREATE_TIME < :endGROUP BY USER_IDORDER BY SUM(AMT) DESC 得到的分页查询SQL(以页容量为10,页码第2页为例): 2.1. MySQL: SELECT USER_ID, SUM(AMT) AMTFROM ORDER_INFO OWHERE O.CREATE_TIME >= :begin AND O.CREATE_TIME < :endGROUP BY USER_IDORDER BY SUM(AMT) DESCLIMIT 10,10 2.2. Oracle SELECT USER_ID, AMTFROM ( SELECT ROWNUM RN__, SQLTOOL.* FROM ( SELECT USER_ID, SUM(AMT) AMT FROM ORDER_INFO O WHERE O.CREATE_TIME >= :begin AND O.CREATE_TIME < :end GROUP BY USER_ID ORDER BY SUM(AMT) DESC ) SQLTOOL WHERE RN__ <= 20)WHERE RN__ > 10 2.3. PostgresSQL SELECT USER_ID, SUM(AMT) AMTFROM ORDER_INFO OWHERE O.CREATE_TIME >= :begin AND O.CREATE_TIME < :endGROUP BY USER_IDORDER BY SUM(AMT) DESCLIMIT 10 OFFSET 10 参与贡献
相关链接DSL开源地址:https://gitee.com/tenmg/dsl |
请发表评论