Spring boot調(diào)用Oracle存儲(chǔ)過程的兩種方式及完整代碼
前言
因工作需要將公司SSH項(xiàng)目改為Spingboot項(xiàng)目,將項(xiàng)目中部分需要調(diào)用存儲(chǔ)過程的部分用entityManagerFactory.unwrap(SessionFactory.class).openSession()
來獲取Session實(shí)現(xiàn)后發(fā)現(xiàn)項(xiàng)目訪問數(shù)據(jù)庫超過十次就會(huì)掛掉,原因是Springboot連接池?cái)?shù)量默認(rèn)為10,猜測(cè)是每次訪問數(shù)據(jù)庫后連接未釋放導(dǎo)致的,手動(dòng)關(guān)閉session后問題解決。
解決問題的過程中又發(fā)現(xiàn)了另外兩種調(diào)用方式:
- 直接用EntityManager的createStoredProcedureQuery()方法調(diào)用 (推薦)
- 通過如下方式獲取Session來調(diào)用,這種方式不需要手動(dòng)關(guān)閉Session來釋放連接,具體原因我也沒搞明白,有知道的朋友歡迎指點(diǎn)
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調(diào)用存儲(chǔ)過程 * * @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調(diào)用存儲(chǔ)過程 * * @param pay_ID * @return */ public QueryResponse queryBill2(String pay_ID) throws Exception { QueryResponse queryResponse = new QueryResponse(); // 調(diào)用完成后需關(guān)閉Session否則會(huì)出現(xiàn)連接失效 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調(diào)用存儲(chǔ)過程 * * @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); } } }
總結(jié)
到此這篇關(guān)于Spring boot調(diào)用Oracle存儲(chǔ)過程的兩種方式及完整代碼的文章就介紹到這了,更多相關(guān)Springboot調(diào)用Oracle存儲(chǔ)過程內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- oracle通過存儲(chǔ)過程上傳list保存功能
- oracle 存儲(chǔ)過程返回 結(jié)果集 table形式的案例
- 解決PL/SQL修改Oracle存儲(chǔ)過程編譯就卡死的問題
- 關(guān)于Oracle存儲(chǔ)過程和調(diào)度器實(shí)現(xiàn)自動(dòng)對(duì)數(shù)據(jù)庫過期數(shù)據(jù)清除的問題
- Oracle如何批量將表中字段名全轉(zhuǎn)換為大寫(利用簡(jiǎn)單存儲(chǔ)過程)
- oracle調(diào)試存儲(chǔ)過程的過程詳解
- Oracle如何使用PL/SQL調(diào)試存儲(chǔ)過程
- Oracle存儲(chǔ)過程案例詳解
相關(guān)文章
java中Callback簡(jiǎn)單使用總結(jié)
正好學(xué)習(xí)到j(luò)ava Callback,就整理了一下,希望整理的文章內(nèi)容對(duì)大家有所幫助2017-04-04SpringBoot的啟動(dòng)過程源碼詳細(xì)分析
這篇文章主要介紹了SpringBoot的啟動(dòng)過程源碼詳細(xì)分析,SpringBoot啟動(dòng)的時(shí)候,會(huì)構(gòu)造一個(gè)SpringApplication的實(shí)例,構(gòu)造SpringApplication的時(shí)候會(huì)進(jìn)行初始化的工作,需要的朋友可以參考下2023-11-11SpringCloud OpenFeign與Ribbon客戶端配置詳解
在springcloud中,openfeign是取代了feign作為負(fù)載均衡組件的,feign最早是netflix提供的,他是一個(gè)輕量級(jí)的支持RESTful的http服務(wù)調(diào)用框架,內(nèi)置了ribbon,而ribbon可以提供負(fù)載均衡機(jī)制,因此feign可以作為一個(gè)負(fù)載均衡的遠(yuǎn)程服務(wù)調(diào)用框架使用2022-11-11Java多種方法實(shí)現(xiàn)合并多個(gè)list對(duì)象列表
Java編程中,合并多個(gè)列表對(duì)象可以通過Stream?API或傳統(tǒng)循環(huán)方式實(shí)現(xiàn),使用Stream?API合并時(shí),利用flatMap方法將嵌套的List展平,再通過collect方法收集成一個(gè)新的列表,傳統(tǒng)循環(huán)則通過創(chuàng)建一個(gè)空的ArrayList,并通過遍歷每個(gè)列表將元素添加進(jìn)去2024-09-09