【Mycat1.6之分页代码源码研究】

使用Mycat感触最深的是：有了Mycat后端都是Mysql数据库了，对外屏蔽了数据库底层，你压根不知道后端使用的啥数据库，甚至是分页你按照Mysql的Limit语法传递参数就没有问题，因为后端的PageSQLUtil会自动包装转换.........

 

 

 

PageSQLUtil类目前兼容ORACLE、SQL_SERVER、DB2、POSTGRESQL、MYSQL分页。

public class PageSQLUtil

{

    public static String convertLimitToNativePageSql(String dbType, String sql,

                                  int offset, int count)

    {

        if (JdbcConstants.ORACLE.equalsIgnoreCase(dbType))

        {

            OracleStatementParser oracleParser = new OracleStatementParser(sql);

            SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();

            return PagerUtils.limit(oracleStmt.getSelect(), JdbcConstants.ORACLE, offset, count);

        } else if (JdbcConstants.SQL_SERVER.equalsIgnoreCase(dbType))

        {

            SQLServerStatementParser oracleParser = new SQLServerStatementParser(sql);

            SQLSelectStatement sqlserverStmt = (SQLSelectStatement) oracleParser.parseStatement();

            SQLSelect select = sqlserverStmt.getSelect();

            SQLOrderBy orderBy=  select.getOrderBy() ;

            if(orderBy==null)

            {

                SQLSelectQuery sqlSelectQuery= select.getQuery();

                if(sqlSelectQuery instanceof SQLServerSelectQueryBlock)

                {

                        SQLServerSelectQueryBlock sqlServerSelectQueryBlock= (SQLServerSelectQueryBlock) sqlSelectQuery;

                    SQLTableSource from=       sqlServerSelectQueryBlock.getFrom();

                    if("limit".equalsIgnoreCase(from.getAlias()))

                    {

                        from.setAlias(null);

                    }

                }

                SQLOrderBy newOrderBy=new SQLOrderBy(new SQLIdentifierExpr("(select 0)"));

                select.setOrderBy(newOrderBy);

            }

            return PagerUtils.limit(select, JdbcConstants.SQL_SERVER, offset, count)  ;

        }

        else if (JdbcConstants.DB2.equalsIgnoreCase(dbType))

        {

            DB2StatementParser db2Parser = new DB2StatementParser(sql);

            SQLSelectStatement db2Stmt = (SQLSelectStatement) db2Parser.parseStatement();

            //注意此处没有调用PagerUtils类中的方法，也许PagerUtils中的limitDB2这个方法有bug

            //因为PagerUtils 来自druid-1.0.26包中的com.alibaba.druid.sql.PagerUtils 

            return limitDB2(db2Stmt.getSelect(), JdbcConstants.DB2, offset, count);

        }  else if (JdbcConstants.POSTGRESQL.equalsIgnoreCase(dbType))

        {

            PGSQLStatementParser pgParser = new PGSQLStatementParser(sql);

            SQLSelectStatement pgStmt = (SQLSelectStatement) pgParser.parseStatement();

            SQLSelect select = pgStmt.getSelect();

            SQLSelectQuery query= select.getQuery();

            if(query instanceof PGSelectQueryBlock)

            {

                PGSelectQueryBlock pgSelectQueryBlock= (PGSelectQueryBlock) query;

                pgSelectQueryBlock.setOffset(null);

                pgSelectQueryBlock.setLimit(null);

 

            }

            return PagerUtils.limit(select, JdbcConstants.POSTGRESQL, offset, count);

        }  else if (JdbcConstants.MYSQL.equalsIgnoreCase(dbType))

        {

            MySqlStatementParser pgParser = new MySqlStatementParser(sql);

            SQLSelectStatement pgStmt = (SQLSelectStatement) pgParser.parseStatement();

            SQLSelect select = pgStmt.getSelect();

            SQLSelectQuery query= select.getQuery();

            if(query instanceof MySqlSelectQueryBlock)

            {

                MySqlSelectQueryBlock pgSelectQueryBlock= (MySqlSelectQueryBlock) query;

                pgSelectQueryBlock.setLimit(null);

            }

            return PagerUtils.limit(select, JdbcConstants.MYSQL, offset, count);

        }

        return sql;

    }

 

 

 

 

原创不易，欢迎打赏,请认准正确地址，谨防假冒

 

 

附件：实验代码

EXPLAIN select * from dept limit 4,3

truncate table dept

INSERT into dept(uuid,id,address) values(1,1,'北京');

INSERT into dept(uuid,id,address) values(2,2,'上海');

INSERT into dept(uuid,id,address) values(3,3,'深圳');

 

 

INSERT into dept(uuid,id,address) values(4,4,'广州');

INSERT into dept(uuid,id,address) values(5,5,'天津');

INSERT into dept(uuid,id,address) values(6,6,'杭州');

INSERT into dept(uuid,id,address) values(7,7,'南京');

INSERT into dept(uuid,id,address) values(8,8,'济南');

INSERT into dept(uuid,id,address) values(9,9,'重庆');

INSERT into dept(uuid,id,address) values(10,10,'青岛');

INSERT into dept(uuid,id,address) values(11,11,'大连');

INSERT into dept(uuid,id,address) values(12,12,'宁波');

INSERT into dept(uuid,id,address) values(13,13,'厦门');

INSERT into dept(uuid,id,address) values(14,14,'成都');

INSERT into dept(uuid,id,address) values(15,15,'武汉');

INSERT into dept(uuid,id,address) values(16,16,'哈尔滨');

INSERT into dept(uuid,id,address) values(17,17,'沈阳');

INSERT into dept(uuid,id,address) values(18,18,'西安');

INSERT into dept(uuid,id,address) values(19,19,'长春');

INSERT into dept(uuid,id,address) values(20,20,'长沙');

INSERT into dept(uuid,id,address) values(21,21,'福州');

INSERT into dept(uuid,id,address) values(22,22,'郑州');

INSERT into dept(uuid,id,address) values(23,23,'石家庄');

INSERT into dept(uuid,id,address) values(24,24,'苏州');

INSERT into dept(uuid,id,address) values(25,25,'佛山');

INSERT into dept(uuid,id,address) values(26,26,'东莞');

INSERT into dept(uuid,id,address) values(27,27,'无锡');

INSERT into dept(uuid,id,address) values(28,28,'烟台');

INSERT into dept(uuid,id,address) values(29,29,'太原');