[GitHub] [shardingsphere] xszhe edited a comment on issue #14334: Oracle pagination and sorting doesn' t work properly

[GitBox <gi...@apache.org>]

xszhe edited a comment on issue #14334:
URL: https://github.com/apache/shardingsphere/issues/14334#issuecomment-1023808235


   我也出现同样的问题，是因为
   D:/maven_bd/org/apache/shardingsphere/shardingsphere-jdbc-core/5.0.0/shardingsphere-jdbc-core-5.0.0.jar!/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatement.class:121
   
   
   this.sqlStatement = sqlParserEngine.parse(sql, true);
   这个语句无法正常解析分页组件组合的SQL，导致无法识别。
   ![Snipaste_2022-01-28_09-29-51](https://user-images.githubusercontent.com/49973087/151471374-3dfc8763-e82c-46b7-901d-295c7f8c66d5.jpg)
   我通过对分页组件 com.github.pagehelper:pagehelper:5.3.0 的源码修改覆盖，将order by 的子查询提取到外部，让 sharding 能正常解析。
   
   com.github.pagehelper.dialect.helper.OracleDialect
   
   `
   
       /**
        * 
        */
       public String getPageSql(String sql, Page page, CacheKey pageKey) {
           StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
           sqlBuilder.append("SELECT * FROM ( ");
           sqlBuilder.append(" SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM ( \n");
           sqlBuilder.append(sql);
           sqlBuilder.append("\n ) TMP_PAGE) TMP_PAGE_2");
           sqlBuilder.append(" WHERE TMP_PAGE_2.PAGEHELPER_ROW_ID <= ? AND TMP_PAGE_2.PAGEHELPER_ROW_ID > ?");
           addOrderBy(sql, sqlBuilder);
           return sqlBuilder.toString();
       }
   
       /**
        * add order by 
        *
        * @param sql
        * @param sqlBuilder
        */
       private void addOrderBy(String sql, StringBuilder sqlBuilder) {
           CCJSqlParserManager parserManager = new CCJSqlParserManager();
           try {
               Statement statement = parserManager.parse(new StringReader(sql));
               if (statement instanceof Select) {
                   Select select = (Select) statement;
                   PlainSelect plain = (PlainSelect) select.getSelectBody();
                   List<OrderByElement> orderByElements = plain.getOrderByElements();
                   int index = 0;
                   if (null != orderByElements && !orderByElements.isEmpty()) {
                       for (OrderByElement orderByElement : orderByElements) {
                           if (null != orderByElement) {
                               if (0 == index) {
                                   sqlBuilder.append(" ORDER BY ");
                               } else {
                                   sqlBuilder.append(" ,");
                               }
                               sqlBuilder.append(orderByElement);
                               index++;
                           }
                       }
                   }
               }
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   `
   
   ![image](https://user-images.githubusercontent.com/49973087/151471309-82f961aa-c001-4a2c-a3ab-c12a8292eb33.png)
   
   执行的sql是
   SELECT * FROM (  SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM ( 
   SELECT * FROM SYS_LOG_12  WHERE (       (  log_type = ? ) ) order by log_date Desc
    ) TMP_PAGE) TMP_PAGE_2 WHERE TMP_PAGE_2.PAGEHELPER_ROW_ID <= ? AND TMP_PAGE_2.PAGEHELPER_ROW_ID > ? ORDER BY log_date DESC
   
   测试可分页及正常排序。
   
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org