What's the right way to integrate SQLite with my Java EE app?

Question

I'm looking to add a pretty simple SQLite database to an existing Java EE application. I'm very used to using EJBs, ORMs, EntityManager, etc. to work with SQL databases. I've never not used some sort of ORM to work with relational DBs in Java.

I've been "recommended" to use a Java wrapper for SQLite, 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.

Context

I've been using an in-memory cache, implemented as a Map, 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 Map).

Questions

• How should I manage resources like SQLiteConnection? Normally I would let the container worry about all this, but since I'm not using JDBC, I have to do all this _{^{!@#$%ing, non-value-added}} stuff manually - right?
• Is there a way to implement this cleanly and transparently? I'd like to be able to just swap out an implementing class - e.g. replace FooMapCacheImpl with FooSQLiteCacheImpl.
• "[Most] methods are confined to the thread that was used to open the connection". Is there a simple, straightforward way to ensure that I don't try to access a SQLiteConnection from threads other than the one that opened it?
• ...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 bona fide 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.

Basically

I'm rather lost when it comes to working with databases in Java/Java EE, without using an ORM.
What's the right way to do it?

Answer 1

I don't think It is too hard to make a front end that would implements Map and save everything to a database using JDBC, but before doing it, think twice about it. The performance of the whole system might be affected badly.

However, if the root cause of your problem is the lack of Heap space, you should take a look at Terracotta's BigMemory. However, it is a commercial (non-free) product.

Terracotta has a pretty good cache framework as well (ehcache) which is opensource. Look at the cookbook, it might be inspiring.

If you want to do everything by hand, and you don't mind using Spring, try spring-jdbc. It is very easy to integrate with any JDBC driver. Take a look at SimpleJdbcTemplate. It does all the boiler plate code for you. You should probably use a connection pool as well, such as commons-dbcp

The easiest SQLite JDBC driver to use is this one. Since it doesn't rely on JNI. It might not be as fast, but for quick testing it is perfect.

If you aren't binded to SQLite, you can take a look at other available JDBC solutions such as hsqldb or derby

I hope this will help you out.

Answer 2

You may also want to look at Berkeley DB Java Edition. It allows you to persist and manage Java objects directly in the library, without requiring an ORM (and the associated overhead). It runs on Android, it's an Java library and can manage data sets ranging in size from very small to very large. It was designed with Java application developers in mind and should be both faster and simpler to use than an ORM+RDBMS solution. You can find more out more about it on our web site at Oracle Berkeley DB Java Edition.

Regards,

Dave

Answer 3

The sqlite4java wrapper is basically a JNI wrapper, it is nowhere near what you want. An ORM like eclipseLink would anyway be a layer on top of JDBC and the Entity manager would always end up using JDBC accesses.

Instead, sqlite4java allows you to call SQLite in java instead of having to do all the JNI wrapping yourself.

If you want to use an ORM and your preferred entity manager then you should use a JDBC driver and the sqlite4java wiki references a few of them.

Hope this helps.