Spring JDBC

Introduction

Spring provides an abstract way to communicate with database using plan old JDBC objects. Spring JDBC Framework takes care of all the low-level details to open the connection, prepare and execute the SQL statements, process exceptions, handle transactions and finally close the connection. You just have to define connection parameters and specify the SQL statements to be executed, fetch the query results and iterate them.

Spring-JDBC provides several approaches and correspondingly classes to communicate with database. The most popular approach is to use JdbcTemplate class to communicate with database. JdbcTemplate is the central framework class that manages all the database communication and exception handling. All approaches require JDBC 2.0 and above drivers

The org.springframework.jdbc.core package contains the JdbcTemplate class and its various callback interfaces, plus a variety of related classes. A subpackage named org.springframework.jdbc.core.simple contains the SimpleJdbcInsert and SimpleJdbcCall classes. Another subpackage named org.springframework.jdbc.core.namedparam contains the NamedParameterJdbcTemplate class and the related support classes.

The JdbcTemplate

It is the basic thread-safe class in the JDBC core package. It handles the creation and release of resources, which helps you avoid common errors such as forgetting to close the connection. It performs the basic tasks of the core JDBC workflow such as statement creation and execution, leaving application code to provide SQL and extract results.

The JdbcTemplate class

executes SQL queries,
executes Update and Insert statements
executes Stored Procedure calls,
Performs iteration over ResultSets and extraction of returned parameter values.
It catches JDBC exceptions and translates them to the generic, more informative, DataAccessException exception hierarchy defined in the Spring DAO.

Execute SELECT Query

You can execute different type s of SELECT queries with help of JdbcTemplate. Here we can see few examples:

Query 1: Get total number of records from PART Table :

int rowCount = jdbcTemplate.queryForObject("select count(*) from PART", Integer.class);

Method queryForObject receives two parameters. First parameter is sql query. Second parameter is class type in which query result is typecasted.

Query 2: Get number of PARTS from unit number 2.

int rowCount = jdbcTemplate.queryForObject( "select count(*) from PART where UNIT_ID = ?", Integer.class, 2);

Here method queryForObject receives 3 parameters. First parameter is sql query. Second parameter is class type in which query result is typecasted. Third parameter is value of placeholder (? ) in the sql query.

RowMapper Interface

This interface is used to convert database result row into the entity object. It is an interface found in org.springframework.jdbc.core package. Its implementation class is used by JdbcTemplate to map a row of ResultSet into an Object.

RowMapper objects are stateless and thus reusable. It is used either by query methods of JdbcTemplate or by OUT parameters of stored procedures.

All exception raised buy RowMapper object will be handled by calling JdbcTemplate.

Here is an example code of RowMapper class that converts ResultSet into College object.

