Spring Data JPA 直接调用存储过程的方法

@NamedNativeQueries(
        {
                @NamedNativeQuery(
                        name = "FixedFeeOrderValidCalculate",
                        query = "insert into kh.tb_cust_tdyw" +
                                " (month,cust_no,user_id,broker_account,index_type,index_value,trade_date)" +
                                " select ?1 ,cr.customer_no ,cr.belong_userid ,cr.broker_account,?2,1,cr.rels_begin" +
                                "  from kh.tb_yxkh_cust_rel cr" +
                                " inner join meso.tb_customer cust on cust.customer_no = cr.customer_no" +
                                " where cr.rels_begin between ?3 || '01' and ?4 || '31' " +
                                " and cust.open_date between ?5 || '01' and ?6 || '31'"
                ),
                @NamedNativeQuery(
                        name = "RatioFeeOrderValidCalculate",
                        query = "insert into kh.tb_cust_tdyw" +
                                "      (month,cust_no,user_id,broker_account,index_type,index_value,trade_date)" +
                                "select t.month as month,t.cust_no,t.user_id,t.broker_account,?1," +
                                "  sum(nvl((select case " +
                                "    when not exists (select i.item_value from KH.TB_SDEPT_INDEX i " +
                                "    where i.branch_code = us.dept_id and i.item_id = ?2 and i.version = '2016') then " +
                                "    (select i.item_value from KH.TB_SDEPT_INDEX i " +
                                "    where i.branch_code = '9999' and i.item_id = ?3 and i.version = '2016') " +
                                "  else (select i.item_value from KH.TB_SDEPT_INDEX i" +
                                "   where i.branch_code = us.dept_id and i.item_id = ?4 and i.version = '2016') end" +
                                "  from dual) * nvl(t.index_value, 0), 0)),'' " +
                                " from KH.TB_CUST_TDYW t inner join KH.TB_USER_SNAP us" +
                                " on us.user_id = t.user_id and us.trademonth = ?5" +
                                " where t.month = ?6 and t.index_type = ?7" +
                                " group by t.month, t.cust_no, t.user_id, t.broker_account"
                ),



 @NamedNativeQuery(
                        name = "FixedFeeOrderValidCalculate",
                        query =

 "      select sp.trademonth, " +
                                "             sp.branch_code, " +
                                "             sp.auth_id, " +
                                "             sp.cust_no, " +
                                "             sp.sorder_id, " +
                                "             sp.pay_way, " +
                                "             sp.price_money, " +
                                "             sp.service_rate, " +
                                "             sp.prod_no, " +
                                "             sp.trade_day, " +
                                "             sp.user_id, " +
                                "             sp.begin_date, " +
                                "             sp.end_date, " +
                                "             nvl(decode(sp.charg_range, 0, am.avg_net_ofs_assets, 1, am.total_avg_asset), 0), " +
                                "             '1', " +
                                "             case " +
                                "               when num > 1 then  " +
                                "                round((case " +
                                "                        when tmp1.totsum / 3000 > 3 then " +
                                "                         sp.price_money / tmp1.totsum * 3 " +
                                "                        else " +
                                "                         sp.price_money / 3000 " +
                                "                      end), 2) " +
                                "               else " +
                                "                round((case " +
                                "                        when sp.price_money / 3000 > 3 then " +
                                "                         3 " +
                                "                        else " +
                                "                         sp.price_money / 3000 " +
                                "                      end), 2) " +
                                "             end, " +
                                "             sp.charg_range, " +
                                "             '1', " +
                                "             sysdate, " +
                                "             sysdate, " +
                                "             '1', " +
                                "             null, " +
                                "             'B' " +
                                "        from kh.tb_sproduct_sale sp " +
                                "       inner join (select sp.auth_id auth_id, " +
                                "                          sp.user_id user_id, " +
                                "                          sum(sp.price_money) totsum, " +
                                "                          count(1) as num " +
                                "                     from kh.tb_sproduct_sale sp " +
                                "                    where sp.pay_way = ?1 " +
                                "                      and sp.pay_status = ?2 " +
                                "                      and sp.del_flag = ?3 " +
                                "                      and sp.trademonth = ?4 " +
                                "                    group by sp.auth_id, sp.user_id) tmp1 " +
                                "          on tmp1.auth_id = sp.auth_id " +
                                "         and sp.user_id = tmp1.user_id " +
                                "        left join xc.tb_mt_cust_asset_gather am " +
                                "          on am.customer_no = sp.cust_no " +
                                "         and am.trademonth = ?4 " +
                                "       where sp.pay_way = ?1  " +
                                "         and sp.pay_status = ?2 " +
                                "         and sp.del_flag = ?3 " +
                                "         and sp.trademonth = ?4"
resultSetMapping = "returnorderValidCalc"
），

@SqlResultSetMappings(
        {
                @SqlResultSetMapping(
                        name = "returnorderValidCalc",
                        entities = {},
                        columns = {
                                @ColumnResult(name = "tradeMonth"),
                                @ColumnResult(name = "branchCode"),
                                @ColumnResult(name = "authID"),
                                @ColumnResult(name = "custNO"),
                                @ColumnResult(name = "orderID"),
                                @ColumnResult(name = "signType"),
                                @ColumnResult(name = "tollSum"),
                                @ColumnResult(name = "serviceFarex"),
                                @ColumnResult(name = "prodNO"),
                                @ColumnResult(name = "tradeDate"),
                                @ColumnResult(name = "userID"),
                                @ColumnResult(name = "startDate"),
                                @ColumnResult(name = "endDate"),
                                @ColumnResult(name = "asset"),
                                @ColumnResult(name = "tradeType"),
                                @ColumnResult(name = "validNum"),
                                @ColumnResult(name = "chargRange"),
                                @ColumnResult(name = "createBy"),
                                @ColumnResult(name = "createDT"),
                                @ColumnResult(name = "updateDT"),
                                @ColumnResult(name = "updateBy"),
                                @ColumnResult(name = "deleteBy"),
                                @ColumnResult(name = "actByType"),


                        }
                )
        }
)

    }

)

在entity的实体类的前面用 @NamedNativeQuery注解定义一整条存储过程的sql。

对于select型的sql语句 执行完后，select出的是一个实体表中的某几列。如果没有或者不想定义临时的entity去接收查询结果时， 可以用@SqlResultSetMappings 注解 定义几个列去接收select的查询结果

二者通过returnorderValidCalc 连接。

对用注解定义好的存储过程，用一个EntityManagerTransaction类去执行这些存储过程。

package com.foundersc.crm.brokerage.util;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;
import java.util.List;

/**
 * 获得支持事务操作的entityManager实体
 *
 * Created by zhongxuhui on 16/9/8.
 */
public class EntityManagerTransaction {

    private EntityManager entityManager;
    private EntityManagerFactory entityManagerFactory;

    public EntityManagerTransaction() {
        String config = "classpath*:spring/spring-jpa.xml";
        ApplicationContext ctx = new ClassPathXmlApplicationContext(config);
        entityManagerFactory = ctx.getBean(EntityManagerFactory.class);
        entityManager = entityManagerFactory.createEntityManager();
    }

    public int update (String methodName, String[] params) {
        entityManager.getTransaction().begin();
        Query query = entityManager.createNamedQuery(methodName);
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i + 1, params[i]);
        }
        int result = query.executeUpdate();
        entityManager.getTransaction().commit();
        close();
        return result;
    }

    public List<Object[]> query(String methodName, String[] params) {

        Query query = entityManager.createNamedQuery(methodName);
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i + 1, params[i]);
        }
        List<Object[]> list = query.getResultList();
        entityManager.close();
        return list;
    }

    private void close() {
        entityManager.close();
        entityManagerFactory.close();
    }
}

