Spring JdbcDaoSupport, DBCP, MySQL demonstration

Following up from the Spring MySQL JdbcDaoSupport demonstration example, we augment this to add DBCP support in our application.

All we have to do is first Add the DBCP dependency in the POM file:

    <dependency>
    	<groupId>commons-dbcp</groupId>
    	<artifactId>commons-dbcp</artifactId>
    	<version>1.4</version>
    </dependency>

and then add the DBCP related datasource in the datasource bean config file:

<bean id="dataSourceDBCP" 
         class="org.apache.commons.dbcp.BasicDataSource">
	<property name="driverClassName" value="${jdbc.driverClassName}" />
	<property name="url" value="${jdbc.url}" />
	<property name="username" value="${jdbc.username}" />
	<property name="password" value="${jdbc.password}" />
	<property name="initialSize" value="${dbcp.initialSize}" />
	<property name="maxActive" value="${dbcp.maxActive}" />
</bean>

In this bean we setup our database connection pool to have an initial size of 5 db connection that can be increased up to 10 connections.

Then, thanks to the dependency injection magic, all we have to do is change the name of the dataSource bean to the DAO bean config file:

   <bean id="personSupportDao" class="com.dimitrisli.springMySQL.dao.PersonDaoSupportImpl" >
   		<property name="dataSource" ref="dataSourceDBCP"></property>
   </bean>

The source code can be found <a href="https://github.com/dimitrisli/SpringJdbcSupportMySQL">in this Github repository</a>.
Advertisements

Sample Spring Database Configuration

This is a sample Spring Database context:

<?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:tx="http://www.springframework.org/schema/tx"
       xmlns:c="http://www.springframework.org/schema/c"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

    <bean  class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
           p:location="properties/database.properties" />

    <bean  id="dataSource"
           class="org.apache.commons.dbcp.BasicDataSource"
           destroy-method="close"
           p:driverClassName="${jdbc.driverClassName}"
           p:url="${jdbc.url}"
           p:username="${jdbc.username}"
           p:password="${jdbc.password}" />

    <bean  class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"
           c:dataSource-ref="dataSource"  />

    <bean  id="jdbcTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
            p:dataSource-ref="dataSource"/>
    <tx:annotation-driven transaction-manager="jdbcTransactionManager"/>

</beans>

Notes:

  • PropertyPlaceholder bean is loading up our database properties file
  • We are using a DBCP BasicDataSource as our datasource where we are setting user/pass/url/driver parameters from the properties files
  • We are supposing that DBCP, Spring, DB Driver related jars are all found in the classpath most commonly by a Maven POM configuration
  • NamedParameterJdbcTemplate is set in this config file so that  it can be conveniently referred from the application to be injected by will. Note the datasource reference we are passing as a parameter to its bean
  • Spring DataSourceTransactionManager is getting used instantiating its bean with the datasource. That will allow us to mark methods @Transactional which will get intercepted by Spring AOP and read our parameter/attribute strategies and other setup as per the ACID operations.

Spring, Hibernate, MySQL integration example

This is a working example showcasing Spring, Hibernate and MySQL integration in a Maven project.

The POM looks like this:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.dimitrisli</groupId>
  <artifactId>SpringMySQL</artifactId>
  <version>1.0</version>
  <build>
  	<finalName>SpringHibernateMySQL</finalName>
  	<plugins>
  		<plugin>
  			<groupId>org.apache.maven.plugins</groupId>
  			<artifactId>maven-compiler-plugin</artifactId>
  			<version>2.3.2</version>
  			<configuration>
  				<source>1.6</source>
  				<target>1.6</target>
  				<encoding>UTF-8</encoding>
  			</configuration>
  		</plugin>
  	</plugins>
  </build>
  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.18</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>3.1.0.RELEASE</version>
      <scope>compile</scope>
    </dependency>
	<dependency>
	  <groupId>org.hibernate</groupId>
	  <artifactId>hibernate-core</artifactId>
	  <version>3.6.9.Final</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-orm</artifactId>
		<version>3.1.0.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>javassist</groupId>
		<artifactId>javassist</artifactId>
		<version>3.12.1.GA</version>
	</dependency>
  </dependencies>
  <properties>
  	<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
