title: What's the right way to integrate SQLite with my Java EE app?
body: <p>I'm looking to add a pretty simple SQLite database to an existing Java EE application. I'm very used to using EJBs, ORMs, <code>EntityManager</code>, etc. to work with SQL databases. I've never <em>not</em> used some sort of ORM to work with relational DBs in Java.</p>
  
  <p>I've been <em>"recommended"</em> to use <a href="http://code.google.com/p/sqlite4java/" rel="nofollow">a Java wrapper for SQLite</a>, rather than a JDBC driver - so I'm kind of naked and ORM-less (right?). I'd like to get this up and running quickly.</p>
  
  <h2>Context</h2>
  
  <p>I've been using an in-memory cache, implemented as a <code>Map</code>, which gets filled with entries linearly over time. At some point, like when the app runs overnight, the cache will use all available heap space. Hence, storing the cache on disk (as a SQLite database) rather than in memory (as a Java <code>Map</code>).</p>
  
  <h2>Questions</h2>
  
  <ul>
  <li>How should I manage resources like <a href="http://almworks.com/sqlite4java/javadoc/com/almworks/sqlite4java/SQLiteConnection.html" rel="nofollow"><code>SQLiteConnection</code></a>? Normally I would let the container worry about all this, but since I'm not using JDBC, I have to do all this <sub><sup>!@#$%ing, non-value-added</sup></sub> stuff manually - right?</li>
  <li>Is there a way to implement this <strong>cleanly</strong> and <strong>transparently</strong>? I'd like to be able to <strong>just</strong> swap out an implementing class - e.g. replace <code>FooMapCacheImpl</code> with <code>FooSQLiteCacheImpl</code>.</li>
  <li><em>"[Most] methods are confined to the thread that was used to open the connection".</em> Is there a simple, straightforward way to ensure that I don't try to access a <code>SQLiteConnection</code> from threads other than the one that opened it?</li>
  <li>...and the flip side of that question: can I avoid creating a new connection every time I want to read from/write to the database? It seems a <em>bona fide</em> PITA to have to manage connections per-thread rather than, say, per instance, which is how I've been thinking about communicating with databases in the past.</li>
  </ul>
  
  <h2>Basically</h2>
  
  <p>I'm rather lost when it comes to working with databases in Java/Java EE, without using an ORM.<br>
  What's the right way to do it?</p>