JDBC调用SQLServerBulkCopy批量插入中文问题

竹林依旧宁静 2020-05-19 09:57:56

目前利用JDBC调用SQLServerBulkCopy实现批量数据插入SQL Server2012，目标表字段类型为VARCHAR(50)时，字段长度够长，写入中文时报错，驱动包版本为mssql-jdbc-6.2.2.jre8.jar，请大家帮忙解决一下，报错信息为：com.microsoft.sqlserver.jdbc.SQLServerException: 从 bcp 客户端收到一个对 colid 11 无效的列长度。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:256)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:108)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:28)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.doInsertBulk(SQLServerBulkCopy.java:1611)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.access$200(SQLServerBulkCopy.java:58)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy$1InsertBulk.doExecute(SQLServerBulkCopy.java:709)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.sendBulkLoadBCP(SQLServerBulkCopy.java:739)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1684)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeResultSet(SQLServerBulkCopy.java:645)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:594)
at com.miextreme.test.sqlserver.task.WriteTask.insertBatch(WriteTask.java:284)
at com.miextreme.test.sqlserver.task.WriteTask.run(WriteTask.java:154)

java代码如下：
private CachedRowSetImpl getCachedRowSet() throws SQLException {
Connection connection = instance.getConnection();
PreparedStatement prepareStatement = connection.prepareStatement("SELECT * FROM [cfa_personbase_info] WHERE 1 = 0");
ResultSet targetTableResultSet = prepareStatement.executeQuery();

CachedRowSetImpl cachedRowSet = new CachedRowSetImpl();
cachedRowSet.populate(targetTableResultSet);

if (prepareStatement != null) {
prepareStatement.close();
prepareStatement = null;
}
if (connection != null) {
connection.close();
connection = null;
}

return cachedRowSet;
}

private void insertIntoCachedRowSet(CachedRowSetImpl cachedRowSet, Map<String, Object> rowData) throws SQLException {
// 移动指针到"插入行"
cachedRowSet.moveToInsertRow();

// 插入行
Set<String> columnSet = rowData.keySet();
Iterator<String> columnIterator = columnSet.iterator();
while (columnIterator.hasNext()) {
String columnName = columnIterator.next();
Object columnValue = rowData.get(columnName);
if (columnValue == null) {
cachedRowSet.updateNull(columnName);
} else {
cachedRowSet.updateObject(columnName, columnValue);
}
}
cachedRowSet.insertRow();

// 移动指针到当前行
cachedRowSet.moveToCurrentRow();
}

private void insertBatch(CachedRowSetImpl cachedRowSet, int batchSize) throws SQLException {
// 目标源URL
StringBuffer connectionUrl = new StringBuffer("jdbc:sqlserver://127.0.0.1:1433;databaseName=tar_test;user=sa;password=sa");

// BulkCopyOptions
SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
copyOptions.setKeepIdentity(true);
copyOptions.setBatchSize(batchSize);
copyOptions.setUseInternalTransaction(true);
copyOptions.setBulkCopyTimeout(3600);

// BulkCopy
SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connectionUrl.toString());
bulkCopy.setBulkCopyOptions(copyOptions);
bulkCopy.setDestinationTableName("[cfa_personbase_info]");
bulkCopy.addColumnMapping("personid", "personid");
bulkCopy.addColumnMapping("gonghao", "gonghao");
bulkCopy.addColumnMapping("name", "name");
bulkCopy.addColumnMapping("cardtype", "cardtype");
bulkCopy.addColumnMapping("cardid", "cardid");
bulkCopy.addColumnMapping("birthday", "birthday");
bulkCopy.addColumnMapping("sex", "sex");
bulkCopy.addColumnMapping("getjobtime", "getjobtime");
bulkCopy.addColumnMapping("workingyears", "workingyears");
bulkCopy.addColumnMapping("nationlity", "nationlity");
bulkCopy.addColumnMapping("birthplace", "birthplace");
bulkCopy.addColumnMapping("nation", "nation");
bulkCopy.addColumnMapping("polity", "polity");
bulkCopy.addColumnMapping("foreignlang", "foreignlang");
bulkCopy.addColumnMapping("highestdegree", "highestdegree");
bulkCopy.addColumnMapping("highstudy", "highstudy");
bulkCopy.addColumnMapping("technicrank", "technicrank");
bulkCopy.addColumnMapping("address", "address");
bulkCopy.addColumnMapping("postcode", "postcode");
bulkCopy.addColumnMapping("telephone", "telephone");
bulkCopy.addColumnMapping("cellphone", "cellphone");
bulkCopy.addColumnMapping("email", "email");
bulkCopy.addColumnMapping("linkman", "linkman");
bulkCopy.addColumnMapping("linkmanphone", "linkmanphone");
bulkCopy.addColumnMapping("addressbirth", "addressbirth");
bulkCopy.addColumnMapping("familyinfo", "familyinfo");
bulkCopy.addColumnMapping("updatedate", "updatedate");
bulkCopy.addColumnMapping("updatetime", "updatetime");
bulkCopy.addColumnMapping("modifyid", "modifyid");
bulkCopy.addColumnMapping("modifytype", "modifytype");
bulkCopy.addColumnMapping("modifystate", "modifystate");
bulkCopy.addColumnMapping("modifyuser", "modifyuser");
bulkCopy.writeToServer(cachedRowSet);
cachedRowSet.close();
bulkCopy.close();
}

...全文