Using Spring with JDBC is not a very common usage I have seen but sometimes you have applications with very few tables and simple CRUD operation and you do not need full fledged ORM framework like hibernate .

JDBC API provides connectivity between the database and java program. It provides support for wide range of databases from complex SQL – Based databases to tabular data sources like spread sheets.  With JDBC – enabled driver one can connect to database and run the queries, fetch data etc in a java program.

JDBC API is widely used to access db in java applications. Often hibernate and JDBC gets compared and Hibernate looks better than JDBC API. But Hibernate it has a big learning curve. People who are aware of JDBC sometime want their projects to be completed fast and might not want to switch to ORM.  In this case one can use JDBC with Spring.

Spring simplifies working in JDBC API with its JDBC abstraction framework. Many low level details of the JDBC like connection creation, specifying statements and its execution etc. are managed by this JDBC abstraction framework. It provides the JdbcTemplate class in this framework, which handles all the creation and release of resources, preparing statements, execution of the query etc. This is very useful and thus simplifies the coding and also minimizes effort and common errors in the code.

You can find more details about the API and usage at

http://static.springsource.org/spring/docs/2.0.8/api/org/springframework/jdbc/core/JdbcTemplate.html

and

http://static.springsource.org/spring/docs/2.0.8/reference/jdbc.html

Below explained a sample project to save user contact data using Spring and JDBC. This also gives exposure to the code.  You can download the full code at the end of the article.

Requirements:

Eclipse 3.0   or  myeclipse 6.0 or above

Mysql Db

This project has following parts

1. The Entity class. There is no mapping in the class to table like ORM. It is just a simple bean which represents a user Contact. Client communicates to server using this bean.
2. The DAO class which does the querying, save, update and deletion part for the Contact table
3. The service class which has the business logic, which intern calls DAO class for database operations.
4. The client code which calls service layer.
5. The spring configuration file application.xml

Download spring from :http://www.springsource.org/download

Create following table and database in mysql db:

> Create database jdbctest;

> Connect jdbctest;

> Create table contact(

Id int(11) not null auto_increment,

Firstname varchar(20) not null,

Lastname varchar(20) not null,

Email varchar(100) not null,

Primary key(id));

Now create an eclipse project by name ‘JDBCtest’ and add below listed jars in your project buildpath / classpath (they can found in downloaded spring).A step by step procedure to create project in eclipse are explained here.

aopalliance.jar

asm-2.2.3.jar

asm-commons-2.2.3.jar

asm-util-2.2.3.jar

aspectjrt.jar

aspectjweaver.jar

cglib-nodep-2.1_3.jar

commons-attributes-api.jar

commons-attributes-compiler.jar

commons-logging.jar

log4j-1.2.14.jar

ojdbc14-j2ee-1.3.jar

spring.jar

spring-mock-2.0.7.jar

mysql-connector-java-5.1.5-bin.jar

The Contact  Bean :This is a simple bean with user contact information:

package com.test.entities;

public class ContactEO {

private String firstName;

private String lastName;

private String email;

private Integer id;

public String getFirstName() {

return firstName;

}

public void setFirstName(String firstName) {

this.firstName = firstName;

}

public String getLastName() {

return lastName;

}

public void setLastName(String lastName) {

this.lastName = lastName;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

}

The DAO class is a Data access class used to do operations on Contact table like querying, saving etc.

package com.test.dao;

import java.util.Collection;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Component;

import com.test.entities.ContactEO;

@Component

public class ContactDAO {

@Autowired

private JdbcTemplate jdbcTemplate;

public Collection<ContactEO> getAllRecords(){

return this.jdbcTemplate.query( “select id, firstname,lastname,email from ” +

“contact”, new ContactMappingExtractor());

}

public void addUser(ContactEO contact){

this.jdbcTemplate.update(

“insert into contact (firstname, lastname, email) ” +

“values (?,?,?)”,

new Object[] {contact.getFirstName(), contact.getLastName(),contact.getEmail()});

}

public void deleteUser(ContactEO contact){

this.jdbcTemplate.update(

“delete from contact where id=? “,

new Object[] {contact.getId()});

}

}

Here the jdbcTemplate is marked as @Autowired. This means spring will initialize this property for us.

Also the getAllRecords function uses a instance of ContactMappingExtractor() in query function. The query method on jdbcTemplate returns collection of objects. The 2^nd parameter to it is called a RowMapper. RowMapper converts each row data into a bean object. Developer has to map the row into Contact Bean. To do so we need to define a class say ContactMappingExtractor which implements RowMapper interface() and define mapRow function in it. (Shown below)

package com.test.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.test.entities.ContactEO;

public class ContactMappingExtractor  implements RowMapper {

public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

ContactEO contact = new ContactEO();

contact.setEmail(rs.getString(“email”));

contact.setFirstName(rs.getString(“firstname”));

contact.setId(rs.getInt(“id”));

contact.setLastName(rs.getString(“lastname”));

return contact;

}

}