</project>

Our MySQL database script:

create database javaTestDB;
use javaTestDB;
CREATE USER javauser IDENTIFIED BY 'javapass';
grant usage on *.* to javauser@localhost identified by 'javapass';
grant all privileges on javaTestDB.* to javauser@localhost;

CREATE TABLE PERSON_RECORD (
	ID INT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(15) NOT NULL,
	SURNAME VARCHAR(15) NOT NULL,
	PRIMARY KEY (ID)
);

This is basically a table that is representing our Person Java bean:

package com.dimitrisli.springHibernateMySQL.model;

import java.io.Serializable;

public class Person implements Serializable {

	private static final long serialVersionUID = 1L;
	private Integer id;
	private String name;
	private String surname;

	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSurname() {
		return surname;
	}
	public void setSurname(String surname) {
		this.surname = surname;
	}

	public void setId(Integer id){
		this.id = id;
	}

	public Integer getId(){
		return this.id;
	}
}

Each DB table record represents a Person Object. The Person CRUD operations are represented in this interface:

package com.dimitrisli.springHibernateMySQL.dao;

import com.dimitrisli.springHibernateMySQL.model.Person;

public interface PersonORMDao {

	public void create(Person person);
	public Person read(String name, String surname);
	public void update(Person person);
	public void delete(Person person);
}

Since we are using Spring it’s a good practise to have an associated interface for every DAO bean we are planning to have since Spring behind the scenes is creating a proxy between this interface and the POJO before creating the bean.

The implementation of this interface looks like this:

package com.dimitrisli.springHibernateMySQL.dao;

import java.util.List;

import com.dimitrisli.springHibernateMySQL.model.Person;

import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

public class PersonDaoHibernateImpl extends HibernateDaoSupport implements PersonORMDao{

	public void create(Person person) {
		getHibernateTemplate().save(person);
	}

	public Person read(String name, String surname) {
		List list = getHibernateTemplate().find("from PERSON_RECORD where NAME=? AND SURNAME=?",name,surname);
		return (Person) list.get(0);
	}

	public List readAll() {
		List list = getHibernateTemplate().find("from " + Person.class.getName());
		return list;
	}

	public void update(Person person) {
		getHibernateTemplate().update(person);
	}

	public void delete(Person person) {
		getHibernateTemplate().delete(person);
	}

	public void deleteAll() {
		getHibernateTemplate().deleteAll(readAll());
	}
}

Notice that this DAO implementation is extending from HibernateDaoSupport which is a Spring Hibernate convenience abstract class and is using its getHibernateTemplate() for all the data access operations. Also notice the hibernate query language (HQL) to express sql where conditions.

We’ll pack the property related files under src/main/resources grouped in meaningful folders maintaining a good housekeeping.

The Hibernate related DB mapping into is maintained under resources/hibernate/person.hbm.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="com.dimitrisli.springHibernateMySQL.model.Person" table="PERSON_RECORD">
        <id name="id" type="java.lang.Integer">
            <column name="ID" />
            <generator class="identity" />
        </id>
        <property name="name" type="string">
            <column name="NAME" length="15" not-null="true" unique="false" />
        </property>
        <property name="surname" type="string">
            <column name="SURNAME" length="15" not-null="true" unique="false" />
        </property>
    </class>
</hibernate-mapping>

MySQL related information like the driver name, the connection URL, the username/pass are found in the resources/properties/database.properties:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/javaTestDB
jdbc.username=javauser
jdbc.password=javapass

The database related beans are stored in resources/spring/database/database.xml

<?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-3.1.xsd">

<bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<property name="location">
		<value>properties/database.properties</value>
	</property>
