sql-paging是一个SQL分页查询方言类库,它原来是Sqltool的智能分页组件,后剥离出来作为独立项目,以供更多组件集成其能力。通过调用相关API,可快速将一个普通SQL转换为一个特定数据库的计数(COUNT
)SQL或分页查询SQL。sql-paging通过内置的SQL分析工具类分析实际调用的SQL,让方言生成最优的计数(COUNT
)SQL或分页查询SQL。
数据库 | 支持版本 | 方言实现类 |
---|---|---|
MySQL | 1.0+ | MySQLPagingDialect |
Oracle | 1.0+ | OraclePagingDialect |
PostgreSQL | 1.0+ | PostgreSQLPagingDialect |
SQLServer | 1.0+ | SQLServerPagingDialect |
SQLite | 1.2.7+ | SQLitePagingDialect |
以基于Maven项目为例
<!-- https://mvnrepository.com/artifact/cn.tenmg/sql-paging -->
<dependency>
<groupId>cn.tenmg</groupId>
<artifactId>sql-paging</artifactId>
<version>${sql-paging.version}</version>
</dependency>
SQLPagingDialect.countSql
方法获取计数SQL(以MySQL数据库为例)String namedSql = "……";
sqlMetaData sqlMetaData = SQLUtils.getSQLMetaData(namedSql);
SQLPagingDialect dialect = MySQLPagingDialect.getInstance();
String countSql = dialect.countSql(namedSql, sqlMetaData);
……
SQLPagingDialect.pageSql
方法获取分页查询SQL(以MySQL数据库为例)……
try {
String pageSql = dialect.pageSql(con, namedSql, params, sqlMetaData, 20, 2);
……
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
……
用于根据实际查询的SQL自动生成计数SQL,完成对总数的统计,结合页容量可计算出总页数。根据对源SQL的分析和智能决策,生成计数SQL会去除不必要的列或者排序子句(ORDER BY),且不会引入不必要子查询,以达到最优性能。例如如下SQL:
SELECT
S.STAFF_ID,
S.STAFF_NAME,
S.DEPARTMENT_ID,
S.POSITION,
S.STATUS
FROM STAFF_INFO S
ORDER 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) AMT
FROM ORDER_INFO O
WHERE O.CREATE_TIME >= :begin AND O.CREATE_TIME < :end
GROUP BY USER_ID
ORDER BY SUM(AMT) DESC
我们得到的是:
SELECT
COUNT(*)
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
) SQL_PAGING
干得漂亮!这完全是我们所期待的。但如果情况再复杂一点呢?比如这样,我们需要查询某段时间内订单金额前一百名的用户:
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
) T
ORDER BY AMT DESC
LIMIT 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
LIMIT 100
) SQL_PAGING
sql-paging没有误杀无辜者,除了去除 ORDER BY
子句之外,其他保留原样,保证了结果正确性的同时,提升了查询效率。
用于根据实际查询的SQL生成分页查询SQL,它也不是简单地对源SQL包裹子查询,同样是按需智能决策。继续上述三个例子:
SELECT
S.STAFF_ID,
S.STAFF_NAME,
S.DEPARTMENT_ID,
S.POSITION,
S.STATUS
FROM STAFF_INFO S
ORDER BY S.STAFF_ID
得到的分页查询SQL(以页容量为10,页码第2页为例):
1.1. MySQL
SELECT
S.STAFF_ID,
S.STAFF_NAME,
S.DEPARTMENT_ID,
S.POSITION,
S.STATUS
FROM STAFF_INFO S
ORDER BY S.STAFF_ID
LIMIT 10,10
1.2. Oracle
SELECT
STAFF_ID,
STAFF_NAME,
DEPARTMENT_ID,
POSITION,
STATUS
FROM (
SELECT
ROWNUM RN__,
SQL_PAGING.*
FROM (
SELECT
S.STAFF_ID,
S.STAFF_NAME,
S.DEPARTMENT_ID,
S.POSITION,
S.STATUS
FROM STAFF_INFO S
ORDER BY S.STAFF_ID
) SQL_PAGING
WHERE RN__ <= 20
)
WHERE RN__ > 10
1.3. PostgresSQL
SELECT
S.STAFF_ID,
S.STAFF_NAME,
S.DEPARTMENT_ID,
S.POSITION,
S.STATUS
FROM STAFF_INFO S
ORDER BY S.STAFF_ID
LIMIT 10 OFFSET 10
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
得到的分页查询SQL(以页容量为10,页码第2页为例):
2.1. MySQL:
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
LIMIT 10,10
2.2. Oracle
SELECT
USER_ID,
AMT
FROM (
SELECT
ROWNUM RN__,
SQL_PAGING.*
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
) SQL_PAGING
WHERE RN__ <= 20
)
WHERE RN__ > 10
2.3. PostgresSQL
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
LIMIT 10 OFFSET 10
DSL开源地址:https://gitee.com/tenmg/dsl
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。