Spring Book – Chapter 8 – Data Access

Spring and JDBC

Spring provides extensive support for traditional JDBC style programming in Java. The Spring JDBC framework in built on top of the Java SE JDBC API. Spring JDBC doesn’t require the Spring IoC container. It provides you with utility classes based on the template pattern which allows doing JDBC style programming with ease. It replaces tedious and cumbersome Java SQL API’s and also mitigates JDBC resource management risks associated with using plain JDBC. Spring JDBC doesn’t have any configuration management overhead and it is a pure code solution addressing the JDBC issues.

Spring JDBC Abstraction Packages

Spring framework’s JDBC abstraction package contains four important packages. They are:

  • org.springframework.jdbc.core – This package has the all-important JdbcTemplate class, various callback interfaces and variety of utility classes which aids you in doing the JDBC operation with ease.
  • org.springframework.jdbc.datasource – This package contains utility class DataSource which can be used for easy data source access.
  • org.springframework.jdbc.object  – This package contains classes that represent RDBMS queries, updates, and stored procedures as thread safe, reusable objects.
  • org.springframework.jdbc.support – This package provides SQLException translation functionality and some utility classes.

Options for JDBC Access

There are various means by which to access JDBC in your Spring application. These can be categorized as below:

  • Using the various templates available in the Spring framework. The templates available are:
    • JdbcTemplate
    • NamedParameterJdbcTemplate
    • Using other classes provided by the Spring frameworks which optimize the database metadata namely:
      • SimpleJdbcInsert
      • SimpleJdbcCall
      • Using SQL object related classes, which brings more object-oriented approach, namely:
        • MappingSqlQuery
        • SqlUpdate
        • StoredProcedure

In the following section, I will be explaining in some detail the various options as shown in the above section.

JdbcTemplate

org.springframework.jdbc.core.JdbcTemplate is the central class in JDBC core package. Spring provides a simplification in handling database access with this class.

What does JdbcTemplate do?

Jdbc template performs the following tasks:

  • Handles the creation and release of resources, which helps you avoid common errors such as forgetting to close the connection.
  • Performs the basic tasks of the core JDBC workflow statement creation and execution, leaving application code to provide SQL and extract results.
  • Executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values
  • Catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy

The Spring JDBC Template has the following advantages compared with standard JDBC.

  • The Spring JDBC template allows clean-up the resources automatically, e.g. releasing the database connections.
  • The Spring JDBC template converts the standard JDBC SQLExceptions into RuntimeExceptions. This allows the programmer to react more flexible to the errors. The Spring JDBC template also converts vendor specific error messages into better understandable error messages.

Figure 8-4. Dependencies of JdbcTemplate class with other relevant classes in our sample

Figure 8-4 shows the dependencies of the JdbcTemplate class with other classes in our sample application. It means that JdbcTemplate requires a data source (DataSource) object and the customer repository implementation namely JdbcCustomerRepository (implementing CustomerRepository interface), uses JdbcTemplate internally to do the various JDBC operations.

Typically there are some steps before which you can actually use the various JDBC templates in Spring. They are given below:

  • Get data source

Data source can be obtained in your application by various means. They are (Refer ‘Connection Management’ section in this same chapter for more details):

  • Using JDBC API
    • DriverManagerDataSource
    • SingleConnectionDataSource
  • JNDI lookup

In your Spring XML configuration, do the following:

  • Using connection pools
    • Apache DBCP connection pool
    • C3P0 connection pool
    • Configure the data source

In your Spring XML configuration, according to the data source that you have got, configure it accordingly.

  • Configure JdbcTemplate

For configuraing JdbcTemplate, in your XML configuration, do the following:

  • Configure repository implementation to use JdbcTemplate

Register your repository implementation and inject the JdbcTemplate in your Spring XML configuration as follows:

Listing 8-6. The Customer repository implementation class

In the following code snippets we will be writing individual methods inside the class shown in Listing 8-6, showing the various capabilities of JdbcTemplate class.

Querying

Please note that the various examples using JdbcTemplate given in the following code listings are based on the Spring documentation.

Listing 8-7. Query for getting the number of rows using JdbcTemplate

Listing 8-8. JdbcTemplate usage for a query having bind variables

Listing 8-9. JdbcTemplate usage for a querying a String

Listing 8-10. JdbcTemplate usage for a querying and populating a single domain object

Listing 8-11. JdbcTemplate usage for a querying and populating a number of domain object

Listing 8-11 can also be done by create a new row mapper class and then supplying this class to the query(..) method of JdbcTemplate. This is shown in Listing 8-12 below.

Listing 8-12. JdbcTemplate usage for a querying and populating a number of domain object using separate mapper class

Updating

Again examples using JdbcTemplate given in the following code listings are based on the Spring documentation. JdbcTemplate’s update(..) method is used to perform insert, update and delete operations. Parameter values are usually provided as var args or alternatively as an object array.

Listing 8-13. JdbcTemplate usage for doing insert operation

Listing 8-14. JdbcTemplate usage for doing insert operation

Listing 8-15. JdbcTemplate usage for doing insert operation

Execute

You can use the execute(..) method to execute any arbitrary SQL, and as such the method is often used for DDL statements. It is heavily overloaded with variants taking callback interfaces, binding variable arrays, and so on.

Listing 8-16. JdbcTemplate usage for executing an arbitrary SQL

Listing 8-17. JdbcTemplate usage for invoking a simple stored procedure

Page Visitors: 9107

The following two tabs change content below.
Tomcy John

Tomcy John

Blogger & Author at javacodebook
He is an Enterprise Java Specialist holding a degree in Engineering (B-Tech) with over 10 years of experience in several industries. He's currently working as Principal Architect at Emirates Group IT since 2005. Prior to this he has worked with Oracle Corporation and Ernst & Young. His main specialization is on various web technologies and acts as chief mentor and Architect to facilitate incorporating Spring as Corporate Standard in the organization.
Tomcy John

Latest posts by Tomcy John (see all)

2 thoughts on “Spring Book – Chapter 8 – Data Access

Leave a Reply

Your email address will not be published. Required fields are marked *