修复无效的数据对话 Java jdbc 到 DB2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31725737/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fix Invalid data conversation Java jdbc to DB2
提问by LoremIpsum
Whenever I run my prepared statement with the following SQL:
每当我使用以下 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 > ?))
I get very random data together with the following error message:
我得到了非常随机的数据以及以下错误消息：
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
com.ibm.db2.jcc.am.SqlSyntaxErrorException：[jcc][10177][11615][3.61.75] 无效的数据转换：请求的转换将导致精度丢失 2579055604。ERRORCODE=-4461，SQLSTATE=42815
- On the DB2 db
TEST_UNITSis aBIGINT - and
S_TSandE_TSare both from the datatypeTIMESTAMP
- 在 DB2 db 上
TEST_UNITS是一个BIGINT - 和
S_TS和E_TS均从数据类型TIMESTAMP
However since I fire a prepared statement I already tried with:
但是，由于我触发了一个准备好的声明，我已经尝试过：
Calendar.getTime()- With a simple Date Format
sdf.format(Calendar.getTime()) new java.sql.Timestamp(Calendar..getTimeInMillis())
Calendar.getTime()- 使用简单的日期格式
sdf.format(Calendar.getTime()) new java.sql.Timestamp(Calendar..getTimeInMillis())
Nothing seems to work correctly without conversion errors. Probably it is not even the time which is causing the problem. Does anybody have an idea what the problem could be?
如果没有转换错误，似乎什么都不能正常工作。可能甚至不是导致问题的时间。有谁知道问题可能是什么？
Edit:
编辑：
The Requested code snippet:
请求的代码片段：
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;
}
And the TIMESTAMP in DB2 is showing data like this
DB2 中的 TIMESTAMP 显示的数据是这样的
2015-07-31-13.37.01.000000
2015-07-31-13.37.01.000000
回答by data_henrik
Because TEST_UNITS is a BigInt in DB2, you need to use the JDBC API getLong() to fetch it. getInt() fetches an Integer and hence this would result in a possible data conversion issue and loss of precision/data.
因为 TEST_UNITS 是 DB2 中的 BigInt，所以您需要使用 JDBC API getLong() 来获取它。getInt() 获取一个整数，因此这将导致可能的数据转换问题和精度/数据丢失。
The line
线
msuSaver[i] = rs.getInt(1);
would need to be
需要
msuSaver[i] = rs.getLong(1);
I didn't look over the entire code whether you would need to change msuSaver and related formatting.
我没有查看整个代码是否需要更改 msuSaver 和相关格式。
