An elegant DAO design for un-elegant queries

I am a big Hibernate fan, but sometimes you can’t use Hibernate. When you can’t, Spring’s JDBC support is awesome. One of the reasons I encounter frequently for not being able to use Hibernate is some ridiculously complicated queries (I’m looking at you, Peoplesoft). When you have a giant long SQL query, you often encounter some code that looks like this:

String sql = “SELECT ” +
“RM.RTITLE1, ” +
“RM.RNAME, ” +
“SR.USER_SEQ_NO, ” +
“SR.INT_SEQ_NO AS SUBREQ, ” +
“SRT.USER_SEQ_NO, ” +
“SRT.TEXT, ” +
“(SELECT SRA.COURSE FROM SUB_REQ_AR SRA WHERE SR.INT_SEQ_NO = SRA.SUB_INT_SEQ_NO AND SRA.USER_SEQ_NO = 1) AS COURSE ” +
“FROM DPROG D JOIN DPROG_REQ DR ON D.INSTIDQ = DR.INSTIDQ ” +
“AND D.INSTID = DR.INSTID ” +
“AND D.INSTCD = DR.INSTCD ” +
“AND D.DPROG = DR.DPROG ” +
“AND D.DPFYT = DR.DPFYT JOIN REQ_MAIN RM ON D.INSTIDQ = RM.INSTIDQ ” +

Ugly, isn’t it? Well, some of my coworkers that were running into this came up with the idea of putting the SQL into its own files. It is a great idea and I doubt they are the first to think of it. They don’t use Spring, though, and I thought Spring would be able to provide some good glue to put it all together.

It turns out, Spring has something called Resource that can automatically load things from various places. I chose to use the classpath, and put the SQL files into the same package as the DAO. The only problem here, is with Maven, you end up with the SQL file in src/main/resources and the code in src/main/java, which is a little annoying. The Resource object doesn’t get the whole job done either; it just provides an InputStream, and having to write code to read the contents of the file has no place in my elegant solution. So, this is where commons-io comes into play. In the setter for the Resource object, I use IOUtils to read the file and stick it in a String.

So, the overall strategy goes like this:

  • Put SQL in file in src/main/resources
  • Create DAO object with a Resource setter for the SQL file
  • Wire the SQL file resource in your applicationContext.xml
  • Read in the file using IOUtils in the setter

Here’s the DAO definition in applicationContext.xml


<bean id="darsCourseDao" class="edu.asu.eadvisor.onoff.dao.DarsCourseDaoImpl">
<property name="dataSource" ref="darsDataSource">
<property name="subreqsForCourseQuery" value="classpath:/edu/asu/eadvisor/onoff/dao/GetSubreqForCourse.sql">
</property>
</property></bean>

And the setter in the DAO looks like this:

    public void setSubreqsForCourseQuery(Resource subreqsForCourseQuery) throws IOException {
InputStream is = subreqsForCourseQuery.getInputStream();
try {
this.subreqsForCourseQuery = IOUtils.toString(is);
} finally {
IOUtils.closeQuietly(is);
}
}

Tapestry 5 pain points

I have been doing Tapestry 5 work for about a year now, and I’m definitely still learning, but I think I know my way around fairly well. I really (mostly) like it, and I think it is way easier to use than Tapestry 4, as well as many other frameworks. It also results in a nice clean code base which isn’t too difficult to approach. There are, however, a couple of areas that I really just find confusing. Some of them aren’t too bad once you get to know them, but they are hard to learn. I think it is important for most aspects of the framework to be easy to learn/use, because if people encounter pain during their initial impression stage, they will abandon the framework and use something else.

The first issue I have is with the way that the Select component works when you want to have a dynamic model for the list. I think this is a common requirement for applications, so it is something people will most likely encounter fairly quickly. For complex models, it is usually necessary to implement two interfaces: SelectModel and ValueEncoder. The interfaces make sense once you get to know them, but I think the learning curve is quite steep for such a common task. I’m not sure how this could be solved, but it seems like Tapestry 4 may have had a more simple system (though I’m sure it had some problems which led to it being redone).

The second issue I have is with page context. Often times in standalone web applications, I have created pages which take query string parameters. These can be referenced by external apps or pages, and used to link between pages of an app. Tapestry’s equivalent is the page context, which can be used to pass contextual data between pages. There are two problems that I’ve encountered with page context. First, it is limited in what data it can represent. It is generally just a list of nameless parameters. This makes it difficult to pass only certain parameters or parameters with names. Second, the format for the URL is not a part of the spec (correct me if I’m wrong), so it can only reliably be used via other Tapestry pages. This makes it difficult to pass data in to a Tapestry page from an external place.

So to sum it up, I’d like to reiterate that I really do like Tapestry 5 and hope for its widespread adoption, but it has a few points that are difficult to deal with. If these issues were addressed in a future version, I think it would help ease a new developer’s adoption of the framework.

Image submit in Tapestry 5.1