在 ServiceRepository 里面实例化一个EntityManagerTransaction类
调用其执行sql的方法：

private static EntityManagerTransaction entityManagerTransaction = new EntityManagerTransaction();


public List getRequestList(String methodName, String[] params) {
        return  entityManagerTransaction.query(methodName,params);
       }

      public int SaveOrUpdateFromSelect(String methodName, String[] params){
          int result= entityManagerTransaction.update(methodName,params);
          if (0==result){
              log.info("no lines had been affected!");
          }
          else {
              log.info(result+" lines has benn affected!");
          }
          return result;
      }

然后在相应的serviceImpl里根据存储过程的名称调用即可：

public void FixedFeeOrderValidCalculate(String tradeMonth){
        String[] params = new String[4];
        params[0] = "1" ;//固定费用客户
        params[1] = "2" ;//已支付的
        params[2] = "0" ;//状态正常
        params[3] = tradeMonth;

        int result=serviceProductOrderValidNumRepository.SaveOrUpdateFromSelect("FixedFeeOrderValidCalculate",params);
 //FixedFeeOrderValidCalculate 是在@NamedNativeQuery注解里定义好的存储过程的名称
    }

public void RatioFeeOrderValidCalculate(String tradeMonth){
        String[] params = new String[4];
        params[0] = "2" ;//固定费用客户
        params[1] = "2" ;//已支付的
        params[2] = "0" ;//状态正常
        params[3] = tradeMonth;

        int result=serviceProductOrderValidNumRepository.SaveOrUpdateFromSelect("RatioFeeOrderValidCalculate",params);

    }//RatioFeeOrderValidCalculate 是entity前的注解里定义好的存储过程的sql