SQL Zone is brought to you in partnership with:

Mr. Freedman is a seasoned architect and developer with over twenty years experience in the software industry. In the last ten years, he has focused on Java technologies and has led many successful projects, from web applications to Swing-based rich clients, back-end systems and large integration efforts. ! Richard is a DZone MVB and is not an employee of DZone and has posted 6 posts at DZone. You can read more from them at their website. View Full User Profile

People are still writing straight JDBC Code?

02.10.2011
| 21178 views |
  • submit to reddit

I’ve been using Hibernate, JPA, and the Spring Framework for so long now, that I tend to forget that there are still apparently lots of Java developers out there still entirely hand-writing JDBC code – getConnection(), etc., and trying (sometimes) to manage the JDBC resources properly in the face of exceptions.

Two things bring this to mind.

First, I recently did a consulting gig for a client who had hand-written JDBC transactions, and they were experiencing locking issues caused by transactions that were leaked when an exception was thrown.

Second was this blog post where the author suggests a few ways to write “pretty database code” that automagically cleans up resources that would otherwise be leaked.

Unfortunately, the author is trying to patch a problem that he shouldn’t have created in the first place. Instead of annotating resources to be cleaned up, I would recommend the use of a library such Spring (see JDBCTemplate) that abstracts the resource management, so that you don’t have to deal with it at all.

In the case of the client mentioned earlier (who was using Spring and JPA), user-transactions should not have been used, when JPA, or even Spring’s JDBCTemplate could have been used to manage the transactions.

The moral of the story is, if you are doing something in your code repeatedly that is complex or requires tricky resource management, others have probably had the same problem, and someone has probably solved the problem elegantly. Look for the elegant solution. Google is your friend. Don’t cut-and-paste, and for heaven’s sake, don’t create a macro in your IDE to do the cut-and-paste for you.

References
Published at DZone with permission of Richard Freedman, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags:

Comments

cowwoc replied on Thu, 2011/02/10 - 2:55pm

Take it from someone who's used Hibernate for over 5 years, ORMs are the crap. Ted Neward had it right: http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx

 

I personally recommend the following combination:

1. Plain JDBC

2. querydsl-sql for type-safe queries: http://www.querydsl.com/

3. MyBatis Migrations or Flyway Migrations for database versioning

 

ORMs might seem easier/faster at the beginning but you'll end up with a higher learning curve and more edge cases than the above combination. Your overall productivity will actually be higher using straight JDBC and you'll end up with simple code.

Andy Leung replied on Thu, 2011/02/10 - 2:59pm

In one of my scenario where a product engineer is looking for an app that can take his command sent from his machine through network, and being able to save into database. I used Tomcat with pure JDBC code to do that because Spring and Hibernate frameworks actually give trouble of ORM overheads.

Jilles Van Gurp replied on Thu, 2011/02/10 - 3:09pm

I actually switched back from JPA/hibernate to raw JDBC after observing the following things:

1) People in my team were blindly trusting Hibernate to do the right things. It wasn't and it proved very hard to fix & debug on multiple occasions. We had a long succession of very obscure bugs that essentially boiled down to hibernate doing things we did not intend or not doing things we actually wanted it to do.

2) The amount of magic was such that people were assuming things to happen that actually weren't happening at all or at the wrong moment. Things like transactions for example. Just because it has an annotation, doesn't mean you've done your job. When the magic stops working you have a problem.

3) It was painfully slow without any obvious reason. Part of the reason was that an optimized database schema looks quite a bit different from what hibernate produces from what looks like an innocent little data model. 

4) People were thinking they were doing OO development instead of database development. That's an illusion that hibernate is designed to provide but if you fall for it it will prove to be fatal. We fell for it and our data model turned into a ridiculously normalized mess with way too many tables. Hibernate + junior engineers is a disastrous combination.

So we did the following:

1) we re-developed the worst offending code from scratch

2) we used plain old jdbc only (very refreshing experience, it's not complicated at all).

3) we kept the data model & database schema for compatibility reasons

4) we offloaded most our complex querying to solr. This gave us a massive boost in performance & scalability as well as ease of use.

This fixed our performance and complexity issues in a pretty dramatic way. Also , the resulting code is quite small & simple. I haven't had to touch it in months since it just works.

We could have probably achieved the same results with hibernate and a complete redesign. But I don't believe the added complexity is worth the magic. I'm a strong believer of keeping things small and simple.

Wujek Srujek replied on Thu, 2011/02/10 - 3:53pm

That's what you usually get for using tools that you don't understand / know well enough to use them correctly. As a junionr I saw usages of Hibernate implemented by seniors in such a way that I wanted to cry - there was no understanding of anything, and it was because nobody ever read anything on the topic. Was this ego? Lack of time? I have no idea; what I know is that the code was utter nonsense, which was not fault of the tool, but of the people who used it incorrectly.

