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:
1 |
<jee:jndi-lookup id="dataSource" jndi-name="/jdbc/springjdbc" resource-ref="true" /> |
- 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.
1 2 3 4 |
<bean id=”dataSource” class=”…”> <property name=”” value=””/> … </bean> |
- Configure JdbcTemplate
For configuraing JdbcTemplate, in your XML configuration, do the following:
1 2 3 |
<bean id ="jdbcTemplate" class ="org.springframework.jdbc.core.JdbcTemplate"> <property name ="dataSource" ref ="dataSource"/> </bean> |
- Configure repository implementation to use JdbcTemplate
Register your repository implementation and inject the JdbcTemplate in your Spring XML configuration as follows:
1 2 3 |
<bean id ="customerRepository" class ="com.mybook.JdbcCustomerRepository"> <property name ="jdbcTemplate" ref ="jdbcTemplate"/> </bean> |
Listing 8-6. The Customer repository implementation class
1 2 3 4 5 6 7 |
public class JdbcCustomerRepository implements CustomerRepository{ private JdbcTemplate jdbcTemplate;//Injected using the XML configuration //Other methods using JdbcTemplate methods } |
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
1 2 3 4 5 6 7 |
public int getNumberOfCustomers() { int customerCount = jdbcTemplate.queryForInt("select count(*) from customer"); return customerCount; } |
Listing 8-8. JdbcTemplate usage for a query having bind variables
1 2 3 4 5 6 7 8 9 |
public int getCustomersWithFirstName(String firstName) { int customerCount = jdbcTemplate.queryForInt( "select count(*) from customer where first_name = ?", firstName); return customerCount; } |
Listing 8-9. JdbcTemplate usage for a querying a String
1 2 3 4 5 6 7 8 9 10 11 |
public String getCustomer(Long customerId) { String lastName = jdbcTemplate.queryForObject( "select last_name from customer where id = ?", // SQL query to execute new Object[]{customerId }, // arguments to bind to the query String.class); // requiredType - the type that the result object is expected to match } |
Listing 8-10. JdbcTemplate usage for a querying and populating a single domain object
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
public Customer getCustomerObject(Long customerId) { Customer customer = jdbcTemplate.queryForObject( "select first_name, last_name from customer where id = ?", new Object[]{customerId}, // RowMapper interface is used by JdbcTemplate for mapping rows of a ResultSet on a // per-row basis. Implementations of this interface perform the actual work of mapping // each row to a result object, but don't need to worry about exception handling. // SQLExceptions will be caught and handled by the calling JdbcTemplate. new RowMapper() { public Customer mapRow(ResultSet rs, int rowNum) throws SQLException { Customer customer = new Customer(); customer.setFirstName(rs.getString("first_name")); customer.setLastName(rs.getString("last_name")); return customer; } }); } |
Listing 8-11. JdbcTemplate usage for a querying and populating a number of domain object
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
public Customer getAllCustomerObjects() { Customer customer = jdbcTemplate.query( "select first_name, last_name from customer", new RowMapper() { public Customer mapRow(ResultSet rs, int rowNum) throws SQLException { Customer customer = new Customer(); customer.setFirstName(rs.getString("first_name")); customer.setLastName(rs.getString("last_name")); return customer; } }); } |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
public Customer getAllCustomerObjects() { Customer customer = jdbcTemplate.query( "select first_name, last_name from customer", new CustomerMapper()); } private static final class CustomerMapper implements RowMapper { public Customer mapRow(ResultSet rs, int rowNum) throws SQLException { Customer customer = new Customer(); customer.setFirstName(rs.getString("first_name")); customer.setLastName(rs.getString("last_name")); return customer; } } |
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
1 2 3 4 5 6 7 |
public void createCustomer(String firstName, String lastName) { jdbcTemplate.update( "insert into customer (first_name, last_name) values (?, ?)", firstName, lastName); } |
Listing 8-14. JdbcTemplate usage for doing insert operation
1 2 3 4 5 6 7 8 9 |
public void updateCustomer(String firstName, Long customerId) { jdbcTemplate.update( "update customer set fisrt_name= ? where id = ?", firstName, customerId); } |
Listing 8-15. JdbcTemplate usage for doing insert operation
1 2 3 4 5 6 7 8 9 |
public void deleteCustomer(Long customerId) { jdbcTemplate.update( "delete from customer where id = ?", Long.valueOf(customerId)); } |
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
1 2 3 4 5 6 7 |
public void createCustomerTable() { jdbcTemplate.execute("create table customer (id integer, first_name varchar(100), last_name varchar(100))"); } |
Listing 8-17. JdbcTemplate usage for invoking a simple stored procedure
1 2 3 4 5 6 7 |
public void invokeProcedureToGetCustomer(Long CustomerId) { jdbcTemplate.update("call GET_CUSTOMER(?)", Long.valueOf(customerId)); } |
Page Visitors: 10603
Tomcy John
Latest posts by Tomcy John (see all)
- A Guide to Continuous Improvement for Architects - February 2, 2023
- Cloud-first Architecture Strategy - January 26, 2023
- Architecture Strategy and how to create One - January 24, 2023
Hi my friend! I wish to say that this article is awesome, nice written and come with approximately all important infos. Iˇ¦d like to peer more posts like this .
simple, well written. keep writing..