Invalid data access resource usage exception

Tech Tutorials

Tutorials and posts about Java, Spring, Hadoop and many more. Java code examples and interview questions. Spring code examples.

Saturday, July 11, 2020

Data Access in Spring Framework

This Spring data access tutorial provides basics about the DB access through Spring framework like how Spring abstracts the data access, how you can use templates for data access, Spring’s data access exception handling.

If you have to describe how data access is handled by Spring Framework using 2 keywords then those 2 words will be-

  • Abstraction
  • Agnostic

Now JDBC or any ORM framework do provide abstraction of its own and you use JDBC or ORM as an abstract layer to interact with DB then what abstraction does spring provide?

Again two keywords are-

  • Template
  • Exception Handling

Before delving any further into those keywords let’s talk about the “agnostic” feature too (which will cover the Exception handling point too!). It is said that data access exception handling in Spring framework is platform agnostic. What does that mean?

Spring’s data access exception handling is platform agnostic

If you have used JDBC you must be knowing that it forces you to catch SQLException. SQLException is an exception that provides information on a database access error or other errors some of the scenarios when SQLException can be thrown are-

  • The application is not able to connect to the DB.
  • SQL Query which has to be executed is not correct syntactically.

But the question here is by catching SQLException you can’t do much anyway. Also the SQLException hierarchy is not very rich. Though it does provide a String describing the error and an integer error code that is specific to each vendor which is helpful in knowing about the error.

Coming to Spring framework it provides data access mechanism for getting data through JDBC, various ORM frameworks like Hibernate, Ibatis. Now think of a situation where Spring provides the layer over the JDBC or any ORM framework but doesn’t provide any mechanism to handle exceptions thrown by them. In that case your code will become a hotchpotch of Spring templates and then exception handling by JDBC, Hibernate, Ibatis or any other way of data access. That’s where Spring’s platform agnostic exception handling comes to the rescue.

For JDBC package provides SQLException translation functionality and some utility classes. Exceptions thrown during JDBC processing are translated to exceptions defined in the org.springframework.dao package. This means that code using the Spring JDBC abstraction layer does not need to implement JDBC or RDBMS-specific error handling.

All translated exceptions are unchecked exceptions, which gives you the option of catching the exceptions from which you can recover while allowing other exceptions to be propagated to the caller.

Same way implementations of PersistenceExceptionTranslator interface in Spring provides exception translation for data access technologies that throw runtime exceptions, such as JPA, TopLink, JDO and Hibernate.

Using these translator classes Spring translates the platform specific exception to the Spring specific exception under the hood, so you are abstracted from how it is done.

Some of the advantages of the exception handling mechanism followed by Spring are-

  1. Non-intrusive– Since data access exceptions thrown by Spring are not checked exceptions so user is not forced to handle it or declare it. That way you are not making your application tightly coupled with the Spring APIs.
  2. Catch Late— In case of checked exceptions, it is enforced by Java compiler to either catch the exception or declare it in throws clause. So generally developer tends to catch it and do nothing except printing stacktrace or put a logger in order to avoid the compiler error. But that way we are not providing the true information of what exactly happened. It is better to catch exception only when it can be handled appropriately. Since Spring’s data access exceptions are unchecked exceptions theses exceptions can be thrown up the call hierarchy, without the botheration of declaring with throws clause or rethrowing them, and the best place in the hierarchy can handle it more effectively.
Читайте также:  After multiple tries the operating system on your pc failed to start error code 0xc0000001

Spring data access Exception Hierarchy

Spring’s exception hierarchy is quite rich and the main thing is that it can be used with any persistence solution. Whatever persistence solution you are using, if exception is thrown Spring framework will translate it and throw a consistent set of exceptions.

Parent class in Spring’s exception hierarchy is DataAccessException and important point about is that it is an unchecked exception. So, you are not forced to catch Spring’s data access exceptions, though you can catch them if you want. In fact the convention is that you should not handle any exceptions in the DAO layer, instead throw it to the front-end and handle it.

