Search in sources :

Example 1 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project Mycat_plus by coderczp.

the class PageSQLUtil method convertLimitToNativePageSql.

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();
        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;
}
Also used : SQLOrderBy(com.alibaba.druid.sql.ast.SQLOrderBy) SQLServerStatementParser(com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser) DB2StatementParser(com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser) PGSelectQueryBlock(com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) OracleStatementParser(com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser) PGSQLStatementParser(com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser) SQLServerSelectQueryBlock(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)

Example 2 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project Mycat-Server by MyCATApache.

the class PageSQLUtil method convertLimitToNativePageSql.

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();
        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;
}
Also used : SQLOrderBy(com.alibaba.druid.sql.ast.SQLOrderBy) SQLServerStatementParser(com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser) DB2StatementParser(com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser) PGSelectQueryBlock(com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) OracleStatementParser(com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser) PGSQLStatementParser(com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser) SQLServerSelectQueryBlock(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)

Example 3 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project druid by alibaba.

the class UnsignedBigIntTest method test_postgresqlUnsignedBitInt.

public void test_postgresqlUnsignedBitInt() {
    String sql = "SELECT a from b where c <> 1 LIMIT 18446744073709551615 OFFSET 1";
    PGSQLStatementParser parser = new PGSQLStatementParser(sql);
    SQLStatement sqlStatement = parser.parseStatement();
    StringBuilder sb = new StringBuilder();
    PGOutputVisitor visitor = new PGOutputVisitor(sb);
    visitor.setPrettyFormat(false);
    sqlStatement.accept(visitor);
    assertEquals("SELECT a FROM b WHERE c <> 1 LIMIT 18446744073709551615 OFFSET 1", sb.toString());
}
Also used : PGOutputVisitor(com.alibaba.druid.sql.dialect.postgresql.visitor.PGOutputVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) PGSQLStatementParser(com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser)

Example 4 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project druid by alibaba.

the class PGSelectTest35 method test_0.

public void test_0() throws Exception {
    String sql = "select 'thomas' !~ '.*thomas.*'";
    PGSQLStatementParser parser = new PGSQLStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    Assert.assertEquals("SELECT 'thomas' !~ '.*thomas.*'", SQLUtils.toPGString(stmt));
    Assert.assertEquals("select 'thomas' !~ '.*thomas.*'", SQLUtils.toPGString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    Assert.assertEquals(1, statementList.size());
    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    stmt.accept(visitor);
    // System.out.println("Tables : " + visitor.getTables());
    // System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    Assert.assertEquals(0, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getTables().size());
}
Also used : PGSchemaStatVisitor(com.alibaba.druid.sql.dialect.postgresql.visitor.PGSchemaStatVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) PGSQLStatementParser(com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser)

Example 5 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project druid by alibaba.

the class PGSelectTest37 method test_0.

public void test_0() throws Exception {
    String sql = "select date '2001-09-28'";
    PGSQLStatementParser parser = new PGSQLStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    Assert.assertEquals("SELECT DATE '2001-09-28'", SQLUtils.toPGString(stmt));
    Assert.assertEquals("select date '2001-09-28'", SQLUtils.toPGString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    Assert.assertEquals(1, statementList.size());
    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    stmt.accept(visitor);
    // System.out.println("Tables : " + visitor.getTables());
    // System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    Assert.assertEquals(0, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getTables().size());
}
Also used : PGSchemaStatVisitor(com.alibaba.druid.sql.dialect.postgresql.visitor.PGSchemaStatVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) PGSQLStatementParser(com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser)

Aggregations

PGSQLStatementParser (com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser)143 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)138 PGSchemaStatVisitor (com.alibaba.druid.sql.dialect.postgresql.visitor.PGSchemaStatVisitor)127 PGSelectStatement (com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectStatement)3 PGOutputVisitor (com.alibaba.druid.sql.dialect.postgresql.visitor.PGOutputVisitor)3 SQLOrderBy (com.alibaba.druid.sql.ast.SQLOrderBy)2 DB2StatementParser (com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser)2 MySqlSelectQueryBlock (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)2 MySqlStatementParser (com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)2 OracleStatementParser (com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser)2 PGSelectQueryBlock (com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock)2 PGExportParameterVisitor (com.alibaba.druid.sql.dialect.postgresql.visitor.PGExportParameterVisitor)2 SQLServerSelectQueryBlock (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock)2 SQLServerStatementParser (com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser)2 ParserException (com.alibaba.druid.sql.parser.ParserException)2 SQLSelectQueryBlock (com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock)1 SQLUnionQuery (com.alibaba.druid.sql.ast.statement.SQLUnionQuery)1 PGDeleteStatement (com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGDeleteStatement)1 PGInsertStatement (com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGInsertStatement)1 SchemaStatVisitor (com.alibaba.druid.sql.visitor.SchemaStatVisitor)1