Failed to create a table on in-memory sqlite database without any error message

Question

I am trying to create a table on in-memory sqlite database. I have a method called createTable to create a table which initializes a connection to the database with getConnection method.

createTable:

private void createTable() {
    try {
        final Connection connection = getConnection();
        final Statement statement = connection.createStatement();
        statement.execute(
            "CREATE TABLE videos (id INTEGER PRIMARY KEY, title VARCHAR(255), description TEXT, path TEXT, category VARCHAR(255), published INTEGER DEFAULT 0, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);");
        statement.close();
        connection.close();
    } catch (final SQLException e) {
        LOGGER.warn("Failed to create table.", e);
    }
}

getConnection

private static Connection getConnection() {
    try {
        Class.forName("org.sqlite.JDBC");
        return DriverManager.getConnection("jdbc:sqlite:");
    } catch (final Exception e) {
        LOGGER.warn("Failed to get connection.", e);
        throw new RuntimeException(e);
    }
}

To test the database I wrote a simple insert method:

public void insert() {
    try {
        final Connection connection = getConnection();
        final Statement statement = connection.createStatement();
        statement.executeUpdate(
            "INSERT INTO videos (title, path, category) VALUES ('test title', 'test path', 'test category');");
        statement.close();
        connection.close();
    } catch (final SQLException e) {
        LOGGER.warn("Failed to insert.", e);
    }
}

When I do

this.createTable();
this.insert();

I got the following error:

2017-09-17 22:38:02 WARN  Database:128 - Failed to insert.
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: videos)
    at org.sqlite.core.DB.newSQLException(DB.java:909)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.throwex(DB.java:886)
    at org.sqlite.core.NativeDB._exec_utf8(Native Method)
    at org.sqlite.core.NativeDB._exec(NativeDB.java:87)
    at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:116)...

For connections I am using org.xerial:sqlite-jdbc's v3.20.0.

Any ideas why table is not getting created? I am not seeing any exception or anything. SQLFiddle for the example above seems to be fine.

Answer 1

If I read the documentation correctly, the database only exists within its database connection by default:

Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

The easiest fix would be to not open a second connection, but keep using the connection that you used to create the table for the other database queries as well.

If you want to open multiple connections, you can set the ?cache=shared parameter on the database url.