UCanAccess执行Access增删改查询时提示权限或对象不存在问题

问题描述 #

使用Java通过UCanAccess连接Access数据库时，Select类型的预定义查询可正常执行，但增删改类的预定义查询（如qryInsNewFriend）执行时抛出异常：

UCAExc:::3.0.7 user lacks privilege or object not found: QRYINSNEWFRIEND

这些增删改查询在Access客户端中直接运行完全正常。

相关代码 #

1. Consts类（连接与查询常量定义） #

package entity;

import java.net.URLDecoder;

public final class Consts {
    private Consts() {
        throw new AssertionError();
    }

    protected static final String DB_FILEPATH = getDBPath();
    public static final String CONN_STR = "jdbc:ucanaccess://" + DB_FILEPATH + ";COLUMNORDER=DISPLAY";
    
    //----------------------------------------- PARTICIPANT LIST QUERIES -----------------------------------------/
    public static final String SQL_SEL_APPROVED_PARTICIPANT = "{ call qryFindApprovedFriends(?) }";
    public static final String SQL_SEL_WAITING_PARTICIPANT = "{ call qryFindWaitingFriends(?) }";
    public static final String SQL_UPD_TO_APPROVED_PARTICIPANT = "{ call qryUpdParticipantToApproved(?,?) }";
    public static final String SQL_UPD_TO_DENIED_PARTICIPANT = "{ call qryUpdParticipantToDenied(?,?) }";
    public static final String SQL_DEL_FRIEND = "{ call qryDelFriend(?,?) }";
    public static final String SQL_INS_NEW_FRIEND_REQUEST = "{ call qryInsNewFriend(?,?) }";
    

