Java - JDBC alternatives

this is just theoretical question.

I use JDBC with my Java applications for using database (select, insert, update, delete or whatever). I make "manually" Java classes which will contain data from DB tables (attribute = db column). Then I make queries (ResultSet) and fill those classes with data. I am not sure, if this is the right way.

But I've read lot of about JDO and another persistence solutions.

Can someone please recommend the best used JDBC alternatives, based on their experience?

I would also like to know the advantages of JDO over JDBC (in simple words).

I've been able to google lot of this stuff, but opinions from the "first hand" are always best.

Thanks

The story of database persistence in Java is already long and full of twists and turns:

• JDBC is the low level API that everybody uses at the end to talk to a database. But without using a higher level API, you have to do all the grunt work yourself (writing SQL queries, mapping results to objects, etc).

• EJB 1.0 CMP Entity Beans was a first try for a higher level API and has been successfully adopted by the big Java EE providers (BEA, IBM) but not by users. Entity Beans were too complex and had too much overhead (understand, poor performance). FAIL!

• EJB 2.0 CMP tried to reduce some of the complexity of Entity Beans with the introduction of local interfaces, but the majority of the complexity remained. EJB 2.0 also lacked portability (because the object-relational mapping were not part of the spec and the deployment descriptor were thus proprietary). FAIL!

• Then came JDO which is a datastore agnostic standard for object persistence (can be used with RDBMS, OODBMS, XML, Excel, LDAP). But, while there are several open-source implementations and while JDO has been adopted by small independent vendors (mostly OODBMS vendors hoping that JDO users would later switch from their RDBMS datastore to an OODBMS - but this obviously never happened), it failed at being adopted by big Java EE players and users (because of weaving which was a pain at development time and scaring some customers, of a weird query API, of being actually too abstract). So, while the standard itself is not dead, I consider it as a failure. FAIL!

• And indeed, despite the existence of two standards, proprietary APIs like Toplink, an old player, or Hibernate have been preferred by users over EJB CMP and JDO for object to relational database persistence (competition between standards, unclear positioning of JDO, earlier failure of CMP and bad marketing have a part of responsibility in this I believe) and Hibernate actually became the de facto standard in this field (it's a great open source framework). SUCCESS!

• Then Sun realized they had to simplify things (and more generally the whole Java EE) and they did it in Java EE 5 with JPA, the Java Persistence API, which is part of EJB 3.0 and is the new standard for object to relational database persistence. JPA unifies EJB 2 CMP, JDO, Hibernate, and TopLink APIs / products and seems to succeed where EJB CMP and JDO failed (ease of use and adoption). SUCCESS!

To summarize, Java's standard for database persistence is JPA and should be preferred over others proprietary APIs (using Hibernate's implementation of JPA is fine but use JPA API) unless an ORM is not what you need. It provides a higher level API than JDBC and is meant to save you a lot of manual work (this is simplified but that's the idea).

If you want to write SQL yourself, and don't want an ORM, you can still benefit from some frameworks which hides all the tedious connection handling (try-catch-finally). Eventually you will forget to close a connection...

One such framework that is quite easy to use is Spring JdbcTemplate.

I can recommend Hibernate. It is widely used (and for good reasons), and the fact that the Java Persistence API specification was lead by the main designer of Hibernate guarantees that it will be around for the foreseeable future :-) If portability and vendor neutrality is important to you, you may use it via JPA, so in the future you can easily switch to another JPA implementation.

Lacking personal experience with JDO, I can't really compare the two. However, the benefits of Hibernate (or ORM in general) at first sight seem to be pretty much the same as what is listed on the JDO page. To me the most important points are:

• DB neutrality: Hibernate supports several SQL dialects in the background, switching between DBs is as easy as changing a single line in your configuration
• performance: lazy fetching by default, and a lot more optimizations going on under the hood, which you woulds need to handle manually with JDBC
• you can focus on your domain model and OO design instead of lower level DB issues (but you can of course fine-tune DML and DDL if you wish so)

