java - 从 UNKNOWN 到 UNKNOWN 的转换是不支持的异常(在用于迁移的 Java 程序中)

Question

我有一个程序可以将数据从oracle数据库迁移到具有相同结构的SQL Server数据库。 private void migrateAllDataToMssql() 抛出异常 {

    Connection connOracle = DBConnection.getConnection();
    Connection connMssql = DBConnection.getConnectionToMsSql();

    Statement selectAll = connOracle.createStatement();

    ResultSet tableResultSet = selectAll.executeQuery("select table_name from dba_tables where owner= 'USER1'");

            //insert to mssql db
     while(tableResultSet.next()) {
         String tableName = tableResultSet.getString("table_name");
         if(tableName.equals("DATABASECHANGELOG_CORE") || tableName.equals("GLOBAL_PARAM") || 
             tableName.equals("LOG") || tableName.equals("ORDERS") ||  tableName.equals("ORDER_BOOK")) {             
             System.out.println("do nothing");
         }
         else {
             System.out.println(tableName);
             try (PreparedStatement s1 = connOracle.prepareStatement("select * from " + tableName);
                     ResultSet rs = s1.executeQuery()) {
                    ResultSetMetaData meta = rs.getMetaData();

                    List<String> columns = new ArrayList<>();
                    for (int i = 1; i <= meta.getColumnCount(); i++)
                        columns.add(meta.getColumnName(i));

                     String identityInsertOn = "";
                     String sql = "INSERT INTO " + tableName + " ("
                              + columns.stream().collect(Collectors.joining(", "))
                              + ") VALUES ("
                              + columns.stream().map(c -> "?").collect(Collectors.joining(", "))
                              + ")";
                     String identityInsertOff = "";

                     if(tableName.equals("COMMODITY") || tableName.equals("DELIVERY_CODE") ||
                     tableName.equals("DELIVERY_CODE_ENUM") || tableName.equals("INS_CLASS") || 
                         tableName.equals("ORDER_TYPE")  || tableName.equals("PRIVS_TO_ROLES") ||
                     tableName.equals("PROD_DEF_GROUP") || tableName.equals("RESULTS") || 
                         tableName.equals("ROLES")) {
                         identityInsertOn = "SET IDENTITY_INSERT " + tableName + " ON ";
                         identityInsertOff = " SET IDENTITY_INSERT " + tableName + " OFF ";
                     }
                    System.out.println(sql);

                    try (PreparedStatement s2 = connMssql.prepareStatement(
                           identityInsertOn +
                           sql +
                           identityInsertOff
                    )) {

                        while (rs.next()) {
                            for (int i = 1; i <= meta.getColumnCount(); i++) {
                                System.out.println(meta.getColumnType(i) + " " + i); //log which data type the column has

                                    s2.setObject(i, rs.getObject(i));

                            }


                            s2.addBatch();
                        }

                        s2.executeBatch();
                    }
                }
     }}

    }

它实际上工作正常，但在某些表中会出现此 SQLServerException:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:1094) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:1595) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:1493) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:1502) at test.DatabaseDataMigrator2.migrateAllDataToMssql(DatabaseDataMigrator2.java:179) at test.DatabaseDataMigrator2.main(DatabaseDataMigrator2.java:36)

我检查了这些表具有哪些数据类型，它们的共同点是每个表中都有一个 TIMESTAMP 数据类型，但在其他一些也出现 TIMESTAMP 的表中，没有出现异常。出现此异常的原因可能是什么？

Answer 1

我只需检查它是否是时间戳列，如果是，则将 setObject 替换为 setDate

while (rs.next()) {
    for (int i = 1; i <= meta.getColumnCount(); i++) {
         System.out.println("Column type: " + meta.getColumnType(i) + " Iteration: " + i); //log which data type the column has
         if(meta.getColumnType(i) == 93) {
              s2.setDate(i, rs.getDate(i));
        }else {
              s2.setObject(i, rs.getObject(i));
                                }                                   
                            }

根据documentation columnType==93 表示时间戳，因此这是 if 子句中检查的内容。完成此操作后，插入和设置工作正常。