Spring Data JPA 直接调用存储过程的方法
来源：互联网 发布：自制地图软件 编辑：程序博客网 时间：2024/04/29 16:04
@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 0 0
- Spring Data JPA 直接调用存储过程的方法
- Spring Data JPA调用存储过程实例
- Spring Data JPA调用存储过程实例
- Spring Data JPA调用存储过程实例
- Spring Data JPA进阶-调用存储过程
- JPA调用存储过程的方法
- Spring Data JPA的启动过程
- JPA调用存储过程
- JPA 调用存储过程
- jpa对存储过程的调用
- spring data jpa 的delete方法
- Spring-data-JPA查询方法的总结
- 在spring data jpa中使用redis的通用list及entity存储方法
- JPA调用存储过程[转]
- JPA调用存储过程[转]
- jpa里调用存储过程
- jpa和spring data jpa的理解
- spring data jpa 与 jpa的区别
- 安卓之使用DexClassLoader&AssetManager启动插件的Activity实现功能插件化
- 文字下划线效果（标题hover效果）
- 接口、抽象类（纯虚类）、重载、重写
- Runtime那些事儿（消息机制）
- linux 修改 dns 配置
- Spring Data JPA 直接调用存储过程的方法
- TYVJ 2018 小猫爬山
- Android Studio打包apk，aar，jar包
- 一个基于多用户的抓取视频下载系统 PhaGrabVideo
- cuda coalesced access to global memory
- phpstorm配置xdebug
- 免安装版MySql5.6 安装流程
- 理解Linux系统/etc/init.d目录和/etc/rc.local脚本
- c++标准库——容器类
