Java中的SQLite3-如何确定我的连接是否有效并且表是否存在？

Question

我正在Java制作游戏，其中将一些数据存储在单个SQLITE表中。由于游戏不是强制性的DB连接，因此，如果发生某些错误，我希望它可以播放，但是在隐藏任何会导致数据库查询或更新的内容时。因此，我想确定连接是否有效以及我的表是否存在。

这是我现有的连接到它的代码：

public Connection connect() {
    try {
        String url = "jdbc:sqlite:C:Program Files/sqlite/points.db";
        // create a connection to the database
        Connection connection = DriverManager.getConnection(url);
        if( connection.isValid(100)) {
            return connection;
        }
        return null;
    } catch (SQLException e) {
        System.out.println("Database connection failed" + e.getMessage());
        return null;
    }
}

但是，即使points.db在指定的位置不存在，这仍然返回连接。

Answer 1

要独立于基础DB-引擎，您可以使用Java元数据来找出是否存在对象。要查找表，您可以使用类似的内容：（我已复制上面的代码）

public Connection connect() {

// The database to connect to
String database = "C:Program Files/sqlite/points.db";

// The tablename, we are checking for
String tableName = "points";

// TableTypes might be: "TABLE", "VIEW", "ALIAS","SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM" to find all
// in our case, we are just interested in the table
String[] tableTypes = {"TABLE"};

ResultSet rs = null;

try {
    String url = "jdbc:sqlite:" + database ;
    // create a connection to the database
    Connection connection = DriverManager.getConnection(url);
    if( connection.isValid(100) ) {

       // get Information about the Database
       DatabaseMetaData dmd = connection.getMetaData(); 

       // I'm not experienced with SQLite, evtl. database can be null as well                 
       rs = dmd.getTables(database,null,tableName,tableTypes);

       // is there a result?
       if ( rs.next) {
          return connection;
       ) else {
         // the Table does not exist, so we can create it and return the connection or directly return null
         
       }
    }
    return null;
} catch (SQLException e) {
    System.out.println("Database connection failed" + e.getMessage());
    return null;
} finally {
    if ( rs != null ) {
      try {
        rs.close();
      } catch ( SQLException sqlex ) {
          System.out.println("Exception while closing resultset. " + sqlex.getMessage();
      }
    }
}
}

Answer 2

有些挖了一些解决方案。

public Connection connect() {
    try {
        String url = "jdbc:sqlite:C:Program Files/sqlite/points.db";
        // create a connection to the database
        Connection connection = DriverManager.getConnection(url);
        if( connection.isValid(100) ) {
            String sql =  "SELECT * FROM sqlite_master where type = ? and tbl_name = ? ";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "table");
            preparedStatement.setString(2, "points");
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next() && resultSet.getString("name").equals("points") ) {
                resultSet.close();
                return connection;
            }
        }
        return null;
    } catch (SQLException e) {
        System.out.println("Database connection failed" + e.getMessage());
        return null;
    }
}

查询可以使用哪些表的查询可能不需要是准备好的语句。