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

Example 11 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 12 with SQLServerStatementParser

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

the class SQLServerSelectTest1 method test_isEmpty.

public void test_isEmpty() throws Exception {
    String sql = "USE AdventureWorks2008R2;";
    String expect = "USE AdventureWorks2008R2";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    String text = TestUtils.outputSqlServer(stmt);
    Assert.assertEquals(expect, text);
//        System.out.println(text);
}

Example 13 with SQLServerStatementParser

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

the class SQLServerSelectTest10 method test_isEmpty.

public void test_isEmpty() throws Exception {
    String sql = //
    "SELECT c.FirstName, c.LastName, e.Title, a.AddressLine1, a.City" + //
    " , sp.Name AS [State/Province], a.PostalCode" + //
    " INTO dbo.EmployeeAddresses" + //
    " FROM Person.Contact AS c" + //
    " JOIN HumanResources.Employee AS e ON e.ContactID = c.ContactID" + //
    " JOIN HumanResources.EmployeeAddress AS ea ON ea.EmployeeID = e.EmployeeID" + //
    " JOIN Person.Address AS a on a.AddressID = ea.AddressID" + " JOIN Person.StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID;";
    String expect = //
    "SELECT c.FirstName, c.LastName, e.Title, a.AddressLine1, a.City" + //
    "\n\t, sp.Name AS [State/Province], a.PostalCode" + //
    "\nINTO dbo.EmployeeAddresses" + //
    "\nFROM Person.Contact c" + //
    "\n\tJOIN HumanResources.Employee e ON e.ContactID = c.ContactID" + //
    "\n\tJOIN HumanResources.EmployeeAddress ea ON ea.EmployeeID = e.EmployeeID" + //
    "\n\tJOIN Person.Address a ON a.AddressID = ea.AddressID" + "\n\tJOIN Person.StateProvince sp ON sp.StateProvinceID = a.StateProvinceID";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    String text = TestUtils.outputSqlServer(stmt);
    Assert.assertEquals(expect, text);
//        System.out.println(text);
}

Example 14 with SQLServerStatementParser

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

the class SQLServerSelectTest13 method test_simple.

public void test_simple() throws Exception {
    String sql = //
    "SELECT" + //
    "    a.* " + //
    "FROM" + //
    "    (" + //
    "        SELECT" + //
    "            row_number () over (ORDER BY a.time_add DESC) ROW," + //
    "            a.detail_no AS detailNo," + //
    "            a.ba_id AS baId," + //
    "            a.ba_name AS baName," + //
    "            a.tran_no AS tranNo," + //
    "            a.tran_name AS tranName, " + //
    "            a.tran_type AS tranType, " + //
    "            a.balance_type AS balanceType, " + //
    "            a.detail_income AS detailIncome, " + //
    "            a.detail_payout AS detailPayout, " + //
    "            a.before_balance AS beforeBalance, " + //
    "            a.after_balance AS afterBalance, " + //
    "            a.time_add AS timeAdd, " + //
    "            a.user_add AS userAdd, " + //
    "            a.remark AS remark, " + //
    "            ( " + //
    "                SELECT " + //
    "                    top 1 t.param_name " + //
    "                FROM " + //
    "                    config.sys_params t " + //
    "                WHERE " + //
    "                    t.param_type = ? " + //
    "                AND t.param_value = a.tran_type " + //
    "            ) AS tranTypeName " + //
    "        FROM " + //
    "            bussiness.account_detail a " + //
    "        WHERE " + //
    "            1 = 1 " + //
    "        AND a.time_add >= ? " + //
    "        AND a.time_add <= ? " + //
    "    ) a " + //
    "WHERE " + //
    "    a. ROW NOT BETWEEN (?+ 1) " + //
    "AND (?+?)";
    String expect = "SELECT a.*" + "\nFROM (SELECT ROW_NUMBER() OVER (ORDER BY a.time_add DESC) AS ROW, a.detail_no AS detailNo, a.ba_id AS baId, a.ba_name AS baName, a.tran_no AS tranNo" + "\n\t\t, a.tran_name AS tranName, a.tran_type AS tranType, a.balance_type AS balanceType, a.detail_income AS detailIncome, a.detail_payout AS detailPayout" + "\n\t\t, a.before_balance AS beforeBalance, a.after_balance AS afterBalance, a.time_add AS timeAdd, a.user_add AS userAdd, a.remark AS remark" + "\n\t\t, (" + "\n\t\t\tSELECT TOP 1 t.param_name" + "\n\t\t\tFROM config.sys_params t" + "\n\t\t\tWHERE t.param_type = ?" + "\n\t\t\t\tAND t.param_value = a.tran_type" + "\n\t\t\t) AS tranTypeName" + "\n\tFROM bussiness.account_detail a" + "\n\tWHERE 1 = 1" + "\n\t\tAND a.time_add >= ?" + "\n\t\tAND a.time_add <= ?" + "\n\t) a" + "\nWHERE a.ROW NOT BETWEEN ? + 1 AND ? + ?";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    String text = TestUtils.outputSqlServer(stmt);
    Assert.assertEquals(expect, text);
//        System.out.println(text);
}

Example 15 with SQLServerStatementParser

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

the class SQLServerSelectTest16 method test_simple.

public void test_simple() throws Exception {
    String sql = //
    "select GEN_VAL " + //
    "from ID_GENERATOR with (updlock, rowlock) " + //
    "where GEN_NAME = 'T_USERS'";
    String expect = //
    "SELECT GEN_VAL" + //
    "\nFROM ID_GENERATOR WITH (updlock, rowlock)" + "\nWHERE GEN_NAME = 'T_USERS'";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    String text = TestUtils.outputSqlServer(stmt);
    Assert.assertEquals(expect, text);
//        System.out.println(text);
}