Creating Spring Boot CRUD Rest APIs with Data JPA and JDBC — Part 2

In the previous part i.e. Creating Spring Boot CRUD Rest APIs with Data JPA and JDBC — Part 1 , we implemented REST APIs for CRUD operations using Data JPA. In this part, we will be implementing the same functionalities using JDBC.

Technology Stack

Spring boot framework

Maven 3.6.3

MySQL 8.0.19

Setting up the Project and Database

Set up the project and database as done in Part 1.

In the case of this project add the following dependencies: Spring Web, Spring Data JDBC, MySQL Driver.

Dependencies

The REST endpoints also remain the same.

Project Layout and Implementation

Project Layout

Implementation of REST CRUD APIs

• Presentation Layer — UserController

UserController is a Rest Controller which exposes REST APIs to the client.

UserServiceImpl class (Service Layer) is autowired in the controller. The methods in the controller call the exposed methods of the service layer to create, update, delete and retrieve users.

The APIs send back a response to the client with a HTTP status (CREATED or OK) along with user/s object in case of GET methods.

To understand the working of the Rest Controller and the annotations used refer Part 1.

• Service Layer — UserService & UserServiceImpl

UserService is an interface which defines the methods implemented in UserServiceImpl class. They are a part of the service layer and used to implement the business logic of the code.

UserDaoImpl class (DAO Layer) is autowired which exposes the methods of the DAO Layer.

• User Bean

The User bean is used by the Data Access Layer to perform CRUD operations.

• Data Access Layer — UserDao & UserDaoImpl

UserDao is an interface which defines the methods implemented in UserDaoImpl class. They are a part of the DAO layer and contain the methods which are used to perform operation on the database.

JDBC produces a lot of boilerplate code like managing the connection to the database, handling SQL exceptions. The Spring JDBC API is an adaptation of the standard JDBC API. JdbcTemplate is one of the ways provided by Spring JDBC to remove boilerplate code, mapping data to classes.

JdbcTemplate manages the connection to the database, executes SQL queries, iterates over the result set retrieving the values. It also handles the exceptions and translates the exceptions into exceptions which are defined under org.springframework.dao package.

The properties for the datasource are configured in application.properties and utilised by JdbcTemplate which is autowired in UserDaoImpl class.

spring.datasource.url = jdbc:mysql://localhost:3306/userdb
spring.datasource.username = 
spring.datasource.password = 
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto = update

Spring Boot gets the datasource properties and injects it to JdbcTemplate object while auto wiring.

Refer to Part 1 to understand the properties defined above.

A RowMapper is implemented to process the ResultSet, mapping each row to an object.

The UserRowMapper class implements RowMapper and overrides its mapRow method which maps a row of the result to the User class.

Creating a User

The query to create a user is defined in INSERT_USER_QUERY.

The save method of the UserDaoImpl class then uses the update method of JdbcTemplate.

update(String sql, Object... args)

The parameters are defined in new Object[] {……} in the order they are written in the query.

The update method binds the given arguments with the sql query and performs an update operation to insert, update or delete with the help of a prepared statement.

It returns an integer count of the number of rows that were changed after the execution of the query.

Updating a User

The query to update a user is defined in UPDATE_USER_QUERY.

The update method of the UserDaoImpl class then uses the update method of JdbcTemplate.

Deleting a User

The query to delete a user is defined in DELETE_USER_QUERY.

The delete method of the UserDaoImpl class then uses the update method of JdbcTemplate.

Retrieving all Users

The query to retrieve all users is defined in GET_USERS_QUERY.

The findAll method of the UserDaoImpl class then uses the query method of JdbcTemplate.

query(String sql, RowMapper<T> rowMapper)

The query method executes the SQL query and maps each row to the result object using the RowMapper specified in the argument.

It returns a list of the result object which was mapped by the mapper.

The custom RowMapper used in this example is the UserRowMapper defined above.

Retrieving a User by ID

The query to retrieve a user by ID is defined in GET_USER_BY_ID_QUERY.

The findById method of the UserDaoImpl class then uses the queryForObject method of JdbcTemplate.

queryForObject(String sql, RowMapper<T> rowMapper, Object... args)

The SQL query and the list of arguments are bound by the prepared statement to execute the query, the result of which is mapped to the result object using the RowMapper.

It returns the result object which was mapped by the mapper.

Test the APIs as shown in Part 1.

Find the link to complete code here.