Spring jdbc example

Spring jdbc example

Spring Framework is most famous java framework to build enterprise applications and database is integral part of enterprise applications. Spring framework provides excellent support for jdbc and other popular frameworks like hibernate, iBatis and many more. Here in this tutorial we will demonstrate example of CRUD operation using spring jdbc.
Technologies used in this examples are :

  1. JDK 1.7
  2. Maven as a build tool.
  3. Spring 4.1, however you can use lower versions of spring as well.
  4. Mysql as a database.

This example is created using Eclipse and maven plugin are used to create the basic folder structure for maven java project. We have added other files further to demonstrate this example. Our final folder structure looks like below image.

Spring jdbc

 

1) Spring JDBC dependencies.

Add spring jdbc and mysql dependencies into the pom.xml file.

<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>springjdbc</groupId>
  <artifactId>springjdbc</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springjdbc</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <!-- Spring -->
	<spring.version>4.1.0.RELEASE</spring.version>
    <mysql.version>5.1.32</mysql.version>
  </properties>

  <dependencies>
    <dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>${mysql.version}</version>
    </dependency>
    <!-- Spring JDBC Support -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>${spring.version}</version>
		</dependency>
  </dependencies>
</project>

2) Table creation in Database

For spring jdbc example we are using mysql database. Now create a Emp table using below database script.

CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `firstName` varchar(45) DEFAULT NULL,
  `lastName` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3) Emp model object.

We need a model to store Emp data. So, create Emp model class as below.

package com.code2succeed.beans;

public class Emp {
	private int id;
	private String firstName;
	private String lastName;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	
	public String toString() {
		StringBuilder sb = new StringBuilder();
		sb.append("Id : "+id);
		sb.append(", FirstName : "+firstName);
		sb.append(", LastName : "+lastName);
		
		return sb.toString();
	}
}

4) Data Access Object (DAO) pattern

For DAO pattern, first we need to create one interface for CRUD application as below.

package com.code2succeed.dao;

import java.util.List;

import com.code2succeed.beans.Emp;

public interface EmpDao {
	//Create
	public void save(Emp emp);
	//Read
	public Emp getById(int id);
	//Update
	public void update(Emp employee);
	//Delete
	public void deleteById(int id);
	//Get All
	public List<Emp> getAll();
}

Now implement above interface and create EmpDaoImpl class which will be containing jdbc code for CRUD operation.

package com.code2succeed.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 org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.code2succeed.beans.Emp;

@Repository("empDao")
public class EmpDaoImpl implements EmpDao {
	
	@Autowired
	DataSource dataSource;
	
