Java DB queries - All of the Options (2014)
When you want to query a DB in java, you have two main options to go:
1. Straight forward queries to the DB
This is the most intuitive (imho), just write down the query you want and get the results from the DB.
The most notable downside to this method is that you pollute your code with sql queries which can be all over the code, so there is no "one point of change" to the queries, which makes it very hard to change the DB schema for example.
2. ORM style queries
This way requires more work to begin qurying the DB but then you use java objects instead of queries so you keep your code clean.
You also are able to separate your model from your controller thus change your DB schema with much less pain.
Which ORMs exist?
Many, while the most notable ones are hibernate (which was the first afaik) & eclipse/total link.
But most ORM are not only a third party library (like hibernate when it began) but are now part of JEE implementation of the JPA interface (which itself was inspired a lot by hibernate).
Which DB querying way should I prefer?
As a thumb rule i would recommend using an ORM for big DB structures (enterprise?), but for small projects I would recommend using straight forward DB querying.
How should I query the DB directly?
The basic no-third-party way is not so complicated, but still it isn't so simple and it requires several connections closing with try-catch segments and in short - it just doesn't flow.
So I would suggest using third party libraries which can make the DB querying much easier, intuitive and nicer (code-wise).
Here are several libraries which can do the job (The orange background shows my picks):
- Spring JdbcTemplate - The Spring library is very intuitive and easy to use BUT it requires many Spring dependency libraries (spring-core, spring-beans, spring-tx & spring-asm to name some of them) - So I don't really recommend this library unless you have Spring in your project anyway (in which case you should probably just use ORM)
- Apache DbUtils - This project aimed to simplify the use of the basic set of sql commands and achieved its goal. But still this is a just a better implementation of the original sql commands and still isn't so intuitive (although it does prevent connections leakage and is simpler than the original code).
- Jdbc-Helper - Very intuitive, very light-weight (70k) and with no external dependencies!
The following libraries are non-ORM libraries but are mapping DB tables to Pojos using reflection, which gets us nice code, works with java entities, with not much boilerplate code needed:
- Sql2o - Simple to use, lightweight and well maintained library.
- Yank - Simple DbUtils wrapper which a uses reflection to map tables to Pojos
And finally there are those querying libraries which are type-safe which means that you don't need to write the actual query using a long string which can have a typo or add an sql-injection vulnerability and won't have compile time IDE fixes but it has "select", "from" where" etc methods which understand the query language:
My first picks are orange-highlighted and my main parameters were light-weight libraries which have an intuitive api to ease the use.
my second picks are yellow-highlighted, these are good projects but heavier to use than my first picks.
Which one will I use for my next project?
For very small & fast projects (Proof of concept projects for example) I use Jdbc-helper
For any regular private project I use Sql2o as I love it's pojo auto-mapping, while Yank is a good substitute
Comments