Examples with DbType com.alibaba.druid.DbType used on opensource projects

Example 96 with DbType

use of com.alibaba.druid.DbType in project druid by alibaba.

the class MySqlParameterizedOutputVisitorTest_25 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "SELECT `xxx_reverse_order`.`id` from xxx_reverse_order_0446 XXX_REVERSE_ORDER limit 1;";
    String psql = ParameterizedOutputVisitorUtils.parameterize(sql, dbType);
    assertEquals("SELECT `xxx_reverse_order`.`id`\n" + "FROM xxx_reverse_order XXX_REVERSE_ORDER\n" + "LIMIT ?;", psql);
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(psql, dbType);
    List<SQLStatement> stmtList = parser.parseStatementList();
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
    List<Object> parameters = new ArrayList<Object>();
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    visitor.setParameters(parameters);
    visitor.setExportTables(true);
    /*visitor.setPrettyFormat(false);*/
    SQLStatement stmt = stmtList.get(0);
    stmt.accept(visitor);
    // System.out.println(parameters);
    assertEquals(0, parameters.size());
    StringBuilder buf = new StringBuilder();
    SQLASTOutputVisitor visitor1 = SQLUtils.createOutputVisitor(buf, dbType);
    visitor1.addTableMapping("xxx_reverse_order", "xxx_reverse_order_0446");
    visitor1.setParameters(visitor.getParameters());
    stmt.accept(visitor1);
    assertEquals("SELECT `xxx_reverse_order`.`id`\n" + "FROM xxx_reverse_order_0446 XXX_REVERSE_ORDER\n" + "LIMIT ?;", buf.toString());
}

Example 97 with DbType

use of com.alibaba.druid.DbType in project druid by alibaba.

the class MySqlParameterizedOutputVisitorTest_27 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "SELECT `xxx_reverse_od`.dept, count(*) from xxx_reverse_od_0446 `xxx_reverse_od` where `xxx_reverse_od`.name=1 group by `xxx_reverse_od`.dept order by `xxx_reverse_od`.dept";
    String psql = ParameterizedOutputVisitorUtils.parameterize(sql, dbType);
    assertEquals("SELECT `xxx_reverse_od`.dept, count(*)\n" + "FROM xxx_reverse_od `xxx_reverse_od`\n" + "WHERE `xxx_reverse_od`.name = ?\n" + "GROUP BY `xxx_reverse_od`.dept\n" + "ORDER BY `xxx_reverse_od`.dept", psql);
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(psql, dbType);
    List<SQLStatement> stmtList = parser.parseStatementList();
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
    List<Object> parameters = new ArrayList<Object>();
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    visitor.setParameters(parameters);
    visitor.setExportTables(true);
    /*visitor.setPrettyFormat(false);*/
    SQLStatement stmt = stmtList.get(0);
    stmt.accept(visitor);
    // System.out.println(parameters);
    assertEquals(0, parameters.size());
    StringBuilder buf = new StringBuilder();
    SQLASTOutputVisitor visitor1 = SQLUtils.createOutputVisitor(buf, dbType);
    visitor1.addTableMapping("xxx_reverse_od", "xxx_reverse_od_0446");
    visitor1.setParameters(visitor.getParameters());
    stmt.accept(visitor1);
    assertEquals("SELECT `xxx_reverse_od`.dept, count(*)\n" + "FROM xxx_reverse_od_0446 `xxx_reverse_od`\n" + "WHERE `xxx_reverse_od`.name = ?\n" + "GROUP BY `xxx_reverse_od`.dept\n" + "ORDER BY `xxx_reverse_od`.dept", buf.toString());
}

Example 98 with DbType

use of com.alibaba.druid.DbType in project druid by alibaba.

the class MySqlParameterizedOutputVisitorTest_29 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "select `a1`.`id`,`a1`.`gmt_create`,`a1`.`gmt_modified`,`a1`.`push_date`,`a1`.`parent_task_id`" + "   ,`a1`.`parent_task_type`,`a1`.`action_type`,`a1`.`schedule_no`,`a1`.`type`,`a1`.`md5`" + "   ,`a1`.`message_content`,`a1`.`retry_count`,`a1`.`level`,`a1`.`extra`,`a1`.`status`" + "   ,`a1`.`is_exist_relation`,`a1`.`begin_time`,`a1`.`end_time`,`a1`.`orig`,`a1`.`dest`" + "   ,`a1`.`airline`,`a1`.`params_stat_id`,`a1`.`total_num`,`a1`.`finish_num`,`a1`.`type_idx_key`" + "   ,`a1`.`seqno`,`a1`.`task_flag`,`a1`.`tariff` " + "from `xx_abcde_ta_0018` `a1` " + "where ((`a1`.`push_date` = '2017-01-19 00:00:00') AND (`a1`.`schedule_no` <= '201701181201') AND (`a1`.`type` IN (1,4,2,3,7,8,11,12,13,14,15,16)) AND (`a1`.`retry_count` < 3) AND (`a1`.`status` IN (3,6)) AND (`a1`.`gmt_modified` <= DATE_ADD(NOW(),INTERVAL -(5) MINUTE))) limit 0,2000";
    String psql = ParameterizedOutputVisitorUtils.parameterize(sql, dbType);
    assertEquals("SELECT `a1`.`id`, `a1`.`gmt_create`, `a1`.`gmt_modified`, `a1`.`push_date`, `a1`.`parent_task_id`\n" + "\t, `a1`.`parent_task_type`, `a1`.`action_type`, `a1`.`schedule_no`, `a1`.`type`, `a1`.`md5`\n" + "\t, `a1`.`message_content`, `a1`.`retry_count`, `a1`.`level`, `a1`.`extra`, `a1`.`status`\n" + "\t, `a1`.`is_exist_relation`, `a1`.`begin_time`, `a1`.`end_time`, `a1`.`orig`, `a1`.`dest`\n" + "\t, `a1`.`airline`, `a1`.`params_stat_id`, `a1`.`total_num`, `a1`.`finish_num`, `a1`.`type_idx_key`\n" + "\t, `a1`.`seqno`, `a1`.`task_flag`, `a1`.`tariff`\n" + "FROM xx_abcde_ta `a1`\n" + "WHERE `a1`.`push_date` = ?\n" + "\tAND `a1`.`schedule_no` <= ?\n" + "\tAND `a1`.`type` IN (?)\n" + "\tAND `a1`.`retry_count` < ?\n" + "\tAND `a1`.`status` IN (?)\n" + "\tAND `a1`.`gmt_modified` <= DATE_ADD(NOW(), INTERVAL -? MINUTE)\n" + "LIMIT ?, ?", psql);
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
    List<SQLStatement> stmtList = parser.parseStatementList();
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
    List<Object> parameters = new ArrayList<Object>();
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    visitor.setParameters(parameters);
    visitor.setExportTables(true);
    /*visitor.setPrettyFormat(false);*/
    SQLStatement stmt = stmtList.get(0);
    stmt.accept(visitor);
    // System.out.println(parameters);
    assertEquals(8, parameters.size());
    // SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(psql, dbType);
    // List<SQLStatement> stmtList = parser.parseStatementList();
    SQLStatement pstmt = SQLUtils.parseStatements(psql, dbType).get(0);
    StringBuilder buf = new StringBuilder();
    SQLASTOutputVisitor visitor1 = SQLUtils.createOutputVisitor(buf, dbType);
    visitor1.addTableMapping("xx_abcde_ta", "xx_abcde_ta_0018");
    visitor1.setInputParameters(visitor.getParameters());
    pstmt.accept(visitor1);
    assertEquals("SELECT `a1`.`id`, `a1`.`gmt_create`, `a1`.`gmt_modified`, `a1`.`push_date`, `a1`.`parent_task_id`\n" + "\t, `a1`.`parent_task_type`, `a1`.`action_type`, `a1`.`schedule_no`, `a1`.`type`, `a1`.`md5`\n" + "\t, `a1`.`message_content`, `a1`.`retry_count`, `a1`.`level`, `a1`.`extra`, `a1`.`status`\n" + "\t, `a1`.`is_exist_relation`, `a1`.`begin_time`, `a1`.`end_time`, `a1`.`orig`, `a1`.`dest`\n" + "\t, `a1`.`airline`, `a1`.`params_stat_id`, `a1`.`total_num`, `a1`.`finish_num`, `a1`.`type_idx_key`\n" + "\t, `a1`.`seqno`, `a1`.`task_flag`, `a1`.`tariff`\n" + "FROM xx_abcde_ta_0018 `a1`\n" + "WHERE `a1`.`push_date` = '2017-01-19 00:00:00'\n" + "\tAND `a1`.`schedule_no` <= '201701181201'\n" + "\tAND `a1`.`type` IN (1, 4, 2, 3, 7, 8, 11, 12, 13, 14, 15, 16)\n" + "\tAND `a1`.`retry_count` < 3\n" + "\tAND `a1`.`status` IN (3, 6)\n" + "\tAND `a1`.`gmt_modified` <= DATE_ADD(NOW(), INTERVAL -5 MINUTE)\n" + "LIMIT 0, 2000", buf.toString());
}

Example 99 with DbType

