温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

Spring boot调用Oracle存储过程的方法有哪些

发布时间:2020-08-17 10:39:12 来源:亿速云 阅读:212 作者:小新 栏目:开发技术

这篇文章主要介绍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);
		}
	}
}

以上是Spring boot调用Oracle存储过程的方法有哪些的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注亿速云行业资讯频道!

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI