I've spend some time recently putting together the DAOs for my project. Quite some time, in fact. God, they are ugly.
The Transfer Objects, the ones that ones that you end up using outside the persistence layer, seem usually to be coded as beans. So, the class which builds these objects has to set a bunch of properties:
principal.setPrincipalName(result.getString("PrincipalName"));
principal.setAddress1(result.getString("PrincipalAddress1"));
principal.setAddress2(result.getString("PrincipalAddress2"));
principal.setAddress3(result.getString("PrincipalAddress3"));
principal.setAddress4(result.getString("PrincipalAddress4"));
principal.setAddress5(result.getString("PrincipalAddress5"));
principal.setPostCode(result.getString("PostCode"));
principal.setContactName(result.getString("ContactName"));
principal.setContactPosition(result.getString("ContactPosition"));
principal.setTelephoneNo(result.getString("TelephoneNo"));
principal.setFax(result.getString("Fax"));
principal.setEmail(result.getString("Email"));
principal.setPredominantCurrency( result.getString("PredominantCurrency"));
There are similar (but not identical) bits of code all through this class; binding parameters into various bits of SQL, that sort of thing:
final String sql =
"select "
+ StringUtils.join(MSSqlPrincipalDAO.FIELDS, ", ")
+ " "
+ "from "
+ MSSqlPrincipalDAO.TABLE
+ " "
+ "where "
+ StringUtils.join(MSSqlPrincipalDAO.KEYS, " = ? " + "and ")
+ " = ? ";
final PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, principal.getPrincipalNumber());
statement.setString(2, principal.getPrincipalSequence());
To my Python-educated eye, this looks hideous. The field names are repeated all over the place, and putting them into constants isn't any better. The bean-property to SQL column mapping is also repeated several times - very error prone. But in Java, I can't think of a cleaner, less repetitive way of doing it. Am I missing something super-elegant?
Building the Transfer Objects as Maps would be a lot prettier, I know. But then using them would be a bit painful, so I don't want to do that. :-(
Another question; I went through this code replacing all the SQL construction literals (like "select ", for example) with constants. But it seemed to me that this made the code less readable, and no more maintainable. So I'm leaving the literals in. Will the code-police be able to make a good case against me?
BTW, clearly these DAOs could be generated automatically. Are there any good tools for this? (Other than sql2java, which I've tried; I can't get it to work.) If not, I think I feel some Python coming on...
Posted to Java by Simon Brunning at February 19, 2004 02:06 PMThis is exaclty why object Relational Mapping tools like Hibernate are so popular - they hide all this crap. At the end of the day you are taking a relational structure and attempting to create an Object structure from it - there is no nice mapping. Hibernate/EJB's attempt to do this for you with varying degrees of success.
If you don't want to use an ORM tool then you'll have to put up with this - just stick the code behind a DAO interface that returns POJO's, and you can always create alternate implementations later on - the Spring Petclinic example shows how multiple DAO implementations can be configured using IoC, swapping them out as needed depdning on your configuration...
POJOs[1] - cool, I've not heard that one!
Sticking the ugly stuff in the DAO interface is exactly what I've done. It's the DAO interface stuff that I'm whinging about. I suppose that once I've build it, I'll not have to look at it again. Unless the database schema changes too often, that is, TULNA. ;-)
The IoC idea is interesting. At the moment, you have to pass an argument into the DAO Factory to tell it which back-end you are using. This can be pulled from our config file, so that's easy enough, I suppose, but IoC would be cleaner.
Thanks for the feedback, Sam!
[1] http://www.martinfowler.com/bliki/POJO.html
Posted by: Simon Brunning on February 19, 2004 03:19 PMShocking isn't it. Of course if you are agile ( http://www.agiledata.org ) - and you should be - then your database will change a lot, causing you pain.
I'd say that you should use an ORM or auto generate this stuff.
I will resist the temptation to say that SQLObject ( http://www.sqlobject.org/ ) is much, much better than these solutions.
Posted by: Andy Todd on February 19, 2004 03:36 PMTry SQL Maps (http://www.ibatis.com/common/sqlmaps.html).
It handles the entire DAO layer for you.
Posted by: Frank B on February 19, 2004 03:52 PMI have had to do this 1000000 times on my current project. I did not want to use Hibernate and have to translate things on the fly, and we can't use CMP, so I wrote a code generator that generates a DTO, and the DAO to go with it. The DAOs use a base class that takes a parameter list and a SQL string and executes it. My life is so much easier now, because I have hard-coded SQL that is quite quick, but I don't have to write it, which makes coding a snap. It's not ideal, but it's pretty good.
Posted by: Dave on February 19, 2004 04:53 PMI understand your reaction, Simon. And I second Sam's comments about Hibernate. If you use Hibernate and implement appropriate behaviour in the POJOs that are mapped via Hibernate, you can reduce your dependency on ugly DAO methods. (Responsibility Driven Design a la Wirfs-Brock is such a good idea and I find it frustrating that so much of J2EE results in anything but following this principle.)
The desire to not have to code this sort of ugly infrastructure stuff is also a strong motivation for me to move towards using the Spring framework.
In the mean time, generating your code with Python seems like a good approach.
Posted by: Keith Pitty on February 19, 2004 10:56 PMI'm currently working on a C++ project, where we have the equivalent of DAOs. The programmer I sit next to, Duncan, spent a day writing a bash/awk script to blat out a DAO classes based on the database column definitions. All I have to do is tidy it up with search criteria and any derived values we want to fetch.
A small table now takes 10 minutes to wrap in a DAO. It used to take two hours.
Every time I use the script I turn around and tell Duncan what a neat idea it was.
That said, go with an O-R mapper if you can. Hibernate is pretty good (but the license is LGPL :()
As for using literals rather than constants... NO, NO, NO! Are You Crazy? Can you imagine how much effort it will be to go through your code and find and change every "select " string every time that ANSI change the SQL spec?
Posted by: Alan Green on February 20, 2004 04:28 AMThis short dissertation on the advantages of named constants in Fortran may be helpful:
http://dqs.worldatwar.org/robots/389.html
Posted by: Alan Green on February 20, 2004 05:03 AM"Squiggle is a little Java library for dynamically generating SQL SELECT statements."
http://joe.truemesh.com/squiggle/
BSD-style license.
Posted by: Anonymous on February 20, 2004 05:08 AMHibernate is LGPL? Yikes! Well, we can rule *that* out, then...
http://weblogs.java.net/pub/wlg/258
Posted by: Simon Brunning on February 20, 2004 09:19 AMFrom http://www.hibernate.org/104.html:
"Hibernate is Free Software. The LGPL license is sufficiently flexible to allow the use of Hibernate in both open source and commercial projects. Using Hibernate (by importing Hibernate's public interfaces in your Java code), and extending Hibernate (by subclassing) is considered by the authors of Hibernate to be dynamic linking. Hence our interpretation of the LGPL is that the use of the unmodified Hibernate source or binary does not affect the license of your application code."
Posted by: Sam Newman on February 20, 2004 10:31 AMThing is, Sam, that no one knows exactly what the LGPL means in relation to Java. The Hibernate team think one thing, (or perhaps they are just saying what they *want* it to mean,) and the FSF think something else.
*I* don't know who's right, and I'm not using anything that *might* put my company at risk.
Clearly, the Hibernate people *want* their stuff to be available to commercial developers. They should relicense.
Posted by: Simon Brunning on February 20, 2004 10:38 AMSquiggle looks really cool, but I'm reluctant to introduce a new dependancy at this stage in the project, unless I can't live without it.
I'll grab it for another project, though...
Posted by: Simon Brunning on February 20, 2004 11:48 AMSimon,
Have you ever looked at Jakarta DynaBeans? You can have a class that you can access like a Map as well as have the "pretty" interface for methods such as setTelephoneNo(). With this, the SQL-related code could simply use the column names (if they match :-) and fill in the DynaBean from the SQL ResultSet using the map-like API. Then, in your code, you could access using the more friendly methods.
You may need to, though, create a mapping between table column names, such as AVX334_VS_TELEPHONE and bean property names, such as "telephoneNo".
Posted by: Eric Foster-Johnson on February 20, 2004 04:08 PMCool - DynaBeans[1] look like *exactly* what I want. Create a bean style object, and have it extend (or be wrapped by) one of the DynaBean classes, and get a Map style interface for free!
This should mean that I can keep all the column names in constant arrays, and nowhere else; just what I was hoping for. Thanks.
[1] http://jakarta.apache.org/commons/beanutils/api/org/apache/commons/beanutils/DynaBean.html
Posted by: Simon Brunning on February 20, 2004 04:29 PMUse DynaBeans with caution. They make your code harder to write as things are stored in maps and not real properties.
Check out Jakarta BeanUtils for xfering between DTO/Value Objects (inc DynaBeans) to domain objects.
Don't like Hibernate? Check out OJB from Apache. I prefer OJB because of its basis on ODMG and its built in transaction capability.
Lastly, as someone already mentioned, SPRING. Learn it and use it :)
In the end, I used the beanutils' spmple property getters and setters, which worked great.
Posted by: Simon Brunning on April 14, 2004 01:06 PMFireStorm/DAO is another tool that can generate your DAO/DTO code for direct from your DB schema. It can generate implementation code based on JDBC, EJB, or JDO.
More info at http://www.codefutures.com
Posted by: Andy Grove on April 17, 2004 11:04 AM