JDBC呼叫SQLServerBulkCopy批量插入中文問題

目前利用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();
}

uj5u.com熱心網友回復：

uj5u.com熱心網友回復：

uj5u.com熱心網友回復：

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/31229.html

標籤：應用實例

上一篇：請教大蝦們一個問題

下一篇：用copy/b命令進行ts檔案合并順序不對