Spring Boot JDBC implementation - Hello World Example
In a previous post we had created an application using JDBC and seen the disadvantages. In another post we had seen the advantages of using Spring JDBC. In this chapter we see how to implement JDBC using Spring boot with MySql database.
Why use Spring Boot JDBC-
JDBC using Spring Boot:
- Only a single spring-boot starter dependency is required.
- Datasource bean gets initialized automatically if not mentioned explicitly.
If user does not want this then it can be done by setting the property spring.datasource.initialize to false. - If the Template beans PlatformTransactionManager, JdbcTemplate, NamedParameterJdbcTemplate not registered, then Spring Boot will register them automatically.
- Any db initialization scripts stored in schema-.sql gets executed automatically.
Lets Begin-
The project will be as follows- <?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>
<groupId>com.Example</groupId>
<artifactId>boot-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>boot-jdbc</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.2.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<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>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
In the application.properties file specify the datasource properties
spring.datasource.url=jdbc:mysql://localhost/cameldb spring.datasource.username=root spring.datasource.password=rida spring.datasource.platform=mysql
DROP TABLE IF EXISTS employee; CREATE TABLE employee ( empId VARCHAR(10) NOT NULL, empName VARCHAR(100) NOT NULL );
Create the Employee Domain class
package com.Example.model;
public class Employee {
private String empId;
private String empName;
public String getEmpId() {
return empId;
}
public void setEmpId(String empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
@Override
public String toString() {
return "Employee [empId=" + empId + ", empName=" + empName + "]";
}
}
package com.Example.service;
import java.util.List;
import com.Example.model.Employee;
public interface EmployeeService {
void insertEmployee(Employee emp);
void insertEmployees(List<Employee> employees);
void getAllEmployees();
void getEmployeeById(String empid);
}
The Service class implementation.
package com.Example.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.Example.dao.EmployeeDao;
import com.Example.model.Employee;
import com.Example.service.EmployeeService;
@Service
public class EmployeeServiceImpl implements EmployeeService {
@Autowired
EmployeeDao employeeDao;
@Override
public void insertEmployee(Employee employee) {
employeeDao.insertEmployee(employee);
}
@Override
public void insertEmployees(List<Employee> employees) {
employeeDao.insertEmployees(employees);
}
public void getAllEmployees() {
List<Employee> employees = employeeDao.getAllEmployees();
for (Employee employee : employees) {
System.out.println(employee.toString());
}
}
@Override
public void getEmployeeById(String empId) {
Employee employee = employeeDao.getEmployeeById(empId);
System.out.println(employee);
}
}
Create the DAO interface.
package com.Example.dao;
import java.util.List;
import com.Example.model.Employee;
public interface EmployeeDao {
void insertEmployee(Employee cus);
void insertEmployees(List<Employee> employees);
List<Employee> getAllEmployees();
Employee getEmployeeById(String empId);
}
package com.Example.dao.impl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import com.Example.dao.EmployeeDao;
import com.Example.model.Employee;
@Repository
public class EmployeeDaoImpl extends JdbcDaoSupport implements EmployeeDao{
@Autowired
DataSource dataSource;
@PostConstruct
private void initialize(){
setDataSource(dataSource);
}
@Override
public void insertEmployee(Employee emp) {
String sql = "INSERT INTO employee " +
"(empId, empName) VALUES (?, ?)" ;
getJdbcTemplate().update(sql, new Object[]{
emp.getEmpId(), emp.getEmpName()
});
}
@Override
public void insertEmployees(List<Employee> employees) {
String sql = "INSERT INTO employee " + "(empId, empName) VALUES (?, ?)";
getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
Employee employee = employees.get(i);
ps.setString(1, employee.getEmpId());
ps.setString(2, employee.getEmpName());
}
public int getBatchSize() {
return employees.size();
}
});
}
@Override
public List<Employee> getAllEmployees(){
String sql = "SELECT * FROM employee";
List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
List<Employee> result = new ArrayList<Employee>();
for(Map<String, Object> row:rows){
Employee emp = new Employee();
emp.setEmpId((String)row.get("empId"));
emp.setEmpName((String)row.get("empName"));
result.add(emp);
}
return result;
}
@Override
public Employee getEmployeeById(String empId) {
String sql = "SELECT * FROM employee WHERE empId = ?";
return (Employee)getJdbcTemplate().queryForObject(sql, new Object[]{empId}, new RowMapper<Employee>(){
@Override
public Employee mapRow(ResultSet rs, int rwNumber) throws SQLException {
Employee emp = new Employee();
emp.setEmpId(rs.getString("empId"));
emp.setEmpName(rs.getString("empName"));
return emp;
}
});
}
}
package com.Example;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;
import com.Example.model.Employee;
import com.Example.service.EmployeeService;
import com.Example.service.impl.EmployeeServiceImpl;
@SpringBootApplication
public class SpringBootJdbcApplication {
@Autowired
EmployeeService employeeService;
public static void main(String[] args) {
ApplicationContext context = SpringApplication.run(SpringBootJdbcApplication.class, args);
EmployeeService employeeService = context.getBean(EmployeeService.class);
Employee emp= new Employee();
emp.setEmpId("emp");
emp.setEmpName("emp");
Employee emp1= new Employee();
emp1.setEmpId("emp1");
emp1.setEmpName("emp1");
Employee emp2= new Employee();
emp2.setEmpId("emp2");
emp2.setEmpName("emp2");
employeeService.insertEmployee(emp);
List<Employee> employees = new ArrayList<>();
employees.add(emp1);
employees.add(emp2);
employeeService.insertEmployees(employees);
employeeService.getAllEmployees();
employeeService.getEmployeeById(emp1.getEmpId());
}
}
Start the application-
