Example of Spring JdbcTemplate class by R4R Team

Example of Spring JdbcTemplate class:-
Let us create a database table Employee in our database Test. I assume you are working with MySQL database, if you work with any other database then you can change your DDL and SQL queries accordingly.

CREATE TABLE EMPLOYEE(

ID INT NOT NULL AUTO_INCREMENT,

NAME VARCHAR(20) NOT NULL,

SALARY INT NOT NULL,

PRIMARY KEY (ID)

);

Now we have working Eclipse IDE in place and follow the following steps to create a Spring application:

1 Create a project with a name SpringTest and create a package com.r4r.in under the src folder in the created project.
2 Add required Spring libraries using Add External JARs .
3 Add Spring JDBC specific latest libraries mysql-connector-java.jar, org.springframework.jdbc.jar and org.springframework.transaction.jar in the project. You can download required libraries if you do not have them already.

4 Create DAO interface EmployeeDAO and list down all the required methods. Though it is not required and you can directly write EmployeeJDBCTemplate class, but as a good practice, let's do it.
5 Create other required Java classes Employee, EmployeeMapper, EmployeeJDBCTemplate and MainApp under the com.r4r.in package.
6 Make sure you already created Employee table in Test database. Also make sure your MySQL server is working fine and you have read/write access on the database using the give username and password.

7 Create Beans configuration file Beans.xml under the src folder.
8 The final step is to create the content of all the Java files and Bean Configuration file and run the application as explained below.

1 Create Data Access Object interface file EmployeeDAO.java:

package com.r4r.in;

import java.util.List;

import javax.sql.DataSource;

public interface EmployeeDAO {

/**

public void setDataSource(DataSource ds);

/**

public void create(String name, Integer salary);

/**

public Employee getEmployee(Integer id);

/**

public List<Employee> listEmployee();

/**

public void delete(Integer id);

/**

public void update(Integer id, Integer salary);

}

2 Create Employee.java file:

package com.r4r.in;

public class Employee {

private Integer salary;

private String name;

private Integer id;

public void setSalary(Integer salary) {

this.salary = salary;}

public Integer getSalary() {

return salary;}

public void setName(String name) {

this.name = name;}

public String getName() {

return name; }

public void setId(Integer id) {

this.id = id;}

public Integer getId() {

return id; }}

3 Create EmployeeMapper.java file:

package com.r4r.in;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class EmployeeMapper implements RowMapper<Employee> {

public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {

Employee emp = new Employee();

emp.setId(rs.getInt("id"));

emp.setName(rs.getString("name"));

emp.setSalary(rs.getInt("salary"));

return emp;}}

4 Create implementation class file EmployeeJDBCTemplate.java for the defined DAO interface EmployeeDAO:

package com.r4r.in;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

public class EmployeeJDBCTemplate implements EmployeeDAO {

private DataSource dataSource;

private JdbcTemplate jdbcTemplateObject;

public void setDataSource(DataSource dataSource) {

this.dataSource = dataSource;

this.jdbcTemplateObject = new JdbcTemplate(dataSource);}

public void create(String name, Integer salary) {

String SQL = "insert into Employee (name, salary) values (?, ?)";

jdbcTemplateObject.update( SQL, name, salary);

System.out.println("Created Record Name = " + name + " Salary = " + salary);

return;}

public Employee getEmployee(Integer id) {

String SQL = "select * from Employee where id = ?";

Employee emp = jdbcTemplateObject.queryForObject(SQL,

new Object[]{id}, new EmployeeMapper());

return emp;}

public List<Employee> listEmployee() {

String SQL = "select * from Employee";

List <Employee> emp = jdbcTemplateObject.query(SQL,

new EmployeeMapper());

return emp;}

public void delete(Integer id){

String SQL = "delete from Employee where id = ?";

jdbcTemplateObject.update(SQL, id);

System.out.println("Deleted Record with ID = " + id );

return;}

public void update(Integer id, Integer salary){

String SQL = "update Employee set salary = ? where id = ?";

jdbcTemplateObject.update(SQL, salary, id);

System.out.println("Updated Record with ID = " + id );

return;}}

5 Create MainApp.java file:

package com.r4r.in;

import java.util.List;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.r4r.in.EmployeeJDBCTemplate;

public class MainApp {

public static void main(String[] args) {

ApplicationContext context =

new ClassPathXmlApplicationContext("Beans.xml");

EmployeeJDBCTemplate empJDBCTemplate =

(EmployeeJDBCTemplate)context.getBean("empJDBCTemplate");

System.out.println("Records are Created" );

empJDBCTemplate.create("Vipul", 2000);

empJDBCTemplate.create("Aryan", 3000);

empJDBCTemplate.create("Peter", 1000);

System.out.println("Show Multiple Records" );

List<Employee> emp = empJDBCTemplate.listemp();

for (Employee record : emp) {

System.out.print("ID : " + record.getId() );

System.out.print(", Name : " + record.getName() );

System.out.println(", Salary : " + record.getSalary());}

System.out.println("Updating Record with ID = 2 " );

empJDBCTemplate.update(2, 5000);

System.out.println("Show Record with ID = 2 " );

Employee emp = empJDBCTemplate.getEmployee(2);

System.out.print("ID : " + emp.getId() );

System.out.print(", Name : " + emp.getName() );

System.out.println(", Salary : " + emp.getSalary());

}}

6 Create Beans.xml file:

<?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 ">

<!-- Initialization for data source -->

<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/Test"/>

<property name="username" value="root"/>

<property name="password" value="password"/>

</bean>

<!-- Definition for empJDBCTemplate bean -->

<bean id="empJDBCTemplate" class="com.r4r.in.EmployeeJDBCTemplate">

<property name="dataSource" ref="dataSource" />

</bean></beans>

7  Now Run the Application .if everything is configured then show the message:

Records are Created
Created Record Name = Vipul Salary = 2000
Created Record Name = Aryan Salary = 3000
Created Record Name = peter Salary = 1000
show Multiple Records
ID : 1, Name : Vipul, Salary : 2000
ID : 2, Name : Aryan, Salary : 3000
ID : 3, Name : Peter, Salary : 1000
Updating Record with ID = 2
Updated Record with ID = 2
show Record with ID = 2
ID : 2, Name : Aryan, Salary : 5000