共享超过32000字节字符串插入CLOB类型方案
概述
超过32000字节字符串通过insert into values的方式插入崖山数据库CLOB类型字段（无论是DBeaver还是yasql），会报YAS-04107 string exceeding limit 32000错误。本文通过jdbc动态变量绑定的方案支持超过32000字节字符串插入到CLOB类型字段的表。
方案
通过jdbc变量绑定的方式可以支持超过32000字节字符串插入到CLOB类型字段的表，但是不通用，不同的表需要编写不同的插入代码，本方案使用yashandb jdbc和druid组件，动态解析插入sql的字段和字段类型，进行动态变量绑定，可以支持不同的表进行超过32000字节字符串插入到CLOB类型字段的表。
关键代码
pom主要依赖
<dependency>
<groupId>com.yashandb</groupId>
<artifactId>yashandb-jdbc</artifactId>
<version>1.6.1</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.20</version>
</dependency>
主要代码
|
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); } } } |
程序使用说明
注：需要安装jdk1.8
1、解压yashandb-sql-imp-1.0-bin.zip，然后设置yashandb-sql-imp/conf/jdbc.properties中需要导入的崖山用户连接
2、导入命令:
win:
cd yashandb-sql-imp/bin
.\yashandb-sql-imp.bat D:\clob_test
linux:
cd yashandb-sql-imp/bin
./yashandb-sql-imp.sh /data/clob_test
注：参数/data/clob_test为sql文件存放目录，支持多sql文件，但只支持执行insert into xxx values这样的插入语句
附件
