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>.

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 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.

JDBC MySQL Maven working example

Here’s a minimal working example of Java connecting to MySQL using the JDBC interface materialised by the MySQL driver connector.

First off we need a new database, a user, privileges and a new table with some values here’s the 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 TEST_TABLE (
		id INT NOT NULL AUTO_INCREMENT,
		TEXT VARCHAR(400) NOT NULL,
		PRIMARY KEY (ID)
	);

INSERT INTO TEST_TABLE values (default, 'test text');

Since we are using Maven the POM looks like this:

4.0.0
  com.dimitrisli
  javaMysql
  0.0.1-SNAPSHOT

  	JavaMySQL
  	

  			org.apache.maven.plugins
  			maven-compiler-plugin
  			2.3.2

  				<source />1.6
  				1.6
  				UTF-8




  		mysql
  		mysql-connector-java
  		5.1.18


  
  	UTF-8

Notice how conveniently we are bringing the MySQL JDBC driver.

The main Class that deals with the JDBC connection is as follows:

package com.dimitrisli.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class MySQLJava {

	enum TestTableColumns{
		id,TEXT;
	}

	private final String jdbcDriverStr;
	private final String jdbcURL;

	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;
	private PreparedStatement preparedStatement;

	public MySQLJava(String jdbcDriverStr, String jdbcURL){
		this.jdbcDriverStr = jdbcDriverStr;
		this.jdbcURL = jdbcURL;
	}

	public void readData() throws Exception {
		try {
			Class.forName(jdbcDriverStr);
			connection = DriverManager.getConnection(jdbcURL);
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from javaTestDB.test_table;");
			getResultSet(resultSet);
			preparedStatement = connection.prepareStatement("insert into javaTestDB.test_table values (default,?)");
			preparedStatement.setString(1,"insert test from java");
			preparedStatement.executeUpdate();
		}finally{
			close();
		}
	}

	private void getResultSet(ResultSet resultSet) throws Exception {
		while(resultSet.next()){
			Integer id = resultSet.getInt(TestTableColumns.id.toString());
			String text = resultSet.getString(TestTableColumns.TEXT.toString());
			System.out.println("id: "+id);
			System.out.println("text: "+text);
		}
	}

	private void close(){
		try {
			if(resultSet!=null) resultSet.close();
			if(statement!=null) statement.close();
			if(connection!=null) connection.close();
		} catch(Exception e){}
	}
}

Few things to notice here:

  • During runtime the MySQL driver is loaded into classpath using the reflective Class.forName(); By the way, since JDBC 4.0, this is not necessary and this line can be omitted.
  • By calling the static factory method DriverManager.getConnection() behind the scenes the registered JDBC driver is serving this request. Pure reflection magic.
  • Out of the materialised connection we creating our statement upon which we execute the query.
  • ResultSet is picking up the results and we query it based on the name of the column and the type we are expecting. Notice we receive the ID which is integer in the Integer wrapper class guarding against null returns in case of non-existence.
  • Another way to query is using preparedStatement where the query string includes question marks for all the inputs and by calling type specific setter classes we securely setting the input.
  • Notice how a try {}finally{} block is guarding all the SQL operations leading to an impossible to escape finally{close();} where we release safely all the resources. Even if there was a return statement or a caught/uncaught exception was thrown, regardless the finally block would be first executed before exiting the try block.

Finally the main method that kickstarts the JVM looks like this:

package com.dimitrisli.mysql;

public class Main {
	public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	public static final String MYSQL_URL = "jdbc:mysql://localhost/javaTestDB?"
											+ "user=javauser&password=javapass";

	public static void main(String[] args) throws Exception {
		MySQLJava dao = new MySQLJava(MYSQL_DRIVER,MYSQL_URL);
		dao.readData();
	}
}

The code can be found in this Github repository.