Enhance support getting result sets from stored procedures lacking SET NOCOUNT ON (especially using JPA)

[DKroot]

I'm writing an app that uses JPA with a Hibernate JPA provider. MS SQL Server database has a lot of legacy stored procedures that are supposed to be used as-is with zero modifications for this project. Most of them return data via SELECTing result sets. Some of them lack SET NOCOUNT ON.

It's possible to deal with such SPs via JDBC API as shown in Calling Microsoft SQL Server Stored Procedures from a Java Application Using JDBC. However, we'd definitely prefer to avoid low-level programming and manually mapping result sets.

With JPA, I found two ways to get result sets from such SPs, but both of them work only with jTDS driver and not with MS JDBC driver. Is this possible? If not, can it be supported?

Solution 1.

private final EntityManager em;

public <T> List<T> queryViaMsSqlStoredProc(String procedureName, Class<T> resultClass,
    Object... spArgs) {
  String spBindParams = (spArgs.length == 0) ? "" : "?" + Strings.repeat(",?", spArgs.length - 1);
  String spQuery = String.format("EXEC %s %s", procedureName, spBindParams);
  Query q = em.createNativeQuery(spQuery, resultClass);
  int pos = 0;
  for (Object arg : spArgs) {
    q.setParameter(++pos, arg);
  }
  return q.getResultList();
}

Solution 2.

private final EntityManager em;

public <T> List<T> queryViaMsSqlStoredProc(String procedureName, Class<T> resultClass,
    Object... spArgs) {
  String spBindParams = (spArgs.length == 0) ? "" : "?" + Strings.repeat(",?", spArgs.length - 1);
  String spQuery = String.format("{call %s(%s)}", procedureName, spBindParams);
  Query q = em.createNativeQuery("SET NOCOUNT ON; " + spQuery, resultClass);
  int pos = 0;
  for (Object arg : spArgs) {
    q.setParameter(++pos, arg);
  }
  return q.getResultList();
}

[v-nisidh]

connect to #309

[ajlam]

Related to #244