使用UCanAccess操作Access时计算字段返回截断值问题排查

问题场景 #

Access数据库表Tab包含以下字段：

• d1：double类型
• d2：double类型
• d：计算字段，表达式为：

  IIf((IIf([d1] Is Null,0,[d1])=-999),Null,IIf([d2] Is Not Null,Round(([d1]+[d2])/2,1),[d1]))
  

  用于计算d1和d2的平均值并保留1位小数。

在Access客户端手动插入/更新数据（如d1=2.2、d2=2.4）时，计算字段d能正常返回2.3；但通过UCanAccess执行插入或更新操作时，d返回截断值2，而非预期的2.3。

相关操作代码：

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
con = DriverManager.getConnection("jdbc:ucanaccess://" + database+ ";singleconnection=true", "", "");
        
try(Statement stmt = con.createStatement();){
    stmt.execute("INSERT INTO Tab (d1, d2) VALUES (2.2, 2.4)");
    //stmt.execute("UPDATE Tab SET d2 = 2.6;");
}

原因分析 #

1. UCanAccess与Access原生引擎的Round函数处理差异：Access客户端的Round函数会严格按指定小数位进行四舍五入，但UCanAccess对该函数的解析存在偏差，可能将计算结果默认转换为整数类型，导致截断小数部分。
2. 数值类型转换逻辑不一致：UCanAccess在执行计算字段表达式时，对(d1+d2)/2的结果类型处理与Access原生引擎不同，未保留浮点精度，后续的Round操作无法生效。

解决办法 #

方法1：使用参数化查询避免精度丢失 #

替换直接执行字符串SQL的方式，改用PreparedStatement传递参数，确保数值的浮点精度被正确保留：

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
con = DriverManager.getConnection("jdbc:ucanaccess://" + database+ ";singleconnection=true", "", "");
        
try(PreparedStatement pstmt = con.prepareStatement("INSERT INTO Tab (d1, d2) VALUES (?, ?)");){
    pstmt.setDouble(1, 2.2);
    pstmt.setDouble(2, 2.4);
    pstmt.executeUpdate();
}

方法2：修改计算字段表达式，显式强制类型转换 #

在计算字段表达式中，给中间结果强制转换为double类型，确保浮点精度不丢失：

IIf((IIf([d1] Is Null,0,[d1])=-999),Null,IIf([d2] Is Not Null,Round(CDbl(([d1]+[d2])/2),1),[d1]))

通过CDbl()函数强制将(d1+d2)/2的结果转换为double类型，再执行Round操作，保证小数位处理符合预期。

方法3：插入/更新后强制刷新计算字段 #

执行SQL语句重新设置计算字段的表达式，触发UCanAccess重新计算字段值：

ALTER TABLE Tab ALTER COLUMN d DROP EXPRESSION;
ALTER TABLE Tab ALTER COLUMN d ADD EXPRESSION IIf((IIf([d1] Is Null,0,[d1])=-999),Null,IIf([d2] Is Not Null,Round(([d1]+[d2])/2,1),[d1]))

内容的提问来源于stack exchange，提问作者Sophia