</bean>

<bean id="dataSource"
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<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>

</beans>

Things to notice:

  • Spring’s PropertyPlaceholderConfigurer is conveniently reading the database properties and make them available to the datasource bean.

The Hibernate session factory represented in a LocalSessionFactoryBean is stored in the resources/spring/database/hibernate.xml:

<?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-3.1.xsd">
 
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource">
      <ref bean="dataSource"/>
    </property>
    <property name="hibernateProperties">
       <props>
         <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
         <prop key="hibernate.show_sql">true</prop>
       </props>
     </property>
 	 <property name="mappingResources">
		<list>
           <value>/hibernate/person.hbm.xml</value>
		</list>
     </property>
    </bean>
</beans>

Note how the mapping config file is loaded as a property on the bean. Also how we are activating the HQL and show the output.

the final configuration file is gathering all the resources and found in resources/spring/config:

<?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-3.0.xsd">
 
	<import resource="../database/datasource.xml" />
	<import resource="../database/hibernate.xml" />
 	<import resource="../beans/beans.xml" />
 	
</beans>

Finally the test class that is creating the application context and making some basic demonstration queries:

package com.dimitrisli.springHibernateMySQL.test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.dimitrisli.springHibernateMySQL.dao.PersonDaoHibernateImpl;
import com.dimitrisli.springHibernateMySQL.model.Person;

public class Main {

	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext("spring/config/beanLocations.xml");
		PersonDaoHibernateImpl personDao = (PersonDaoHibernateImpl) context.getBean("personHibernateDao"); 
		
		System.out.println("Adding data...");
		Person dimitris = new Person(); dimitris.setName("dimitris"); dimitris.setSurname("liapis");
		Person michalis = new Person(); michalis.setName("michalis"); michalis.setSurname("liapis");
		Person kostas = new Person(); kostas.setName("kostas"); kostas.setSurname("liapis");
		Person stella = new Person(); stella.setName("stella"); stella.setSurname("liapis");
		personDao.create(dimitris);
		personDao.create(michalis);
		personDao.create(kostas);
		personDao.create(stella);
		
		System.out.println("Retrieving data..");
		for(Person currentPerson : personDao.readAll()){
			System.out.println("From DB: name="+currentPerson.getName()+", surname="+currentPerson.getSurname());
		}
		
		System.out.println("Deleting a record and viewing...");
		personDao.delete(kostas);
		for(Person currentPerson : personDao.readAll()){
			System.out.println("From DB: name="+currentPerson.getName()+", surname="+currentPerson.getSurname());
		}
		
		System.out.println("Updating a record and viewing...");
		dimitris.setName("newDimitrisName");
		personDao.update(dimitris);
		for(Person currentPerson : personDao.readAll()){
			System.out.println("From DB: name="+currentPerson.getName()+", surname="+currentPerson.getSurname());
		}
		
		System.out.println("Deleting everything and viewing...");
		personDao.deleteAll();
		for(Person currentPerson : personDao.readAll()){
			System.out.println("From DB: name="+currentPerson.getName()+", surname="+currentPerson.getSurname());
		}
		
	}
}

The output, subtracting the initialisation coding of the frameworks, looks like this:

Adding data...
Hibernate: insert into PERSON_RECORD (NAME, SURNAME) values (?, ?)
Hibernate: insert into PERSON_RECORD (NAME, SURNAME) values (?, ?)
Hibernate: insert into PERSON_RECORD (NAME, SURNAME) values (?, ?)
Hibernate: insert into PERSON_RECORD (NAME, SURNAME) values (?, ?)
Retrieving data..
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_
From DB: name=newDimitrisName, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
From DB: name=dimitris, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=kostas, surname=liapis
From DB: name=stella, surname=liapis
Deleting a record and viewing...
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_
From DB: name=newDimitrisName, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
From DB: name=dimitris, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
Updating a record and viewing...
Hibernate: update PERSON_RECORD set NAME=?, SURNAME=? where ID=?
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_
From DB: name=newDimitrisName, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
From DB: name=newDimitrisName, surname=liapis
From DB: name=michalis, surname=liapis
From DB: name=stella, surname=liapis
Deleting everything and viewing...
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: delete from PERSON_RECORD where ID=?
Hibernate: select person0_.ID as ID0_, person0_.NAME as NAME0_, person0_.SURNAME as SURNAME0_ from PERSON_RECORD person0_

