1) pure JDBC
JDBC code has to be written to manage all database activities: opening (and closing) database connections, mapping parameters to queries and updates, executing statements, fetching query results…
+ fine when working with only a few sql statements
– the amount of code for each sql statement is consequent and often subject to copy-and-paste.
– as always the more code lines written, the bigger the probabilities of generating bugs: in particular it’s not rare for connections to leak (opened and never closed) or for queries parameters to not be associated with the correct values.
2) in-house ORM framework
An in-house framework aims at solving the issues found in stage 1). Typically it performs the following:
+ hides low-level bug-prone JDBC code behind a custom facade.
+ externalise SQL in configuration files, which allow modification of the SQL without recompiling the code and makes for a clearer java code.
– learning curve can be significant as in-house documentation is rarely up to date (when it exists at all)
– the effort required to maintain and document the framework, and keep it up to date with new releases of drivers/ databases / JDBC api is consequent.
Ibatis is an open source ORM framework which automatically maps java objects to sql queries using xml
+ (almost) totally eliminates the plumbing code needed for getting data in and out of the database.
+ transaction support
+ declarative caching
+ lazy loading
+ very good documentation available online / offline
+ fairly bug free
– As the number of sql statements grows, so does the complexity of the Ibatis configuration files.
All benefits from Ibatis and also:
+ Autogeneration of sql statements
+ Advanced functionalities (Shards)
– steep learning curve
– when using HQL: loss of control over how SQL queries are generated
Note that there’s no hierarchy of values here: Hibernate is not always preferable to raw JDBC (especially on very small projects)
It’s all depending on the context (although I find that Ibatis strikes a nice balance between Hibernate and pure-JDBC code).