java - 修复 Java jdbc 到 DB2 的无效数据对话

Question

每当我使用以下 SQL 运行准备好的语句时:

SELECT SUM(TEST_UNITS) FROM TESTDB.TEST 
WHERE ((S_TS >= ? AND S_TS <= ?) or (E_TS >= ? AND E_TS <= ?) OR (S_TS < ? AND E_TS > ?))

我得到非常随机的数据以及以下错误消息:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10177][11615][3.61.75] Invalid data conversion: Requested conversion would result in a loss of precision of 2579055604. ERRORCODE=-4461, SQLSTATE=42815

• 在 DB2 数据库上 TEST_UNITS 是一个 BIGINT
• 和 S_TS 和 E_TS 均来自数据类型 TIMESTAMP

但是，自从我发出了一个准备好的声明后，我已经尝试过:

1. Calendar.getTime()
2. 使用简单的日期格式sdf.format(Calendar.getTime())
3. new java.sql.Timestamp(Calendar..getTimeInMillis())

如果没有转换错误，似乎一切都无法正常工作。也许甚至不是时间造成了问题。有人知道问题出在哪里吗？

编辑:

请求的代码片段:

public ChartDataSetMSU msuWOMachine(Connection con, Date date){
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    ChartDataSetMSU chartDataMsu = new ChartDataSetMSU();
    int[] msuSaver  = chartDataMsu.getHouresMsu();
    SimpleDateFormat sdf =  new SimpleDateFormat("yyyy-MM-dd-HH.mm.ss.SSSSSS");


    Calendar vonCal = Calendar.getInstance();
    Calendar bisCal = Calendar.getInstance();

    vonCal.setTime(date);
    vonCal.set(Calendar.HOUR_OF_DAY, 0);
    vonCal.set(Calendar.MINUTE, 0);
    vonCal.set(Calendar.SECOND, 0);
    vonCal.set(Calendar.MILLISECOND, 0);

    bisCal.setTime(date);
    bisCal.set(Calendar.HOUR_OF_DAY, 1);
    bisCal.set(Calendar.MINUTE, 0);
    bisCal.set(Calendar.SECOND, 0);
    bisCal.set(Calendar.MILLISECOND, 0);

    for(int i = 0; i < 24; i++){

    try {                                           
        prepStmt = con.prepareStatement("SELECT SUM(TEST_UNITS) FROM TESTDB.TEST WHERE ((S_TS >= ? AND S_TS <= ?) or (E_TS >= ? AND E_TS <= ?) OR (S_TS < ? AND E_TS > ?))"); 

        prepStmt.setString(1, sdf.format(vonCal.getTime()));
        prepStmt.setString(2, sdf.format(bisCal.getTime()));
        prepStmt.setString(3, sdf.format(vonCal.getTime()));
        prepStmt.setString(4, sdf.format(bisCal.getTime()));
        prepStmt.setString(5, sdf.format(vonCal.getTime()));
        prepStmt.setString(6, sdf.format(bisCal.getTime()));

        rs = prepStmt.executeQuery();

        if(rs.next())
        {
        msuSaver[i] = rs.getInt(1); 
        systemLog.debug("Ammount this hour:  " +  i  +"  --> " + msuSaver[i]);
        }


    } catch (SQLException e) {
        e.printStackTrace();
    }

    vonCal.add(Calendar.HOUR_OF_DAY, 1);
    bisCal.add(Calendar.HOUR_OF_DAY, 1);

    }
    chartDataMsu.setHouresMsu(msuSaver);
    return chartDataMsu;
}

DB2 中的 TIMESTAMP 显示这样的数据

2015-07-31-13.37.01.000000

Answer 1

因为 TEST_UNITS 是 DB2 中的 BigInt，所以您需要使用 JDBC API getLong() 来获取它。 getInt() 获取一个整数，因此这可能会导致数据转换问题和精度/数据丢失。

线路

msuSaver[i] = rs.getInt(1); 

需要

msuSaver[i] = rs.getLong(1); 

我没有查看整个代码是否需要更改 msuSaver 和相关格式。