VARCHAR\u3000\u3000|\u3000\u3000po_id\u3000\u3000 | \u3000\u3000 \u8ba2\u5355\u8868id<\/p>\n
VARCHAR\u3000\u3000|\u3000\u3000oi_id\u3000\u3000|\u3000\u3000\u8ba2\u5355\u4fe1\u606fid VARCHAR\u3000\u3000|\u3000\u3000po_id\u3000\u3000 | \u3000\u3000 \u8ba2\u5355\u8868id TEXT\u3000\u3000\u3000\u3000|\u3000\u3000info\u3000\u3000\u3000|\u3000\u3000\u3000\u4fe1\u606f\u8be6\u60c5<\/p>\n
<select id="getProviderOrderWithOrderInfoTest" resultMap="ResultMapWithOrderInfo" parameterType="java.lang.String"> SELECT * FROM (SELECT * FROM provider_order WHERE po_id LIKE #{po_id}) AS limitable LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id <\/select><\/code><\/pre>\n\n- \u8fdb\u884cPageHelper\u7684\u5206\u9875\u64cd\u4f5c\uff0c\u67e5\u8be2\u524d\u5341\u6761\u8bb0\u5f55\uff1a<\/li>\n<\/ul>\n
Page page = PageHelper.startPage(0, 10); List<ProviderOrder> providerOrderList = testService.getProviderOrderWithOrderInfoTest("%-%"); PageInfo<ProviderOrder> pageInfo = new PageInfo<>(providerOrderList); System.out.println(pageInfo); <\/code><\/pre>\n\n- \u76ee\u524d\u6211\u4eec\u7684\u4e3b\u8868\u6709\u4e24\u6761\u8bb0\u5f55\uff0c\u4ece\u8868\u6709\u4e8c\u5341\u6761\u8bb0\u5f55\u3002<\/li>\n<\/ul>\n
\u6211\u4eec\u4f1a\u53d1\u73b0\uff0c\u67e5\u8be2\u51fa\u6765\u7684\u603b\u6570\u662f20\uff0c\u7136\u540e\u67e5\u8be2\u7ed3\u679c\u662f\u4e24\u4e2alist\uff08\u54045\u6761\u4ece\u8868\u4fe1\u606f\uff09\uff0c\u4e0d\u4ec5\u5982\u6b64\uff0c\u5206\u9875\u6570\u636e\u4e5f\u5f88\u5947\u602a\uff0c\u6211\u4eec\u53ea\u6709\u4e24\u6761\u4e3b\u8868\u8bb0\u5f55\uff0c\u4f46\u5374\u88ab\u5206\u6210\u4e86\u4e24\u9875\uff08pages = 2\uff09\uff0c\u5e76\u4e14hasNextPage = true\u3002\u8fd9\u5e76\u4e0d\u662f\u6211\u4eec\u8981\u7684\u7ed3\u679c\u3002\u6211\u4eec\u60f3\u8981\u67e5\u8be2\u7684\u6548\u679c\u662f\uff1a<\/p>\n
\u524d\u5341\u6761 \u8ba2\u5355 \u8bb0\u5f55\uff0c\u7136\u540e\u8fde\u8868\u67e5\u8be2\u51fa\u8be5\u5341\u6761\u8ba2\u5355\u8bb0\u5f55\u4e0b\u7684\u6240\u6709 \u8ba2\u5355\u4fe1\u606f<\/strong>
\u4e5f\u5c31\u662f\u8bf4\uff0c\u7ed3\u679c\u96c6\u4e2d\uff0c\u9996\u5148\u6211\u4eec\u5e94\u8be5\u5f97\u5230\u4e24\u4e2alist\uff0clist\u4e2d\u540410\u6761\u4ece\u8868\u4fe1\u606f\uff0c\u800c\u4e14\u4e5f\u4e0d\u8be5\u6709\u4e0b\u4e00\u9875\uff0c\u603b\u9875\u6570\u5e94\u4e3a1\uff0c\u603b\u6570\u636e\u6570\u5e94\u4e3a2\u3002<\/p>\n\u67e5\u770bPageHelper\u7684\u6e90\u7801\u6211\u4eec\u6765\u770b\u770b\uff0c\u5b83\u7684count\uff0c\u4ee5\u53calimit\uff0c\u5230\u5e95\u662f\u600e\u4e48\u56de\u4e8b\u3002<\/strong><\/p>\nPageHelper\u7684count\u5b9e\u73b0<\/h3>\n private Long executeAutoCount(Executor executor, MappedStatement countMs, Object parameter, BoundSql boundSql, RowBounds rowBounds, ResultHandler resultHandler) throws IllegalAccessException, SQLException { Map<String, Object> additionalParameters = (Map) this.additionalParametersField.get(boundSql); CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql); String countSql = this.dialect.getCountSql(countMs, boundSql, parameter, rowBounds, countKey); BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter); Iterator var11 = additionalParameters.keySet() .iterator(); while (var11.hasNext()) { String key = (String) var11.next(); countBoundSql.setAdditionalParameter(key, additionalParameters.get(key)); } Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql); Long count = (Long) ((List) countResultList).get(0); \/\/ \u83b7\u53d6count return count; }<\/code><\/pre>\n\u6211\u4eec\u5f88\u5bb9\u6613\u770b\u51fa\u6765\uff0ccount\u7684sql\u8bed\u53e5\uff0c\u5728\u8fd9\u91cc\u7ecf\u8fc7\u4e86\u5904\u7406\uff0c\u8fd9\u91cc\u8c03\u7528\u4e86dialect\u7684getCountSql\u65b9\u6cd5\u3002 \u5b83\u7684\u5b9e\u73b0\u5982\u4e0b\uff1a<\/p>\n
public String getSmartCountSql(String sql, String name) { Statement stmt = null; if (sql.indexOf("\/*keep orderby*\/") >= 0) { return this.getSimpleCountSql(sql); } else { try { stmt = CCJSqlParserUtil.parse(sql); } catch (Throwable var8) { return this.getSimpleCountSql(sql); } Select select = (Select)stmt; SelectBody selectBody = select.getSelectBody(); try { this.processSelectBody(selectBody); } catch (Exception var7) { return this.getSimpleCountSql(sql); } this.processWithItemsList(select.getWithItemsList()); this.sqlToCount(select, name); String result = select.toString(); return result; } } public String getSimpleCountSql(String sql) { return this.getSimpleCountSql(sql, "0"); } public String getSimpleCountSql(String sql, String name) { StringBuilder stringBuilder = new StringBuilder(sql.length() + 40); stringBuilder.append("select count("); stringBuilder.append(name); stringBuilder.append(") from ("); stringBuilder.append(sql); stringBuilder.append(") tmp_count"); return stringBuilder.toString(); }<\/code><\/pre>\n\u7b80\u5355\u6765\u8bf4\uff0c\u5c31\u662f\u5728sql\u5916\u9762\u5957\u4e00\u5c42select count\u8bed\u53e5\uff0c\u6211\u4eec\u7684sql\u8bed\u53e5\u53d8\u6210\u4e86 SELECT count(0) FROM (\u6211\u4eec\u7684sql\u8bed\u53e5)\uff08\u771f\u5b9e\u60c5\u51b5\u6ca1\u6709\u8fd9\u4e48\u7b80\u5355\uff0c\u8fd9\u53ea\u662f\u5176\u4e2d\u4e00\u79cd\u60c5\u51b5\u3002\uff09\u8fd9\u6837\u5c31\u5f88\u5bb9\u6613\u5f97\u77e5\uff0c\u4e3a\u4ec0\u4e48\u6700\u540e\u7684total\u662f20\uff08\u6240\u6709\u6570\u636e\u6570\uff09\uff0c\u800c\u4e0d\u662f2\uff08\u4e3b\u8868\u6570\u636e\u6570\uff09\u4e86\u3002<\/p>\n
\u4e09\u3001 PageHelper\u7684\u5206\u9875\u5b9e\u73b0<\/h3>\n
\u4e0e\u4e0a\u9762\u76f8\u540c\uff0c\u8fd9\u91cc\u4ee5dialect\uff08\u4e3a\u4e86\u9002\u914d\u5404\u79cd\u6570\u636e\u5e93\uff09\u4e3amysql\u4e3a\u4f8b\uff0c\u6211\u4eec\u53ef\u4ee5\u770b\u5230\uff0c\u5982\u679c\u4f60\u6ca1\u6709\u5728sql\u8bed\u53e5\u91cc\u9762\u5199limit\uff0c\u5c31\u4f1a\u5728sql\u8bed\u53e5\u7684\u6700\u540e\u9762\uff0c\u6dfb\u52a0limit\u8bed\u53e5\u3002<\/p>\n
public String getPageSql(String sql, RowBounds rowBounds, CacheKey pageKey) { StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14); sqlBuilder.append(sql); if (rowBounds.getOffset() == 0) { sqlBuilder.append(" LIMIT "); sqlBuilder.append(rowBounds.getLimit()); } else { sqlBuilder.append(" LIMIT "); sqlBuilder.append(rowBounds.getOffset()); sqlBuilder.append(","); sqlBuilder.append(rowBounds.getLimit()); pageKey.update(rowBounds.getOffset()); } pageKey.update(rowBounds.getLimit()); return sqlBuilder.toString(); }<\/code><\/pre>\n\u7a0b\u5e8f\u8fd0\u884c\u5f97\u5230sql\u8bed\u53e5\u5982\u4e0b\uff0cPageHelper\u5bf9\u67e5\u8be2\u7684\u603b\u7ed3\u679c\u96c6\uff08\u5305\u62ec\u4e3b\u8868\u6570\u636e\u4e0e\u4ece\u8868\u6570\u636e\uff09\uff0c\u8fdb\u884c\u4e86\u5206\u9875 <\/p>\n
\u5728\u5f97\u77e5PageHelper\u5728\u8fd9\u4e24\u4e2a\u6b65\u9aa4\u7684\u539f\u7406\u540e\uff0c\u6211\u4eec\u5f00\u59cb\u5bf9PageHelper\u8fdb\u884c\u6539\u9020\u3002<\/p>\n
\u56db\u3001 count\u7ed3\u679c\u4fee\u6b63<\/h3>\n
\u5728\u4e0a\u9762\u7684count\u6e90\u7801\u4e2d\uff0c\u6211\u4eec\u53ef\u4ee5\u770b\u5230\u8fd9\u4e48\u4e00\u6bb5\u4ee3\u7801\uff1a<\/p>\n
public String getSmartCountSql(String sql, String name) { Statement stmt = null; if (sql.indexOf("\/*keep orderby*\/") >= 0) { return this.getSimpleCountSql(sql); } else { try { stmt = CCJSqlParserUtil.parse(sql); } catch (Throwable var8) { return this.getSimpleCountSql(sql); } Select select = (Select)stmt; SelectBody selectBody = select.getSelectBody(); try { this.processSelectBody(selectBody); } catch (Exception var7) { return this.getSimpleCountSql(sql); } this.processWithItemsList(select.getWithItemsList()); this.sqlToCount(select, name); String result = select.toString(); return result; } } public void sqlToCount(Select select, String name) { SelectBody selectBody = select.getSelectBody(); List<SelectItem> COUNT_ITEM = new ArrayList(); COUNT_ITEM.add(new SelectExpressionItem(new Column("count(" + name + ")"))); if (selectBody instanceof PlainSelect && this.isSimpleCount((PlainSelect)selectBody)) { ((PlainSelect)selectBody).setSelectItems(COUNT_ITEM); } else { PlainSelect plainSelect = new PlainSelect(); SubSelect subSelect = new SubSelect(); subSelect.setSelectBody(selectBody); subSelect.setAlias(TABLE_ALIAS); plainSelect.setFromItem(subSelect); plainSelect.setSelectItems(COUNT_ITEM); select.setSelectBody(plainSelect); } }<\/code><\/pre>\n\u5b9e\u9645\u4e0aPageHelper\u662f\u6709\u63d0\u4f9b\u7ed9\u6211\u4eec\u4e00\u5957\u6307\u5b9acolum\u67e5\u8be2\u7684\u5165\u53e3\u7684\u3002\u6211\u4eec\u5c06\u67e5\u8be2\u4ee3\u7801\u505a\u5982\u4e0b\u6539\u9020\uff1a<\/p>\n
Page page = PageHelper.startPage(0, 10); page.setCountColumn("DISTINCT po_id"); List<ProviderOrder> providerOrderList = testService.getProviderOrderWithOrderInfoTest("%-%"); PageInfo<ProviderOrder> pageInfo = new PageInfo<>(providerOrderList); System.out.println(pageInfo); <\/code><\/pre>\n\u6211\u4eec\u4f1a\u53d1\u73b0\uff0c\u6211\u4eec\u9700\u8981\u7684\u5206\u9875\u6570\u636e\u6b63\u5e38\u4e86\uff0c\u603b\u9875\u7801\uff08pages\uff1a1\uff09\uff0ctotal\uff1a2\u3002<\/p>\n
\u4e94\u3001 \u67e5\u8be2\u7ed3\u679c\u96c6\u4fee\u6b63<\/p>\n
\u6211\u4eec\u5728\u4e09\u4e2d\uff0c\u53d1\u73b0\u4e86PageHelper limit\u6dfb\u52a0\u7684\u4f4d\u7f6e\u4e0d\u6b63\u786e\uff0c\u6211\u4eec\u9700\u8981\u5c06\u5176\u653e\u5728\u6b63\u786e\u7684\u4f4d\u7f6e\u4e0b\u3002\u6240\u4ee5\u65b0\u5efa\u4e00\u4e2a\u7c7b\u5982\u4e0b\uff1a<\/p>\n
\/** * Created by Anur IjuoKaruKas on 2018\/6\/8 *\/ public class PageSqlResolver { public static final String SQL_SIGN = "AS limitable"; public static final String LIMIT_SIGN = "LIMIT ?"; public static final String LIMIT_SIGN_EX = "LIMIT ?, ?"; public static String resolveLimit(String pageSql) { if (pageSql == null) { return null; } if (pageSql.contains(SQL_SIGN)) {\/\/ \u5982\u679c\u9700\u8981\u7279\u6b8a\u5206\u9875 String changer = ""; if (pageSql.contains(LIMIT_SIGN_EX)) { changer = LIMIT_SIGN_EX; } else if (pageSql.contains(LIMIT_SIGN)) { changer = LIMIT_SIGN; } pageSql = pageSql.replace(changer, ""); StringBuilder sqlBuilder = new StringBuilder(pageSql); StringBuilder mae = new StringBuilder(sqlBuilder.substring(0, sqlBuilder.indexOf(SQL_SIGN)));\/\/ mae \u622a\u6b62sql\u8bed\u53e5\u5230 limitable StringBuilder uShiRo = new StringBuilder(sqlBuilder.substring(sqlBuilder.indexOf(SQL_SIGN), sqlBuilder.length()));\/\/ \u5269\u4f59\u7684 mae.insert(mae.lastIndexOf(")"), String.format(" %s", changer)); return mae.append(uShiRo) .toString(); } else { return pageSql; } } }<\/code><\/pre>\n\u8fd9\u6bb5\u4ee3\u7801\u7684\u542b\u4e49\u662f\uff1a\u5c06AS limitable\uff0c\u4e5f\u5c31\u662f\u4e3b\u8868\uff0c\u6807\u8bb0\u4e3a\u771f\u6b63\u5206\u9875\u7684\u5730\u65b9\uff0c\u5e76\u5411\u5176\u6dfb\u5982limit ? ,?<\/p>\n
SELECT * FROM (SELECT * FROM provider_order WHERE po_id LIKE ?) AS limitable LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id LIMIT ? <\/code><\/pre>\nsql\u8bed\u53e5\u4ece\u4e0a\u9762\u53d8\u6210\u4e86\uff1a<\/p>\n
SELECT * FROM (SELECT * FROM provider_order WHERE po_id LIKE ? LIMIT ?) AS limitable LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id <\/code><\/pre>\n\u6211\u4eec\u53d1\u73b0\u8fd9\u5c31\u662f\u6211\u4eec\u60f3\u8981\u7684\u7ed3\u679c\uff0c\u5b83\u7684\u5206\u9875\u662f\u9488\u5bf9\u4e3b\u8868\u8fdb\u884c\u7684\uff0c\u800c\u4e0d\u662f\u9488\u5bf9\u7ed3\u679c\u96c6\u3002<\/p>\n