Examples with SQLSelect com.alibaba.druid.sql.ast.statement.SQLSelect used on opensource projects

Example 11 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect in project druid by alibaba.

the class MySqlSelectTest_BinaryConcat method test_0.

public void test_0() throws Exception {
    String sql = //
    "select campaignId from Campaign" + //
    "   where   advertiserId =   ?" + //
    "   and   deleteStatus = false" + "   and   campaignName like binary CONCAT('%',?,'%')";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelect select = selectStmt.getSelect();
    Assert.assertNotNull(select.getQuery());
    MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
    Assert.assertNull(queryBlock.getOrderBy());
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    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(4, visitor.getColumns().size());
    Assert.assertEquals(3, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("mytable")));
}

Example 12 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect in project druid by alibaba.

the class MySqlSelectTest_POSITION method test_0.

public void test_0() throws Exception {
    String sql = //
    "select a.rule_id, " + //
    "b.object_id, " + //
    "a.user_nick, " + //
    "SUBSTRING(b.content, POSITION('bizOrderId' IN b.content), 30) as dingdanhao, " + //
    "SUBSTRING(b.content, POSITION('loginIn7Days' IN b.content), 88) as shengdajieguo, " + //
    "a.action_time " + //
    "from ctu_scheme_0025 a, " + "ctu_scheme_detail_0025 b";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelect select = selectStmt.getSelect();
    Assert.assertNotNull(select.getQuery());
    MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
    Assert.assertNull(queryBlock.getOrderBy());
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(2, visitor.getTables().size());
    Assert.assertEquals(5, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("ctu_scheme_0025")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("ctu_scheme_detail_0025")));
}

Example 13 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect in project druid by alibaba.

the class MySqlSelectTest_10 method test_0.

public void test_0() throws Exception {
    String sql = "SELECT * FROM t_department  WHERE name IN ('0000','4444') ORDER BY name ASC";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelect select = selectStmt.getSelect();
    Assert.assertNotNull(select.getQuery());
    MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
    Assert.assertNotNull(queryBlock.getOrderBy());
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(2, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
    Assert.assertEquals(1, visitor.getOrderByColumns().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t_department")));
}

Example 14 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect in project druid by alibaba.

the class MySqlSelectTest_14 method test_0.

public void test_0() throws Exception {
    String sql = "SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelect select = selectStmt.getSelect();
    Assert.assertNotNull(select.getQuery());
    MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
    Assert.assertNull(queryBlock.getOrderBy());
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(2, visitor.getTables().size());
    Assert.assertEquals(3, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t1")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));
    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(//
    "SELECT *" + //
    "\nFROM t1" + //
    "\nWHERE column1 = (" + //
    "\n\tSELECT column1" + //
    "\n\tFROM t2" + //
    "\n\t)", output);
    String output_lcase = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
    Assert.assertEquals(//
    "select *" + //
    "\nfrom t1" + //
    "\nwhere column1 = (" + //
    "\n\tselect column1" + //
    "\n\tfrom t2" + //
    "\n\t)", output_lcase);
}

Example 15 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect in project druid by alibaba.

the class MySqlSelectTest_group_concat method test_0.

public void test_0() throws Exception {
    String sql = //
    "SELECT GROUP_CONCAT(ext_customer_id Separator '.') " + //
    "from ad_texts " + //
    "where customer_id=13001 " + //
    "and description1 like '%爱丽%' " + "order by id asc;";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelect select = selectStmt.getSelect();
    Assert.assertNotNull(select.getQuery());
    MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
    Assert.assertNotNull(queryBlock.getOrderBy());
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertEquals(1, visitor.getOrderByColumns().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("ad_texts")));
    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(//
    "SELECT GROUP_CONCAT(ext_customer_id SEPARATOR '.')" + //
    "\nFROM ad_texts" + //
    "\nWHERE customer_id = 13001" + //
    "\n\tAND description1 LIKE '%爱丽%'" + //
    "\nORDER BY id ASC", output);
}