Support JPA 2.1 stored procedures returning result sets [DATAJPA-1092]

Explanation of the problem

The issue at hand pertains to the support for stored procedures (SPs) returning result sets in certain databases, such as MS SQL and MySQL. These databases allow SPs to return one or multiple result sets using simple SELECT statements. This pattern is commonly observed in MS Transact-SQL. The JPA 2.1 specification explicitly supports the mapping of SP result sets in the context of named stored procedure queries. It provides options to specify the mapping of result sets using either resultClasses or resultSetMappings annotations. When there are multiple result sets, the mappings should be specified in the same order as the result sets will be returned. If no explicit resultClasses or resultSetMappings are provided, the result sets are assumed to be returned as a list of type Object[]. However, Spring Data JPA does not seem to support this feature, as indicated in a related discussion on Stack Overflow.

To demonstrate the issue, an example test scenario is presented using pure JPA 2.1 executed within a Spring Boot 1.5.2 application. The example involves creating a stored procedure called “tmp_demo” that takes an integer argument. The procedure simply performs a SELECT operation on the argument, adding 1 to it, and returns the result as the “simple_result”. To handle this scenario, a custom repository named CustomDemoRepository is defined. It utilizes the EntityManager to create a StoredProcedureQuery object, registering the required parameter and setting its value. The getResultList method is called to obtain the result set from the stored procedure execution.

Troubleshooting with the Lightrun Developer Observability Platform

Getting a sense of what’s actually happening inside a live application is a frustrating experience, one that relies mostly on querying and observing whatever logs were written during development.
Lightrun is a Developer Observability Platform, allowing developers to add telemetry to live applications in real-time, on-demand, and right from the IDE.

• Instantly add logs to, set metrics in, and take snapshots of live applications
• Insights delivered straight to your IDE or CLI
• Works where you do: dev, QA, staging, CI/CD, and production

Start for free today

Problem solution for Support JPA 2.1 stored procedures returning result sets [DATAJPA-1092]

Other popular problems with Spring Projects Spring Data JPA

@OneToOne(fetch = FetchType.EAGER)
private Address address;

EntityGraph graph = entityManager.createEntityGraph("graph.Person.address");
Map hints = new HashMap();
hints.put("javax.persistence.fetchgraph", graph);

Person person = entityManager.find(Person.class, 1L, hints);

EntityGraph graph = entityManager.createEntityGraph("graph.Person.address");
Map hints = new HashMap();
hints.put("javax.persistence.fetchgraph", graph);

List<Person> people = entityManager
        .createQuery("SELECT p FROM Person p", Person.class)
        .setHint("javax.persistence.fetchgraph", graph)
        .getResultList();

List<Person> people = entityManager
        .createQuery("SELECT p FROM Person p JOIN FETCH p.address", Person.class)
        .getResultList();

@ManyToMany(mappedBy = "people")
private List<Project> projects;

This will allow you to navigate from the related entity to the parent entity.

for (Project project : person.getProjects()) {
    System.out.println(project.getName());
}

A brief introduction to Spring Projects Spring Data JPA

Spring Data JPA is a library for simplifying the implementation of data access layers in Spring-based applications. It provides a convenient and efficient way to interact with databases using the Java Persistence API (JPA) and to implement common data access operations, such as CRUD (create, read, update, delete) operations.

Spring Data JPA extends the functionality provided by JPA and adds additional features, such as dynamically generated query methods, pagination and sorting, and the ability to interact with multiple databases using the same API. It also supports the use of different JPA implementations, such as Hibernate, EclipseLink, and OpenJPA. With the use of Spring Data JPA, developers can significantly reduce the amount of boilerplate code required to implement data access layers and focus on the application’s business logic.

Most popular use cases for Spring Projects Spring Data JPA

1. Implementing data access layers for JPA-based applications:

Spring Data JPA can be used to simplify the implementation of data access layers in applications that use JPA. It provides a convenient and efficient way to interact with databases, perform common data access operations, and handle the underlying JPA implementation details.

For example, with Spring Data JPA, you can implement a simple data access layer using the following code:

@Repository
public interface PersonRepository extends JpaRepository<Person, Long> {

}

2. Generating dynamic queries:

Spring Data JPA supports the generation of dynamic queries based on method names. This allows developers to write concise and expressive query methods without the need to write JPQL or SQL statements.

For example, you can retrieve all the Person entities with a specific firstName by defining the following method in the repository:

List<Person> findByFirstName(String firstName);

3. Providing support for pagination and sorting:

Spring Data JPA supports pagination and sorting of query results. This allows developers to retrieve a subset of the results and present them in a specific order without the need to manually write the code to implement these features.

For example, you can retrieve a page of Person entities sorted by lastName by defining the following method in the repository:

Page<Person> findByAgeGreaterThan(int age, Pageable pageable);