运行相同查询时从SQL返回不同结果的Java
运行相同查询时从SQL返回不同结果的Java
提问于 2016-05-31 14:15:42
我有一个用Java编写的SQL语句(netbeans/uncanaccess)，它是一个相当简单的select语句，带有几个IIF和来自我数据库中一个表的SUM。
当我在Access中运行SQL语句时，它会返回正确的结果，但是当我试图在Java中运行它时，它会返回类似但不完全相同的结果。
Java代码
public int getActualMHDetails(String strNumber, String strYear, String strPeriod){
String strSQLString = null;
System.out.println("Getting cost details for: " + strNumber);
try{
strSQLString = "SELECT tblExportCost.ProjDef, tblExportCost.Year,\n"
+ "Sum(IIf([Per]=1,[Val/ObjCur],0)) AS Jan, Sum(IIf([Per]=2,[Val/ObjCur],0)) AS Feb, Sum(IIf([Per]=3,[Val/ObjCur],0)) AS Mar,\n"
+ "Sum(IIf([Per]=4,[Val/ObjCur],0)) AS Apr, Sum(IIf([Per]=5,[Val/ObjCur],0)) AS May, Sum(IIf([Per]=6,[Val/ObjCur],0)) AS Jun,\n"
+ "Sum(IIf([Per]=7,[Val/ObjCur],0)) AS Jul, Sum(IIf([Per]=8,[Val/ObjCur],0)) AS Aug, Sum(IIf([Per]=9,[Val/ObjCur],0)) AS Sep,\n"
+ "Sum(IIf([Per]=10,[Val/ObjCur],0)) AS Oct, Sum(IIf([Per]=11,[Val/ObjCur],0)) AS Nov, Sum(IIf([Per]=12,[Val/ObjCur],0)) AS Dec\n"
+ "FROM tblExportCost\n"
+ "GROUP BY tblExportCost.ProjDef, tblExportCost.Year, tblExportCost.Year\n"
+ "HAVING (((tblExportCost.Year)= '" + strYear + "') AND ((tblExportCost.ProjDef)= 'T3415" + strNumber + "'))";
//SETTING PREPARED STATEMENT
PreparedStatement preStatement = con.prepareStatement(strSQLString, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = preStatement.executeQuery();
rs.next();
//IF CHECKS TO MAKE SURE RECORDS
if(rs.getRow()==0){
rs.close();
preStatement.close();
return 3;
}
strTest = rs.getString("Jan");
System.out.println("Test Value: " + strTest);
intAMHCJan = rs.getInt("Jan");
intAMHCFeb = rs.getInt("Feb");
intAMHCMar = rs.getInt("Mar");
intAMHCApr = rs.getInt("Apr");
intAMHCMay = rs.getInt("May");
intAMHCJun = rs.getInt("Jun");
intAMHCJul = rs.getInt("Jul");
intAMHCAug = rs.getInt("Aug");
intAMHCSep = rs.getInt("Sep");
intAMHCOct = rs.getInt("Oct");
intAMHCNov = rs.getInt("Nov");
intAMHCDec = rs.getInt("Dec");
//CLOSES CONNECTIONS
System.out.println("Database query successful; closing connections");
rs.close();
preStatement.close();
return 1;
}catch(Exception ex){
ex.printStackTrace();
strEXMessage=ex.getMessage();
return 2;
}
} 访问码
SELECT tblExportCost.ProjDef, tblExportCost.Year,
Sum(IIf([Per]=1,[Val/ObjCur],0)) AS Jan, Sum(IIf([Per]=2,[Val/ObjCur],0)) AS Feb, Sum(IIf([Per]=3,[Val/ObjCur],0)) AS Mar,
Sum(IIf([Per]=4,[Val/ObjCur],0)) AS Apr, Sum(IIf([Per]=5,[Val/ObjCur],0)) AS May, Sum(IIf([Per]=6,[Val/ObjCur],0)) AS Jun,
Sum(IIf([Per]=7,[Val/ObjCur],0)) AS Jul, Sum(IIf([Per]=8,[Val/ObjCur],0)) AS Aug, Sum(IIf([Per]=9,[Val/ObjCur],0)) AS Sep,
Sum(IIf([Per]=10,[Val/ObjCur],0)) AS Oct, Sum(IIf([Per]=11,[Val/ObjCur],0)) AS Nov, Sum(IIf([Per]=12,[Val/ObjCur],0)) AS Dec
FROM tblExportCost
GROUP BY tblExportCost.ProjDef, tblExportCost.Year, tblExportCost.Year
HAVING (((tblExportCost.Year)= 2016) AND ((tblExportCost.ProjDef)= 'T34151234'))我甚至尝试保存Access查询，只需使用
strSQLString = "SELECT * FROM qryTestJava";但这也会返回相同的不正确结果。
结果
SQL
ProjDef Year Jan Feb Mar Apr May
T34151234 2016 22358.1 18742.9 3443.33000000001 10251.03 12706.78 Java
ProjDef Year Jan Feb Mar Apr May
T34151234 2016 22,329 18,714 3,420 10,226 12,684我做了一些调查，发现了与舍入和ucanaccess Here类似的问题，但据报道，它是在早期版本中修复的。
我当前版本的ucanaccess是2.0.9.3
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-06-01 23:44:29
我在UCanAccess 3.0.5下复制了你的问题。当调用时，IIf()函数似乎将双值截断为其整数值
SELECT IIf([Per]=1,[Val/ObjCur],0) AS ...当以IIf()作为数字文字值调用0.0时，将正确返回整个双值，即，
SELECT IIf([Per]=1,[Val/ObjCur],0.0) AS ...因此，您应该能够通过以下方法检索正确的和
strSQLString = "SELECT tblExportCost.ProjDef, tblExportCost.Year,\n"
+ "Sum(IIf([Per]=1,[Val/ObjCur],0.0)) AS Jan, Sum(IIf([Per]=2,[Val/ObjCur],0.0)) AS Feb, Sum(IIf([Per]=3,[Val/ObjCur],0.0)) AS Mar,\n"
+ "Sum(IIf([Per]=4,[Val/ObjCur],0.0)) AS Apr, Sum(IIf([Per]=5,[Val/ObjCur],0.0)) AS May, Sum(IIf([Per]=6,[Val/ObjCur],0.0)) AS Jun,\n"
+ "Sum(IIf([Per]=7,[Val/ObjCur],0.0)) AS Jul, Sum(IIf([Per]=8,[Val/ObjCur],0.0)) AS Aug, Sum(IIf([Per]=9,[Val/ObjCur],0.0)) AS Sep,\n"
+ "Sum(IIf([Per]=10,[Val/ObjCur],0.0)) AS Oct, Sum(IIf([Per]=11,[Val/ObjCur],0.0)) AS Nov, Sum(IIf([Per]=12,[Val/ObjCur],0.0)) AS Dec\n"
+ "FROM tblExportCost\n"
+ "GROUP BY tblExportCost.ProjDef, tblExportCost.Year\n"
+ "HAVING (((tblExportCost.Year)= '" + strYear + "') AND ((tblExportCost.ProjDef)= 'T3415" + strNumber + "'))";页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37548210
复制相关文章
相似问题
腾讯云开发者