The source code can be found in this Github repository.

Spring JUnit How to Rollback Database Transaction

It’s common when you are testing your DAOs at a safe TDD environment to perform the DB action, assertThat what you’ve done is as expected and then safely rollback the transaction. All should look untouched at the DB level (except the DB sequences that are always moving forward) thereafter.

The annotations needed in the JUnit test class to achieve this are:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "/pathTo/spring/context/applicationContext.xml")
@TransactionConfiguration(transactionManager = "jdbcTransactionManager", defaultRollback = true)
@Transactional
public class LocationDaoTest {
  @Inject daoInTest;
  @Test
  public void testDAOsCRUD(){}
}

Notes:

  • The ContextConfiguration annotation points to the main application’s context configuration file.
  • The TransactionConfiguration annotation points to your transaction manager defined in your application context which can either be a DataSourceTransactionManager for JDBC operations or a HibernateTransactionManager for Hibernate related ORM operations.
  • The defaultRollback=true attribute of the TransactionConfiguration is responsible for rolling back safely our test transactions.

Maven Integration Tests

Unit tests shouldn’t take long to complete and by definition should test a unit in isolation. Therefore unit tests shouldn’t include database or web framework resources.

Integration Testing is responsible putting everything together and check whether things work as expected. Maven has a special phase in its lifecycle called integration-tests that accommodates and executes integration related tests by calling mvn integration-test. A nice technique a colleague suggested is to have a name convention for integration tests and explicitly exclude/include during related phases in the Maven Surefire plugin. Assuming the naming convention referring to classes meant to be Integration Tests have the “IntegrationTest” suffix, the POM configuration of the maven-surefire-plugin would look something like this:

  <build>
  ...
   	<plugins>
   		<plugin>
   			<artifactId>maven-surefire-plugin</artifactId>
   			<executions>
   				<execution>
   					<id>unit-tests</id>
   					<phase>test</phase>
   					<goals>
   						<goal>test</goal>
   					</goals>
   					<configuration>
	   					<excludes>
	   						<exclude>**/*IntegrationTest.java</exclude>
	   					</excludes>
   					</configuration>
   				</execution>
   				<execution>
   					<id>integration-tests</id>
   					<phase>integration-test</phase>
   					<goals>
   						<goal>test</goal>
   					</goals>
   					<configuration>
   						<includes>
   							<<include>**/*IntegrationTest.java</include>
   						</includes>
   					</configuration>
   				</execution>
   			</executions>
   		</plugin>
   ...... 
    </plugins>
.....
   </build>

Spring JdbcDaoSupport, MySQL demonstration

This is a variation of the Spring MySQL Jdbc demonstration article that we will alter to use Spring’s JdbcDaoSupport abstract Class.

Our DaoSupport Class bean will read now:

package com.dimitrisli.springMySQL.dao;

import java.util.List;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.dimitrisli.springMySQL.model.Person;


public class PersonDaoSupportImpl extends JdbcDaoSupport implements PersonDao{

	public void createPerson(String name, String surname) {
		getJdbcTemplate().update("INSERT INTO PERSON_RECORD (NAME,SURNAME) VALUES (?,?)",
				new Object[]{name,surname});
	}

	public List<Person> selectPerson(String name, String surname) {
		return getJdbcTemplate().query("SELECT NAME,SURNAME FROM PERSON_RECORD WHERE NAME=? AND SURNAME=?",
				new Object[] {name,surname},
				new PersonRowMapper());
	}

	public List<Person> selectAll() {
		return getJdbcTemplate().query("SELECT NAME,SURNAME FROM PERSON_RECORD",
				new PersonRowMapper());
	}

	public void deletePerson(String name, String surname) {
		getJdbcTemplate().update("DELETE FROM PERSON_RECORD WHERE NAME=? AND SURNAME=?",
				new Object[]{name,surname});
	}

	public void deleteAll() {
		getJdbcTemplate().update("DELETE FROM PERSON_RECORD");
	}

	
}

while the bean declaration of this Class is similar in that it gets the dataSource injected:


   <bean id="personSupportDao" class="com.dimitrisli.springMySQL.dao.PersonDaoSupportImpl" >
   		<property name="dataSource" ref="dataSource"></property>
   </bean>

The difference is that we don’t need in our class a setter method since super class JdbcDaoSupport is taking care of that.

The code can be found in this Github repository.

Spring JdbcTemplate, MySQL demonstration

This is a demonstration of Spring, MySQL and Maven integration and a quick showcase of database CRUD operations using Spring’s JdbcTemplate.

First off the POM:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.dimitrisli</groupId>
  <artifactId>SpringMySQL</artifactId>
  <version>1.0</version>
  <build>
  	<finalName>SpringMySQL</finalName>
  	<plugins>
  		<plugin>
  			<groupId>org.apache.maven.plugins</groupId>
  			<artifactId>maven-compiler-plugin</artifactId>
  			<version>2.3.2</version>
  			<configuration>
  				<source>1.6</source>
  				<target>1.6</target>
  				<encoding>UTF-8</encoding>
  			</configuration>
  		</plugin>
  	</plugins>
  </build>
  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.18</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>3.1.0.RELEASE</version>
      <scope>compile</scope>
    </dependency>
  </dependencies>
  <properties>
  	<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
</project>

Things to notice:

  • Adding the spring-jdbc dependency is enough to bring all the spring jars required including the spring-context that is integral for creating the application context. The minimum number of spring dependencies we need are:
    • spring-beans
    • spring-core
    • spring-asm
    • commons-logging
    • spring-context
    • spring-expression
  • mysql-connector-java is the java MySQL driver. Effortlessly added thanks to the Maven central repo.

The Bean we are going to deal with is a simple POJO bean representing a Person having a name and a surname:

package com.dimitrisli.springMySQL.model;

public class Person {

	private String name;
	private String surname;

	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSurname() {
		return surname;
	}
	public void setSurname(String surname) {
		this.surname = surname;
	}
}

The MySQL script to create the database, user, privileges and table:

create database javaTestDB;
use javaTestDB;
CREATE USER javauser IDENTIFIED BY 'javapass';
grant usage on *.* to javauser@localhost identified by 'javapass';
grant all privileges on javaTestDB.* to javauser@localhost;

CREATE TABLE PERSON_RECORD (
	ID INT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(15) NOT NULL,
	SURNAME VARCHAR(15) NOT NULL,
	PRIMARY KEY (ID)
);

Each row in the database corresponds to a Person object and Spring does this transition for us using the RowMapper:

package com.dimitrisli.springMySQL.dao;

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

import org.springframework.jdbc.core.RowMapper;

import com.dimitrisli.springMySQL.model.Person;

public class PersonRowMapper implements RowMapper<Person> {

	public Person mapRow(ResultSet resultSet, int arg1) throws SQLException {
		Person person = new Person();
		person.setName(resultSet.getString(1));
		person.setSurname(resultSet.getString(2));
		return person;
	}
}

In big projects good housekeeping principles are rewarded along the way so although a small test case project we’ll follow a structured way storing our resources.

First, under resources/properties/database.properties:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/javaTestDB
jdbc.username=javauser
jdbc.password=javapass

We’ll store the Spring related resources under resources/spring.

For the dataSource we have resources/spring/database/datasource.xml:

<?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-3.1.xsd">

<bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<property name="location">
		<value>properties/database.properties</value>
	</property>
</bean>

<bean id="dataSource"
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<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>

</beans>

Things to notice here:

  • Spring’s PropertyPlaceholderConfigurer is taking good care of retrieving property entries and make it available in the Spring xml config files.
  • Spring’s DriverManagerDataSource is picking up the database property specifics and initialising the dataSource for us.

For the PersonDao now we first create an interface. It’s a good practice to hide our implementation behind an interface when using Spring since behind the scenes when the Spring framework is creating a bean it is creating a proxy between our interface and the implementation.

Our CRUD operations are captured in the PersonDao Interface:

package com.dimitrisli.springMySQL.dao;

import java.util.List;

import com.dimitrisli.springMySQL.model.Person;

public abstract interface PersonDao {

	public abstract void createPerson(String name, String surname);
	public abstract List<Person> selectPerson(String name, String surname);
	public abstract List<Person> selectAll();
	public abstract void deletePerson(String name, String surname);
	public abstract void deleteAll();
}

and its implementation looks like this:

package com.dimitrisli.springMySQL.dao;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.dimitrisli.springMySQL.model.Person;

public class PersonDaoImpl implements PersonDao{

	DataSource dataSource;

	public void createPerson(String name, String surname) {
		JdbcTemplate createPerson = new JdbcTemplate(dataSource);
		createPerson.update("INSERT INTO PERSON_RECORD (NAME,SURNAME) VALUES (?,?)",
				new Object[]{name,surname});
	}

	public List<Person> selectPerson(String name, String surname) {
		JdbcTemplate selectPerson = new JdbcTemplate(dataSource);
		return selectPerson.query("SELECT NAME,SURNAME FROM PERSON_RECORD WHERE NAME=? AND SURNAME=?",
				new Object[] {name,surname},
				new PersonRowMapper());
	}

	public List<Person> selectAll() {
		JdbcTemplate selectPerson = new JdbcTemplate(dataSource);
		return selectPerson.query("SELECT NAME,SURNAME FROM PERSON_RECORD",
				new PersonRowMapper());
	}

	public void deletePerson(String name, String surname) {
		JdbcTemplate deletePerson = new JdbcTemplate(dataSource);
		deletePerson.update("DELETE FROM PERSON_RECORD WHERE NAME=? AND SURNAME=?",
				new Object[]{name,surname});
	}

	public void deleteAll() {
		JdbcTemplate deleteAll = new JdbcTemplate(dataSource);
		deleteAll.update("DELETE FROM PERSON_RECORD");
	}

	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
	}

}

Things to note:

  • JdbcTemplate is taking care of the DB interaction doing our CRUD operation. All it needs is the dataSource which is injected as a property bean as we’ll see further on.
  • No need to open connections, close resources or deal with the low level technicalities surrounding DB operations JdbcTemplate is doing that for free. Also the Exceptions are all Runtime in a more expressive flavour than the java.sql.SQLException that developers are welcome to catch and deal with otherwise don’t deal programatically.
  • Notice how we are passing the parameters as an array of Objects and whenever we are expecting results back we are passing our PersonRowMapper.

All beans are defined in the resources/spring/beans/beans.xml which in our case is the PersonDaoImpl having explicitly injecting the dataSource that is needed for all the CRUD operations. The dataSource interface includes two getConnection() methods responsible to setup the DB connection.

<?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-3.1.xsd">
 
   <bean id="personDao" class="com.dimitrisli.springMySQL.dao.PersonDaoImpl" >
   		<property name="dataSource" ref="dataSource"></property>
   </bean>
 
</beans>

The central point for the Spring configs which gathers up all the Spring resources is resources/spring/config/beanLocations.xml that looks like this:

<?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-3.0.xsd">
 
 	<import resource="../beans/beans.xml" />
	<import resource="../database/datasource.xml" />
 
</beans>

The code can be found in this Github repository.