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

[spring-projects-issues]

Dmitriy Korobskiy opened DATAJPA-1092 and commented

Some databases: notably MS SQL and MySQL (but not Oracle) support SPs returning result sets (one or multiple) via simple SELECTs. In MS Transact-SQL this seems to be a pervasive pattern. JPA 2.1 spec explicitly supports mapping of SP result set(s) e.g. in 3.10.17.1 Named Stored Procedure Queries:

A stored procedure may return more than one result set. As with native queries, the mapping of result sets can be specified either in terms of a resultClasses or as a resultSetMappings annotation element. If there are multiple result sets, it is assumed that they will be mapped using the same mechanism — e.g., all via a set of result class mappings or all via a set of result set mappings. The order of the specification of these mappings must be the same as the order in which the result sets will be returned by the stored procedure invocation. If the stored procedure returns one or more result sets and no resultClasses or resultSetMappings element has been specified, any result set will be returned as a list of type Object[]. The combining of different strategies for the mapping of stored procedure result sets is undefined.

This feature does not seem to be supported by Spring Data JPA: e.g. see https://stackoverflow.com/questions/31097667/illegalargumentexception-type-cannot-be-null. I spent some time trying to make it work with both 1.5.2 and the current 2.0 snapshot to no avail.

Here is a test example working via pure JPA 2.1 (executed in Spring Boot 1.5.2 app):

CREATE PROCEDURE tmp_demo (@arg INT)
AS
BEGIN
  SELECT @arg + 1 AS simple_result;
END;

CustomDemoRepository:

import DemoResult;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureQuery;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
@Data
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class CustomDemoRepository {

  //region Injected beans (via a RequiredArgsConstructor)
  private final EntityManager em;
  //endregion

  public List<DemoResult> execStoredProdDirectly(Integer arg) {
    StoredProcedureQuery spq = em.createStoredProcedureQuery("tmp_demo", DemoResult.class);
    spq.registerStoredProcedureParameter("arg", Integer.class, ParameterMode.IN);
    spq.setParameter("arg", 42);
    return spq.getResultList();
  }
}

DemoResult:

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.Id;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Data // = @ToString @EqualsAndHashCode @Getter-s @Setter-s (non-final) @RequiredArgsConstructor
@NoArgsConstructor
public class DemoResult implements Serializable {

  @Id private Integer simpleResult;
}

My thoughts:

1. I understand that Spring Data JPA tries to map an SP OUT parameter to a repository @Procedure method result. This is fine, but when such a method is expected to return a list (List<DomainObject>) it means that developers need to get a result set out of an SP. In MS SQL result sets can be returned implicitly without declaring any additional SP params.
2. Multiple result sets are used more rarely and support for those could be done separately from a single result set.

---

Affects: 1.11.1 (Ingalls SR1)

Reference URL: https://stackoverflow.com/questions/31097667/illegalargumentexception-type-cannot-be-null

Issue Links:

• DATAJPA-1555 InvalidDataAccessApiUsageException occurs when using @Procedure annotation
  ("is duplicated by")

2 votes, 5 watchers

[spring-projects-issues]

Jeff Sheets commented

I looked into implementing this, but the major stumbling block for @NamedStoredProcedureQuery is writing tests since the Hibernate HSQLDialect does not support REF_CURSOR ResultSets yet. Looking around, H2Dialect and MariaDBDialect also do not support.

If the code is tested locally against a licensed DB2 database, is there any guidance for how to submit the PR since tests will not be possible?

Spring Data JPA's @Procedure annotation can also be used for direct JPA adhoc stored procedure calls if we add a resultClasses param to the annotation. At least tests can be written for this since the direct calls do not use hibernate.

The simplest return type I can think of would be to just have the query return List<?> and have the ResultSet(s) be the last items in the List. The other option would be returning a new object like StoredProcedureResult that holds a List of outputParamValues and a List of resultSets

[spring-projects-issues]

Jens Schauder commented

Using DB2 in a test would be big handicap for a PR, almost a show stopper.

But I guess one could use a free database like MySql or Postgres via Testcontainers. We already do that for Spring Data JDBC.

[netdragonboberb]

For the thousands of others reading this that are up against the same brick wall, before jumping to another library: I had the most luck using a straight Hibernate Session access with createQuery/createNativeQuery and setParameterList which is NOT available with JPA. However, due to different SQL dialects (e.g. PLPGSQL vs HQL) it's still tricky and you'll get things like mismatch of array notation you have to work through (e.g. missing brackets for postgres on createNativeQuery). I'll follow up with "workaround" code samples when I get it working then turn the code into Foos and Bars.

