Springboot调用Oracle存储过程的几种方式

时间：2023-03-09 07:21:46
Springboot调用Oracle存储过程的几种方式
  • 因工作需要将公司SSH项目改为Spingboot项目，将项目中部分需要调用存储过程的部分用entityManagerFactory.unwrap(SessionFactory.class).openSession()来获取Session实现后发现项目访问数据库超过十次就会挂掉，原因是Springboot连接池数量默认为10，猜测是每次访问数据库后连接未释放导致的，手动关闭session后问题解决。
  • 解决问题的过程中又发现了另外两种调用方式，
  1. 直接用EntityManager的createStoredProcedureQuery（）方法调用 （推荐）
  2. 通过如下方式获取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);

		}

	}

}

相关文章