public static void insertSql(DataSource dataSource, String sql) {

log.info("insertSql sql = {}", sql);

// 获取数据库连接

Connection connection = getConnection(dataSource);

PreparedStatement ps = null;

// 通过 sql 获取 SQLStatement

SQLStatement stmt = InsertSqlParserUtil.getInsertStmt(sql);

// 通过 SQLStatement 动态获取 insert into xxx values (?,?,?......) 插入语句

String insertSQL = InsertSqlParserUtil.getInsertSQL(stmt);

try {

// 通过 insertSQL 获取 PreparedStatement 对象

ps = connection.prepareStatement(insertSQL);

// 动态绑定插入变量

InsertSqlParserUtil.bindPreparedStatement(stmt, ps);

// 执行插入

ps.execute();

} catch (SQLException e) {

log.error ("insert 异常", e);

} finally {

closePreparedStatement(ps);

closeConnection(connection);

}

}

public static SQLStatement getInsertStmt(String sql) {

SQLStatement stmt = null;

DbType druidDbType = DbType.oracle;

String formatSQL = SQLUtils.format(sql, druidDbType);

List<SQLStatement> stmtList = new ArrayList<>();

try {

stmtList = SQLUtils.parseStatements(formatSQL, druidDbType);

} catch (Exception exp) {

log.error("parseStatements sql is :{}", formatSQL);

log.error("parseStatements exception : {}", exp.getMessage());

}

log.info("getInsertValues param stmtList size is {}", stmtList.size());

stmt = stmtList.get(0);

SchemaStatVisitor statVisitor = SQLUtils.createSchemaStatVisitor(druidDbType);

stmt.accept(statVisitor);

log.info(SQLUtils.toSQLString(stmt));

if (stmt instanceof OracleInsertStatement) {

return stmt;

}

return stmt;

}

public static String getInsertSQL(SQLStatement stmt) {

StringBuilder sb = new StringBuilder();

String schemaName = null;

String tableName = null;

if (stmt instanceof OracleInsertStatement) {

schemaName = ((OracleInsertStatement) stmt).getTableSource().getSchema();

tableName = ((OracleInsertStatement) stmt).getTableSource().getTableName();

if (StringUtils.isEmpty(tableName)) {

return sb.toString();

} else if (!StringUtils.isEmpty(schemaName)) {

tableName = schemaName + "." + tableName;

}

if (!StringUtils.isEmpty(tableName)) {

sb.append("INSERT INTO ").append(tableName.toUpperCase()).append(" VALUES(");

}

List<SQLExpr> values = ((OracleInsertStatement) stmt).getValuesList().get(0).getValues();

for (int i = 0; i < values.size(); i++) {

if (i == values.size() - 1) {

sb.append("?");

} else {

sb.append("?,");

}

}

sb.append(")");

}

return sb.toString();

}

public static void bindPreparedStatement(SQLStatement stmt, PreparedStatement pst) {

List<SQLExpr> values = ((OracleInsertStatement) stmt).getValuesList().get(0).getValues();

for (int i = 0; i < values.size(); i++) {

SQLExpr value = values.get(i);

try {

if (value instanceof SQLIntegerExpr) {

pst.setInt(i + 1, ((SQLIntegerExpr) value).getNumber().intValue());

} else if (value instanceof SQLBigIntExpr) {

pst.setLong(i + 1, ((SQLBigIntExpr) value).getNumber().intValue());

} else if (value instanceof SQLNumberExpr) {

Number number = ((SQLNumberExpr) value).getNumber();

if (number instanceof BigDecimal) {

pst.setBigDecimal(i + 1, (BigDecimal) number);

}

} else if (value instanceof SQLCharExpr) {

pst.setString(i + 1, ((SQLCharExpr) value).getText());

} else if (value instanceof SQLTimestampExpr) {

pst.setTimestamp(i + 1, Timestamp.valueOf(((SQLTimestampExpr) value).getValue()));

} else if (value instanceof SQLDateExpr) {

pst.setDate(i + 1, Date.valueOf(((SQLDateExpr) value).getValue()));

} else if (value instanceof SQLDateTimeExpr) {

pst.setDate(i + 1, Date.valueOf(((SQLDateTimeExpr) value).getValue()));

} else if (value instanceof SQLNullExpr) {

pst.setNull(i + 1, Types.NULL);

} else {

pst.setString(i + 1, ((SQLCharExpr) value).getText());

}

} catch (SQLException e) {

log.error ("绑定插入变量异常", e);

}

}

}