SQLite3 in Java - How can I determine if my connection is valid and a table exists?-CodePudding

I'm making a game in Java where I store some data in a single SQLite table. Since a db connection isn't mandatory for the game, I want it playable if some error happens, but while hiding anything that would lead to a database query or update. Therefore I want to determine if the connection is valid and if my table is present.

Here's my existing code for connecting to it:

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;
    }
}

However this still returns the connection, even if points.db does not exist at the specified location.

CodePudding user response：

To be independent of the underlying DB-engine, you can use Java MetaData to find out if an object exists. To find a table, you can use something like this: ( I've copied the code above)

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();
      }
    }
}
}

CodePudding user response：

Did some digging around and found a solution.

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;
    }
}

The query to check what tables are available likely doesn't need to be a prepared statement.