One potential drawback (of ORM tools in general) is that it is not that suitable for batch processing. If you need to update 1 million rows in your table, ORM by default will never perform as well as a JDBC batch update or a stored procedure. Hibernate can incorporate stored procedures though, and it supports batch processing to some extent (I am not familiar with that yet, so I can't really say whether it is up to the task in this respect compared to JDBC - but judging from what I know so far, probably yes). So if your app requires some batch processing but mostly deals with individual entities, Hibernate can still work. If it is predominantly doing batch processing, maybe JDBC is a better choice.

Hibernate requires that you have an object model to map your schema to. If you're still thinking only in terms of relational schemas and SQL, perhaps Hibernate is not for you.

You have to be willing to accept the SQL that Hibernate will generate for you. If you think you can do better with hand-coded SQL, perhaps Hibernate is not for you.

Another alternative is iBatis. If JDBC is raw SQL, and Hibernate is ORM, iBatis can be thought of as something between the two. It gives you more control over the SQL that's executed.

JDO builds off JDBC technology. Similarly, Hibernate still requires JDBC as well. JDBC is Java's fundamental specification on database connectivity.

This means JDBC will give you greater control but it requires more plumbing code.

JDO provide higher abstractions and less plumbing code, because a lot of the complexity is hidden.

If you are asking this question, I am guessing you are not familiar with JDBC. I think a basic understanding of JDBC is required in order to use JDO effectively, or Hibernate, or any other higher abstraction tool. Otherwise, you may encounter scenario where ORM tools exhibit behavior you may not understand.

Sun's Java tutorial on their website provide a decent introductory material which walks you through JDBC. http://java.sun.com/docs/books/tutorial/jdbc/.

Have a look at MyBatis. Often being overlooked, but great for read-only complex queries using proprietary features of your DBMS.

http://www.mybatis.org

Here is how it goes with java persistence. You have just learnt java, now you want to persist some records, you get to learn JDBC. You are happy that you can now save your data to a database. Then you decide to write a bit bigger application. You realize that it has become tedious to try, catch , open connection, close connection , transfer data from resultset to your bean .... So you think there must be an easier way. In java there is always an alternative. So you do some googling and in a short while you discover ORM, and most likely, hibernate. You are so exited that you now dont have to think about connections. Your tables are being created automatically. You are able to move very fast. Then you decide to undertake a really big project, initially you move very fast and you have all the crud operations in place. The requirements keep comming, then one day you are cornered. You try to save but its not cascading to the objects children. Somethings done work as explained in the books that you have read. You dont know what to do because you didnt write the hibernate libraries. You wish you had written the SQL yourself. Its now time to rethink again... As you mature , you realize that the best way to interact with the Database is through SQL. You also realize that some tools get you started very fast but they cant keep you going for long. This is my story. I am now a very happy ibatis/User.

Ebean ORM is another alternative http://ebean-orm.github.io/

Ebean uses JPA Annotations for Mapping but it is architected to be sessionless. This means that you don't have the attached/detached concepts and you don't persist/merge/flush - you just simply save() your beans.

• I'd expect Ebean to be much simplier to use than Hibernate, JPA or JDO

So if you are looking for a powerful alternative approach to JDO or JPA you could have a look at Ebean.

JPA/Hibernate is a popular choice for ORM. It can provide you with just about every ORM feature that you need. The learning curve can be steep for those with basic ORM needs.

There are lots of alternatives to JPA that provide ORM with less complexity for developers with basic ORM requirements. Query sourceforge for example: http://sourceforge.net/directory/language:java/?q=ORM

I am partial to my solution, Sormula: sourceforge or bitbucket. Sormula was designed to minimize complexity while providing basic ORM.

Hibernate, surely. It's popular, there is even a .NET version.

Also, hibernate can be easily integrated with Spring framework.

And, it will mostly fit any developer needs.

A new and exciting alternative is GORM, which is the ORM implementation from Grails. Can now be used stand alone. Under the hood it uses Hibernate, but gives you a nice layer on top with cool dynamic finders etc.

All these different abstraction layers eventually use JDBC. The whole idea is to automate some of the tedious and error prone work much in the same way that compilers automate a lot of the tedious work in writing programs (resizing a data structure - no problem, just recompile).

Note, however, that in order for these to work there are assumptions that you will need to adhere to. These are usually reasonable and quite easy to work with, especially if you start with the Java side as opposed to have to work with existing database tables.

JDO is the convergence of the various projects in a single Sun standard and the one I would suggest you learn. For implementation, choose the one your favorite IDE suggests in its various wizards.

There is also torque (http://db.apache.org/torque/) which I personally prefer because it's simpler, and does exactly what I need.

With torque I can define a database with mysql(Well I use Postgresql, but Mysql is supported too) and Torque can then query the database and then generate java classes for each table in the database. With Torque you can then query the database and get back Java objects of the correct type.

It supports where clauses (Either with a Criteria object or you can write the sql yourself) and joins.

It also support foreign keys, so if you got a User table and a House table, where a user can own 0 or more houses, there will be a getHouses() method on the user object which will give you the list of House objects the user own.

To get a first look at the kind of code you can write, take a look at http://db.apache.org/torque/releases/torque-3.3/tutorial/step5.html which contains examples which show how to load/save/query data with torque. (All the classes used in this example are auto-generated based on the database definition).

I recommend to use the Hibernate, its really fantastic way of connecting to the database, earlier there were few issues, but later it is more stable. It uses the ORM based mapping, it reduces your time on writing the queries to an extent and it allows to change the databases at a minimum effort. If you require any video based tutorials please let me know I can uplaod in my server and send you the link.

Use hibernate as a stand alone JAR file then distribute it to your different web apps. This far is the best solution out there. You have to design your Classes, Interfaces, Enums to do an abstract DAO pattern. As long as you have correct entities and mappings. You will only need to work with Objects(Entities) and not HSQL.