Spring’s NamedParameter Jdbc Template

Attention: The following article was published over 13 years ago, and the information provided may be aged or outdated. Please keep that in mind as you read the post.

Recently I’ve been working on a project that is centered around batch processes that leverage the Spring Batch project and also uses hibernate’s JPA implementation. In addition to the batch processing, there was also a need for Reporting on the result of these processes that was also implemented using the Spring Batch framework.

During the process of analyzing the reporting queries it was clear that JPA entities and named queries weren’t going to be suitable for use with queries that could get pretty complex. What I really needed was a simple solution that accepted a sql query, mapped some parameters, executed the query and automatically mapped the results to simple POJOs. Surely I’m not the first dude that needed this kind of functionality and luckily I wasn’t. The Spring developers appear to have covered a ton of scenarios and didn’t miss this one either.

NamedParameterJdbcTemplate (api docs)

NamedParameterJdbcTemplate is part of the core Spring project and a Template class that exposes some basic JDBC operations using named parameters instead of the traditional JDBC “?” placeholders. One of the query methods on NamedParameterJdbcTemplate, which will be demonstrated here, accepts as parameters a SQL string, a SqlParameterSource and a RowMapper. These parameters are as follows:

SQL String: Any sql string and any parameters are in the form of “:[paramName]”
SqlParameterSource: We’ll be using a MapSqlParameterSource, which is a simple Map of named parameter values and the parameter type.
RowMapper: We’ll be using the BeanPropertyRowMapper, which takes the query’s result set and maps them to the POJO. It does this by matching the column name from the result set to the equivalent camel cased property name on the POJO.

First, let’s start with the SQL string and for this we’ll use a query that is intended to search for employee’s who have been hired between two dates ( i.e. the first of the month and the end of the month )

String sqlString = "SELECT LAST_NAME, FIRST_NAME, EMAIL, OFFICE_PHONE, HIRE_DATE FROM EMPLOYEE WHERE HIRE_DATE >= :fromDate AND HIRE_DATE <= :toDate";

As you can see, there are two named parameters “fromDate” and “toDate” in the SQL string using the named parameter format that a lot of people will be familiar with if you’ve used Hibernate.

Next, you’ll need a POJO named Employee with the following properties:

private String lastName; private String firstName; private String email; private String officePhone; private Date hireDate;

It’s important that all of the properties on your POJO are camel cased versions of the column names in your SQL query. Now that you have your SQL query and the POJO you want the results mapped to, all that remains is the code to implement the execution of your query. For purposes of brevity in the example dataSource, fromDate and toDate declarations and initializations have been left out.

NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource); MapSqlParameterSource sqlParams = new MapSqlParameterSource(); sqlParams.addValue("fromDate", jobParams.getFromDate(), Types.DATE); sqlParams.addValue("toDate", jobParams.getToDate(), Types.DATE); List<Employee> results = template.query(sqlString, sqlParams, new BeanPropertyRowMapper<Employee>(Employee.class));

As you can see the code is about as straightforward and simple as you can get, which is perfect for reporting queries that may involve joining quite a few tables and calculations. As a bonus, if you’re using a reporting tool/framework that accepts a list of beans as the input for generating the report then this will come in pretty handy.

— Jonny Hackett, [email protected]