Java 在运行相同的查询时从 SQL 返回不同的结果

Question

我有一个用 Java (netbeans/uncanaccess) 编写的 SQL 语句，它是一个相当简单的 select 语句，带有来 self 数据库中一个表的一些 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;
    }
} 

Access 代码

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

我做了一些挖掘，发现舍入和 ucanacess 也有类似的问题 Here , 但据报道它已在早期版本中修复。

我目前的ucanaccess版本是2.0.9.3

Answer 1

我能够在 UCanAccess 3.0.5 下重现您的问题。 IIf() 函数在调用时似乎将 double 值截断为其整数值

SELECT IIf([Per]=1,[Val/ObjCur],0) AS ...

当 IIf() 以 0.0 作为数字字面值调用时，整个 double 值被正确返回，即，

SELECT IIf([Per]=1,[Val/ObjCur],0.0) AS ...

因此您应该能够通过使用检索正确的 SUM

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 + "'))";