Spring boot调用Oracle存储过程的两种方式及完整代码

Spring boot调用Oracle存储过程的两种方式及完整代码
前言

因工作需要将公司SSH项目改为Spingboot项目,将项目中部分需要调用存储过程的部分用entityManagerFactory.unwrap(SessionFactory.class).openSession()来获取Session实现后发现项目访问数据库超过十次就会挂掉,原因是Springboot连接池数量默认为10,猜测是每次访问数据库后连接未释放导致的,手动关闭session后问题解决。

解决问题的过程中又发现了另外两种调用方式:

直接用EntityManager的createStoredProcedureQuery()方法调用 (推荐)
通过如下方式获取Session来调用,这种方式不需要手动关闭Session来释放连接,具体原因我也没搞明白,有知道的朋友欢迎指点

Session session = entityManager.unwrap(Session.class);

package com.hzjd.produre.repository;
 
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;
 
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.procedure.ProcedureCall;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
 
import com.hzjd.produre.bean.QueryResponse;
import com.hzjd.produre.utils.Assistant;
 
@Repository
public class ProdureDAO {
    public final static String PUBLIC_PAG_SYS_GETNEXTID = "PUBLIC_PAG.SYS_GETNEXTID";
    public final static String PSBC_QUERYBILL = "PSBCPAY.QUERYBILL";
    @PersistenceContext
    EntityManager entityManager;
    @Autowired
    EntityManagerFactory entityManagerFactory;
 
    public Session getSession() {
        return entityManagerFactory.unwrap(SessionFactory.class).openSession();
    }
 
    /**
     * 使用entityManager调用存储过程
     * 
     * @param pay_ID
     * @return
     */
    public QueryResponse queryBill1(String pay_ID) throws Exception {
        QueryResponse queryResponse = new QueryResponse();
        StoredProcedureQuery call = entityManager.createStoredProcedureQuery(PSBC_QUERYBILL);
        call.registerStoredProcedureParameter(1, String.class, ParameterMode.IN).setParameter(1, pay_ID);
        call.registerStoredProcedureParameter(2, String.class, ParameterMode.OUT);
        call.registerStoredProcedureParameter(3, String.class, ParameterMode.OUT);
        call.registerStoredProcedureParameter(4, String.class, ParameterMode.OUT);
        call.registerStoredProcedureParameter(5, String.class, ParameterMode.OUT);
        call.registerStoredProcedureParameter(6, String.class, ParameterMode.OUT);
        call.registerStoredProcedureParameter(7, String.class, ParameterMode.OUT);
        call.registerStoredProcedureParameter(8, String.class, ParameterMode.OUT);
        call.registerStoredProcedureParameter(9, String.class, ParameterMode.OUT);
        call.registerStoredProcedureParameter(10, String.class, ParameterMode.OUT);
        call.execute();
        queryResponse.getBody().setPAY_ID(pay_ID);
        queryResponse.getBody().setCUSTNAME(Assistant.nullToEmpty(call.getOutputParameterValue(2)));
        queryResponse.getBody().setHOME_ADDR(Assistant.nullToEmpty(call.getOutputParameterValue(3)));
        queryResponse.getBody().setTRAN_AMT(Assistant.nullToEmpty(call.getOutputParameterValue(5)));
        queryResponse.getBody().setTOTAL_AMT(Assistant.nullToEmpty(call.getOutputParameterValue(6)));
        queryResponse.getBody().setBALANCE(Assistant.nullToEmpty(call.getOutputParameterValue(8)));
        int errorcode = Assistant.nullToInt(call.getOutputParameterValue(9));
        String errormsg = Assistant.nullToEmpty(call.getOutputParameterValue(10));
        if (errorcode == 0) {
            return queryResponse;
        } else {
            throw new Exception(errormsg);
        }
    }
 
    /**
     * 使用sessionFactory开启Session调用存储过程
     * 
     * @param pay_ID
     * @return
     */
    public QueryResponse queryBill2(String pay_ID) throws Exception {
        QueryResponse queryResponse = new QueryResponse();
        // 调用完成后需关闭Session否则会出现连接失效
        try (Session session = getSession();) {
            ProcedureCall call = session.createStoredProcedureCall(PSBC_QUERYBILL);
            call.registerParameter(1, String.class, ParameterMode.IN).bindValue(pay_ID);
            call.registerParameter(2, String.class, ParameterMode.OUT);
            call.registerParameter(3, String.class, ParameterMode.OUT);
            call.registerParameter(4, String.class, ParameterMode.OUT);
            call.registerParameter(5, String.class, ParameterMode.OUT);
            call.registerParameter(6, String.class, ParameterMode.OUT);
            call.registerParameter(7, String.class, ParameterMode.OUT);
            call.registerParameter(8, String.class, ParameterMode.OUT);
            call.registerParameter(9, String.class, ParameterMode.OUT);
            call.registerParameter(10, String.class, ParameterMode.OUT);
            queryResponse.getBody().setPAY_ID(pay_ID);
            queryResponse.getBody().setCUSTNAME(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(2)));
            queryResponse.getBody().setHOME_ADDR(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(3)));
            queryResponse.getBody().setTRAN_AMT(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(5)));
            queryResponse.getBody().setTOTAL_AMT(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(6)));
            queryResponse.getBody().setBALANCE(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(8)));
            int errorcode = Assistant.nullToInt(call.getOutputs().getOutputParameterValue(9));
            String errormsg = Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(10));
            if (errorcode == 0) {
                return queryResponse;
            } else {
                throw new Exception(errormsg);
            }
        }
    }
 
    /**
     * 使用sessionFactory开启Session调用存储过程
     * 
     * @param pay_ID
     * @return
     */
    public QueryResponse queryBill3(String pay_ID) throws Exception {
        QueryResponse queryResponse = new QueryResponse();
        Session session = entityManager.unwrap(Session.class);
        ProcedureCall call = session.createStoredProcedureCall(PSBC_QUERYBILL);
        call.registerParameter(1, String.class, ParameterMode.IN).bindValue(pay_ID);
        call.registerParameter(2, String.class, ParameterMode.OUT);
        call.registerParameter(3, String.class, ParameterMode.OUT);
        call.registerParameter(4, String.class, ParameterMode.OUT);
        call.registerParameter(5, String.class, ParameterMode.OUT);
        call.registerParameter(6, String.class, ParameterMode.OUT);
        call.registerParameter(7, String.class, ParameterMode.OUT);
        call.registerParameter(8, String.class, ParameterMode.OUT);
        call.registerParameter(9, String.class, ParameterMode.OUT);
        call.registerParameter(10, String.class, ParameterMode.OUT);
        queryResponse.getBody().setPAY_ID(pay_ID);
        queryResponse.getBody().setCUSTNAME(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(2)));
        queryResponse.getBody().setHOME_ADDR(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(3)));
        queryResponse.getBody().setTRAN_AMT(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(5)));
        queryResponse.getBody().setTOTAL_AMT(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(6)));
        queryResponse.getBody().setBALANCE(Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(8)));
        int errorcode = Assistant.nullToInt(call.getOutputs().getOutputParameterValue(9));
        String errormsg = Assistant.nullToEmpty(call.getOutputs().getOutputParameterValue(10));
        if (errorcode == 0) {
            return queryResponse;
        } else {
            throw new Exception(errormsg);
        }
    }
}
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值