Thursday, July 28, 2022

How To Use JDBCTemplate In Spring Boot With Swagger OpenAPI

JAVA,REST API,JDBCTemplate,Swagger,Open API,Spring Boot,programming,software development,technology
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@123

JdbcTemplate 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:
JAVA,REST API,JDBCTemplate,Swagger,Open API,Spring Boot,Spring,


You can download the source code.
Happy coding!!! 😊
in

References


No comments:

Post a Comment

Popular posts