Spring JDBC Tutorial

Below is a quick tutorial on how to get things up and running with Spring and JDBC. The code in the tutorial is intended to utilize a MySQL database as its data source.

Let's start the tutorial off with getting MySQL database installed. If you do not have it installed, follow this link to the MySQL download site. Follow the installation instructions and remember your database username and password.

REQUIRED LIBRARIES
Next, download all the necessary libraries needed to execute this tutorial:

• JDBC driver for MySQL, MySQL Connector/J.
• The latest spring framework. 3.0.0.M1.
• ANTLR 3.0.1
• Commons Logging.

Because you won't need the whole spring framework, below is a list of all the required Spriong libraries to build and execute the code in this tutorial:

• spring beans
• spring context
• spring core
• spring expression
• spring jdbc
• spring transaction
• mysql connector/j
• commons logging
• antlr

Make sure you add these dependencies to your build path.

CREATE THE DATABASE
Next, we'll need to create a database along with a table to act as the data source for this tutorial.

For this example, we'll create a table called siteAdmin:

CREATE TABLE siteAdmin (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
username VARCHAR (20) NOT NULL,
password VARCHAR (20) NOT NULL,
firstName VARCHAR (20) NOT NULL,
lastName VARCHAR (20) NOT NULL,
email VARCHAR (40) NOT NULL,
created TIMESTAMP NOT NULL,
lastModified TIMESTAMP NOT NULL,
status INT NOT NULL,
PRIMARY KEY (id)
);

CREATE YOUR DOMAIN MODEL
Next, generate your domain model of the siteAdmin table you just created.

package com.edwardwebnerd.persistence.model;

import java.sql.Date;

public class SiteAdmin{

private int id;
private String username;
private String password;
private String firstname;
private String lastname;
private String email;
private Date created;
private Date lastModified;
private int status;

/** @return the id */
public int getId() {
return id;
}
/** @param id the id to set */
public void setId(int id) {
this.id = id;
}
/** @return the username */
public String getUsername() {
return username;
}
/** @param username the username to set */
public void setUsername(String username) {
this.username = username;
}
/** @return the password */
public String getPassword() {
return password;
}
/** @param password the password to set */
public void setPassword(String password) {
this.password = password;
}
/** @return the firstname */
public String getFirstname() {
return firstname;
}
/** @param firstname the firstname to set */
public void setFirstname(String firstname) {
this.firstname = firstname;
}
/** @return the lastname */
public String getLastname() {
return lastname;
}
/** @param lastname the lastname to set */
public void setLastname(String lastname) {
this.lastname = lastname;
}
/** @return the email */
public String getEmail() {
return email;
}
/** @param email the email to set */
public void setEmail(String email) {
this.email = email;
}
/** @return the created */
public Date getCreated() {
return created;
}
/** @param created the created to set */
public void setCreated(Date created) {
this.created = created;
}
/** @return the lastModified */
public Date getLastModified() {
return lastModified;
}
/** @param lastModified the lastModified to set */
public void setLastModified(Date lastModified) {
this.lastModified = lastModified;
}
/** @return the status */
public int getStatus() {
return status;
}
/** @param status the status to set */
public void setStatus(int status) {
this.status = status;
}
}

CREATE YOUR DAO INTERFACE
Next, generate your DAO interface.

package com.edwardwebnerd.persistence.jdbc.dao;

import java.util.List;
import javax.sql.DataSource;
import com.edwardwebnerd.persistence.model.SiteAdmin;


public interface SiteAdminDAO {

void setDataSource(DataSource ds);

List selectAll();

void create(String username, String password, String firstName, String lastName,
String email, int status);

List select(String username, String password);

List select(int id);

List selectAll();

void delete(int id);
}

IMPLEMENT YOUR DAO

The implementing DAO class.

package com.edwardwebnerd.persistence.jdbc.dao.impl;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.edwardwebnerd.persistence.model.SiteAdmin;
import com.edwardwebnerd.persistence.jdbc.dao.SiteAdminDAO;
import com.edwardwebnerd.persistence.model.SiteAdmin;
import com.edwardwebnerd.persistence.jdbc.dao.impl.mapper.SiteAdminMapper;


public class SiteAdminDAOImpl implements SiteAdminDAO {

private DataSource dataSource;

@Override
public void setDataSource(DataSource ds) { dataSource = ds; }


@Override
public void create(String username, String password, String firstName, 
String lastName, String email, int status)
{
JdbcTemplate insert = new JdbcTemplate(dataSource);
insert.update("INSERT INTO siteAdmin " +
"(username, password, firstName, lastName, email, status)" +
" VALUES(?,?,?,?,?)",
new Object[] { username, password, firstName, lastName, email,
status});
}


@Override
public void delete(int id) 
{
JdbcTemplate delete = new JdbcTemplate(dataSource);
delete.update("DELETE from siteAdmin where id= ?",
new Object[] { id });
}


@Override
public List select(String username, String password) 
{
JdbcTemplate select = new JdbcTemplate(dataSource);
return select.query(
"select * from siteAdmin where username = ? AND password = ?",
new Object[] { username, password },
new SiteAdminMapper());
}


@Override
public List select(int id) 
{
JdbcTemplate select = new JdbcTemplate(dataSource);
return select.query(
"select * from siteAdmin where id = ?",
new Object[] { id },
new SiteAdminMapper());
}


@Override
public List selectAll() 
{
JdbcTemplate select = new JdbcTemplate(dataSource);
return select.query("select * from siteAdmin",
new SiteAdminMapper());
}
}