public class CollegeMapper implements RowMapper<College> {

public College mapRow(ResultSet rs, int args) throws SQLException {

College dto = new College();

dto.setId(rs.getLong("id"));

dto.setName(rs.getString("name"));

dto.setAddress(rs.getString("address"));

//...

return dto;

}

Get list of colleges from COLLEGE table using RowMapper object.

List<College> colleges = jdbcTemplate.query("SELECT * FROM ST_COLLEGE",new CollegeMapper());

Execute INSERT

String sql = "INSERT INTO ST_COLLEGE VALUES(?,?,?)";

jdbcTemplate.update(sql, 1, "IPS", "Rau");

Execute UPDATE

String sql = "UPDATE ST_COLLEGE SET NAME=?,ADDRESS=? WHERE ID=?";

jdbcTemplate.update(sql, "Medicaps", "Rau",1);

Execute DELETE

String sql = "delete from ST_COLLEGE where id = ?";

int i = jdbcTemplate.update(sql, 1);

Execute DDL Statements

Method execute() can be used to execute any arbitrary SQL. It is typically used to execute DDL statements, Stored Procedures and Stored Functions.

Here is an example of calling DDL Statement.

jdbcTemplate.execute("create table ACCOUNT (id integer, name varchar(100))");

Execute Stored Procedure and Stored Function

Here is an example of calling Stored Procedure.

jdbcTemplate.update("call USER_COUNT(?)", 25L);

JDBCTemplate best practices.

Instances of the JdbcTemplate class are thread-safe once configured. This is important because it means that you can configure a single instance of a JdbcTemplate and then safely inject this shared reference into multiple DAOs (or repositories). The JdbcTemplate is stateful, it contains a reference of a DataSource, but this state is not conversational state.

A common practice when using the JdbcTemplate class ( and the associated NamedParameterJdbcTemplate classes) is to configure a DataSource in your Spring configuration file, and then dependency-inject that shared DataSource bean into your DAO classes; the JdbcTemplate is created in the setter for the DataSource. Here is the sample code of best practice.

@Repository

public class CollegeDAOJDBCImpl implements CollegeDAOInt {

private JdbcTemplate jdbcTemplate;

@Autowired

public void setDataSource(DataSource dataSource) {

this.jdbcTemplate = new JdbcTemplate(dataSource);

}

Example of CRUD Operations

Native Approach (Recommended)

Recommended approach is to inject the Datasource and create JdbcTemplate object in the DAO WITHOUT inheriting JdbcDaoSuppot class.

Step #1 : Create College POJO class that's object will represent a record in COLLEGE table.

public class College{

protected long id;

//...

}

Step #2 : Create RowMapper class that will map a record from COLLEGE table to College object.

public class CollegeMapper implements RowMapper<College> {

public College mapRow(ResultSet rs, int args) throws SQLException {

College dto = new College();

dto.setId(rs.getLong("id"));

//...

return dto;

}

Step #3 : Create interface CollegeDAOInt that contains CRUD methods.

public interface CollegeDAOInt {

public long add(College dto);

public long update(College dto);

public College delete(long id);

public College findByName(String name);

public College findByPK(long pk);

public List search(College dto);

}

Step #4: Create class CollegeDAOImpl that will implement CollegeInt interface.

@Repository

public class CollegeDAOJDBCImpl implements CollegeDAOInt {

private JdbcTemplate jdbcTemplate;

@Autowired

public void setDataSource(DataSource dataSource) {

this.jdbcTemplate = new JdbcTemplate(dataSource);

}

Step #5: Configure datasource in applicationContext.xml.

</bean>

Step #6: Test bean

ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");

CollegeDAOInt dao = (CollegeDAOInt) context.getBean("collegeDAO");

College dto = new College();

dto.setId(1L);

dto.setName("Vijay");

dto.setAddress("Mumbai");

dto.setCity("Indore");

dto.setState("MP");

dto.setPhoneNo("99999999");

dao.add(dto);

Service Class

Service class contains business logic. It is defined by @Service annotation. It does transaction handling with help of Spring AOP.

There are two ways to apply transactions:

XML Configuration
@Transactional annotation

XML Configuration

Here is sample code of UserService and its respective configuration setting in applicationContext.xml.

public class UserServiceImpl implements UserServiceInt {

private UserDAOInt dao = null;

private static Logger log = Logger.getLogger(UserServiceImpl.class);

public long add(UserDTO dto) {

long pk = dao.add(dto);

return pk;

}

applicationContext.xml configuration

<bean id="userService" class="com.sunilos.service.UserServiceImpl"

autowire="byType" />

<bean id="jdbcTransactionManager"

class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

</bean>

<!-- The transactional advice contains methods and attribute mapping for

JDBC Transaction Manager -->

<tx:advice id="jdbcTxAdvice" transaction-manager="jdbcTransactionManager">

<tx:attributes>

<tx:method name="find*" read-only="true" />

<tx:method name="search*" read-only="true" />

<tx:method name="*" propagation="REQUIRED" />

</tx:attributes>

</tx:advice>

<!-- ensure that the above transactional advice runs for any execution of

an operation defined by the *DAOHibImpl* classes -->

<aop:config>

<aop:pointcut id="serviceOperations"

expression="execution(* com.sunilos.service.*ServiceImpl.*(..))" />

<aop:advisor advice-ref="jdbcTxAdvice" pointcut-ref="serviceOperations" />

</aop:config>

Annotation @Transactional

Here is sample beans College and its applicationContext.xml configuration that applies annotation based transaction.

@Service(value = "collegeService")

public class CollegeServiceImpl implements CollegeServiceInt {

@Autowired

private CollegeDAOInt dao = null;

@Transactional(readOnly = true)

public College get(long id) {

return dao.findByPK(id);

}

@Transactional(propagation = Propagation.REQUIRED)

public long add(College dto) {

long id = dao.add(dto);

return id;

}

applicationContext.xml configuration

<context:component-scan base-package="com.sunilos.annotation" />

<tx:annotation-driven transaction-manager="transactionManager" />

Test case

UserServiceTestase

CollegeServiceTestcase

AccountServiceTestcase

Maven Dependency

<!-- JDBC Data Access Library (depends on spring-core, spring-beans, spring-context,

spring-tx) Define this if you use Spring's JdbcTemplate API (org.springframework.jdbc.*) -->

<groupId>org.springframework</groupId>

<artifactId>spring-jdbc</artifactId>

<version>3.0.5.RELEASE</version>

</dependency>