Spring JDBC Example - DigitalOcean

Spring JDBC

Spring Framework provides excellent integration with JDBC API and provides JdbcTemplate utility class that we can use to avoid bolier-plate code from our database operations logic such as Opening/Closing Connection, ResultSet, PreparedStatement etc. Let’s first look at a simple Spring JDBC example application and then we will see how JdbcTemplate class can help us in writing modular code with ease, without worrying whether resources are closed properly or not. Spring Tool Suite to develop Spring based applications is very helpful, so we will use STS to create our Spring JDBC application. Our final project structure will look like below image. Create a simple Spring Maven Project from the STS Menu, you can choose whatever name you like or stick with my project name as SpringJDBCExample.

Spring JDBC Dependencies

First of all we need to include Spring JDBC and Database drivers in the maven project pom.xml file. My final pom.xml file looks like below.

<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.springframework.samples</groupId> <artifactId>SpringJDBCExample</artifactId> <version>0.0.1-SNAPSHOT</version> <properties> <!-- Generic properties --> <java.version>1.6</java.version> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <!-- Spring --> <spring-framework.version>4.0.2.RELEASE</spring-framework.version> <!-- Logging --> <logback.version>1.0.13</logback.version> <slf4j.version>1.7.5</slf4j.version> </properties> <dependencies> <!-- Spring and Transactions --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring-framework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>${spring-framework.version}</version> </dependency> <!-- Spring JDBC Support --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring-framework.version}</version> </dependency> <!-- MySQL Driver --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.0.5</version> </dependency> <!-- Logging with SLF4J & LogBack --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>${slf4j.version}</version> <scope>compile</scope> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>${logback.version}</version> <scope>runtime</scope> </dependency> </dependencies> </project>

Most of the part is automatically generated by STS, however I have update Spring Framework version to use latest version as 4.0.2.RELEASE. Also we have added required artifacts spring-jdbc and mysql-connector-java. First one contains the Spring JDBC support classes and second one is database driver. I am using MySQL database for our testing purposes, so I have added MySQL JConnector jar dependencies. If you are using some other RDBMS then you should make the corresponding changes in the dependencies.

Spring JDBC Example - Database Setup

Let’s create a simple table that we will use in our application for CRUD operations example.

CREATE TABLE `Employee` ( `id` int(11) unsigned NOT NULL, `name` varchar(20) DEFAULT NULL, `role` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Spring JDBC Example - Model Class

We will use DAO Pattern for JDBC operations, so let’s create a java bean that will model our Employee table.

package com.journaldev.spring.jdbc.model; public class Employee { private int id; private String name; private String role; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getRole() { return role; } public void setRole(String role) { this.role = role; } @Override public String toString(){ return "{ID="+id+",Name="+name+",Role="+role+"}"; } }

Spring JDBC Example - DAO Interface and Implementation

For DAO pattern, we will first have an interface declaring all the operations we want to implement.

package com.journaldev.spring.jdbc.dao; import java.util.List; import com.journaldev.spring.jdbc.model.Employee; //CRUD operations public interface EmployeeDAO { //Create public void save(Employee employee); //Read public Employee getById(int id); //Update public void update(Employee employee); //Delete public void deleteById(int id); //Get All public List<Employee> getAll(); } package com.journaldev.spring.jdbc.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import com.journaldev.spring.jdbc.model.Employee; public class EmployeeDAOImpl implements EmployeeDAO { private DataSource dataSource; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } @Override public void save(Employee employee) { String query = "insert into Employee (id, name, role) values (?,?,?)"; Connection con = null; PreparedStatement ps = null; try{ con = dataSource.getConnection(); ps = con.prepareStatement(query); ps.setInt(1, employee.getId()); ps.setString(2, employee.getName()); ps.setString(3, employee.getRole()); int out = ps.executeUpdate(); if(out !=0){ System.out.println("Employee saved with id="+employee.getId()); }else System.out.println("Employee save failed with id="+employee.getId()); }catch(SQLException e){ e.printStackTrace(); }finally{ try { ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Override public Employee getById(int id) { String query = "select name, role from Employee where id = ?"; Employee emp = null; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try{ con = dataSource.getConnection(); ps = con.prepareStatement(query); ps.setInt(1, id); rs = ps.executeQuery(); if(rs.next()){ emp = new Employee(); emp.setId(id); emp.setName(rs.getString("name")); emp.setRole(rs.getString("role")); System.out.println("Employee Found::"+emp); }else{ System.out.println("No Employee found with id="+id); } }catch(SQLException e){ e.printStackTrace(); }finally{ try { rs.close(); ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return emp; } @Override public void update(Employee employee) { String query = "update Employee set name=?, role=? where id=?"; Connection con = null; PreparedStatement ps = null; try{ con = dataSource.getConnection(); ps = con.prepareStatement(query); ps.setString(1, employee.getName()); ps.setString(2, employee.getRole()); ps.setInt(3, employee.getId()); int out = ps.executeUpdate(); if(out !=0){ System.out.println("Employee updated with id="+employee.getId()); }else System.out.println("No Employee found with id="+employee.getId()); }catch(SQLException e){ e.printStackTrace(); }finally{ try { ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Override public void deleteById(int id) { String query = "delete from Employee where id=?"; Connection con = null; PreparedStatement ps = null; try{ con = dataSource.getConnection(); ps = con.prepareStatement(query); ps.setInt(1, id); int out = ps.executeUpdate(); if(out !=0){ System.out.println("Employee deleted with id="+id); }else System.out.println("No Employee found with id="+id); }catch(SQLException e){ e.printStackTrace(); }finally{ try { ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Override public List<Employee> getAll() { String query = "select id, name, role from Employee"; List<Employee> empList = new ArrayList<Employee>(); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try{ con = dataSource.getConnection(); ps = con.prepareStatement(query); rs = ps.executeQuery(); while(rs.next()){ Employee emp = new Employee(); emp.setId(rs.getInt("id")); emp.setName(rs.getString("name")); emp.setRole(rs.getString("role")); empList.add(emp); } }catch(SQLException e){ e.printStackTrace(); }finally{ try { rs.close(); ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return empList; } }

The implementation of CRUD operations are simple to understand. If you want to learn more about DataSource, please read JDBC DataSource Example.

Spring JDBC Example - Bean Configuration

If you look at all the classes above, they are all using standard JDBC API and there is no reference to Spring JDBC framework. Spring JDBC framework classes comes into picture when we create Spring Bean Configuration file and define the beans. We will create the DataSource in the Spring Bean context file and set it to our DAO implementation class. My Spring Bean Configuration file looks like below.

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="https://www.springframework.org/schema/beans" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd"> <bean id="employeeDAO" class="com.journaldev.spring.jdbc.dao.EmployeeDAOImpl"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/TestDB" /> <property name="username" value="pankaj" /> <property name="password" value="pankaj123" /> </bean> </beans>

First of all we are creating a DataSource object of class DriverManagerDataSource. This class provides the basic implementation of DataSource that we can use. We are passing MySQL database URL, username and password as properties to the DataSource bean. Again dataSource bean is set to the EmployeeDAOImpl bean and we are ready with our Spring JDBC implementation. The implementation is loosely coupled and if we want to switch to some other implementation or move to other database server, all we need is to make corresponding changes in the bean configurations. This is one of the major advantage provided by Spring JDBC framework.

Spring JDBC Test Class

Let’s write a simple test class to make sure everything is working fine.

package com.journaldev.spring.jdbc.main; import java.util.List; import java.util.Random; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.journaldev.spring.jdbc.dao.EmployeeDAO; import com.journaldev.spring.jdbc.model.Employee; public class SpringMain { public static void main(String[] args) { //Get the Spring Context ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml"); //Get the EmployeeDAO Bean EmployeeDAO employeeDAO = ctx.getBean("employeeDAO", EmployeeDAO.class); //Run some tests for JDBC CRUD operations Employee emp = new Employee(); int rand = new Random().nextInt(1000); emp.setId(rand); emp.setName("Pankaj"); emp.setRole("Java Developer"); //Create employeeDAO.save(emp); //Read Employee emp1 = employeeDAO.getById(rand); System.out.println("Employee Retrieved::"+emp1); //Update emp.setRole("CEO"); employeeDAO.update(emp); //Get All List<Employee> empList = employeeDAO.getAll(); System.out.println(empList); //Delete employeeDAO.deleteById(rand); //Close Spring Context ctx.close(); System.out.println("DONE"); } }

I am using Random Class to generate random number for employee id. When we run above program, we get following output.

Mar 25, 2014 12:54:18 PM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@4b9af9a9: startup date [Tue Mar 25 12:54:18 PDT 2014]; root of context hierarchy Mar 25, 2014 12:54:18 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions INFO: Loading XML bean definitions from class path resource [spring.xml] Mar 25, 2014 12:54:19 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName INFO: Loaded JDBC driver: com.mysql.jdbc.Driver Employee saved with id=726 Employee Found::{ID=726,Name=Pankaj,Role=Java Developer} Employee Retrieved::{ID=726,Name=Pankaj,Role=Java Developer} Employee updated with id=726 [{ID=726,Name=Pankaj,Role=CEO}] Employee deleted with id=726 Mar 25, 2014 12:54:19 PM org.springframework.context.support.ClassPathXmlApplicationContext doClose INFO: Closing org.springframework.context.support.ClassPathXmlApplicationContext@4b9af9a9: startup date [Tue Mar 25 12:54:18 PDT 2014]; root of context hierarchy DONE