The implementation also uses a RowMapper class to help shorten the implementation and provide a single point of managing the mapping between the siteAdmin's table column names and the domain model's class members.

package com.edwardwebnerd.persistence.jdbc.dao.impl.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.edwardwebnerd.persistence.model.SiteAdmin;

public class SiteAdminMapper implements RowMapper {

public SiteAdminMapper(){}


@Override
public Object mapRow(ResultSet resultSet, int arg1) throws SQLException {
SiteAdmin siteAdmin = new SiteAdmin();
siteAdmin.setId(resultSet.getInt("id"));
siteAdmin.setUsername(resultSet.getString("username"));
siteAdmin.setPassword(resultSet.getString("password"));
siteAdmin.setFirstname(resultSet.getString("firstname"));
siteAdmin.setLastname(resultSet.getString("lastname"));
siteAdmin.setEmail(resultSet.getString("email"));
siteAdmin.setCreated(resultSet.getDate("created"));
siteAdmin.setLastModified(resultSet.getDate("lastModified"));
siteAdmin.setStatus(resultSet.getInt("status"));
return siteAdmin;
}
}

CREATE A SERVICE LAYER INTERFACE OVER YOUR DAO IMPLEMENTATION
Next, generate your Service interface.

package com.edwardwebnerd.persistence;

import java.util.List;

import com.edwardwebnerd.persistence.jdbc.dao.SiteAdminDAO;
import com.edwardwebnerd.persistence.model.SiteAdmin;


public interface LittleLeagueServices {

void setSiteAdminDAO(SiteAdminDAO siteAdminDAO);

List siteAdminSelectAll();
}

CREATE A SERVICE LAYER INTERFACE OVER YOUR DAO IMPLEMENTATION
Next, generate your implementing Service class.

package com.edwardwebnerd.persistence.jdbc;

import java.util.List;

import com.edwardwebnerd.persistence.LittleLeagueServices;
import com.edwardwebnerd.persistence.jdbc.dao.SiteAdminDAO;
import com.edwardwebnerd.persistence.model.SiteAdmin;


public class LittleLeagueServicesImpl implements LittleLeagueServices {

private SiteAdminDAO siteAdminDAO;

@Override
public void setSiteAdminDAO(SiteAdminDAO siteAdminDao) 
{    
siteAdminDAO = siteAdminDao;    
}

@Override
public List siteAdminSelectAll(){
return siteAdminDAO.selectAll();
}   
}

CONFIGURE SPRING
Next, we'll define an XML config file to have Spring inject the DAO Implementation into our services and inject a MySQL data source into the DAO Implmentation. Remember to replace the connection information appropriately.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

<bean id="littleLeagueServices"
class="com.edwardwebnerd.persistence.jdbc.LittleLeagueServicesImpl">
<property name="siteAdminDAO" ref="siteAdminDAO"/>
</bean>

<bean id="siteAdminDAO" 
class="com.edwardwebnerd.persistence.jdbc.dao.impl.SiteAdminDAOImpl">
<property name="dataSource" ref="dataSource"/>
</bean>

<bean id="dataSource" destroy-method="close" 
class="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
<property name="url" 
value="jdbc:mysql://localhost:3306/?autoReconnect=true"/>
<property name="user" value=""/>
<property name="password" value=""/>
</bean>
</beans> 

TRY A TEST RUN
Next, test your services with a test run class.

package com;

import java.util.List;

import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.context.ApplicationContext;

import com.edwardwebnerd.persistence.LittleLeagueServices;
import com.edwardwebnerd.persistence.model.SiteAdmin;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Logger;

public class SpringTest {

static Logger logger = Logger.getLogger(SpringTest.class );

public static void main(String [] args){
BasicConfigurator.configure();

ApplicationContext applicationContext = 
new ClassPathXmlApplicationContext("config.xml");
BeanFactory factory = applicationContext;

logger.info("LittleLeagueServices Initializing");
LittleLeagueServices littleLeagueServices = 
(LittleLeagueServices) factory.getBean("littleLeagueServices");
logger.info("LittleLeagueServices Initialized");

logger.info("LittleLeagueServices: Retrieving SiteAdmins");
List siteAdmins = littleLeagueServices.siteAdminSelectAll();
for(int i = 0; i < siteAdmins.size(); i++){
SiteAdmin siteAdmin = siteAdmins.get(i);
logger.info(siteAdmin.toString());
}
}
} 

If you have any questions on this tutorial, just comment on the blog and I'll be sure to get back to you.