Examples with MySqlInsertStatement com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement used on opensource projects

Example 36 with MySqlInsertStatement

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement in project druid by alibaba.

the class MySqlInsertTest_27_str_concat method test_insert_concat_3.

public void test_insert_concat_3() throws Exception {
    String sql = "insert ignore into ktv_sms_test (cp) values (\"a\" 'b')";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    MySqlInsertStatement insertStmt = (MySqlInsertStatement) stmt;
    assertEquals("INSERT IGNORE INTO ktv_sms_test (cp)\n" + "VALUES ('ab')", SQLUtils.toMySqlString(insertStmt));
    System.out.println(sql);
    System.out.println(stmt.toString());
    {
        List<Object> outParameters = new ArrayList<Object>();
        String psql = ParameterizedOutputVisitorUtils.parameterize(sql, JdbcConstants.MYSQL, outParameters);
        assertEquals("INSERT IGNORE INTO ktv_sms_test(cp)\n" + "VALUES (?)", psql);
        assertEquals(1, outParameters.size());
        String rsql = ParameterizedOutputVisitorUtils.restore(psql, JdbcConstants.MYSQL, outParameters);
        assertEquals("INSERT IGNORE INTO ktv_sms_test (cp)\n" + "VALUES ('ab')", rsql);
    }
}

Example 37 with MySqlInsertStatement

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement in project druid by alibaba.

the class MySqlInsertTest_43 method test_insert_0.

public void test_insert_0() throws Exception {
    String sql = "/*+engine=MPP, mppNativeInsertFromSelect=true*/\n" + "insert into adl_indication_ums_warehouse_ebax_a (sys_pk,sys_ds,sys_biztime,sys_gmt_modified,business_type,parcelled_order_num,warehouse_id)\n" + "select\n" + "    concat(20190115,'_',ifnull(businessType, '#null#') ,'_',ifnull(warehouseId, '#null#') ) as sys_pk,\n" + "    cast(20190115 as bigint) as sys_ds,\n" + "    str_to_date('2019-01-15 21:51:00', '%Y-%m-%d %H:%i:%s') as sys_biztime,\n" + "    now() as sys_gmt_modified,\n" + "    cast(businessType as varchar) as business_type,\n" + "    cast(parcelledOrderNum as bigint) as parcelled_order_num,\n" + "    cast(warehouseId as varchar) as warehouse_id\n" + "from (\n" + "\n" + "\n" + "select  cast(t1.warehouse_id as varchar) as warehouseId\n" + "      , t2.businessType\n" + "      , count(1) as parcelledOrderNum\n" + "from sales_order t1\n" + "join (\n" + "           select t2_1.warehouse_id\n" + "                , t2_1.external_batch_code\n" + "                ,(case\n" + "                   when t2_2.operation_type = 2 or t2_2.operation_type is null then '2'\n" + "                   when t2_2.operation_type = 3 then '800'\n" + "                   when t2_2.operation_type = 1 then '900'\n" + "                  end) as businessType\n" + "             from batch_order t2_1\n" + "             join wave_order t2_2\n" + "               on t2_1.wave_id = t2_2.id\n" + "              and t2_1.warehouse_id = t2_2.warehouse_id\n" + "              and t2_2.wave_status in (5,6)\n" + "              and t2_1.ds >= (201901 - 1)\n" + "              and t2_2.ds >= (201901 - 1)\n" + "              and t2_1.gmt_create >= date_add(now(), interval -3 day)\n" + "              and t2_1.gmt_create <= now()\n" + "              and t2_2.gmt_create >= date_add(now(), interval -3 day)\n" + "              and t2_2.gmt_create <= now()\n" + "\n" + ") t2 on t1.out_batch_code = t2.external_batch_code\n" + " and t1.warehouse_id = t2.warehouse_id\n" + " and t1.out_operation_mode = 3\n" + " and t1.gmt_create >= CURDATE()\n" + " and t1.gmt_create <= now()\n" + " and t1.ds >= 201901\n" + " and t2.businessType is not null\n" + "group by t1.warehouse_id, t2.businessType\n" + ") a\n";
    MySqlStatementParser parser = new MySqlStatementParser(sql, false, true);
    parser.config(SQLParserFeature.KeepInsertValueClauseOriginalString, true);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    MySqlInsertStatement insertStmt = (MySqlInsertStatement) stmt;
    assertEquals("/*+engine=MPP, mppNativeInsertFromSelect=true*/\n" + "INSERT INTO adl_indication_ums_warehouse_ebax_a (sys_pk, sys_ds, sys_biztime, sys_gmt_modified, business_type\n" + "\t, parcelled_order_num, warehouse_id)\n" + "SELECT concat(20190115, '_', ifnull(businessType, '#null#'), '_', ifnull(warehouseId, '#null#')) AS sys_pk\n" + "\t, CAST(20190115 AS bigint) AS sys_ds, str_to_date('2019-01-15 21:51:00', '%Y-%m-%d %H:%i:%s') AS sys_biztime, now() AS sys_gmt_modified\n" + "\t, CAST(businessType AS varchar) AS business_type, CAST(parcelledOrderNum AS bigint) AS parcelled_order_num, CAST(warehouseId AS varchar) AS warehouse_id\n" + "FROM (\n" + "\tSELECT CAST(t1.warehouse_id AS varchar) AS warehouseId, t2.businessType, count(1) AS parcelledOrderNum\n" + "\tFROM sales_order t1\n" + "\t\tJOIN (\n" + "\t\t\tSELECT t2_1.warehouse_id, t2_1.external_batch_code\n" + "\t\t\t\t, CASE \n" + "\t\t\t\t\tWHEN t2_2.operation_type = 2\n" + "\t\t\t\t\t\tOR t2_2.operation_type IS NULL\n" + "\t\t\t\t\tTHEN '2'\n" + "\t\t\t\t\tWHEN t2_2.operation_type = 3 THEN '800'\n" + "\t\t\t\t\tWHEN t2_2.operation_type = 1 THEN '900'\n" + "\t\t\t\tEND AS businessType\n" + "\t\t\tFROM batch_order t2_1\n" + "\t\t\t\tJOIN wave_order t2_2\n" + "\t\t\t\tON t2_1.wave_id = t2_2.id\n" + "\t\t\t\t\tAND t2_1.warehouse_id = t2_2.warehouse_id\n" + "\t\t\t\t\tAND t2_2.wave_status IN (5, 6)\n" + "\t\t\t\t\tAND t2_1.ds >= 201901 - 1\n" + "\t\t\t\t\tAND t2_2.ds >= 201901 - 1\n" + "\t\t\t\t\tAND t2_1.gmt_create >= date_add(now(), INTERVAL -3 DAY)\n" + "\t\t\t\t\tAND t2_1.gmt_create <= now()\n" + "\t\t\t\t\tAND t2_2.gmt_create >= date_add(now(), INTERVAL -3 DAY)\n" + "\t\t\t\t\tAND t2_2.gmt_create <= now()\n" + "\t\t) t2\n" + "\t\tON t1.out_batch_code = t2.external_batch_code\n" + "\t\t\tAND t1.warehouse_id = t2.warehouse_id\n" + "\t\t\tAND t1.out_operation_mode = 3\n" + "\t\t\tAND t1.gmt_create >= CURDATE()\n" + "\t\t\tAND t1.gmt_create <= now()\n" + "\t\t\tAND t1.ds >= 201901\n" + "\t\t\tAND t2.businessType IS NOT NULL\n" + "\tGROUP BY t1.warehouse_id, t2.businessType\n" + ") a", insertStmt.toString());
}