Some of the sub classes of the DataAccessException class are– BadSqlGrammarException, DuplicateKeyException, EmptyResultDataAccessException, CannotGetJdbcConnectionException, QueryTimeoutException. There are many more if you want to go through the whole list please refer Spring reference doc.

In order to take advantage of Spring’s data-access exceptions, you need to use one of the data access templates provided by Spring framework. That brings us to the point about templates.

Using Templates for Data Access in Spring

Spring provides many templates for data access (For JDBC, for Hibernate, for MongoDB and many more). If you have idea about template design pattern you can get an idea what these templates are doing.

You generally use Template design pattern if you have to design the functionality where some implementation is common among the classes and some implementation differs. So in template design pattern —

  • There is a base class with common implementations.
  • Methods that require individual implementation are mere place holder in base class.
  • Sub classes can provide implementation for those place holder methods.

So, base class provides the template of the implementation and fill what it can (common implementation) and delegates the other parts to the implementing classes.

Same process is used by the Spring data access. Whatever persistence mechanism is used there are some common steps like getting the DB connection, handling exception if thrown and cleaning up the resources (closing the connection) once done. These can be termed as fixed part.

But how and what data is accessed, what data is updated is different for different application. That can be termed as variable part.

Template classes in Spring framework provide implementation for the fixed part and uses a callback approach to handle variable part which is your custom data access code, benefit of template class is that it frees application code from having to do the boilerplate tasks like getting DB connection, handling exceptions, closing connection and results in code that is intention driven. That way the code that is written focuses solely on what the developer wants to do.

As example— JdbcTemplate class is the central class in the JDBC core package. It handles the creation and release of resources, which helps you avoid common errors such as forgetting to close the connection.
It performs the basic tasks of the core JDBC workflow such as statement creation and execution, leaving application code to provide SQL and extract results. The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

  • Refer Spring JdbcTemplate Insert, Update And Delete Example to see how to use JDBCTemplate in order to insert, update and delete.
Читайте также:  Protocol error node js

When you use the JdbcTemplate for your code, you only need to implement callback interfaces, giving them a clearly defined contract. The PreparedStatementCreator callback interface creates a prepared statement given a Connection provided by this class, providing SQL and any necessary parameters. The same is true for the CallableStatementCreator interface, which creates callable statements. The RowCallbackHandler interface extracts values from each row of a ResultSet.

  • Refer Spring JdbcTemplate Select Query Example to see an example of RowMapper Spring callback.

Some of the templates provided by Spring framework-

  • jdbc.core.JdbcTemplate– For JDBC connections.
  • jdbc.core.namedparam.NamedParameterJdbcTemplate— For JDBC connections with named parameters.
  • orm.hibernate3.HibernateTemplate– For Hibernate 3.x sessions.
  • orm.hibernate4.HibernateTemplate— For Hibernate 4.x sessions.
  • org.springframework.orm.hibernate5.HibernateTemplate— For Hibernate 5.x sessions.

In order to manage the fixed part like getting connection, releasing resources Spring template needs a reference to a DataSource. Refer Configuring DataSource in Spring Framework to see different ways to configure data source in Spring.

That’s all for this topic Data Access in Spring Framework. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Spring Data JPA with central exception handling and VO validations – framework

1. Introduction

Over the time, Spring framework became the de-facto standard to create any applications that are REST API based. Spring offers a variety of out of the box components to avoid writing the repeated and cumbersome boiler plate code. Also, the beautiful thing about Spring is that if there is a ready solution; it offers you the effortless ways to integrate with that available library/framework. In this article let’s see how to write a Spring based RESTful API’s using a full stack spring technology; Spring Boot, Spring Validations and Spring data JPA with an example that shows a complete information on the following:

  • Spring Boot and it’s configuration
  • Dependency Management with Spring boot starters
  • Avoiding bottleneck DAO code using Spring data JPA.
  • Spring’s support for validations at VO level.
  • Centralized exception handling.

We use the Gradle for dependency management and as the build tool. Let’s go through the steps.

2. Generate the project

