Tomcat中HSQLDB外部应用连接失败，提示权限或表不存在

Tomcat部署的Web服务启动时会初始化HSQLDB，内部所有数据库操作（连接、存储过程、建表、查询等）均正常，但独立外部Java应用连接同一HSQLDB查询client_sign_on表时，报错user lacks privilege or object not found: client_sign_on。已确认表存在且SQL工具可正常访问，但外部应用始终失败。

Web服务应用代码 #

HSQLDB初始化代码（HSQLServer.java） #

private HsqlServer() {
    //initialze new server
    hsqlServer = new Server();

    hsqlServer.setLogWriter(null);
    hsqlServer.setSilent(true);

    // can use custom writer
    hsqlServer.setLogWriter(null); 
    hsqlServer.setErrWriter(null); 
}

public void startServer(String dbName, String dbPath) {  
    try {
        HsqlProperties p = new HsqlProperties();

        //dbName: "hsqldb" dbPath: "file:/usr/share/tomcat/webapps/TicketingAPIWS/DB/hsqldb"
        p.setProperty("server.database.0", "file:" + dbPath);
        p.setProperty("server.dbname.0", dbName);

        //set properties for DB
        hsqlServer.setProperties(p);

        //start the database!
        hsqlServer.start();

        LogUtils.log(CustomLogger.LogType.DEBUG, Boolean.TRUE, String.format("startServer:HSQL Database Server Has been Started"));
    } catch(Exception ex) {
        LogUtils.logException(CustomLogger.LogType.SEVERE, Boolean.TRUE, "startServer:Exception due to", ex);
    }
}

Web服务监听器初始化代码（运行正常） #

public static void initDB(ServletContext sc) {
    Connection conn = null;
    
    ConfigMgr configMgr = new ConfigMgr(sc);
    LogUtils.setupConfigurationMgr(configMgr);

    try {           
        boolean serverStatus = startHsqlServer(getDBName(configMgr), getDBPath(configMgr));           
        LogUtils.log(CustomLogger.LogType.DEBUG, Boolean.TRUE, String.format("initDB: Hsql Server Status=[%s]",serverStatus));
        
        //check if Hsql server is running
        if(serverStatus) {
            //get connection from the connection pool
            conn = getDefaultDatabaseConnection(configMgr);

            //add today's date as newDay in system_config
            addToSysConfig(conn, "newDay", new SimpleDateFormat("yyyy-MM-dd").format(Calendar.getInstance().getTime()));
        }

            LogUtils.log(CustomLogger.LogType.DEBUG, Boolean.TRUE, "initDB: Successfully initialized the database");
        } else {
            LogUtils.log(CustomLogger.LogType.SEVERE, Boolean.TRUE, "initDB: Failed to initialize the database");
        }
    } catch(Exception ex){
        LogUtils.logException(CustomLogger.LogType.SEVERE, Boolean.TRUE, "initDB: Exception due to", ex);
    } finally {
        closeConnections(conn);
    } 
}

Web服务连接字符串：url="jdbc:hsqldb:hsql://localhost/hsqldb;file:/usr/share/tomcat/webapps/TicketingAPIWS/DB/hsqldb"

外部Java应用代码 #

public static void main(String[] args) {
    Connection conn1 = null;
    // Display versions
    //clntObj.displayAppVersion(args);
        
    try {
        
        Class.forName("org.hsqldb.jdbcDriver");    
        //jdbc:hsqldb:hsql://localhost/pgmcdb
        //conn1 = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/hsqldb;file:/usr/share/tomcat/webapps/TicketingAPIWS/DB/hsqldb;server.remote_open=true;", "SA", "");
        conn1 = DriverManager.getConnection("jdbc:hsqldb:file:/usr/share/tomcat/webapps/TicketingAPIWS/DB/hsqldb;server.remote_open=true;", "sa", "");

        System.out.println(conn1);
        Statement s = conn1.createStatement();
        ResultSet rs = s.executeQuery ("SELECT web_client_id FROM client_sign_on;");
        
        while(rs.next()) {
            System.out.println("Table_Name: " + rs.getString(1) );
        }
        
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(HsqlClient.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(HsqlClient.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if(conn1 != null){
            try {
                conn1.close();
            } catch (SQLException ex) {
                Logger.getLogger(HsqlClient.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}

错误信息 #

SEVERE: null
java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: client_sign_on
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at hsqlclient.HsqlClient.main(HsqlClient.java:173)
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: client_sign_on
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserDQL.readTableName(Unknown Source)
    at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
    at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 3 more

解决方案 #

1. 修正外部应用的连接方式 #

Web服务通过服务器模式启动HSQLDB，外部应用必须通过TCP连接到该服务器，而非直接访问数据库文件。当前外部应用使用file:协议直接读写文件，会导致文件锁冲突，且无法访问服务器模式下创建的表（表可能存在于服务器默认Schema中）。

修改外部应用的连接字符串为服务器模式：

conn1 = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/hsqldb", "SA", "");

注意：

• 无需添加file:路径，服务器已管理数据库文件
• 用户名保持大写SA（HSQLDB默认用户名，避免大小写不一致问题）

2. 检查Schema与权限 #

若连接服务器模式后仍报错，先执行SQL确认表所在Schema：

SELECT TABLE_NAME, TABLE_SCHEM FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CLIENT_SIGN_ON';

如果表不在默认PUBLIC Schema中，查询时需指定Schema：

SELECT web_client_id FROM [SCHEMA_NAME].client_sign_on;

或在连接时指定默认Schema：

conn1 = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/hsqldb?default_schema=[SCHEMA_NAME]", "SA", "");

3. 配置服务器允许远程连接 #

在Web服务的startServer方法中添加以下配置，确保服务器允许外部连接：

p.setProperty("server.remote_open", "true"); // 允许远程打开数据库
p.setProperty("server.address", "0.0.0.0"); // 监听所有IP（若外部应用在其他机器需配置）

4. 验证文件权限 #

确保外部应用运行用户拥有数据库文件目录的读写权限：

chmod -R 755 /usr/share/tomcat/webapps/TicketingAPIWS/DB/
chown -R [外部应用运行用户] /usr/share/tomcat/webapps/TicketingAPIWS/DB/

内容的提问来源于stack exchange，提问作者sandesh b nataraj