Examples with SQLServerStatementParser com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser used on opensource projects

Example 21 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser 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;
}

Example 22 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser 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;
}

Example 23 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerAlterTableTest_0 method test_alter_first.

public void test_alter_first() throws Exception {
    String sql = "alter table st_jklsxxb enable constraint FK_ST_xxx_REFERENCE_ST_xxx";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    parser.match(Token.EOF);
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    // System.out.println("Tables : " + visitor.getTables());
    // System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    // System.out.println("orderBy : " + visitor.getOrderByColumns());
    String output = SQLUtils.toSQLString(stmt, JdbcConstants.SQL_SERVER);
    Assert.assertEquals(// 
    "ALTER TABLE st_jklsxxb" + "\n\tENABLE CONSTRAINT FK_ST_xxx_REFERENCE_ST_xxx", output);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(0, visitor.getColumns().size());
}

Example 24 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerSelectTest_outer_apply method test_0.

public void test_0() throws Exception {
    String sql = "SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary FROM Departments d OUTER APPLY dbo.GetReports(d.DeptMgrID)";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelect select = selectStmt.getSelect();
    Assert.assertNotNull(select.getQuery());
    SQLServerSelectQueryBlock queryBlock = (SQLServerSelectQueryBlock) select.getQuery();
    Assert.assertNull(queryBlock.getGroupBy());
    String fomatSQL = SQLUtils.toSQLString(statementList, JdbcUtils.SQL_SERVER);
    // System.out.println(fomatSQL);
    Assert.assertEquals(1, statementList.size());
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    // System.out.println("Tables : " + visitor.getTables());
    // System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    // System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(7, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());
    String expected = // 
    "SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName" + // 
    "\n\t, EmpSalary" + // 
    "\nFROM Departments d" + "\n\tOUTER APPLY dbo.GetReports(d.DeptMgrID)";
    Assert.assertEquals(expected, fomatSQL);
}

Example 25 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerSelectTest25 method test_simple.

public void test_simple() throws Exception {
    // 
    String sql = // 
    "SELECT A.value FROM A oost LEFT JOIN B oo WITH (NOLOCK) ON oost.order_id = oo.id";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    {
        String text = SQLUtils.toSQLServerString(stmt);
        Assert.assertEquals("SELECT A.value\n" + "FROM A oost\n" + "\tLEFT JOIN B oo WITH (NOLOCK) ON oost.order_id = oo.id", text);
    }
    {
        String text = SQLUtils.toSQLServerString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
        Assert.assertEquals("select A.value\n" + "from A oost\n" + "\tleft join B oo with (NOLOCK) on oost.order_id = oo.id", text);
    }
}