Here are the steps that needed to be followed to generate the project.

2.1 Spring Intializer

Spring provides an easy to start project generation tool at this location Spring INITIALIZR. At this web page you can boot strap your application by adding the required dependencies. You can generate the project skeleton by adding the 3 dependencies mentioned below (please see the image below for clear understanding).
1. ‘Web’: This dependency is needed to code the web layer and to create the APIs. When the project gets generated, it shows up as the following dependency in the build.gralde file.
2. ‘Validation’ : To enable the spring validation. It shows up as the following dependency in the build.gradle. compile(‘org.springframework.boot:spring-boot-starter-validation’)
3. ‘JPA’ : To enable the usage of spring data JPA. It shows up as the following dependency in the build.gradle.

2.2 Eclipse configuration

Generate the project and import it to eclipse. Once this is done, you are all set to create your API. The project imported in the eclipse should look like the below.

3. Create the API

Before writing the API, let’s create the packages as per the Java conventions as shown below.

With the generated code, we get a class at the root package i.e. com.example.spring.springrestapp. This is our bootup class.

Note: Startup classes should be created at the root package level with the default configurations.
Now let’s go ahead and create a class for controller and an API method to add user details to DB. For this API which we are going to build, let’s assume some constraints as our requirements:

  • This API should collect First name, Last name, email, address and phone number of a user and store them in MySQL DB
  • API callers to pass First name, last name and email as mandatory fields. Email should be validated for its format.
  • Home address and phone number can be optional.
Читайте также:  How to fix certificate error

4. Configure DB details:

For this project, you need to have a local instance of MySQL DB running. You can provide the DB details in as below.

In the MySQL DB, lets create a table containing first name, last name, email, address, and phone number in the MySQL DB using the following script.


5. Configure the Spring Data JPA

Once table is ready, we need to map it a Java object using the JPA. Every field of the table is mapped in the java object using the annotations. Below is how our entity is going to look.

Now, create the Spring data JPA repository. Data JPA repositories can be created by extending the interface JpaRepository as below. (Note that you need to pass the entity and data type of the ID field. In my example, entity is User and type of id field is Integer)

That simple, our DAO code is ready! Spring takes care of generating the underlying DAO implementation.

6. Service and Controller layer

Let’s now create a service class just to save the User details. You can add the business logic as per your needs in the methods.

Let’s now go and create a controller and API method. The saveUser api accepts the json data in request body and returns the response as JSON in body.

@RequestMapping is used for mapping the resources.
@PostMapping is same as HttpPost assigned to @RequestMapping .

7. Configuring VO level validations

Our API needs validations on the data it receives as per the requirements mentioned in the beginning. For that, we are going to apply data validations at entity level as shown below.

Note that the annotation @NotBlank is to not allow empty or null values and @Email to check a valid email format. Also, we added messages for the failed validations.

Now, we need to tell Spring to do validations as per the annotations specified in the entity. For that, we can use @Valid annotation on request payload as below.

8. Configure exception Handling

When a validation is failed, we need to give the properly formatted error response to the API consumers. For example, if first name is not given, I want to return error message in below format with HTTP error code bad request. Its’s not good idea to give exception stack trace for the API consumers.

We can do it in each API controller method or we can create a single global exception handling, thus avoid writing duplicate code in multiple places for the same need.
To handle the central exception in a single place, we take advantage of Spring’s error handler. With Spring 3.2, @ControllerAdvice is provided to globalize the exception/error handling. To return error response lets create a VO having error Code and message.


When a validation fails, Spring throws a MethodArgumentNotValidException . We can catch this exception and extract the error message from the thrown exception. We use @ExceptionHandler to catch the exception as below.

@ResponseStatus is used to specify the HTTP Bad request status.
@ResponseBody makes sure error is written to response body.

9. Conclusion

Now let’s test the API.

Case 1: validation failure
URL: http://localhost:8080/restApp/api/user/save
RequestPayload: Notice the blank first name

Response: with Http Status 400

Case 2: When provided all required values
Request payload: