Spring jdbc template example

12 Flares Twitter 0 Facebook 4 Google+ 8 LinkedIn 0 Filament.io 12 Flares ×

JdbcTemplate class is the central class in the JDBC core package of Spring framework. It handles the creation and release of resources, performs the basic tasks of the core JDBC programming  such as statement creation and execution, closing the statement and the jdbc connection. The JdbcTemplate helps mapping the returned query results to java objects, catches jdbc exceptions and translates them into more informative exception hierarchy defined in org.springframework.dao package.

In short, JdbcTemplates helps in

  • Handling Creation and Release of Database resources
  • Execution of Select Queries
  • Update , Insert, Delete Queries
  • Invocation of Stored procedures
  • Mapping Result Sets to Java Objects

Let us explore the usage of JdbcTemplate  in detail.

Configuring Data source

We will use the annotation’s approach instead of too many XML configurations. All dependent objects will be auto-wired.

Please note that – The DataSource should always be configured as a bean in the Spring IoC container. We will inject this datasource into our DAO and pass it on to the JdbcTemplate class.

Let us configure the Data Source first.

The above XML configures the Data Source. We have used the properties file approach to pass the required parameters to the Daatasource. You should create a jdbc.properties file and add it to the application’s classpath. Substitute the values based on the database you use. You should also add the corresponding JDBC driver for your chosen database to the classpath or if using maven, add it as  a dependency.

Configure DAO and JdbcTemplate

We have used @Repository annotation  Indicating that the above class is a “Repository” (or “DAO”).  We have put @Autowird on  setDataSource method there by instructing Spring to inject the Datasource instance using setter injection. What is left is to pass the datasource to the jdbcTemplate.  That is all we have to do. We now have the jdbcTemplate properly configured and available inside our DAO.

JdbcTemplate: SELECT Query

You can create a method inside the JdbcSampleDAO  defined above and include the above query there to run it.

Query for a Single Object

In the above example, we are using the RowMapper class provided by Spring framework to map the returned ResultSet to Java Object. The framework uses the callback method mapRow defined our RowMapper instance.

Query for a List of  Objects

Let us now see how can we query multiple rows and convert them to a List of Java Objects. We will take the example of querying all rows in the Users table ( A simple table with the following columns-  id, firstname, lastname). When you want to fetch all users, the obvious choice is to map the Result to a List of Users ( List<User>).

Above code is self explanatory. We again use a RowMapper and for each row we will get a callback to the mapRow method and inside this method, we create a new User object for each row. Spring will add each of these returned user objects to the List.

JdbcTemplate: Update

The update(..) method of jdbcTemplate can be used  to perform insert, update and delete operations.


Spring JdbcTemplate Documentation