使用spring-data-JPA调用存储过程

第一种情况，对于只有一个或没有返回值的存储过程，使用如下方式调用:

@Entity
@NamedStoredProcedureQuery(name = "pro1", procedureName = "pro1", parameters = {
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
  @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res", type = Integer.class) })
public class User {
    @Id @GeneratedValue
    private Long id;
}

public interface UserRepository extends CrudRepository<User, Long> {
  @Procedure(name = "pro1")
  Integer plus1BackedByOtherNamedStoredProcedure(@Param("arg") Integer arg);
}

如果没有返回值，repository中的方法返回void即可。

 

第二种情况，对于有多个返回值的存储过程，使用如下方式调用:

@Entity
@NamedStoredProcedureQuery(name = "pro1", procedureName = "pro1", parameters = {
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
  @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res", type = Integer.class),
  @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res2", type = Integer.class) })
public class User {
    @Id @GeneratedValue
    private Long id;
}

public class SomeSerice {
    @Autowired
    private EntityManager entityManager;

    public void test() {
        StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("pro1");

        query.setParameter("arg", 1);

        query.execute();

        Integer res = query.getOutputParameterValue("res");
        Integer res2 = query.getOutputParameterValue("res2");
    }
}

 

第三种情况，对于返回游标的存储过程，使用如下方式:

@Entity
@NamedStoredProcedureQuery(name = "pro1", resultSetMappings={"mapping1"} procedureName = "pro1", parameters = {
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
  @StoredProcedureParameter(mode=ParameterMode.REF_CURSOR, name="cur", type=void.class) })

@SqlResultSetMapping(name="mapping1", classes={@ConstructorResult(targetClass=User.class, 
  columns={@ColumnResult(name="value1", type=String.class), @ColumnResult(name="value2", type=String.class)})})

public class User {
    @Id @GeneratedValue
    private Long id;

    private String value1;

    private String value2;

    public User(String value1, String value2) {
        this.value1 = value1;
        this.value2 = value2;
    }

    //...getter setter
}

注意此处，@NamedStoredProcedureQuery注解增加了resultSetMappings属性，用于定义如何将游标的返回值转换为实体类，具体实现在@SqlResultSetMapping中。实体类需要实现对应的构造函数。

public class SomeSerice {
    @Autowired
    private EntityManager entityManager;

    public void test() {
        StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("pro1");

        query.setParameter("arg", 1);

        query.execute();

        query.getResultList();
    }
}