Hibernate Stored Procedure calls with multiple named parameters and result set not working

Question

I have a stored procedure which takes in a list of inputs and then outputs a result set. The DB used is SQL Server 2008 and the JPA 2.1 spec provider is Hibernate.

I have the entity structure as below:

@NamedStoredProcedureQuery(name = "ModelSearchByModel",
        resultClasses = ModelSearchByModelEntity.class,
        procedureName = "SPR_ModelSearchByModel",
        parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "RegionCode", type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "BuildingCode", type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "ProductCode", type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "StatusCode", type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "MinWidth", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "MaxWidth", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "MinDepth", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "MaxDepth", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "MinArea", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "MaxArea", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "MinImperviousArea", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "MaxImperviousArea", type = Integer.class)
})
@Entity
public class ModelSearchByModelEntity implements Serializable{

//Properties with @Id, @Basic and @Column annotations, along with getters and setters
}

Now, I tried to use Spring DATA JPA

public interface IModelSearchByModelRepository extends Repository<ModelSearchByModelEntity, String> {

    @Procedure(name = "ModelSearchByModel")
    List<ModelSearchByModelEntity> findByCriteria(@Param("RegionCode") String regionCode,
                                                  @Param("BuildingCode") String buildingCode,
                                                  @Param("ProductCode") String productCode,
                                                  @Param("StatusCode") String statusCode,
                                                  @Param("MinWidth") Integer minWidth,
                                                  @Param("MaxWidth") Integer maxWidth,
                                                  @Param("MinDepth") Integer minDepth,
                                                  @Param("MaxDepth") Integer maxDepth,
                                                  @Param("MinArea") Integer minArea,
                                                  @Param("MaxArea") Integer maxArea,
                                                  @Param("MinImperviousArea") Integer minImperviousArea,
                                                  @Param("MaxImperviousArea") Integer maxImperviousArea);
}

With the above, code the program fails with "Invalid mix of positional and named parameters".

Then I tried to ignore DATA JPA and tried to use the StoredProcedureQuery as below

    StoredProcedureQuery storedProcedureQuery = entityManagerFactory.createEntityManager().createStoredProcedureQuery("ModelSearchByModel",ModelSearchByModelEntity.class);
    storedProcedureQuery.setParameter("RegionCode",regionCode);
    //all other parameter setters.. 


    return storedProcedureQuery.execute() ? storedProcedureQuery.getResultList() : null;

With this, I get the error as below

Caused by: java.lang.IllegalArgumentException: Parameter with that name [ArchRegionCode] did not exist
    at org.hibernate.jpa.spi.BaseQueryImpl.findParameterRegistration(BaseQueryImpl.java:503) [hibernate-entitymanager-4.3.7.Final.jar:4.3.7.Final]

I'm not understanding how to get this right. Not sure if there is a way to control the ParameterStrategy value.

Any suggestions here?

Answer 1

If you want to call a stored procedure that you declared with the annotation @NamedStoredProcedureQuery you should use the method createNamedStoredProcedureQuery, not createStoredProcedureQuery. The latter is for when you don't want to use the annotation and in that case you have to register the parameters by hand.

So if you want to use the annotation you could write something like

entityManagerFactory.createEntityManager()
.createNamedStoredProcedureQuery("ModelSearchByModel", ModelSearchByModelEntity.class)
.setParameter("RegionCode",regionCode);

otherwise

StoredProcedureQuery storedProcedureQuery = entityManagerFactory.createEntityManager().createStoredProcedureQuery("SPR_ModelSearchByModel",ModelSearchByModelEntity.class);
storedProcedureQuery.registerStoredProcedureParameter("RegionCode", String.class , ParameterMode.IN);
// etc.
storedProcedureQuery.setParameter("RegionCode",regionCode);
// etc.

Note that in the second example the name to use is the original name of the stored procedure.