Scott Leberknight's Weblog

A long time ago, in a galaxy far away I posted about limiting the number of query results when performing SQL queries having ORDER BY clauses in them. For example, you might want to run a SQL query that sorts people by last then first name, and returns rows 41-50. You then might want to go to the next "page" of results, still sorting by last then first name, which is rows 51-60. That post talked about how easy it is to do using MySql, and it is because MySql provides the lovely "LIMIT" clause. Oracle, on the other hand, seemingly makes it very difficult to do what is quite simple conceptually. I am pretty sure that's on purpose, so you have to spend $300 per hour for one of their service consultants. Alternatively, you could use something like Hibernate to figure out how to write queries like this in Oracle, without spending a dime.

So basically I give all the credit to Hibernate and its developers as they obviously figured this out and incorporated it nicely into their product, abstracting away all the pain. So in Hibernate you could write the following Java code:

Criteria criteria = session.createCriteria(Person.class);
criteria.addOrder(Order.asc("lastName"));
criteria.addOrder(Order.asc("firstName"));
criteria.setFirstResult(40);  // rows are zero-based
criteria.setMaxResults(10);
List results = criteria.list();

So now all we have to do is turn the hibernate.show_sql property to true and look at the SQL that Hibernate generates. So I ran the query and figured out the gist of what Hibernate is doing. Here is an example of a SQL query you could run in Oracle, formatted for readability. That is, assuming you consider this query readable at all!

select * from
    ( select row_.*, rownum rownum_ from
        (
            select * from people p
            where lower(p.last_name) like 's%'
            order by p.last_name, p.first_name
        )
      row_ where rownum <= 50
    )
where rownum_ > 40

Isn't that intuitive? How nice of Oracle to provide such a succinct, easily understandable way of limiting a query to a specific set of rows when using ORDER BY. Also, note that the entire reason we have to go through all these hoops is because Oracle applies the special rownum pseudo-field before the ORDER BY clause, which means once the results have been ordered according to your criteria (e.g. last name then first name) the rownum is in some random order and thus cannot be used anymore to select rows N through M.

So what is exactly going on in the above query? First, the actual query is the innermost query which returns all the possible results, ordered the way you want them. At this point the results are in the correct order, but the rownum for this query is in some random order which we cannot use for filtering. The query immediately wrapping the actual query selects all its results, aliases rownum as rownum_, and filters out all rows where rownum is equal to or less than 50. Note that the values of rownum_ will be in the correct order, since the rownum pseudo-column has been applied to the results of the already sorted innermost query.

So if we stopped now we'd have rows one through 50 of the actual query. But we only want rows 41-50, so we need to wrap our current results in one more outer query. This outermost query uses the aliased rownum_ column and only includes rows where rownum_ is greater than 40. So that's it. A generic solution you can use for paging result sets in Oracle databases. One caveat: I've not done any performance testing on this and so don't really know whether first selecting all results before filtering them in the database would cause performance problems on really large numbers of results. It certainly seems that it could affect performance, but then again how else could you sort the results and grab some set of rows in the "middle" of the results without using a technique similar to this? I suppose if I were really enterprising I could look under the covers of some other database whose code is open source, and which has a simple syntax for limiting the rows, to find out how databases internally handle and perhaps optimize this kind of thing, but it is late and I'm already going to be hating myself for staying up this late as it is.

Normally in Rails when you want to display validation error messages for a model object, you simply use the error_messages_for() helper method. For simple sites this is usually just fine, as it displays a message stating that something went wrong along with a list of validation error messages. If you use the Rails helper methods to generate your HTML controls, then those fields are also wrapped in a div element which can be styled to indicate there were problems with that field. So you have some explanation of the validation errors, generally at the top of the page, and the fields where validation errors occurred can be styled as such, for example with a red outline or a red backgroun or whatever makes it clear to users there is a problem with the field.

This works very well when you are validating a single model object. But what if you are validating multiple models? You could have a separate error_messages_for() for each model object. That will work, but it is ugly, since you'll have a separate list of error messages for every model object you are validating. I searched the Rails API and could not find a method to display errors for multiple models, so I wrote one that is based on the error_messages_for() method. Basically I copied Rails' error_messages_for() method and then modified it to display messages for multiple models.

It works like this. You pass in an array of object names for which to collect and display errors instead of a single object name. The first object name is assumed to be the "main" object (e.g. the "master" in a master/detail relationship) and is used in the error explanation should any validation errors occur. For example, assume you have a page that allows you to create a stock watch list and also add stock ticker symbols for that watch list. The "main" object here should be the watch list object, and if validation errors occur then the message the gets printed is "2 errors prohibited this watch list from being saved." This makes some sense, at least to me, since the watch list is the main thing you are saving; the stock ticker symbols can be entered if the user wants, but are not required since they could be added later. As with the Rails error_messages_for() method, you can pass in additional options.

That's pretty much it, except for the code. So here is the code:

def error_messages_for_multiple_objects(object_names, options = {})
  options = options.symbolize_keys
  object_name_for_error = object_names[0]
  all_errors = ""
  all_errors_count = 0
  object_names.each do |object_name|
    object = instance_variable_get("@#{object_name}")
    if object && !object.errors.empty?
      object_errors = object.errors.full_messages.collect {
        |msg| content_tag("li", msg)
      }
      all_errors_count += object_errors.size
      all_errors << "#{object_errors}"
    end
  end

  if all_errors_count > 0
    tag = content_tag("div",
      content_tag(
        options[:header_tag] || "h2",
        "#{pluralize(all_errors_count, "error")} prohibited this" \
        " #{object_name_for_error.to_s.gsub("_", " ")} from being saved"
      ) +
      content_tag("p", "There were problems with the following fields:") +
      content_tag("ul", all_errors),
        "id" => options[:id] || "errorExplanation",
        "class" => options[:class] || "errorExplanation"
    )
  else
    ""
  end
end

The code works like this. First we extract the name of the "main" object as the first element of the object_names array. Next we loop through all the model objects, checking if there are any errors. If there are errors we collect them in a string containing an li tag for each error and append them to the all_errors string. Once we've checked all the objects for errors, and if there were errors, we wrap all_errors in a div containing a header, the main error message, and finally create an unordered list and stuff all_errors in that list. If there were no errors at all we simply return an empty string. That's it. Now you can easily display a list of validation errors for multiple model objects, using code like this: error_messages_for_multiple_objects( ["watch_list", "stock1", "stock2"] ). Of course if we have an unknown number of stocks in this case, then we could construct the array of object names first and then pass it into the method, which would be more flexible.

I am sitting in Oakland Airport waiting for my plane back home, so I thought I'd actually write a quick summary about JavaOne which I just attended this past week. I went to the 2003 and 2004 editions of JavaOne, and after that had decided not to come back as the quality of technical content was very low, especially compared to boutique conferences like No Fluff Just Stuff where almost every speaker is a book author, a well-known industry expert, an author of some popular open-source framework, or just is an excellent speaker and expert in their topic. But one of my friends went in 2005 (he was lucky enough to get a free ticket) and said that Sun had done a lot to improve the technical content and speaker quality; mostly it seemed they started inviting No Fluff speakers and other industry experts. So this year, after looking at the session descriptions and seeing a bunch of No Fluff speakers, people like Rod Johnson, Josh Bloch, and Neal Gafter, I decided to give it a try again. There's certainly no question JavaOne is always a good time with good parties, lots of food, lots of drinking, and is really a vacation!

Overall the conference was pretty good, and the speakers were in general very good. Then again, I did attend mostly sessions given by speakers I already knew were good and avoided the vendor-driven talks on EJB 3, JSF, and other such things that I never plan to actually use in a real project. I only attended the conference opening General Session on Tuesday morning, and found it to be quite anemic. Even the announcement that Sun planned to open source Java was subdued and not really much of an announcement - in fact most people sort of looked around and seemed to be asking "Did they just say they were going to open source Java?" since the way they announced it was so, well, lame. Much of the conference centered around the "Compatibility Matters" theme and the ubiquitous "Ease of Use" mantra, also known as "Ode to Visual Basic Programmers."

It continues to amaze me that the people doing the EJB 3 specification don't really seem to have learned much from Spring and Hibernate. Oh, they say they have a POJO-based programming model, inversion of control, dependency injection, and AOP, but in reality they are very limited and basic and don't approach the power that Spring provides, for example. There are annotations all over the place, which do remove a lot of the need for mounds of XML configuration code, but the "POJOs" are now littered with EJB imports, since you need to import the annotations you plan to use. So if you import the EJB annotations, are the POJOs really POJOs? Adding the @Stateless annotation to get yourself a stateless session bean still ties you to an EJB container. In Spring you are not tied to anything, and you generally don't have framework or vendor-specific imports in your classes, and most definitely not in your interfaces. And because the new JPA (Java Persistence API) does not include a "Criteria" API nor any notion of a second-level cache a la Hibernate, why in the world would I choose it over Hibernate 3?

I did attend some pretty interesting "non-standard" talks on things like JRuby, grid computing, TestNG, and Spring. I say "non-standard" because they are not the typical vendor-driven talks and are about topics that are not "standard" Java EE. It is refreshing that Sun has finally allowed these types of talks and has realized that not all Java developers are using the "standard" Java EE technologies as found in the specifications. Other interesting talks included one about new JVM features designed to permit dynamically typed languages like Ruby and Python to run on the JVM; and the new scripting language features built into Java SE 6. Initially I thought the addition of scripting support directly in the Java SE was a little silly, but after going to several talks I now at least see some potential benefits to it and think it might actually be a Good Thing - only time will tell I suppose.

Overall I think I got my money's worth, and that even included an appearance at the "After Dark Bash" from the Mythbusters who shot some t-shirts at extremely high velocities across the Moscone Center! Assuming Sun keeps bringing back the good speakers and keeps permitting the "non-standard" topics, I just might keep going back!