Remember below version only supports com.mysql.jdbc.Driver
<version>2.0.0.RELEASE</version>
<?xml version="1.0" encoding="UTF-8"?>
<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>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>JBK_Spring_JdbcTemplate</groupId>
<artifactId>JBK_Spring_JdbcTemplate</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>JBK_JDBC_Template</name>
<description>JDBC Template Example for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
DROP TABLE IF EXISTS `jbkdb`.`employee1`;
CREATE TABLE `jbkdb`.`employee1` (
`eid` int(10),
`ename` varchar(45),
PRIMARY KEY (`eid`))
We need to create pojo according to table. Properties of pojo will be column names. Not compulsorily same name but as a standard we will keep same name.
Remember as a standard implement getters and setters, constructor and override toString, equals, hashcode method.
hashCode and equals methods need for comparing objects in a test cases.
Employee.java
package com.javabykiran;
public class Employee {
public Employee(int id, String name) {
super();
this.id = id;
this.name = name;
}
public Employee() {
// TODO Auto-generated constructor stub
}
private int id;
private String name;
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;
}
@Override
public String toString() {
return "Employee [id=" + id + ", name=" + name + "]";
}
}
This class needs to be created for mapping employee pojo and column of table. Generally we can say if I have 10 tables then like this we need to have 10 classes for mapping.
package com.javabykiran;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class EmployeeMapper implements RowMapper<Employee> {
@Override
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee employee = new Employee();
employee.setId(Integer.parseInt(rs.getString("eid")));
employee.setName(rs.getString("ename"));
return employee;
}
}
In this class we must autowire JdbcTemplate.
This class must be qualified bean so annotate class with repository annotation.
package com.javabykiran;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class EmployeeDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public int saveEmployee(Employee e) {
String query = "insert into employee
values('" + e.getId() + "','" + e.getName() + "')";
return jdbcTemplate.update(query);
}
public int updateEmployee(Employee e) {
String query = "update employee
set name='" + e.getName() + "' where eid='" + e.getId() + "' ";
return jdbcTemplate.update(query);
}
public int deleteEmployee(Employee e) {
String query = "delete from employee
where eid='" + e.getId() + "' ";
return jdbcTemplate.update(query);
}
public List<Employee> getEmployee(int id) {
String query = "select * from employee
where eid='" + id + "' ";
return jdbcTemplate.query(query, new EmployeeMapper());
}
public Employee getEmployeeById(int id) {
String query = "select * from employee
where eid='" + id + "' ";
return jdbcTemplate.queryForObject(query, new EmployeeMapper());
}
public void employeeOperation(Employee e) {
saveEmployee(e);
updateEmployee(e);
deleteEmployee(e);
}
}
In above program we can see:
DML operations can be performed by using update method.
Select operation are achieved by query and queryForObject method.
Observe in these all scenarios we have control over queries that’s beauty of JdbcTemplate.
In hibernate most of the time we not get control.
In pure jdbc lot of code needs to be written for connection.
spring.datasource.url=jdbc:mysql://localhost/jbkdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
Now our setup is ready.
We will start testing application.