This just took me a really long time to figure out, so I’m writing it down. Tapestry 5.1 adds support for submit buttons with an image (I think it was added in 5.1). In any case, you just use the normal tag, but you add an image attribute which is an “asset”. I added an Asset property to my page class and used @Inject and @Path to inject the asset. I used that property name in the image attribute, and it didn’t work. It turns out, you don’t need to declare the asset as a property in your page class. You just specify the path in the image attribute just like you would in the @Path annotation. So, it looks something like this:

<t:submit image=”context:images/submit.gif”/>

Much simpler than I was thinking. Sometimes I get really frustrated with these little Tapestry issues…

Tabs vs spaces in Netbeans


I work with some people that like spaces and some people that like tabs in their code. I don’t really care, so I try to follow whatever the project is using. I ran into a big problem with Netbeans, though, in that it seems like getting it to use tabs only is impossible. It turns out they appear to be following a weird part of the Java Code Conventions spec:

Four spaces should be used as the unit of indentation. The exact construction of the indentation (spaces vs. tabs) is unspecified. Tabs must be set exactly every 8 spaces (not 4).

http://java.sun.com/docs/codeconv/html/CodeConventions.doc3.html#262

So, Netbeans sort of has two different “numbers” around tabs. There is a number that says how many spaces a tab should appear as, and a number that says how many spaces should be used when you indent something. By default a tab appears as 8 spaces, but a level of indentation is 4 (like the spec says above). As a result, it inserts four hard spaces when you indent one level, and a tab if you indent two levels. The solution is to set the two numbers to the same value. Just a wee bit confusing for a new user.

Here’s a bug report where someone describes it:

http://www.netbeans.org/issues/show_bug.cgi?id=52053

Opera!

I got really sick of Firefox hogging all of the memory on my laptop (it has 4 gigs), and I thought it would be fun to drive my coworkers nuts by reporting bugs specific to an exotic browser, so I started checking them out the other day. I started with Amaya because I remember it being really crappy back in like ’98. Unfortunately it still is. So, I tried Opera and it is actually pretty nice. The interface is a little ugly, but it seems to be way faster than Firefox. It also has this cool speed dial thing that comes up whenever you open a new tab that shows you all of your favorite web sites, and it shows them as pictures, which I think is really cool. So far it is pretty much able to keep up with FF on ajaxy web sites, although I have noticed a few issues with facebook. It also works with this web site I use that has a complicated Java applet. Pretty cool.

Browsing old versions of a subversion repository

I was trying to look at the contents of an old file in subversion today that had long ago been deleted, with no luck. In the Eclipse plugin for CVS, they had a feature that would allow you to define a date, then browse the repository based on that date, but it seems to be missing from subversion.

I found a workaround, but it isn’t quite as nice. You can browse a specific revision of the entire subversion repository, then look at the file. All you have to do is go to this URL:

http://<host>/<repo>/!svn/bc/<revision>/

Where the base URL is the root of your repository, and the extra stuff after the ! specifies the revision. And don’t ask me what bc stands for, I have no idea.

As for finding out which revision to look at, that is a little tricky. It can be helpful to look at logs of other files and see which dates correspond to which revisions.

Managing ehcache through Spring

There does not appear to be any documentation in the Spring manual about setting up an ehcache cache in your Spring container. There is a Spring bean built in to do it. To set it up with the default configuration, all that is needed is a line like this:

<bean id=”mediaCache” class=”org.springframework.cache.ehcache.EhCacheFactoryBean”/>

It looks like you can also customize quite a bit of the config without providing an ehcache.xml file. That could be nice so you don’t have to go chasing around to find all of the settings.

Tapestry 5: radio buttons coming back with value "on"

I ran into a problem today where I was setting the value of a radio button in Tapestry 5 to a long field within a property. I was getting back the value “on” and Tapestry was crashing trying to coerce it into a long type. It turns out the value was null, and the default value for a radio button is “on”. So, if you encounter this, make sure your value fields are not null.

Computer lingo

I often find myself staring down some technical thing and step back for a moment to interpret the words on my screen the way a normal person would. I think I might start blogging these anecdotes. Here’s the one I’m looking at right now:

Upgrading Confluence EAR-WAR Edition

To a normal person, an ear is a thing that you hear with and a war is a thing that involves guns and bombs. The concept of fighting a war with ears or having a war involving ears is pretty funny. Confluence is also a word that means something. Perhaps the ear war is taking place near the confluence of two rivers.

A couple of Netbeans issues

I think this is week 3 of Netbeans now, so I’ve used it a bit longer than planned. I found a couple of things that are annoying.

  • I miss the synchronize view in Eclipse. There is a simple “Show Changes” view that shows all of your subversion changes, but it isn’t hierarchical at all. I had to do a massive commit today, and it was very tedious selecting each file without being able to select whole directory structures.
  • It seems to hide certain project files from you, kind of like Visual Studio. For example, in one of my Maven based projects, I had a README.txt at the top level, and I can’t see it in the “Projects” view. If I go to the “Files” view, I can see it, but then I don’t get any other organizational stuff, like source folders.