MySqlDumpInsertStatementRewriter { public static QueryWithParameters rewriteSimpleInsertStatement(String query) { if (!query.startsWith("INSERT INTO ")) { return null; } int startValues = query.indexOf("VALUES "); if (startValues <= 0) { return null; } String tableName = query.substring(12, startValues).trim(); List<Object> jdbcParameters = new ArrayList<>(); int pos = startValues + "VALUES ".length(); StringBuilder rewritten = new StringBuilder(query.substring(0, pos)); StringBuilder currentValue = new StringBuilder(); int end = query.length(); int state = STATE_START_RECORD; while (pos < end) { char c = query.charAt(pos++); switch (state) { case STATE_START_RECORD: { if (c == '(') { rewritten.append(c); state = STATE_IN_VALUE; } else if (c == ',') { rewritten.append(c); state = STATE_START_RECORD; } else { return null; } break; } case STATE_IN_VALUE: { if (c == '\'') { state = STATE_IN_STRING_VALUE; currentValue.setLength(0); } else if (Character.isDigit(c)) { state = STATE_IN_NUMERIC_VALUE; currentValue.setLength(0); currentValue.append(c); } else if (c == 'N' || c == 'n') { state = STATE_IN_NULL_VALUE; currentValue.setLength(0); } else { return null; } break; } case STATE_IN_NULL_VALUE: { if (c == 'U' || c == 'u') { state = STATE_IN_NULL_VALUE_1; } else { return null; } break; } case STATE_IN_NULL_VALUE_1: { if (c == 'L' || c == 'l') { state = STATE_IN_NULL_VALUE_2; } else { return null; } break; } case STATE_IN_NULL_VALUE_2: { if (c == 'L' || c == 'l') { state = STATE_IN_NULL_VALUE_3; } else { return null; } break; } case STATE_IN_NULL_VALUE_3: { if (c == ',') { jdbcParameters.add(null); rewritten.append("?,"); currentValue.setLength(0); state = STATE_IN_VALUE; break; } else if (c == ')') { jdbcParameters.add(null); rewritten.append("?)"); currentValue.setLength(0); state = STATE_START_RECORD; break; } else { currentValue.append(c); } break; } case STATE_IN_STRING_VALUE: { if (c == '\'') { if (query.charAt(pos) == '\'') { currentValue.append('\''); pos++; } else { jdbcParameters.add(currentValue.toString()); rewritten.append('?'); currentValue.setLength(0); state = STATE_END_VALUE; } break; } else { currentValue.append(c); } break; } case STATE_IN_NUMERIC_VALUE: { if (c == ',') { Number value; try { value = Long.parseLong(currentValue.toString()); } catch (NumberFormatException bigNumber) { try { value = Double.parseDouble(currentValue.toString()); } catch (NumberFormatException noAgain) { return null; } } jdbcParameters.add(value); rewritten.append("?,"); currentValue.setLength(0); state = STATE_IN_VALUE; break; } else if (c == ')') { Number value; try { value = Long.parseLong(currentValue.toString()); } catch (NumberFormatException bigNumber) { try { value = Double.parseDouble(currentValue.toString()); } catch (NumberFormatException noAgain) { return null; } } jdbcParameters.add(value); rewritten.append("?)"); currentValue.setLength(0); state = STATE_START_RECORD; break; } else { currentValue.append(c); } break; } case STATE_END_VALUE: if (c == ',') { rewritten.append(','); state = STATE_IN_VALUE; } else if (c == ')') { rewritten.append(')'); state = STATE_START_RECORD; } else { return null; } break; default: throw new IllegalStateException(state + " at pos:" + pos); } } if (state != STATE_START_RECORD) { return null; } return new QueryWithParameters(rewritten.toString(), tableName, jdbcParameters, null); }  static QueryWithParameters rewriteSimpleInsertStatement(String query);  }
@Test public void testRewriteSimpleInsertStatement() throws Exception { check("INSERT INTO table VALUES ('a',1)", "INSERT INTO table VALUES (?,?)", Arrays.asList("a", Long.valueOf(1))); check("INSERT INTO table VALUES ('a')", "INSERT INTO table VALUES (?)", Arrays.asList("a")); check("INSERT INTO table VALUES ('a','b')", "INSERT INTO table VALUES (?,?)", Arrays.asList("a", "b")); check("INSERT INTO table VALUES (1,2)", "INSERT INTO table VALUES (?,?)", Arrays.asList(Long.valueOf(1), Long.valueOf(2))); check("INSERT INTO table VALUES (1,2,null)", "INSERT INTO table VALUES (?,?,?)", Arrays.asList(Long.valueOf(1), Long.valueOf(2), null)); check("INSERT INTO table VALUES (null,2,1)", "INSERT INTO table VALUES (?,?,?)", Arrays.asList(null, Long.valueOf(2), Long.valueOf(1))); check("INSERT INTO table VALUES (null)", "INSERT INTO table VALUES (?)", Collections.singletonList(null)); check("INSERT INTO table VALUES (a,2)", null, null); check("INSERT INTO table VALUES ('a',1),('b','c')", "INSERT INTO table VALUES (?,?),(?,?)", Arrays.asList("a", Long.valueOf(1), "b", "c")); check("INSERT INTO table VALUES ('a',null),('b','c')", "INSERT INTO table VALUES (?,?),(?,?)", Arrays.asList("a", null, "b", "c")); }