Hey 2011: Where is my easy pagination and sort?

Update (6/22/12): Even better; I've found a great new ORM library called ebean. Article forthcoming!


Update (5/23/11): I found some exciting stuff around JPA and pagination in the new Spring Data project: http://www.springsource.org/spring-data/jpa

No matter which framework I use, it always seems like I need to re-invent the wheel when it comes to supporting pagination and sort in my web applications. Now, I'll admit that my experience is still mostly limited to Java and Spring (and JPA/Hibernate, for that matter), but c'mon, at least give me something basic, like what I've written below. When you want to display a pagable, sortable table in a UI, you are going to want to do common things like determine total rows, total filtered rows, current count, as well as sort on one or more fields.



This snippet did require me to come up with a cool bit of code to statically determine the type of a nested property specified using bean notation:



*note that this uses org.springframework.beans.BeanUtils, and not org.apache.commons.beanutils.BeanUtils; this is mainly because Spring's version allows for static type resolution already.

Recursively Scanning Packages for Marked Classes with Spring

I thought I'd share a neat snippet of code which allows you to recursively scan all of the classes in a given package for those which have been marked with a particular annotation. This could be useful, for example, if you have a static factory class that wanted to know all of the classes which could be instantiated for a particular purpose, without having to add them in some sort of static block at the top of the factory.

String pkg = MyBaseClass.class.getPackage().getName();
String pkgPath = pkg.replace('.', '/');
            
List<Class<?>> classes = new ArrayList<Class<?>>();
            
PathMatchingResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
Resource[] packageClasses = resourceResolver.getResources("classpath*:" + pkgPath + "/**/*.class");

for( Resource res : packageClasses ){
    classes.add(Class.forName(pkg + '.' + FilenameUtils.getBaseName(res.getFilename())));
}            
            
for( Class<?> c : classes ){
    MyAnnotation myAnnotation = c.getAnnotation(MyAnnotation.class);

    if( myAnnotation != null ){
        // this class was marked with @MyAnnotation; handle accordingly
    }
}

I could devote a whole post to Spring's resource loading capabilities; they are the nicest I've used. The above snippet utilizes Spring's PathMatchingResourcePatternResolver, which is able to list resources which match a given ant-style path description. The secret is "classpath*:some/path"; note the asterisk before the colon. This searches *all* accessible jars on the classpath, instead of stopping in the first one in which we find a match. One gotcha here is that you can't do something like "classpath*:*/service/*Service.class"; it will choke if you attempt a leading wildcard search.

Fun with JDBC CallableStatements

I spent the better part of a day debugging an issue involving stored procedure calls over Spring JDBC, and came across some interesting gotchas which I felt might help others save some time in the future, so I thought I'd write a quick article about it.

Basically, I was given a defect where decimal values from the database were being rounded somewhere in my service method, and was able to isolate it to within my StoredProcedureImpl class. In Spring JDBC, a common approach to calling a stored procedure is to extend StoredProcedure, wherein you declare the input and output parameters and their corresponding types:

    private class MyStoredProcedure extends StoredProcedure {        
        private static final String SQL = "sysdate";

        public MyStoredProcedure(DataSource ds) {
            setDataSource(ds);
            setFunction(true);
            setSql(SQL);
            declareParameter(new SqlOutParameter("date", Types.DATE));
            compile();
        }

        public Map execute() {
            return execute(new HashMap());
        }
    }

This works well, and is indeed an elegant way of calling a stored procedure, as it eliminates all of the JDBC nastiness (try / catch / finally (try / finally / etc / etc) / etc). The problem lies in the (obscure?) fact that when you specify DECIMAL or NUMERIC types for input or output parameters in the JDBC API, you are expected to also specify a scale, or it will default to 0 (and either truncate or round, depending on your database driver).

From the Java API for CallableStatement:

void registerOutParameter(int parameterIndex,
                          int sqlType,
                          int scale)
                          throws SQLException

    Registers the parameter in ordinal position parameterIndex 
to be of JDBC type sqlType. All OUT parameters must be 
registered before a stored procedure is executed.

    The JDBC type specified by sqlType for an OUT parameter 
determines the Java type that must be used in the get method 
to read the value of that parameter.

    This version of registerOutParameter should be used 
when the parameter is of JDBC type NUMERIC or DECIMAL.

    Parameters:
        parameterIndex - the first parameter is 1, the second 
        is 2, and so on
        sqlType - the SQL type code defined by java.sql.Types.
        scale - the desired number of digits to the right 
           of the decimal point. It must be greater than or 
           equal to zero. 

As it turns out, Spring JDBC's SqlParameter class didn't support setting scale on them until 2.0.5 (and here), and SqlOutParameter didn't have this in a final release until 2.5.

So, to make a long story short (too late, I know), if you are calling stored procedures in JDBC which use DECIMAL or NUMERIC parameters in either the input or output, you must specify a scale to use, and if you are currently running a non-current version of Spring (less than 2.5.1), this is a great reason to upgrade.

P.S. When researching this issue, I noticed a new set of classes in the Spring JDBC set (SimpleJdbc) which looked cool; they may warrant an article later on...