Annotations in repos like @procedure and @NamedStoredProcedure won't work too well for you for anything beyond basic. If you don't have arrays, you can use a custom JPA method. However, if you have arrays that isn't going to work for you unless you hack it in as a string and manually build the array list to feed into the driver basically building a full SQL string to call the stored proc, which is messy. It also won't work with Hibernate sessions and createStoredProcedureQuery. Stackoverflow will mislead you in a lot of cases because they are talking about simpler cases than you may be facing if you already are reading this.

Hibernate sessions and createQuery/createNativeQuery makes the arrays work with the aforementioned setParameterList method, and some elbow grease.

Before you tear your hair out, you should consider inline SQL or QueryDSL as well. Makes unit testing easier too. However, there are reasons companies often use procedures,views, and functions. Also, I'd like to see someone take a large stored proc or view written by a data architect and re-write that in QueryDSL or even inline SQL. Good luck with that.

I think Spring really needs to make this happen to be in line with other solutions. By the time a developer realizes these limitations, they are already on track to miss deadlines, and architects' estimates destroyed because no one ever expects this limitation to exist in such a widely used product. I figure this story has happened thousands of times.

Sadly, I think the limitation is in JPA, so it's not as simple as it seems. Multiple layers will need to be adjusted.

[netdragonboberb]

Follow-up: I thought I should say what I ended up with. It turned out I didn't need setParameterList, but some of you may...
Since this is for postgres, you may be using table type parameters in other sql implementations (e.g. mysql or mssql) and have to change this a bit.

Because this is just an array parameter I was able to do this:

	public List<Foo> findFoo(Collection<java.util.Date> dates, Integer someOtherVar)
	{

		var session = entityManager.unwrap( Session.class );

			var sql = "select * from my_function(:months, :some_other_var)"; 

			var spq = session.createNativeQuery(sql, Foo.class);

			spq.setParameter("months", dates.toArray(), DateArrayType.INSTANCE); // DateArrayType from types_52

			spq.setParameter("some_other_var", someOtherVar);

			return spq.getResultList();
}

effectively, it's doing something similar to:

session.createNativeQuery("select * from my_function(array [cast('20180101' as date),cast('20180102' as date)],100)", Foo.class);

Here is the postgres function (with changes to remove specific vars):

 create or replace function my_function(months date[], some_other_var integer) returns setof foo

 as $$

 begin

	return query

	with f_months as (select distinct d from unnest(months) as d)

	select

			f.*

	from foo f

	inner join  f_months cdate on

	(f.effective_from is null or date_trunc('month', f.effective_from) <= date_trunc('month', cdate.d) )

			and (f.effective_to is null or date_trunc('month', f.effective_to) > date_trunc('month', cdate.d) )

	where

		brg.some_other_var = some_other_var

		and f.is_removed = false

		and f.is_active = true;

 end;

 $$

 language plpgsql;

 -- Example:
 -- SELECT public.my_function(array['2020-10-01', '2020-09-01']::date[],10100);

To import types-52 for the DataTypeArray.INSTANCE in pom.xml

        <dependency>
            <groupId>com.vladmihalcea</groupId>
            <artifactId>hibernate-types-52</artifactId>
            <version>2.10.1</version>
        </dependency>

Warning: Due to changing the code a bit so it was more general, I can't guarantee it will work out of the box, but should only need minor tweaking

Note:

I believe you'll need setParameterList for a sql IN clause (correct me if I'm wrong), however that isn't the use case of sending and receiving arrays/lists to functions/procedures:

			var session = entityManager.unwrap( Session.class );

			var sql = "select * from foo where bar in :months)";

			var spq = session.createNativeQuery(sql, Foo.class);

			spq.setParameterList("months", dates.toArray(), DateArrayType.INSTANCE); // DateArrayType from types_52

			return spq.getResultList();

[netdragonboberb]

If you absolutely run up against a wall, you can do something like this and then just toss it into the sql directly (again, postgres)

	private String getFormattedArray(Collection<Date> dates)
	{
		var strings = new ArrayList<String>();
		for (var date: dates)
		{
			strings.add("cast('" + date.toString() + "' as date )");
		}

		return "array [" + String.join(",", strings) + "]";
	}

and use as follows (can be done with entityManager this time)

	public List<Foo> findFoo(Collection<java.util.Date> dates, Integer someOtherVar)
	{
			var sql = "select * from my_function("
					  + getFormattedArray(dates)
					  +", :some_other_var)"; // Warning: With xmin as 0, you can't re-save rule groups
			var spq = entityManager.createNativeQuery(sql, Foo.class);
			spq.setParameter("some_other_var", someOtherVar);
			return spq.getResultList();
	}

[schauder]

Triage result: This would be a nice feature but won't be implemented in the near future.

A workaround is available: Execute the query in a custom method using either an injected EntityManager or a JDBC Connection possibly wrapped in a JdbcTemplate.

[mp911de]

Implemented via #1959