In the application.properties file specify the datasource properties
spring.datasource.url=jdbc:mysql://localhost/bootdb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.platform=mysql
spring.datasource.initialization-mode=always
Create the schema-mysql.sql file and specify the initialization scripts-
CREATE TABLE IF NOT EXISTS employee (
empId VARCHAR(10) NOT NULL,
empName VARCHAR(100) NOT NULL
);
Create the Employee Domain class
package com.javainuse.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 + "]";
}
}
Create the DAO interface where we specify the database operation methods to be performed.
package com.javainuse.dao;
import java.util.List;
import com.javainuse.model.Employee;
public interface EmployeeDao {
List<Employee> getAllEmployees();
void insertEmployee(Employee employee);
}
The DAO implementation class. We have autowired the DataSource bean here. The DataSource bean uses the properties defined in the application.properties.
package com.javainuse.dao;
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.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import com.javainuse.dao.EmployeeDao;
import com.javainuse.model.Employee;
@Repository
public class EmployeeDaoImpl extends JdbcDaoSupport implements EmployeeDao {
@Autowired
DataSource dataSource;
@PostConstruct
private void initialize() {
setDataSource(dataSource);
}
@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 void insertEmployee(Employee employee) {
String sql = "INSERT INTO employee " + "(empId, empName) VALUES (?, ?)";
getJdbcTemplate().update(sql, new Object[] { employee.getEmpId(), employee.getEmpName() });
}
}
Create Service interface to specify employee operations to be performed.
package com.javainuse.service;
import java.util.List;
import com.javainuse.model.Employee;
public interface EmployeeService {
List<Employee> getAllEmployees();
void insertEmployee(Employee employee);
}
The Service class implementation. Here we autowire the EmployeeDao bean.
package com.javainuse.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.javainuse.dao.EmployeeDao;
import com.javainuse.model.Employee;
import com.javainuse.service.EmployeeService;
@Service
public class EmployeeServiceImpl implements EmployeeService {
@Autowired
EmployeeDao employeeDao;
public List<Employee> getAllEmployees() {
List<Employee> employees = employeeDao.getAllEmployees();
return employees;
}
@Override
public void insertEmployee(Employee employee) {
employeeDao.insertEmployee(employee);
}
}
Create the controller class to expose two REST API's. One is a GET API to return the list of employees from the database.
The other is a POST API which takes the employee object and persists it in the database.
package com.javainuse.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import com.javainuse.model.Employee;
import com.javainuse.service.EmployeeService;
@RestController
public class EmployeeController {
@Autowired
EmployeeService empService;
@RequestMapping(value = "/employees", method = RequestMethod.GET)
public List<Employee> getEmployees() {
return empService.getAllEmployees();
}
@RequestMapping(value = "/insertemployee", method = RequestMethod.POST)
public void insertEmployee(@RequestBody Employee employee) {
empService.insertEmployee(employee);
}
}
Finally create the class with @SpringBootApplication annotation.
package com.javainuse;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.RestController;
@RestController
@EnableAutoConfiguration
@SpringBootApplication
public class SpringBootHelloWorldApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBootHelloWorldApplication.class, args);
}
}
Start the Spring Boot Application.
Open POSTMAN and create a POST request to url - localhost:8080/insertemployee with employee object to be persisted in DB.
In the browser use localhost:8080/employees to fetch the list of employees.
Deploying Spring Boot + MYSQL to Docker-
Since are going to create two docker containers which should communicate with each other, we will need to start them on same network.
We had seen the Docker Networking details in a previous tutorial.
Open the terminal and start the docker
systemctl start docker
First lets create a network named employee-mysql
docker network create employee-mysql
MYSQL provides an image in dockerhub which we can run as container.
We will use the image provided by dockerhub to run as container. Also we will specify following when running the container
-
a. name of the mysql container
-
a. What should be the password for MYSQL
-
b. We want to create the Database named bootdb.
-
c. specify the network employee-mysql on which this container should be created.
-
d. start the container in detached mode.
docker container run --name mysqldb --network employee-mysql -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=bootdb -d mysql:8
Next let us check if container has started correctly using logs command.
docker container logs -f ae
Using the exec command we can also inspect if the database named bootdb is created successfully.
docker container exec -it ae bash
Next we will modify the application.properties in the Spring Boot application to make use of the mysql container name i.e.mysqldb
instead of localhost
spring.datasource.url=jdbc:mysql://mysqldb/bootdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.platform=mysql
spring.datasource.initialization-mode=always
The docker file for spring boot project will be as follows-
From openjdk:8
copy ./target/employee-jdbc-0.0.1-SNAPSHOT.jar employee-jdbc-0.0.1-SNAPSHOT.jar
CMD ["java","-jar","employee-jdbc-0.0.1-SNAPSHOT.jar"]
Build the docker image for the spring boot project
docker image build -t employee-jdbc .
Next run this as a container. Also we are running the container on the employee-mysql network.
docker container run --network employee-mysql --name employee-jdbc-container -p 8080:8080 -d employee-jdbc
docker container logs -f 34
Both our containers have started successfully. Let us insert data with POST request using curl-
curl --header "Content-Type: application/json" --request POST --data '{"empId":"emp001","empName":"emp001"}' http://localhost:8080/insertemployee
Finally if we go to localhost:8080/employees we get the inserted records.
In previous tutorial we had seen
what is docker compose and its advantages. To achieve the above using docker-compose create the docker-compose.yml as follows-
version: "3"
services:
employee-jdbc:
image: employee-jdbc
ports:
- "8080:8080"
networks:
- employee-mysql
depends_on:
- mysqldb
mysqldb:
image: mysql:8
networks:
- employee-mysql
environment:
- MYSQL_ROOT_PASSWORD=root
- MYSQL_DATABASE=bootdb
networks:
employee-mysql:
Use docker-compose up to start docker compose.
Download Source Code
Download it -
Spring Boot + MYSQL Application for Docker Deployment