	public void save(Emp emp) {
		String sql = "insert into Emp(id, firstname, lastname) values (?,?,?)";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, emp.getId());
			preparedStatement.setString(2, emp.getFirstName());
			preparedStatement.setString(3, emp.getLastName());
			int output = preparedStatement.executeUpdate();
			if(output > 0) {
				System.out.println("Employee saved successfully with id = "+emp.getId());
			}else {
				System.out.println("Error while saving Employee with id = "+emp.getId());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
				preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

	public Emp getById(int id) {
		String sql = "SELECT * FROM EMP WHERE id = ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Emp emp = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			
			if(resultSet.next()){
				emp = new Emp();
				emp.setId(id);
				emp.setFirstName(resultSet.getString("firstName"));
				emp.setLastName(resultSet.getString("lastName"));
			}else{
				System.out.println("No Employee found with id="+id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return emp;
	}

	public void update(Emp emp) {
		String sql = "UPDATE Emp SET firstname=?, lastname=? where id=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, emp.getFirstName());
			preparedStatement.setString(2, emp.getLastName());
			preparedStatement.setInt(3, emp.getId());
			int output = preparedStatement.executeUpdate();
			if(output > 0) {
				System.out.println("Employee updated successfully with id = "+emp.getId());
			}else {
				System.out.println("Error while updating Employee with id = "+emp.getId());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
				preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void deleteById(int id) {
		String sql = "DELETE FROM Emp WHERE id=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			int output = preparedStatement.executeUpdate();
			if(output > 0) {
				System.out.println("Employee deleted successfully with id = "+id);
			}else {
				System.out.println("Error while deleting Employee with id = "+id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Emp> getAll() {
		String sql = "SELECT * FROM EMP";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Emp> empList = new ArrayList<Emp>();
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				Emp emp = new Emp();
				emp.setId(resultSet.getInt("id"));
				emp.setFirstName(resultSet.getString("firstName"));
				emp.setLastName(resultSet.getString("lastName"));
				empList.add(emp);
			} 
		}catch(Exception e) {
			e.printStackTrace();
		}finally{
			try {
				resultSet.close();
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return empList;
	}

}

5) Spring configuration file.

We are using Spring framework 4.1. In case you are using above or below versions of spring, your applicationContext might be different. We have created dataSource bean which contain details of mysql driver and other properties like username and password which might be different if you have created database with different username and password. Below configuration file is annotation driven so it doesn’t have any other bean other than dataSource. In EmpDaoImpl class, dataSource bean is autowired to the local variable.

<?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:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    		http://www.springframework.org/schema/beans/spring-beans.xsd
    		http://www.springframework.org/schema/context 
    		http://www.springframework.org/schema/context/spring-context-4.0.xsd
    		http://www.springframework.org/schema/tx 
    		http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
	
	<!-- scans packages to find and register beans within the application context -->
    <context:component-scan base-package="com.code2succeed" />
	
	<!-- dataSource configuration -->
    <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/code2succeed" />
        <property name="username" value="root" />
        <property name="password" value="manager" />
    </bean>    
</beans>

6) Load and Run Test class

We have created App.java class which contains simple logic for CRUD application. Run the main class.

package com.code2succeed.main;

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

import com.code2succeed.beans.Emp;
import com.code2succeed.dao.EmpDao;

public class App {
    public static void main( String[] args ) {
    	ApplicationContext context = new GenericXmlApplicationContext("applicationContext.xml"); 
        EmpDao empDao = (EmpDao) context.getBean("empDao"); 
        
        //get all Employees
        System.out.println("All Employees");
        System.out.println(empDao.getAll());
        
        //get Employee with id = 1
        System.out.println("Employee with id = 1");
        System.out.println(empDao.getById(1));
        
        //Save new Employee
        Emp emp = new Emp();
        emp.setId(3);
        emp.setFirstName("Sandeep");
        emp.setLastName("Kulkarni");
        empDao.save(emp);
        //now get all Employees
        System.out.println(empDao.getAll());
        
        //Update Employee
        emp.setLastName("Kulkarni - new");
        empDao.update(emp);
        //now get all Employees
        System.out.println(empDao.getAll());
        
        //Delete Employee
        empDao.deleteById(3);
        System.out.println(empDao.getAll());
    }
}

7) Output

All Employees
[Id : 1, FirstName : Rahul, LastName : Anand, Id : 2, FirstName : Daniel, LastName : Anderson]
Employee with id = 1
Id : 1, FirstName : Rahul, LastName : Anand
Employee saved successfully with id = 3
[Id : 1, FirstName : Rahul, LastName : Anand, Id : 2, FirstName : Daniel, LastName : Anderson, Id : 3, FirstName : Sandeep, LastName : Kulkarni]
Employee updated successfully with id = 3
[Id : 1, FirstName : Rahul, LastName : Anand, Id : 2, FirstName : Daniel, LastName : Anderson, Id : 3, FirstName : Sandeep, LastName : Kulkarni - new]
Employee deleted successfully with id = 3
[Id : 1, FirstName : Rahul, LastName : Anand, Id : 2, FirstName : Daniel, LastName : Anderson]

Stay tuned for more tutorials and examples.

Leave a Reply

Your email address will not be published. Required fields are marked *