spring-data-jpa调用Mysql存储过程

使用　spring-data-jpa(hibernate) 调用Mysql存储过程，踩坑记录。╮（╯＿╰）╭

@NamedStoredProcedureQuery,@Procedure

需要返回多个值不能用，好像一个返回值的时候，可以用

// 实体类

@Data
@Entity
@Table(name="sys_org")
@NamedStoredProcedureQuery(name = "findAllChildren", procedureName ="findOrgChildren",
        parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "oid", type = Long.class),
                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "orgId", type = Long.class),
                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "parentId", type = Long.class),
                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "orgName", type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "orgType", type = String.class)
        }/*,resultClasses = SysOrg.class*/)
public class SysOrg extends AuditableEntity<SysOrg,Long> {
// ......
｝

// Dao
@Repository
public interface SysOrgRepository extends BaseRepository<SysOrg,Long> {
    @Procedure(name = "findOrgChildren")
    List<SysOrg> findAllChildren(@Param("oid")Long oid);
}

@Query(nativeQuery = true)

返回多个值只能用Object[]接收，可以使用.
但是，存储过程用到INOUT参数，必须拆分成IN和OUT两个参数。。。
下面有连个错误示例

// 实体类上不需要写啥

//Dao
@Repository
public interface SysOrgRepository extends BaseRepository<SysOrg,Long> {
	// IN oid ,  其他是OUT
    @Query(value = " { call findOrgChildren(:oid,@orgId,@parentId,@orgName,@orgType) }", nativeQuery = true)
    Object[] findAllChildren(@Param("oid")Long oid);
 
// 存储过程的INOUT参数，不拆成IN和OUT，错误示例：
// 1.这样写，INOUT参数(:orgId),存储过程接收到的是jpa解析后的常量值，存储过程不能做把它为 OUT使用
@Query(value = "call prOrgChildren(:orgId,@parentId,@orgName,:orgType)", nativeQuery = true)

// 2.这样写，INOUT参数(:orgId),:orgId 后面必须写空格不然jpa会把orgId;当属性；不写分分号，不是单独的sql语句，也是不行的。。。
// 即便是写成这样了，还是报错了 // java.lang.NullPointerException: null at com.mysql.cj.jdbc.result.ResultSetImpl.getMetaData
@Query(value = "set @orgId = :orgId ; set @orgType = :orgType ; call prOrgChildren(@orgId,@parentId,@orgName,@orgType)", nativeQuery = true)
}

@NamedStoredProcedureQuery, entityManager.createNamedStoredProcedureQuery

返回多个值只能用List<Object[]>接收， 可以使用

@Data
@Entity
@Table(name="sys_org")
@NamedStoredProcedureQuery(name = "findAllChildren", procedureName ="findOrgChildren",
        parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "oid", type = Long.class),
                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "orgId", type = Long.class),
                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "parentId", type = Long.class),
                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "orgName", type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "orgType", type = String.class)
        }
        /*,resultClasses = SysOrg.class // 打开,报错 Column 'xxx_xxx' not found.*/
        )
public class SysOrg extends AuditableEntity<SysOrg,Long> {
// ......
｝

// Dao不用写啥

// Service 中

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    @Transactional(readOnly = false)
    public void test(Long pid) {
        StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("findAllChildren").setParameter("oid",pid);
        query.execute();  
        //返回List<Object[]>不能通过query.getOutputParameterValue方法获取存储过程中的OUT参数值
        List<Object[]> allChildren = query.getResultList();
        if (!ObjectUtils.isEmpty(allChildren)) {
            allChildren.forEach(o -> {
                BigInteger orgId = (BigInteger)o[0];    
                BigInteger parentId = (BigInteger)o[1]; 
                String orgName = String.valueOf(o[2]);  
                String orgType = String.valueOf(o[3]);  

                log.info("orgId={}, parentId={}, orgName={}, orgType={}",orgId, parentId, orgName, orgType);
                // orgId=1, parentId=0, orgName=能力有限公司, orgType=1
            });
        }
    }