Once this id done we define service class and business methods into it which uses ContactDAO. We make ContactDAO autowired.(Spring will do the initialization for us)

Service class is a simple class with all business methods.

@Component(“contactService”)

public class ContactServiceImpl implements ContactService {

@Autowired

ContactDAO userDAO;

public void registerUser(ContactEO contact) throws Exception{

if(contact.getEmail() == null)

throw new Exception(“Email Required!”);

userDAO.addUser(contact);

}

public void deleteUser(ContactEO contact) throws Exception{

if(contact.getId() == null)

throw new Exception(“Id Required!”);

userDAO.deleteUser(contact);

}

public List<ContactEO> getAllContacts(){

Collection<ContactEO> contacts =  userDAO.getAllRecords();

List<ContactEO> retList = new ArrayList<ContactEO>(contacts);

return retList;

}

}

Now its time to define application.xml file which looks like below:

<?xml version=”1.0″ encoding=”UTF-8″?>

<beans xmlns=”http://www.springframework.org/schema/beans” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”

xmlns:p=”http://www.springframework.org/schema/p” xmlns:aop=”http://www.springframework.org/schema/aop”

xmlns:context=”http://www.springframework.org/schema/context” xmlns:jee=”http://www.springframework.org/schema/jee”

xmlns:tx=”http://www.springframework.org/schema/tx”

xsi:schemaLocation=”

http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd

http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd”>

<context:property-placeholder location=”classpath:jdbc.properties”/>

<!– Enter your database information here –>

<bean id=”dataSource” destroy-method=”close”>

<property name=”driverClassName” value=”${jdbc.driverClassName}”/>

<property name=”url” value=”${jdbc.url}”/>

<property name=”username” value=”${jdbc.username}”/>

<property name=”password” value=”${jdbc.password}”/>

</bean>

<!– Activates various annotations to be detected in bean classes for eg @Autowired–>

<context:annotation-config/>

<!– enable the configuration of transactional behavior based on annotations –>

<tx:annotation-driven transaction-manager=”txManager”/>

<!– Transactional properties applied on data source –>

<bean id=”txManager”>

<property name=”dataSource” ref=”dataSource”/>

</bean>

<bean id=”jdbcTemplate”

>

<property name=”dataSource”>

<ref bean=”dataSource” />

</property>

</bean>

<context:component-scan base-package=”com.test.service”/>

<context:component-scan base-package=”com.test.dao”/>

</beans>

(Your database settings like the connection string, drivername, db username and password etc will be read from a file jdbc.properties which is placed in the src folder)

Here first we initialize dataSource

<bean id=”dataSource” destroy-method=”close”>

<property name=”driverClassName” value=”${jdbc.driverClassName}”/>

<property name=”url” value=”${jdbc.url}”/>

<property name=”username” value=”${jdbc.username}”/>

<property name=”password” value=”${jdbc.password}”/>

</bean>

Then we initialize the JDBC abstraction framework like below sending the datasource to it:

<bean id=”jdbcTemplate”

>

<property name=”dataSource”>

<ref bean=”dataSource” />

</property>

</bean>

by adding :

<context:component-scan base-package=”com.test.service”/>

<context:component-scan base-package=”com.test.dao”/>

We ensure that the classes marked @Component get created when spring loads

by adding :

<context:annotation-config/>

we ensure that all the service and dao classes get scanned for autowired variables and they get initialized.

Now its time to write the client program.  First we have to get the service class object from spring. We do so by spring’s application context as below:

ClassPathXmlApplicationContext appContext = new ClassPathXmlApplicationContext(new String[] {

“applicationContext.xml”

});

contactService = (ContactService) appContext.getBean(“contactService”);

One can save a user contact by calling:

contactService.registerUser(contact);

To download full code for the project click here. You can also import this project into eclipse workspace.

You can run the client code at: com.test.client.Test class and insert, delete and query data.