In this tutorial, we'll go over how to query or save data to already-existing
database tables using JdbcTemplate and then expose those operations
using REST APIs.
Using JdbcTemplate, we can run queries and stored procedures from our
JAVA code and gain low-level database access.
👉 We'll be working with a sample MySQL database.
Here
are a couple of sample MySQL databases. I used the classicmodels sample
database. This website contains the database and installation instructions.
This database contains several tables, but for the purposes of this
tutorial, we will only use the Customers table.
Create a Spring Boot Project
We created a new Spring Boot project with
Spring Initializr,
which will generate the basic structure of our Spring Boot project. The
pom.xml file for this project is 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 https://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.6.9</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.raven</groupId>
<artifactId>spring-boot-jdbctemplate</artifactId>
<version>1.0.0-SNAPSHOT</version>
<name>spring-boot-jdbctemplate</name>
<description>Spring Boot project with JDBCTemplate</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</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>
<!-- https://mvnrepository.com/artifact/org.springdoc/springdoc-openapi-ui -->
<dependency>
<groupId>org.springdoc</groupId>
<artifactId>springdoc-openapi-ui</artifactId>
<version>1.6.9</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>We have added the following dependencies:
-
Spring Boot DevTools- necessary development tools -
Rest Repositories- to expose Spring Data repositories over REST. -
JDBC API- Database Connectivity API that defines how a client may connect and query a database. -
MySQL Driver- JDBC driver for MySQL (for other DB you have to choose that dependency for that DB) -
Springdoc OpenAPI- for Swagger UI.
Connect to the Database
In the application.properties file, we mentioned the MySQL database
configurations and some Swagger OpenAPI-related configurations:
application-description=@project.description@
application-version=@project.version@
logging.level.org.springframework.boot.autoconfigure=ERROR
api.response-codes.ok.desc=OK
api.response-codes.badRequest.desc=BAD_REQUEST
api.response-codes.notFound.desc=NOT_FOUND
## first db
spring.datasource.jdbcUrl=jdbc:mysql://172.17.0.2:3306/classicmodels?useSSL=false
spring.datasource.username=root
spring.datasource.password=admin@123JdbcTemplate Configuration
In the config package, we created a class called
ApplicationConfiguration to configure the JdbcTemplate:
package com.raven.jdbctemplate.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
public class ApplicationConfiguration {
@Bean(name = "dataSource")
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "jdbcTemplate")
public JdbcTemplate jdbcTemplate1(@Qualifier("dataSource") DataSource ds) {
return new JdbcTemplate(ds);
}
}
The annotation @Configuration indicates that this class contains one
or more beans that can be used throughout the project.
We declared two beans using the @Bean annotation:
dataSource and jdbcTemplate. The spring context will manage
these beans, and we will not need to create a new instance each time we use
them.
The @ConfigurationProperties annotation is used to construct the
datasource by reading the application.properties file's
spring.datasource property values.
We use the @Qualifier annotation to specify the
datasource bean as our DataSource when creating a new instance of
JdbcTemplate.
Customer Model
To begin, we create a CustomerModel class in the
controller package to hold the data from the customer table. Here's
our CustomerModel class from the model package:
package com.raven.jdbctemplate.model;
public class CustomerModel {
private int customerNumber = 0;
private String customerName = "";
private String phone = "";
private String address1 = "";
private String city = "";
private String country = "";
public CustomerModel() {
super();
}
public CustomerModel(int customerNumber, String customerName, String phone, String address1, String city, String country) {
super();
this.customerNumber = customerNumber;
this.customerName = customerName;
this.phone = phone;
this.address1 = address1;
this.city = city;
this.country = country;
}
public int getCustomerNumber() {
return customerNumber;
}
public void setCustomerNumber(int customerNumber) {
this.customerNumber = customerNumber;
}
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress1() {
return address1;
}
public void setAddress1(String address1) {
this.address1 = address1;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
@Override
public String toString() {
return "CustomerModel [customerNumber=" + customerNumber + ", customerName=" + customerName + ", phone=" + phone
+ ", address1=" + address1 + ", city=" + city + ", country=" + country + "]";
}
}
We use some columns to map in CustomerModel, but you are free to map
other columns from the Customer table.
Repository
Make a repository package and add the
CustomerRepository interface to it. The
CustomerRepository interface defines a collection of abstract methods
for performing database CRUD operations:
package com.raven.jdbctemplate.repository;
import java.util.List;
import java.util.Optional;
import com.raven.jdbctemplate.model.CustomerModel;
public interface CustomerRepository {
// gets the total record count
int count();
// saves a customer
int saveCustomer(CustomerModel customerModel);
// updates an existing customer
int updateCustomer(CustomerModel customerModel, int id);
// deletes ann existing customer
int deleteCustomer(int id);
// get all customer
List<CustomerModel> findAll();
// get a customer by CustomerNumber
Optional<CustomerModel> findByCustomerNumber(int id);
}Repository Implementation
We now add the CustomerJDBCRepository class to the
repository package. This class will implement the
CustomerRepository interface and will have complete control over all
CRUD operations:
package com.raven.jdbctemplate.repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;
import com.raven.jdbctemplate.model.CustomerModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
@Repository
public class CustomerJDBCRepository implements CustomerRepository {
@Qualifier("jdbcTemplate")
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int count() {
return jdbcTemplate
.queryForObject("select count(*) from customers", Integer.class);
}
@Override
public int saveCustomer(CustomerModel customerModel) {
String sql = "INSERT INTO customers(customerName, contactLastName, contactFirstName, phone, addressLine1, city, country) " +
"VALUES(?,?,?,?,?,?,?)";
return jdbcTemplate.update(sql, customerModel.getCustomerName(),
customerModel.getCustomerName().split(" ")[1],
customerModel.getCustomerName().split(" ")[0],
customerModel.getPhone(),
customerModel.getAddress1(),
customerModel.getCity(),
customerModel.getCountry());
}
@Override
public int updateCustomer(CustomerModel customerModel, int id) {
String sql = "UPDATE customers " +
"SET customerName= ?, contactLastName= ?, contactFirstName= ?, phone=?, addressLine1=?, " +
"city= ?, country= ? WHERE customerNumber= ?;";
return jdbcTemplate.update(sql, customerModel.getCustomerName(),
customerModel.getCustomerName().split(" ")[1],
customerModel.getCustomerName().split(" ")[0],
customerModel.getPhone(),
customerModel.getAddress1(),
customerModel.getCity(),
customerModel.getCountry(),
id);
}
@Override
public int deleteCustomer(int id) {
String sql = "DELETE FROM customers WHERE customerNumber = ?";
return jdbcTemplate.update(sql, id);
}
@Override
public List<CustomerModel> findAll() {
String sql = "select customerNumber, customerName, phone, addressLine1, city, country from customers";
return jdbcTemplate.query(sql, new CustomerRowMapper());
}
@Override
public Optional<CustomerModel> findByCustomerNumber(int id) {
String sql = "select customerNumber, customerName, phone, addressLine1, city, country from customers where customerNumber = ?";
return jdbcTemplate.query(sql, new CustomerRowMapper(), id).stream().findFirst();
}
private class CustomerRowMapper implements RowMapper<CustomerModel> {
@Override
public CustomerModel mapRow(ResultSet rs, int rowNum) throws SQLException {
return new CustomerModel(rs.getInt("customerNumber"),
rs.getString("customerName"),
rs.getString("phone"),
rs.getString("addressLine1"),
rs.getString("city"),
rs.getString("country"));
}
}
}
CustomerRowMapper, an inner class that implements the
RowMapper interface, has been declared. Because RowMapper has
a method for mapping rows to objects, we override the
mapRow() method, which maps rows from the ResultSet to
CustomerModel. CustomerRowMapper is a database retrieval tool
that can return a single or a list of customer records.
Use jdbcTemplate.queryForObject() to retrieve a single row or value.
We are only concerned with the count(*) value in this case.
To retrieve the data, we used the query() method of JdbcTemplate,
which will retrieve the records from the database based on the SQL query and
map the records to CustomerModel objects using
CustomerRowMapper.
The update() method of JdbcTemplate is used to perform insert,
update, and delete operations, and it accepts a string containing your SQL
query and values as arguments.
Controller
Make a controller package that contains the
CustomerController class:
package com.raven.jdbctemplate.controller;
import com.raven.jdbctemplate.model.CustomerModel;
import com.raven.jdbctemplate.repository.CustomerJDBCRepository;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.media.ArraySchema;
import io.swagger.v3.oas.annotations.media.Content;
import io.swagger.v3.oas.annotations.media.ExampleObject;
import io.swagger.v3.oas.annotations.media.Schema;
import io.swagger.v3.oas.annotations.parameters.RequestBody;
import io.swagger.v3.oas.annotations.responses.ApiResponse;
import io.swagger.v3.oas.annotations.responses.ApiResponses;
import io.swagger.v3.oas.annotations.tags.Tag;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.validation.Valid;
import java.util.List;
import java.util.Optional;
@CrossOrigin(origins = "*", allowedHeaders = "*", maxAge = 4800, allowCredentials = "false", methods = {
RequestMethod.POST, RequestMethod.GET, RequestMethod.PUT, RequestMethod.DELETE })
@RestController
@RequestMapping("/api/customer")
@Tag(description = "APIs related with Customers", name = "Customer")
public class CustomerController {
private CustomerJDBCRepository customerJDBCRepository;
@Autowired
public CustomerController(CustomerJDBCRepository customerJDBCRepository) {
this.customerJDBCRepository = customerJDBCRepository;
}
@Operation(summary = "Total record count", description = "Get total Customer count")
@ApiResponses(value = {
@ApiResponse(responseCode = "200", description = "${api.response-codes.ok.desc}", content = {
@Content(mediaType = "application/json", array = @ArraySchema(schema = @Schema(implementation = CustomerModel.class))) }),
@ApiResponse(responseCode = "400", description = "${api.response-codes.badRequest.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }),
@ApiResponse(responseCode = "404", description = "${api.response-codes.notFound.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }) })
@RequestMapping(value = "/getRecordNumber", method = RequestMethod.GET)
@ResponseBody
public int getRecordNumber() {
return customerJDBCRepository.count();
}
@Operation(summary = "All customer", description = "Get all customer details")
@ApiResponses(value = {
@ApiResponse(responseCode = "200", description = "${api.response-codes.ok.desc}", content = {
@Content(mediaType = "application/json", array = @ArraySchema(schema = @Schema(implementation = CustomerModel.class))) }),
@ApiResponse(responseCode = "400", description = "${api.response-codes.badRequest.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }),
@ApiResponse(responseCode = "404", description = "${api.response-codes.notFound.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }) })
@RequestMapping(value = "/getAllCustomer", method = RequestMethod.GET)
@ResponseBody
public List<CustomerModel> getAllCustomer() {
return customerJDBCRepository.findAll();
}
@Operation(summary = "Customer details by customer number", description = "Customer details by customer number")
@ApiResponses(value = {
@ApiResponse(responseCode = "200", description = "${api.response-codes.ok.desc}", content = {
@Content(mediaType = "application/json", array = @ArraySchema(schema = @Schema(implementation = CustomerModel.class))) }),
@ApiResponse(responseCode = "400", description = "${api.response-codes.badRequest.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }),
@ApiResponse(responseCode = "404", description = "${api.response-codes.notFound.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }) })
@RequestMapping(value = "/{id}", method = RequestMethod.GET)
@ResponseBody
public Optional<CustomerModel> getCustomerByNumber(@PathVariable("id") int id) {
return customerJDBCRepository.findByCustomerNumber(id);
}
@Operation(summary = "Save customer details", description = "Save customer details")
@ApiResponses(value = { @ApiResponse(responseCode = "200", description = "${api.response-codes.ok.desc}"),
@ApiResponse(responseCode = "400", description = "${api.response-codes.badRequest.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }),
@ApiResponse(responseCode = "404", description = "${api.response-codes.notFound.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }) })
@RequestMapping(value = "/save", method = RequestMethod.POST)
@ResponseBody
public int save(@Valid @RequestBody CustomerModel customerModel) {
return customerJDBCRepository.saveCustomer(customerModel);
}
@Operation(summary = "Update customer details", description = "Update customer details using customer number")
@ApiResponses(value = { @ApiResponse(responseCode = "200", description = "${api.response-codes.ok.desc}"),
@ApiResponse(responseCode = "400", description = "${api.response-codes.badRequest.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }),
@ApiResponse(responseCode = "404", description = "${api.response-codes.notFound.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }) })
@RequestMapping(value = "/update/{id}", method = RequestMethod.PUT)
@ResponseBody
public int update(@Valid @RequestBody CustomerModel customerModel, @PathVariable("id") int id) {
return customerJDBCRepository.updateCustomer(customerModel, id);
}
@Operation(summary = "Delete a customer", description = "Delete a customer using customer number")
@ApiResponses(value = { @ApiResponse(responseCode = "200", description = "${api.response-codes.ok.desc}"),
@ApiResponse(responseCode = "400", description = "${api.response-codes.badRequest.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }),
@ApiResponse(responseCode = "404", description = "${api.response-codes.notFound.desc}", content = {
@Content(examples = { @ExampleObject(value = "") }) }) })
@RequestMapping(value = "/delete/{id}", method = RequestMethod.DELETE)
@ResponseBody
public int delete(@PathVariable("id") int id) {
return customerJDBCRepository.deleteCustomer(id);
}
}
The @RestController annotation is added to the controller class,
informing Spring Boot that this is a REST-based service and that the
request/response data will be serialized or deserialized to JSON
automatically.
The request base path was specified in @RequestMapping as
/api/customer. The @RequestMapping annotation informs the
Spring container that the specified HTTP endpoint is available for this
service.
@Tag is an Open API annotation that allows you to change resource
details like the resource name and description.
Another Open API annotation is @Operation, which is used to customize
the API's name and description.
@ApiResponses is another Open API annotation that specifies the API
response format, such as success and error response formats.
👉 Run the application now. The Swagger UI is accessible at
http://localhost:8080/swagger-ui/index.html, where we can see a list of APIs:
You can download the
source code.
Happy coding!!! 😊
