Spring JDBC : Basics

Spring provides a simplification in handling database access with the Spring JDBC Template. Spring JDBC provides several approaches and different classes to form the basis for a JDBC database access. The most popular approach makes use of JdbcTemplate class. This is the central framework class that manages all the database communication and exception handling. The Spring JDBC Template has the following advantages compared with standard JDBC.

  • avoid bolier-plate code from our database operations logic such as Opening/Closing Connection, ResultSet, PreparedStatement etc.
  • The Spring JDBC template converts the standard JDBC SQLExceptions into RuntimeExceptions. The Spring JDBC template converts also the vendor specific error messages into better understandable error messages.

To understand the concepts related to Spring JDBC framework with JdbcTemplate class, let us write a simple example which will implement all the CRUD operations on the following Employee table.

spring_jdbc_table

Before going into spring stuff, just recollect what a normal jdbc steps are. You can quickly see here. I am using MySQL database. Also, your core spring basic concepts should be clear, things like DI, autowiring etc. I am using the same project used for core spring. You can see here for setup. Now we have to add spring-jdbc jars and the mysql connector jar. pom.xml:


<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>mynotes.corespring.test</groupId>
<artifactId>MyNotes_core_spring</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<org.springframework.version>4.0.3.RELEASE</org.springframework.version>
</properties>
<dependencies>
<!-- Core utilities used by other modules. Define this if you use Spring
Utility APIs (org.springframework.core.*/org.springframework.util.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${org.springframework.version}</version>
</dependency>

<!-- Bean Factory and JavaBeans utilities (depends on spring-core) Define
this if you use Spring Bean APIs (org.springframework.beans.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${org.springframework.version}</version>
</dependency>

<!-- Application Context (depends on spring-core, spring-expression, spring-aop,
spring-beans) This is the central artifact for Spring's Dependency Injection
Container and is generally always defined -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.26</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework.version}</version>
</dependency>

</dependencies>
</project>

Our model Employee class


package mynotes.springjdbc.basics;

public class Employee{

private int id;
private String name;
private String phoneNumber;

@Override
public String toString() {
return ("Id=>" + this.id + " Name=>" + this.name+ " PhoneNumber=>"
+ this.phoneNumber);
}

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 getPhoneNumber() {
return phoneNumber;
}

public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}

}

spring.xml:


<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd">

<context:annotation-config />
<context:component-scan base-package="mynotes.springjdbc.basics" />

<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/javatest" />
<property name="username" value="root" />
<property name="password" value="admin" />
</bean>

</beans>

Notice we hve a datasource bean with our DB crendentials. There are other datacource classes implementation also. component-scan base-package will scan for all classes for any bean declaration.

MySpringDao .java


package mynotes.springjdbc.basics;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class MySpringDao {

@Autowired
private DataSource datasource;
private JdbcTemplate jdbcTemplate;
public DataSource getDatasource() {
return datasource;
}
public void setDatasource(DataSource datasource) {
this.datasource = datasource;
}

public int getEmployeeCount(){
String sql="Select count(*) from Employee";
jdbcTemplate=new JdbcTemplate(this.datasource);
return jdbcTemplate.queryForInt(sql);
}
}

Notice @Component which makes this class a bean. The ‘datasource’ member variable is aurowired with the bean we created in spring.xml. In the getEmployeeCount() method we have a SQL query. The jdbcTemplate is initialized using the datasource. Now we used jdbcTemplate one of many overloaded method for int result, as we know that the result is going to be an int. all the Prepared statement, execution, getting resulr is handled by jdbc template. It automatically clean up resources as well.

Testing using App.java:


package mynotes.springjdbc.basics;

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

public class App {

public static void main(String[] args) {
ApplicationContext context=new ClassPathXmlApplicationContext("/mynotes/springjdbc/basics/spring.xml");
MySpringDao mySpringDao= context.getBean("mySpringDao",MySpringDao.class);
System.out.println("Total Count=>"+mySpringDao.getEmployeeCount());
}
}

Output:


Total Count=>5

Notice again in MySpringDao we have autowired datasource but never actually directly using it. In getEmployeeCount() we just passed it to initialize JdbcTemplate. We can further shorten our code by moving out @Autowired to datasource setter and intialize JdbcTemplate there. Changing MySpringDao.java:


package mynotes.springjdbc.basics;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class MySpringDao {

private DataSource datasource;
private JdbcTemplate jdbcTemplate;
public DataSource getDatasource() {
return datasource;
}
@Autowired
public void setDatasource(DataSource datasource) {
this.jdbcTemplate = new JdbcTemplate(datasource);
}

public int getEmployeeCount(){
String sql="Select count(*) from Employee";
return jdbcTemplate.queryForInt(sql);
}}