use of com.alibaba.druid.DbType in project druid by alibaba.

the class MySqlParameterizedOutputVisitorTest_30 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "/* 0a67bca314863468702364451e/0.3// */select `udata`.`id` as `id`,`udata`.`gmt_create` as `gmtCreate`,`udata`.`gmt_modified` as `gmtModified`,`udata`.`uid` as `userId`,`udata`.`user_nick` as `userNick`,`udata`.`user_type` as `userType`,`udata`.`aps` as `acPeSe`,`udata`.`rn` as `rn`,`udata`.`start_period_time` as `startPeriodTime`,`udata`.`ept` as `adTm`,`udata`.`status` as `status`,`udata`.`charging_period` as `chargingPeriod`,`udata`.`sn` as `sn`,`udata`.`cpd` as `chargingPeriodDesc`,`udata`.`task_total_num` as `taskTotalNum`,`udata`.`tcn` as `taCoNu`,`udata`.`task_type` as `taskType`,`udata`.`ilbu` as `isLaBiUs`" + " from `udata_0888` `udata` where ((`udata`.`id` IN (" + "   (select MAX(`udata`.`id`) " + "   from `udata_0888` `udata` " + "   where ((`udata`.`uid` = 1039100792) AND (`udata`.`user_type` = 2) AND (`udata`.`start_period_time` <= '2017-01-01 00:00:00') AND (`udata`.`status` = 10) AND (`udata`.`charging_period` = 1) AND (`udata`.`task_type` = 1) AND (`udata`.`task_total_num` <= `udata`.`tcn`)) group by `udata`.`charging_period`,`udata`.`start_period_time`,`udata`.`ept`))) AND ((`udata`.`uid` = '1039100792') AND (`udata`.`user_type` = 2))) order by `udata`.`start_period_time` desc  limit 0,6";
    String psql = ParameterizedOutputVisitorUtils.parameterize(sql, dbType);
    assertEquals("SELECT `udata`.`id` AS `id`, `udata`.`gmt_create` AS `gmtCreate`, `udata`.`gmt_modified` AS `gmtModified`, `udata`.`uid` AS `userId`, `udata`.`user_nick` AS `userNick`\n" + "\t, `udata`.`user_type` AS `userType`, `udata`.`aps` AS `acPeSe`, `udata`.`rn` AS `rn`, `udata`.`start_period_time` AS `startPeriodTime`, `udata`.`ept` AS `adTm`\n" + "\t, `udata`.`status` AS `status`, `udata`.`charging_period` AS `chargingPeriod`, `udata`.`sn` AS `sn`, `udata`.`cpd` AS `chargingPeriodDesc`, `udata`.`task_total_num` AS `taskTotalNum`\n" + "\t, `udata`.`tcn` AS `taCoNu`, `udata`.`task_type` AS `taskType`, `udata`.`ilbu` AS `isLaBiUs`\n" + "FROM udata `udata`\n" + "WHERE `udata`.`id` IN (\n" + "\t\tSELECT MAX(`udata`.`id`)\n" + "\t\tFROM udata `udata`\n" + "\t\tWHERE `udata`.`uid` = ?\n" + "\t\t\tAND `udata`.`user_type` = ?\n" + "\t\t\tAND `udata`.`start_period_time` <= ?\n" + "\t\t\tAND `udata`.`status` = ?\n" + "\t\t\tAND `udata`.`charging_period` = ?\n" + "\t\t\tAND `udata`.`task_type` = ?\n" + "\t\t\tAND `udata`.`task_total_num` <= `udata`.`tcn`\n" + "\t\tGROUP BY `udata`.`charging_period`, `udata`.`start_period_time`, `udata`.`ept`\n" + "\t)\n" + "\tAND (`udata`.`uid` = ?\n" + "\t\tAND `udata`.`user_type` = ?)\n" + "ORDER BY `udata`.`start_period_time` DESC\n" + "LIMIT ?, ?", psql);
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
    List<SQLStatement> stmtList = parser.parseStatementList();
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
    List<Object> parameters = new ArrayList<Object>();
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    visitor.setParameters(parameters);
    visitor.setExportTables(true);
    /*visitor.setPrettyFormat(false);*/
    SQLStatement stmt = stmtList.get(0);
    stmt.accept(visitor);
    // System.out.println(parameters);
    assertEquals(10, parameters.size());
    // SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(psql, dbType);
    // List<SQLStatement> stmtList = parser.parseStatementList();
    SQLStatement pstmt = SQLUtils.parseStatements(psql, dbType).get(0);
    StringBuilder buf = new StringBuilder();
    SQLASTOutputVisitor visitor1 = SQLUtils.createOutputVisitor(buf, dbType);
    visitor1.addTableMapping("udata", "udata_0888");
    visitor1.setInputParameters(visitor.getParameters());
    pstmt.accept(visitor1);
    assertEquals("SELECT `udata`.`id` AS `id`, `udata`.`gmt_create` AS `gmtCreate`, `udata`.`gmt_modified` AS `gmtModified`, `udata`.`uid` AS `userId`, `udata`.`user_nick` AS `userNick`\n" + "\t, `udata`.`user_type` AS `userType`, `udata`.`aps` AS `acPeSe`, `udata`.`rn` AS `rn`, `udata`.`start_period_time` AS `startPeriodTime`, `udata`.`ept` AS `adTm`\n" + "\t, `udata`.`status` AS `status`, `udata`.`charging_period` AS `chargingPeriod`, `udata`.`sn` AS `sn`, `udata`.`cpd` AS `chargingPeriodDesc`, `udata`.`task_total_num` AS `taskTotalNum`\n" + "\t, `udata`.`tcn` AS `taCoNu`, `udata`.`task_type` AS `taskType`, `udata`.`ilbu` AS `isLaBiUs`\n" + "FROM udata_0888 `udata`\n" + "WHERE `udata`.`id` IN (\n" + "\t\tSELECT MAX(`udata`.`id`)\n" + "\t\tFROM udata_0888 `udata`\n" + "\t\tWHERE `udata`.`uid` = 1039100792\n" + "\t\t\tAND `udata`.`user_type` = 2\n" + "\t\t\tAND `udata`.`start_period_time` <= '2017-01-01 00:00:00'\n" + "\t\t\tAND `udata`.`status` = 10\n" + "\t\t\tAND `udata`.`charging_period` = 1\n" + "\t\t\tAND `udata`.`task_type` = 1\n" + "\t\t\tAND `udata`.`task_total_num` <= `udata`.`tcn`\n" + "\t\tGROUP BY `udata`.`charging_period`, `udata`.`start_period_time`, `udata`.`ept`\n" + "\t)\n" + "\tAND (`udata`.`uid` = '1039100792'\n" + "\t\tAND `udata`.`user_type` = 2)\n" + "ORDER BY `udata`.`start_period_time` DESC\n" + "LIMIT 0, 6", buf.toString());
}

Example 100 with DbType

use of com.alibaba.druid.DbType in project druid by alibaba.

the class MySqlParameterizedOutputVisitorTest_31 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "/* 0a67bca314863468702364451e/0.3// */select `udata`.`id` as `id`,`udata`.`gmt_create` as `gmtCreate`,`udata`.`gmt_modified` as `gmtModified`,`udata`.`uid` as `userId`,`udata`.`user_nick` as `userNick`,`udata`.`user_type` as `userType`,`udata`.`aps` as `acPeSe`,`udata`.`rn` as `rn`,`udata`.`start_period_time` as `startPeriodTime`,`udata`.`ept` as `adTm`,`udata`.`status` as `status`,`udata`.`charging_period` as `chargingPeriod`,`udata`.`sn` as `sn`,`udata`.`cpd` as `chargingPeriodDesc`,`udata`.`task_total_num` as `taskTotalNum`,`udata`.`tcn` as `taCoNu`,`udata`.`task_type` as `taskType`,`udata`.`ilbu` as `isLaBiUs`" + " from `udata_0888` `udata` where ((`udata`.`id` IN ((select MAX(`udata`.`id`) from `udata_0888` `udata` where ((`udata`.`uid` = 1039100792) AND (`udata`.`user_type` = 2) AND (`udata`.`start_period_time` <= '2017-01-01 00:00:00') AND (`udata`.`status` = 10) AND (`udata`.`charging_period` = 1) AND (`udata`.`task_type` = 1) AND (`udata`.`task_total_num` <= `udata`.`tcn`)) group by `udata`.`charging_period`,`udata`.`start_period_time`,`udata`.`ept`))) AND ((`udata`.`uid` = '1039100792') AND (`udata`.`user_type` = 2))) order by `udata`.`start_period_time` desc  limit 0,6";
    String psql = ParameterizedOutputVisitorUtils.parameterize(sql, dbType);
    assertEquals("SELECT `udata`.`id` AS `id`, `udata`.`gmt_create` AS `gmtCreate`, `udata`.`gmt_modified` AS `gmtModified`, `udata`.`uid` AS `userId`, `udata`.`user_nick` AS `userNick`\n" + "\t, `udata`.`user_type` AS `userType`, `udata`.`aps` AS `acPeSe`, `udata`.`rn` AS `rn`, `udata`.`start_period_time` AS `startPeriodTime`, `udata`.`ept` AS `adTm`\n" + "\t, `udata`.`status` AS `status`, `udata`.`charging_period` AS `chargingPeriod`, `udata`.`sn` AS `sn`, `udata`.`cpd` AS `chargingPeriodDesc`, `udata`.`task_total_num` AS `taskTotalNum`\n" + "\t, `udata`.`tcn` AS `taCoNu`, `udata`.`task_type` AS `taskType`, `udata`.`ilbu` AS `isLaBiUs`\n" + "FROM udata `udata`\n" + "WHERE `udata`.`id` IN (\n" + "\t\tSELECT MAX(`udata`.`id`)\n" + "\t\tFROM udata `udata`\n" + "\t\tWHERE `udata`.`uid` = ?\n" + "\t\t\tAND `udata`.`user_type` = ?\n" + "\t\t\tAND `udata`.`start_period_time` <= ?\n" + "\t\t\tAND `udata`.`status` = ?\n" + "\t\t\tAND `udata`.`charging_period` = ?\n" + "\t\t\tAND `udata`.`task_type` = ?\n" + "\t\t\tAND `udata`.`task_total_num` <= `udata`.`tcn`\n" + "\t\tGROUP BY `udata`.`charging_period`, `udata`.`start_period_time`, `udata`.`ept`\n" + "\t)\n" + "\tAND (`udata`.`uid` = ?\n" + "\t\tAND `udata`.`user_type` = ?)\n" + "ORDER BY `udata`.`start_period_time` DESC\n" + "LIMIT ?, ?", psql);
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
    List<SQLStatement> stmtList = parser.parseStatementList();
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
    List<Object> parameters = new ArrayList<Object>();
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    visitor.setParameters(parameters);
    visitor.setExportTables(true);
    /*visitor.setPrettyFormat(false);*/
    SQLStatement stmt = stmtList.get(0);
    stmt.accept(visitor);
    // System.out.println(parameters);
    assertEquals(10, parameters.size());
    // SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(psql, dbType);
    // List<SQLStatement> stmtList = parser.parseStatementList();
    SQLStatement pstmt = SQLUtils.parseStatements(psql, dbType).get(0);
    StringBuilder buf = new StringBuilder();
    SQLASTOutputVisitor visitor1 = SQLUtils.createOutputVisitor(buf, dbType);
    visitor1.addTableMapping("udata", "udata_0888");
    visitor1.setInputParameters(visitor.getParameters());
    pstmt.accept(visitor1);
    assertEquals("SELECT `udata`.`id` AS `id`, `udata`.`gmt_create` AS `gmtCreate`, `udata`.`gmt_modified` AS `gmtModified`, `udata`.`uid` AS `userId`, `udata`.`user_nick` AS `userNick`\n" + "\t, `udata`.`user_type` AS `userType`, `udata`.`aps` AS `acPeSe`, `udata`.`rn` AS `rn`, `udata`.`start_period_time` AS `startPeriodTime`, `udata`.`ept` AS `adTm`\n" + "\t, `udata`.`status` AS `status`, `udata`.`charging_period` AS `chargingPeriod`, `udata`.`sn` AS `sn`, `udata`.`cpd` AS `chargingPeriodDesc`, `udata`.`task_total_num` AS `taskTotalNum`\n" + "\t, `udata`.`tcn` AS `taCoNu`, `udata`.`task_type` AS `taskType`, `udata`.`ilbu` AS `isLaBiUs`\n" + "FROM udata_0888 `udata`\n" + "WHERE `udata`.`id` IN (\n" + "\t\tSELECT MAX(`udata`.`id`)\n" + "\t\tFROM udata_0888 `udata`\n" + "\t\tWHERE `udata`.`uid` = 1039100792\n" + "\t\t\tAND `udata`.`user_type` = 2\n" + "\t\t\tAND `udata`.`start_period_time` <= '2017-01-01 00:00:00'\n" + "\t\t\tAND `udata`.`status` = 10\n" + "\t\t\tAND `udata`.`charging_period` = 1\n" + "\t\t\tAND `udata`.`task_type` = 1\n" + "\t\t\tAND `udata`.`task_total_num` <= `udata`.`tcn`\n" + "\t\tGROUP BY `udata`.`charging_period`, `udata`.`start_period_time`, `udata`.`ept`\n" + "\t)\n" + "\tAND (`udata`.`uid` = '1039100792'\n" + "\t\tAND `udata`.`user_type` = 2)\n" + "ORDER BY `udata`.`start_period_time` DESC\n" + "LIMIT 0, 6", buf.toString());
}