SQLite JDBC in Java Web App Servlet

Question

I am relatively new to developing web apps and I am currently working on a Java servlet web application and have need for a temporary database to store output from a method while it is running in scope. Once the method is completed, I will query the database to retrieve the results I need and then return it back to the servlet to be served up in the web page. Since there will be a lot of i/o from the database and there is no need to persist the database once the method is completed, I am thinking that a light-weight DB like SQLite JDBC will work best. https://bitbucket.org/xerial/sqlite-jdbc/downloads

Here is the code I am planning on using to instantiate the database:

Connection c = null;
    try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
    } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
    }
    System.out.println("Opened database successfully");

Question: Can SQLite be run like this where a database is instantiated while the method is in scope and then is erased once the method goes out of scope once the method and thread is completed?

Answer 1

Declan,

You are creating an external resource, which is a connection to a database. You are responsible for closing the resource before you return from the method. Luckily Java provides a good way to do this, using try with resources. You can do something like:

Class.forName("org.sqlite.JDBC"); try (final Connection c = DriverManager.getConnection("jdbc:sqlite:test.db");) { // ... write and read from the database } catch ( Exception e ) { System.err.println( e.getClass().getName() + ": " + e.getMessage() ); // -- do not exit yet: System.exit(0); } // ... don't forget to delete test.db

Also, since this is a servlet, you may have multiple threads creating a file with the same name. See if you can find a way to uniquely name the database file.

Sualeh.