Lets see a different case. Adding following method to MySpringDao.java:


public String getEmployeeName(int id){
String sql="Select NAME from Employee where id=?";
return jdbcTemplate.queryForObject(sql, new Object[]{id}, String.class);
}

getEmployeeName() method takes an id and return the name of the Employee. For this we used one of the overloaded method of jdbcTemplate queryForObject. This method takes in the sql statement, all the parameters as Object[] in the same order that needs to be substituted for ?, and the class which it would return. Since we know that NAME is varchar so it would be String. Confirm this by calling from your test class.

Now so far we have been using int, String. Spring knows how to convert a number to int or a varchar to String, but what if its a whole resultset that needs to be mapped with Employee. In this case we have to tell spring about the mappings. Let us take an example where Employee object is returned when we pass an id. Adding following to MySpringDao


//all same

public class MySpringDao {

//all same as previous

public Employee getEmployeeDetail(int id){
String sql="Select * from Employee where id=?";
return jdbcTemplate.queryForObject(sql, new Object[]{id}, new EmployeeMapper());
}

private static final class EmployeeMapper implements RowMapper<Employee>{

public Employee mapRow(ResultSet resultSet, int rowNum) throws SQLException {
Employee employee=new Employee();
employee.setId(resultSet.getInt("ID"));
employee.setName(resultSet.getString("NAME"));
employee.setPhoneNumber(resultSet.getString("Phone"));
return employee;
}

}

}

getEmployeeDetail method have a sql statement that will return all columns based on id. Now we need to tell spring about resultset mapping. For that we have created a private inner class EmployeeMapper which implements RowMapper. We have to implement mapRow method which takes in 2 params – resultSet and rowNum. resultSet  is the number of rows return drom the query execution. romNum is the current row that is being processed, i.e if the resultset has 5 rows, this mapRow method will be called 5 times with resultSet being same but the rowNum is incrementet on each call (0,1 etc.). Here it will be called only once. Run the App.java class to test this.

Lets add a List call also. Full MySpringDao.java


package mynotes.springjdbc.basics;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component
public class MySpringDao {

private DataSource datasource;
private JdbcTemplate jdbcTemplate;
public DataSource getDatasource() {
return datasource;
}
@Autowired
public void setDatasource(DataSource datasource) {
this.jdbcTemplate = new JdbcTemplate(datasource);
}

public int getEmployeeCount(){
String sql="Select count(*) from Employee";
return jdbcTemplate.queryForInt(sql);
}
public String getEmployeeName(int id){
String sql="Select NAME from Employee where id=?";
return jdbcTemplate.queryForObject(sql, new Object[]{id}, String.class);
}
public Employee getEmployeeDetail(int id){
String sql="Select * from Employee where id=?";
return jdbcTemplate.queryForObject(sql, new Object[]{id}, new EmployeeMapper());
}

public List<Employee> getEmployeeList(){
String sql="Select * from Employee";
return jdbcTemplate.query(sql,new EmployeeMapper());
}

private static final class EmployeeMapper implements RowMapper<Employee>{

public Employee mapRow(ResultSet resultSet, int rowNum) throws SQLException {
Employee employee=new Employee();
employee.setId(resultSet.getInt("ID"));
employee.setName(resultSet.getString("NAME"));
employee.setPhoneNumber(resultSet.getString("Phone"));
return employee;
}

}

}

full App.java:


package mynotes.springjdbc.basics;

import java.util.List;

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

public class App {

public static void main(String[] args) {
ApplicationContext context=new ClassPathXmlApplicationContext("/mynotes/springjdbc/basics/spring.xml");
MySpringDao mySpringDao= context.getBean("mySpringDao",MySpringDao.class);
System.out.println("Total Count=>"+mySpringDao.getEmployeeCount());
System.out.println("Name=>"+mySpringDao.getEmployeeName(3));
System.out.println("Employee=>"+mySpringDao.getEmployeeDetail(4));

List<Employee> myList=mySpringDao.getEmployeeList();
System.out.println("List=>");
for (Employee employee : myList) {
System.out.println(employee);
}
}
}

Output:


Total Count=>5
Name=>Brad
Employee=>Id=>4 Name=>Tom PhoneNumber=>456789
List=>
Id=>1 Name=>Jake PhoneNumber=>123456
Id=>2 Name=>Jane PhoneNumber=>234567
Id=>3 Name=>Brad PhoneNumber=>345678
Id=>4 Name=>Tom PhoneNumber=>456789
Id=>5 Name=>Sam PhoneNumber=>567890

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

%d bloggers like this: