【YashanDB 知识库】共享超过 32000 字节字符串插入 CLOB 类型方案

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);
        }
    }
}