As for the auto-generated schema - nobody on the Hibernate team ever said you should use it for production. It is only natural to employ a DBA for 2 straight days / weeks to pimp it up.

That being said, I prefer other tools to Hibernate.

Fab Mars replied on Thu, 2011/02/10 - 6:34pm

I still find JPA/Hibernate very convenient for CRUD-like screens. And I agree with the previous commenter: 80-90% of the people I worked with just didn't understand what they were doing with Hibernate.

 

From my experience it's better to use:

- ORMs for CRUDs (eg administration sections of your app)

- SQL/StoredProcs for  front end stuff (eg parts with lots of hits or searches)

Jarrod Roberson replied on Thu, 2011/02/10 - 6:42pm

I too have had a terrible experience not only with Hibernate, and with every other ORM out there, when trying to do anything remotely advanced or complex. Most are brain dead and impose naive schemas that are not optimized for anything other than small scale applications. Hibernate doesn't play nicely with transactions at all, it doesn't work reliably with caching causing all kinds of weird errors because of extremely poor cache management. I do agree that writing alot of the boilerplate code is error prone and messy and Java is nothing if not full of boiler plate code. I don't think moving the boilerplate code to Spring is any better, it is just as messy. I created the following project to do the boilerplate for me. http://code.google.com/p/sqlck/ it is moving to GitHub just as soon as I get a chance to update it and move it.

Steven Yong replied on Thu, 2011/02/10 - 9:14pm

Why not?

Fabien Bergeret replied on Fri, 2011/02/11 - 1:27am

The illusion of Hibernate is that it pretends that you don't have to care about the database to deal with it, which is completely false. At least, with JDBC (and the help of annotated transactions, Sproing JDBC templates and RowMapper), you're aware of what you're doing.

Hibernate or JPA can be great productivity tools, but they have to be used very cautiously, under the supervision of experienced people. As a technical team leader, I'd certainly recommand it neither for a junior team, nor for a big expericeed team.

Liam Knox replied on Fri, 2011/02/11 - 3:51am

Personally these two concepts should be addressed separately

1. There seems no reason to favour straight JDBC when you have Spring's excellent JDBC support which hides the resource management and general gunk and comes also with great transactional support. I would always use Spring over straight JDBC

2. ORM's have their place but they are not the solution for all problems. I wouldn't say there are just edge case there are simply lots of cases where ORM's don't fit.

David Rabinowitz replied on Fri, 2011/02/11 - 3:56am

Depends on the scope of the application and the exact nature of the sql. For complex model and dependency management it is worth the effort to invest in an ORM, but if you have simple table (or several) with simple flows then why to put ORM and its adjoining complexity? In many cases this code also reside in its own project (let's say a war that provides some services) and for me there is no reason to increase its size just for the sake of ORM.

Using Spring and JdbcTemplate (which is what we do), takes almost all the pain if handling connections, statements and all the preparation and clean up which are the main problem of using jdbc.

Dimitris Menounos replied on Fri, 2011/02/11 - 4:13am

Even though I am an avid Spring proponent, I'll disagree. Most of us are proud for our profession and skills. Your article treats the average developer like an idiot who should only rely on canned solutions made by the smart people. This is the same annoying attitude Hibernate people had a couple of years ago.

Mladen Girazovski replied on Fri, 2011/02/11 - 5:13am

The reasons why people fail with frameworks are usually always the same: They do not read the documentation well enough and /or lack the theoretical background, then they jump straight to the implementation, usually without automated tests, so they see the wrong results just when they thought they were almost done.

ORMs are complex by nature, and always will be.

Of course, i'd prefer a working application with "plain" JDBC  over a non-working (buggy) application with an ORM, but the cause is not the ORM or any framework as such, but the lack of skills in the development team.

Pick your frameworks wisely, based on evalution and the skill level/experience of the team, not on marketing. The choices made that have lots of consequences are also known to be part of the architecture.

This is not just the case with ORM, but also applies to WebFrameworks, build tools, DI Frameworks, etc. pp.

Some frameworks have their own set of problems, like EJB 2.x and JSF 1.x.

For most green field projects i use an ORM, but using iBatis/MyBatis for brown field projects with an existing schema often has advantages ime.

 

Jan Kotek replied on Fri, 2011/02/11 - 6:07am

Yes I do write JDBC code. I guess it is not so painful in Scala :-)

Andy Leung replied on Fri, 2011/02/11 - 10:01am

I totally agree with everyone here about not trusting Hibernate. One of my projects was to build middleware for multiple front end. I was using Tomcat with Hibernate and I tried to make it lightweight enough on everything else and so my middleware could survive the non-functional requirements because it was sitting on a 1U Dell Dual Core 2GB. I was believing it that it should have plenty of power to handle all requests because I made all requests from front ends all lightweight.

Yes, it worked. BUT, it crashed.

I thought the story ended when I tested so many times, end-to-end and UAT. Everything went well. But after the weekend, users reported "no response" from front end on Monday. I spent so much time on investigating it and finally I found that it was a Hibernate memory leak issue. Weird, isn't it? The problem was, when there were requests coming continuously, the memory were reused and so server and hibernate were considered active. However, when there were no requests coming in for certain amount of time, the JDBC pool was trying to shrink down to minimum pool size and that triggered the memory unable to release error in Hibernate because it had references to it. It was tracked as major defect in major release of 3.2.4 or something. That was a very bad one!

Roger Marin replied on Fri, 2011/02/11 - 4:48pm

In my experience, iBatis(now MyBatis) is a great alternative to Hibernate/JPA when you don't want to go the JDBC route. I've used it with great success in several projects in the past, you should give it a try if you haven't.

Liam Knox replied on Fri, 2011/02/11 - 6:33pm in response to: Dimitris Menounos

I agree that having an understanding of the behind the scenes knowledge important and I would advise developers to invest time in this.

However, there are fundamentals in evolution at work here. Would I trust building my application on Spring, written by experts, used by millions in countless applications world wide, or on your proprietary implementation?

Should we always all have knowledge of lower level details? At some point you need to build upon something you don't have full understanding of and can trust. For example what do you know about hardware design? Does it really matter relative to what you know at the higher level?

The abstraction point is very important here. Spring JDBC provides what JDBC does but at a higher abstraction by hiding boiler plate, and therefore should always be favoured. ORM's address different concerns so cannot be taken as a straight form of substitution for JDBC.

David Lee replied on Sat, 2011/02/12 - 11:00pm

 For those claiming ORMs aren't the problem but people not reading the documentation is, please stop.  Most projects simply don't benefit from an ORM, not matter how well you know said ORM.  Period. And that's why people still write JDBC code and SQL.    I would suggest to most, to give groovy a look.  It pretty much can eliminate most of your broilerplate JDBC code.  I've moved from iBatis to Groovy for SQL and I love it.  Personally, I've always hated hibernate(maven and spring are on my hate list too).     The author should do a post titled "Why are you using an ORM, when you could use iBatis or Groovy". 

 

Igor Polevoy replied on Sun, 2011/02/13 - 3:27am

I could not agree more with Jilles van Gurp, all of his points are valid and resonate with my experience as well. What is interesting, I have learned Ruby on Rails a few years back, and was thoroughly impressed by ActiveRecord - this is a Ruby ORM for RoR. Experience using it was a refresher after heavy handed frameworks in Java, so much so, that after waiting for some time for someone to implement this in Java, I decided to do this myself. If anyone interested in a truly lightweight Java ORM, you can try ActiveJDBC. I will be publishing an introductory article on DZone soon. Some initial blogs can be found here: http://igorpolevoy.blogspot.com/2010/02/activerecord-in-java-activejdbc.html and here: http://java.productiveedge.com/

 I tried to strike a good balance with the API simplicity, performance of the ORM layer, and especially making it as intuitive as possible. 

enjoy

igor

Robin Bygrave replied on Sun, 2011/02/13 - 7:06pm

For people looking for a simplier alternative to Hibernate/JPA they could have a look at Ebean.

The main simplification is that is uses a "session-less" API - so the developer doesn't need to worry about attachment/detachment and the lifecycle of the Hibernate session (or JPA EntityManager).

It has a few other interesting features such as "Autofetch" which is automatic query tuning based on profiling of your application.

Anyway, just thought I'd mention it here for those people looking for a simplier alternative.

Eric Giese replied on Mon, 2011/02/14 - 5:40am

Hibernate itself isn't bad, as long as you observe some rules as soon as you are working in bigger scenarios:
  1. Do not integrate the DAOs directly into upper layers as first-class values. The problem in both scenarios is that the structure of DAOs usually does not match the exact requirements of the usecases in which your are working. Rather write your own Types which match the usecase, and do not try to "hack" the DAOs to match it by a series of if's and elses. There is a large difference between the UI view and the DB structure, which can rarely be translated directly.
  2. Never use DAOs as data transfer objects! The worst problem with hibernate-daos is that you can never rely on lazily-loaded values. The type has a getter for a list of relation objects, but who says that these have been read? This was a common source of bugs in my applications.
  3. Relations Again: IMHO, OR-Mapping should not map these things directly anyway into the beans. Relation-Fetching can eat up a lot of performance and it is very complex. I prefer beans without relations and manual querying for the relations. Also, as seen in the previous point, it cannot be transparent and then can be source of bugs.

Jessie Mear replied on Wed, 2011/09/07 - 8:19am

This application combines the two into a single applet and adds the capability to specify the graphic returned. Listing 9 shows the complete application. ruby on rails

Lukas Eder replied on Wed, 2013/11/20 - 6:22am

Many people who are writing plain JDBC are switching over to using jOOQ for typesafe SQL.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.