How To Use JdbcTemplate In Spring Framework

Posted By : Gourav Kumar | 30-Jul-2019

In this blog we are going to learn, how can we use JdbcTemplate class to provide spring database integration using xml based configuration.

 

JdbcTemplate class present into org.springframework.jdbc.core package and it accepts org.springframework.jdbc.datasource.DriverManagerDataSource object to establish connection with particular database which require information pass into DriverManagerDataSource class through dependency injection in applicationContext.xml such as driver class name, url, username and password.

 


JdbcTemplate class most common methods to perform DML operations:

 

i) update(String sql, Object... args): This method is use to perform manipulation operation on table and pass arguments using varargs such as insert, update, delete.

 

ii) update(String sql, PreparedStatementSetter pss): This method is also use to perform manipulation operation using PreparedStatementSetter interface which is use to pass arguments through setXXX methods like PreparedStatement interface used into jdbc.

iii) query(String sql, ResultSetExtractor<T> rse): This method is use to retrive only single record from table using select sql query.

iv) query(String sql, RowMapper<T> rm): This method is use to retrive more than one records using RowMapper so we don't need to collect each retrived record from the table and we can automatically get List<T> of records.

v) query(String sql, Object[] args, RowMapper<T> rm): This method is use to retrive the records on the basis of passing arguments using select sql query and map all the records through RowMapper and return List<T> of records.

vi) query(String sql, Object[] args, ResultSetExtractor<T> rse): This method is use to retrive single record on the basis of passing arguments using select sql query and map the single record into ResultSetExtractor<T> and return particular bean object.

 

We need to perform following steps to use JdbcTemplate:

 

 

1) Add dependency into pom file

 

<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>x.x.x.RELEASE</version>
</dependency>
        

OR

add spring core jars and include one more jar with these jars spring-jdbc-x.x.x.RELEASE.jar.

 

2) In applicationContext.xml file

 

a) Add org.springframework.jdbc.datasource.DriverManagerDataSource class definition with its properties.

<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
     <property name="driverClassName" value="oracle.jdbc.OracleDriver"></property>
     <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></property>
     <property name="username" value="root"></property>
     <property name="password" value="root"></property>
</bean>
        

b) Now org.springframework.jdbc.core.JdbcTemplate class definition accept DriverManagerDataSource object to inject into dataSource property.

  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="ds"></property>
  </bean>
        

 

3) Create Employee bean class

 

package com.oodles;

public class Employee {
    private int id;
    private String name;
    private Gender gender;
    private long salary;
	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 Gender getGender() {
		return gender;
	}
	public void setGender(Gender gender) {
		this.gender = gender;
	}
	public long getSalary() {
		return salary;
	}
	public void setSalary(long salary) {
		this.salary = salary;
	}

	public String toString(){
       return this.getId() + " :: " + this.getName() + " :: " + this.getGender().name() + " :: " + this.getSalary();
    }
 
}
        

 

4) Use JdbcTemplate class methods into MainClass class

 

package com.oodles;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MainClass {

     public static void main(String... args) {

        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate", JdbcTemplate.class);

        //---------Add one employee into emptab----------
        String sqlnsert = "insert into emptab (id, name, gender, salary) value(?,?,?,?)";
        int count = jdbcTemplate.update(sql, 103, "Mohan singh", Gender.MALE.getValue(), 12000);
        //or
        int count = jdbcTemplate.update(sql, new PreparedStatementSetter() {
                                    @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, 103);
                ps.setString(2, "Mohan singh");
                ps.setInt(3, Gender.MALE.getValue());
                                                 ps.setLong(4, 12000);
            }
        });
        System.out.println("Number of inserted record :: "+count);


        //-------Update employee record------------------
        String sqlUpdate = "update emptab set name=?, gender=?, salary=? where id=?";
        int count = jdbcTemplate.update(sql, "Monika singh", Gender.FEMALE.getValue(), 18500, 102);
        //or
        int count = jdbcTemplate.update(sql, new PreparedStatementSetter() {
                                    @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, "Monika singh");
                ps.setInt(2, Gender.FEMALE.getValue());
                                                 ps.setLong(3, 18500);
                                                 ps.setInt(4, 102);
            }
        });
        System.out.println("Number of updated records :: "+count);

        //-------Delete employee record------------
        String sqlDelete = "delete from emptab where id=?";
        int count = jdbcTemplate.update(sql, 100);
        //or
        int count = jdbcTemplate.update(sql, new PreparedStatementSetter() {
                                    @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, 100);
            }
        }); 
        System.out.println("Number of deleted records :: "+count);

        //-----All employee records------------
        String sqlSelectAll = "select * from emptab";
        List<Employee> employees = jdbcTemplate.query(sql, Employee.class);
        for( Employee employee in employees ) {
           System.out.println( employee );
        }
       
        //-----Single employee record-----------
        String sqlSelect = "select * from emptab where id=?";
        Employee emp = jdbcTemplate.query(sql, new Object[]{101}, new ResultSetExtractor<Employee>() {

            @Override
            public Employee extractData(ResultSet rs) throws SQLException, DataAccessException {
                Employee obj = new Employee();
                                                 obj.setId(rs.getInt(1));
                                                 obj.setName(rs.getString(2));
                                                 obj.setGender(Gender.values()[rs.getInt(3)]);
                                                 obj.setSalary(rs.getLong(4));
            }
        });
        System.out.println(emp);
       
       

     }

}
       
   

 

5) Output:

 

        Number of inserted record :: 1
        Number of updated records :: 1
        Number of deleted records :: 1

        101 :: Amit ahuja :: MALE :: 10000
        102 :: Monika singh :: FEMALE :: 18500
        103 :: Mohit singh :: MALE :: 12000

        101 :: Amit ahuja :: MALE :: 10000
       

 

Conclusion: We can use JdbcTemplate class different methods to perform DML operation on database table but before all database integration information you need to inject into DriverManagerDataSource class while configuration and then inject DriverManagerDataSource id or name value into JdbcTemplate while configuration using ref tag or attribute in applicationContext.xml file to interact with the database on the basis of above given example.

 

About Author

Author Image
Gourav Kumar

Gourav is a bright Web App Developer and has good knowledge of Core java, Spring and Hibernate and his hobbies listen and sing songs.

Request for Proposal

Name is required

Comment is required

Sending message..