Spring JDBC Example

While working with the database using plain old JDBC, it becomes cumbersome to write unnecessary code to handle exceptions, opening and closing database connections, etc. However, Spring JDBC Framework takes care of all the low-level details starting from opening the connection, prepare and execute the SQL statement, process exceptions, handle transactions and finally close the connection.

Spring provides several approaches to interface with databases. Let us see the most popular approach i.e. JdbcTemplate class of Spring framework in this tutorial.

1. Create table Emp in  MySQL [world] db.

  CREATE TABLE  emp
(
    id          INT NOT NULL AUTO_INCREMENT,
    name     VARCHAR(100) NOT NULL,
    sal         INT NOT NULL,
    PRIMARY KEY (id)
);

2. Create a simple Java Project in Eclipse. Right Click and Goto Build Path -> Configure Build Path and add all the jars of spring framework and commons logging. You should download latest spring framework from https://repo.spring.io/release/org/springframework/spring and commons logging from https://commons.apache.org/logging/. Add latest mysql-connector-java.jar.

3. Create a POJO class Emp which has the property according to column name of Emp table.

package com.vo;

public class Emp {
    private Integer id;
    private String name;
    private Integer sal;
  
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getSal() {
        return sal;
    }
    public void setSal(Integer sal) {
        this.sal = sal;
    }
}

4. Create a class EmpMapper to map row values of particular columns to the class property. For this purpose, EmpMapper should implement org.springframework.jdbc.core.RowMapper.

package com.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.vo.Emp;

public class EmpMapper implements RowMapper {

    @Override
    public Emp mapRow(ResultSet arg0, int arg1) throws SQLException {
      
        Emp emp = new Emp();
      
        emp.setId(arg0.getInt("id"));
      
        emp.setName(arg0.getString("name"));
      
        emp.setSal(arg0.getInt("sal"));
      
        return emp;
    }
}

5. Create a DAO interface and list all methods to interface with Emp table in DB. It's a good practice but not mandatory.

 package com.dao;

 import java.util.List;

 import javax.sql.DataSource;

 import com.vo.Emp; 

 public interface EmpDao {
   
    public void setDataSource(DataSource dataSource);
   
    public void addEmp(String name, Integer sal);
   
    public Emp getEmp(Integer id);
   
    public List listEmp();
   
    public void updateEmp(Integer id, Integer sal);
   
}

6. Create EmpDaoImpl class which implements EmpDao class and writes all the required functionalities for the DB.

package com.jdbc;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.dao.EmpDao;
import com.mapper.EmpMapper;
import com.vo.Emp;

public class EmpDaoImpl implements EmpDao {

    private DataSource dataSource;
    private JdbcTemplate template;
   
   

    @Override
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        template = new JdbcTemplate(dataSource);
       
    }

    @Override
    public void addEmp(String name, Integer sal) {
        String sql = "insert into emp(name, sal) values(?,?)";
        int insert = template.update(sql, name, sal);
       
        System.out.println("Record inserted: " + insert);
       
    }

    @Override
    public Emp getEmp(Integer id) {
        String sql = "select * from emp where id=?";
        Emp emp = template.queryForObject(sql,new EmpMapper(), id);
       
        return emp;
    }

    @Override
    public List listEmp() {
        String sql = "select * from emp";
        List lstEmp =  template.query(sql,new EmpMapper());
        return lstEmp;
    }

    @Override
    public void updateEmp(Integer id, Integer sal) {
        String sql = "update emp set sal=? where id=?";
       
        int update = template.update(sql, sal, id);
       
        System.out.println("Record updated: " + update);
       
    }
}

7. Create a XML i.e. jdbc.xml under src folder which has a bean entry for class DriverManagerDataSource and set all its property with the appropriate DB connection information. And another bean entry for EmpDaoImpl which dataSource property value contains the reference of previous i.e. DriverManagerDataSource bean. 

<?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 ">
 
   <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/world"/>
           <property name="username" value="root"/>
           <property name="password" value="root12"/>
 
   </bean>
 
   <bean id="emp" class="com.jdbc.EmpDaoImpl">
           <property name="dataSource" ref="dataSource"></property>
   </bean>

</beans>  

8. Finally, Create a Main program to load the emp bean in the XML and test it methods.

package com.jdbc;

import java.util.List;

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

import com.vo.Emp;
import com.vo.Student;

public class JDBCApp {

    /**
     * @param args
     */
    public static void main(String[] args) {
        ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbc.xml");
       
        EmpDaoImpl impl = (EmpDaoImpl) ctx.getBean("emp");
       
        System.out.println("......Record Creation......");
       
        impl.addEmp("Md. Ashik Ali Khan", 10000);
        impl.addEmp("Rahim", 20000);
        impl.addEmp("Karim", 30000);
       
       
        impl.updateEmp(3, 40000);
       
        List lstEmp = impl.listEmp();
       
        for(Emp emp: lstEmp)
        {
            System.out.println(emp.getId() + "\t" + emp.getName() + "\t"+ emp.getSal());
        }
    }

}
 

Output:

 ......Record Creation......

Record inserted: 1

Record inserted: 1

Record inserted: 1

Record updated: 1

1    Md. Ashik Ali Khan    10000
2    Rahim    20000
3    Karim    40000