Example 38 with MySqlInsertStatement

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement in project druid by alibaba.

the class MySqlInsertTest_6 method test_0.

public void test_0() throws Exception {
    String sql = // 
    "insert into document(the_key,the_namespace,Gmt_create,Gmt_modify,Expired_time,the_value) " + // 
    "values (?,?,now(),now(),date_add(now(),interval ? second),?) " + "on duplicate key update Gmt_modify = values(Gmt_modify),Expired_time = values(Expired_time),the_value = values(the_value)";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    MySqlInsertStatement insertStmt = (MySqlInsertStatement) stmt;
    Assert.assertEquals(6, insertStmt.getColumns().size());
    Assert.assertEquals(1, insertStmt.getValuesList().size());
    Assert.assertEquals(6, insertStmt.getValuesList().get(0).getValues().size());
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(// 
    "INSERT INTO document (the_key, the_namespace, Gmt_create, Gmt_modify, Expired_time" + // 
    "\n\t, the_value)" + // 
    "\nVALUES (?, ?, now(), now(), date_add(now(), INTERVAL ? SECOND)" + // 
    "\n\t, ?)" + "\nON DUPLICATE KEY UPDATE Gmt_modify = VALUES(Gmt_modify), Expired_time = VALUES(Expired_time), the_value = VALUES(the_value)", SQLUtils.toMySqlString(insertStmt));
    Assert.assertEquals(// 
    "insert into document (the_key, the_namespace, Gmt_create, Gmt_modify, Expired_time" + // 
    "\n\t, the_value)" + // 
    "\nvalues (?, ?, now(), now(), date_add(now(), interval ? second)" + // 
    "\n\t, ?)" + "\non duplicate key update Gmt_modify = VALUES(Gmt_modify), Expired_time = VALUES(Expired_time), the_value = VALUES(the_value)", SQLUtils.toMySqlString(insertStmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}

Example 39 with MySqlInsertStatement

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement in project druid by alibaba.

the class MySqlInsertTest_11 method test_insert_rollback_on_fail.

public void test_insert_rollback_on_fail() throws Exception {
    String sql = "insert ROLLBACK_ON_FAIL into t(a,b) values ('a1','b1')";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    parser.setParseCompleteValues(false);
    parser.setParseValuesSize(3);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    MySqlInsertStatement insertStmt = (MySqlInsertStatement) stmt;
    Assert.assertEquals(1, insertStmt.getValuesList().size());
    Assert.assertEquals(2, insertStmt.getValues().getValues().size());
    Assert.assertEquals(2, insertStmt.getColumns().size());
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    String formatSql = // 
    "INSERT ROLLBACK_ON_FAIL INTO t (a, b)" + "\nVALUES ('a1', 'b1')";
    Assert.assertEquals(formatSql, SQLUtils.toMySqlString(insertStmt));
}

Example 40 with MySqlInsertStatement

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement in project druid by alibaba.

the class MySqlInsertTest_10 method test_parseCompleteValues_true.

public void test_parseCompleteValues_true() throws Exception {
    String sql = "insert into t(a,b) values ('a1','b1'),('a2','b2'),('a3','b3'),('a4','b4');";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    parser.setParseCompleteValues(true);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    MySqlInsertStatement insertStmt = (MySqlInsertStatement) stmt;
    Assert.assertEquals(4, insertStmt.getValuesList().size());
    Assert.assertEquals(2, insertStmt.getValues().getValues().size());
    Assert.assertEquals(2, insertStmt.getColumns().size());
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    String formatSql = // 
    "INSERT INTO t (a, b)" + // 
    "\nVALUES ('a1', 'b1')," + // 
    "\n\t('a2', 'b2')," + // 
    "\n\t('a3', 'b3')," + "\n\t('a4', 'b4');";
    Assert.assertEquals(formatSql, SQLUtils.toMySqlString(insertStmt));
}