    /**
     * find the correct path of the DB file
     * @return the path of the DB file (from eclipse or with runnable file)
     */
    private static String getDBPath() {
        try {
            String path = Consts.class.getProtectionDomain().getCodeSource().getLocation().getPath();
            String decoded = URLDecoder.decode(path, "UTF-8");
            // System.out.println(decoded) - Can help to check the returned path
            if (decoded.contains(".jar")) {
                decoded = decoded.substring(0, decoded.lastIndexOf('/'));
                return decoded + "/database/HW1_Database_211923158_207975632.accdb";
            } else {
                decoded = decoded.substring(0, decoded.lastIndexOf("bin/"));
                System.out.println(decoded);
                return decoded + "//HW1_Database_211923158_207975632.accdb";
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

2. ParticipantLogic类（查询调用逻辑） #

package control;
import entity.Participant;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import entity.Consts;

public class ParticipantLogic {
    private static ParticipantLogic _instance;

    private ParticipantLogic() {
    }

    public static ParticipantLogic getInstance() {
        if (_instance == null)
            _instance = new ParticipantLogic();
        return _instance;
    }
    
    // select all approved participant
    public ArrayList<Participant> getApprovedParticipant(long firstPhone) {
        ArrayList<Participant> results = new ArrayList<Participant>();
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            try (Connection conn = DriverManager.getConnection(Consts.CONN_STR);
                    PreparedStatement stmt = conn.prepareStatement(Consts.SQL_SEL_APPROVED_PARTICIPANT))
                            {
                    stmt.setLong(1, firstPhone);
                    ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                    int i = 1;
                    results.add(new Participant(rs.getLong(i++), rs.getString(i++), rs.getString(i++)));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return results;
    }
    
    // select all waiting participant
    public ArrayList<Participant> getWaitingParticipant(Long phone) {
            ArrayList<Participant> results = new ArrayList<Participant>();
            try {
                Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
                try (Connection conn = DriverManager.getConnection(Consts.CONN_STR);
                        PreparedStatement stmt = conn.prepareStatement(Consts.SQL_SEL_WAITING_PARTICIPANT))
                                {                       
                        stmt.setLong(1, phone);
                        ResultSet rs = stmt.executeQuery(); 
                    while (rs.next()) {
                        int i = 1;
                        results.add(new Participant(rs.getLong(i++), rs.getString(i++), rs.getString(i++)));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            return results;
        }
        
        // delete friend 
        public boolean removeFriend(long firstPhone,long secondphone) {
                    try {
                        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
                        try (Connection conn = DriverManager.getConnection(Consts.CONN_STR);
                                CallableStatement stmt = conn.prepareCall(Consts.SQL_DEL_FRIEND)) {
                            
                            stmt.setLong(1, firstPhone);
                            stmt.setLong(2, secondphone);
                        
                            stmt.executeUpdate();
                            return true;
                            
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    return false;
                }
                
        // new friend request
        public boolean addFriend(long firstPhone,long secondphone) {
                    try {
                        System.out.println("test1");
                        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
                        try (Connection conn = DriverManager.getConnection(Consts.CONN_STR);
                                CallableStatement stmt = conn.prepareCall(Consts.SQL_INS_NEW_FRIEND_REQUEST)) {
                            System.out.println("test2");
                            int i = 1;
                            stmt.setLong(i++, firstPhone);
                            stmt.setLong(i++, secondphone);
                            stmt.executeUpdate();
                            System.out.println("test3");
                            return true;
                            
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    return false;
                }
                
        // accept friend 
        public boolean acceptFriend(long firstPhone,long secondphone ) {
                    try {
                        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
                        try (Connection conn = DriverManager.getConnection(Consts.CONN_STR);
                                CallableStatement stmt = conn.prepareCall(Consts.SQL_UPD_TO_APPROVED_PARTICIPANT)) {
                            
                            int i = 1;
                            stmt.setLong(i++, firstPhone);
                            stmt.setLong(i++, secondphone);
                            stmt.setString(i++, "APPROVED");
                            stmt.executeUpdate();
                            return true;
                            
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    return false;
                }
                
        // decline friend 
        public boolean declineFriend(long firstPhone,long secondphone ) {
                    try {
                        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
                        try (Connection conn = DriverManager.getConnection(Consts.CONN_STR);
                                CallableStatement stmt = conn.prepareCall(Consts. SQL_UPD_TO_DENIED_PARTICIPANT)) {
                            
                            int i = 1;
                            stmt.setLong(i++, firstPhone);
                            stmt.setLong(i++, secondphone);
                            stmt.setString(i++, "DENIED");
                            stmt.executeUpdate();
                            return true;
                            
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    return false;
                }   
}

3. Access预定义查询SQL #

qryInsNewFriend

INSERT INTO TblFriends ( ParticipantPhone, FriendPhone, Status )
SELECT [1] AS Expr1, [2] AS Expr2, "WAITING" AS Expr3;

qryDelFriend

DELETE TblFriends.ParticipantPhone, TblFriends.FriendPhone
FROM TblFriends
WHERE (((TblFriends.ParticipantPhone)=[1]) AND ((TblFriends.FriendPhone)=[2]));

已尝试的排查方法 #

• 检查Access数据库安全选项
• 修复数据库
• 创建新数据库测试
• 更换电脑测试
• 改用PreparedStatement替代CallableStatement

解决建议 #

1. 检查查询名称的大小写一致性 #

UCanAccess对Access对象名称的大小写敏感，但Access客户端本身不敏感。确认Java代码中调用的查询名称（如qryInsNewFriend）和Access里实际的查询名称完全一致，包括大小写。

2. 修改Access查询的参数写法 #

将增删改查询中的SELECT ...形式改为直接VALUES形式，UCanAccess对这种简单Action Query的支持更稳定：

-- 修改后的qryInsNewFriend
INSERT INTO TblFriends (ParticipantPhone, FriendPhone, Status)
VALUES ([1], [2], "WAITING");

3. 直接在Java中编写SQL语句替代预定义查询 #

绕过Access预定义查询，直接在Java代码中写增删改SQL，避免UCanAccess对预定义Action Query的支持问题：

// 示例：替代addFriend方法中的预定义查询
public boolean addFriend(long firstPhone, long secondphone) {
    try {
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        String sql = "INSERT INTO TblFriends (ParticipantPhone, FriendPhone, Status) VALUES (?, ?, 'WAITING')";
        try (Connection conn = DriverManager.getConnection(Consts.CONN_STR);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, firstPhone);
            stmt.setLong(2, secondphone);
            stmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    return false;
}

4. 升级UCanAccess版本 #

当前使用的UCanAccess 3.0.7版本较旧，可能存在兼容性问题。升级到最新稳定版，同时确保依赖的Jackcess等库版本匹配。

5. 验证数据库路径与写入权限 #

• 打印Consts.DB_FILEPATH的值，确认数据库路径正确，避免双斜杠等路径错误
• 确保程序运行时对数据库文件所在目录有写入权限，增删改操作需要修改数据库文件

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