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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s