It’s Only Software

I have noticed a trend recently among some folks in the Java world, where it is simply taken for granted that an ORM provider (usually Hibernate) will be automatically inserted in the technology stack of any new project. Quite often, this happens with little to no technical justification or analysis. If you’re reading this and nodding your head, this should be frightening to you. The reason is that if the use of an ORM isn’t required, it can cause projects (especially small ones) to have unneeded complexity. Additionally, abstraction without understanding what’s underneath can set a dangerous level of ignorance on the part of developers, who will simply collapse when the abstraction breaks and critical thinking through the abstraction is required to solve a problem.

In that spirit, I recently worked on a personal project to learn how one can write dead-simple plain old JDBC applications using only Spring Framework 2.5 without an ORM layer. Spring 2.5 has many features that provide some of the convenience of ORM libraries (simple mapping from ResultSets to Objects), some convenience above and beyond ORM libraries (mapping from ResultSets to primitives!*), and removes some of the complexity (caching, cascading, etc.). For applications with fewer tables than you have fingers on your hand, this can greatly ease development.

I’ll assume you already know how to work with Spring, and at least know (or can dredge up) the basics of JDBC. We’ll work through a simple example, mapping the ubiquitous “Person” table to a Person object, and back again through an insert operation. Hopefully this will open your mind to non-ORM solutions. I promise that in 5 minutes (or less!) you will be amazed at the things you can do with simple JDBC

Table Definition

Although the particular database we’re using isn’t really important, if you’re following along, in this particular example I’ve used SQL Server. The table definition looks like this (a bit verbose, since I used the SQL export feature of SQL server):

CREATE TABLE [dbo].[Person](
	[ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Person_ID]  DEFAULT (newid()),
	[FirstName] [nchar](30),
	[MiddleName] [nchar](20),
	[LastName] [nchar](50),
	[Suffix] [nchar](10),
	[Address1] [nchar](100),
	[Address2] [nchar](100),
	[City] [nchar](35),
	[State] [nchar](2),
	[ZipCode] [nchar](9),
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
 (
	[ID] ASC
 )
);

Basically, this is a table with a few columns containing attributes common to people.

Data Access

First, we will write a simple DAO. This DAO will have a method to find people residing in a specific state. The method will look something like the following:

	public List<Person> findPeopleByState(String state) {
		// <fill in>
	}

Seems pretty simple. OK, let’s fill in the method body.

	public List<Person> findPeopleByState(String state) {
		return jdbcTemplate.query("select p.* from Person p where p.state = ?", new PersonRowMapper(), state);
	}

Let’s look at what we have here. We have a single line which will do the following:

• Execute a SQL JDBC query
• Accept a parameter
• Map the JDBC ResultSet to a List of Person objects

Did you know you could do this with Spring and JDBC? I didn’t, until I ran across this gem. Let’s work from the bottom up. We write an inner class PersonRowMapper, which implements the ParameterizedRowMapper<T> interface from the org.springframework.jdbc.core.simple package.

Basically, classes implementing this interface must, given a ResultSet, return an object of the declared generic type. Here’s our PersonRowMapper implementation (Note that @Override is supported on interface implementation methods only on Java 6, so remove that annotation if you’re on Java 5):

	class PersonRowMapper implements ParameterizedRowMapper<Person> {
 
		@Override
		public Person mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			Person p = new Person();
			p.setFirstName(resultSet.getString("FirstName"));
			p.setLastName(resultSet.getString("LastName"));
			// etc.
			p.setZip(resultSet.getString("ZipCode"));
			return p;
		}		
	}

Dead simple ORM-like behavior. If you’re like me, you’ve probably written this exact same thing yourself at least 3 times before using JDBC. The magic lies in the SimpleJdbcTemplate class which was introduced in Spring 2.0. (Interested readers will note that due to its reliance on several language features, SimpleJdbcTemplate and its siblings require Java 5 or higher at runtime.)

Constructing our DAO looks like the following:

@Repository
public class JdbcPersonSearchDao implements PersonSearchDao {
	private SimpleJdbcTemplate jdbcTemplate;
 
	@Autowired
	public JdbcPersonSearchDao(DataSource dataSource) {
		jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}
 
	// class body
}

Note that from the outside, the DI weaver doesn’t notice anything different from any other JDBC-using DAO.

To return to our JDBC query, let’s quickly explain the rest of the one liner:

The query method is one of the many convenience methods built into SimpleJdbcTemplate. Others allow you to easily return a list of primitives, a Map of column name, value pairs, etc. Adding query parameters is supported in many flavors, my favorite being the varargs option that we’re using here.
The beauty of the ParameterizedRowMapper that we write is that it can easily be reused in other queries, or subclassed for queries that return supersets of data (imagine a complex table, which in some cases returns a set of columns C1 … Cn, and in other cases C1 … Cn+m).
Ease of Extension
I ran pretty quickly into a simple case that wasn’t covered by the provided functionality. I wanted to return a List of the distinct set of states in the Person table. With a (literally) one minute inner class, I was in business:

	public List<String> getAvailableStates() {
		return jdbcTemplate.query("select distinct State from Person order by State", new ParameterizedRowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getString("State");
			}
		});
	}

Hopefully you’re excited by this, and it gives you a chance to try out some raw JDBC to simplify your Spring-based applications. Please write in the comments if you have any additional questions/concerns/feedback. As and when I get time, I’ll cover JDBC inserts using the SimpleJdbcTemplate, which are, as expected, also quite, erm, simple  
Related Reading
I tried very hard to come up with reference material for this entry. Unfortunately I was only able to find Ben Hale’s entry on the SpringSource Team Blog from way back in 2006 when this stuff was introduced.
Of course, the friendly Spring Data Access forum is a good place to ask questions as well. Enjoy!