如何调用使用java + hibernate返回sys_refcursor的oracle函数 [英] How to call oracle function which return sys_refcursor using java + hibernate
本文介绍了如何调用使用java + hibernate返回sys_refcursor的oracle函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在调用oracle函数的时候遇到了一个问题，
的函数看起来像下面这样：
CREATE OR REPLACE FUNCTION get_port_out_select（pi_id_pout_order IN PORT_OUT_ORDER.ID_POUT_ORDER％TYPE）RETURN sys_refcursorISlc_cursor SYS_REFCURSOR; BEGINOPEN lc_cursor FORSELECT ID_POUT_ORDER，ID_LEC_USER，ID_POUT_LEC，ID_SERVICE_REC，POUT_TN，ID_IPTN，POUT_LEC_OCN，POUT_LEC_SPID，POUT_ALI_CODE，LEC_CONTACT_FNAME，LEC_CONTACT_LNAME， LEC_CONTACT_TN，LEC_CONTACT_EMAIL，DL_LEC_OCN，CUST_ACCT，CUST_F_NAME，CUST_L_NAME，SVC_ADDR_STR，SVC_ADDR_NUM，SVC_ADDR_CITY，SVC_ADDR_STATE，SVC_ADDR_ZIP，POUT_LOA_DATE，POUT_DDD，POUT_FOC，E_MAIL_SENT_DATE，V_MAIL_SENT_DATE，ID_POUT_STATUS，CUSTOMER_TYPE，AUTHORIZATION_NAME，ID_MASTER_POUT_ORDERFROM PORT_OUT_ORDERWHERE ID_POUT_ORDER = pi_id_pout_orderAND ID_POUT_STATUS < 95; RETURN lc_cursor; END;
Bellow是我在DB中执行查询的结果。
{ID_POUT_ORDER = 2004，ID_LEC_USER = 39，ID_POUT_LEC = 36，ID_SERVICE_REC = 20277159，POUT_TN = 7186846814，ID_IPTN = 3990，POUT_LEC_OCN = 9147，POUT_LEC_SPID = 9147，POUT_ALI_CODE = 9147，LEC_CONTACT_FNAME =唯一，LEC_CONTACT_LNAME =用户，LEC_CONTACT_TN = 5165551234，LEC_CONTACT_EMAIL = michael21474@gmail.com，DL_LEC_OCN = 9147，CUST_ACCT = 0785852664101，CUST_F_NAME = YYYYYYY，CUST_L_NAME = ZZZZZZ，SVC_ADDR_STR = 10thav，SVC_ADDR_NUM = 812，SVC_ADDR_CITY = levittown，SVC_ADDR_STATE = NY，SVC_ADDR_ZIP = 11756，POUT_LOA_DATE = 11- APR-07，POUT_DDD = 11-APR-07，POUT_FOC = 11-APR-07，E_MAIL_SENT_DATE = 10-APR-07，V_MAIL_SENT_DATE = null，ID_POUT_STATUS = 1，CUSTOMER_TYPE = Residential，AUTHORIZATION_ NAME =唯一身份用户，ID_MASTER_POUT_ORDER = null>，}
我用这个来调用函数
Query query = this.em.createNativeQuery（SELECT get_port_out_select（2004）FROM DUAL）; query.getSingleResult（）;
结果。
我检查了数据库的连通性，没有问题。
如何处理这个
更新1：
创建实体类
@NamedNativeQueries（value = {@NamedNativeQuery（name =functionCall，query ={ SELECT get_port_out_select（2004）FROM DUAL}，resultClass = PortOutSelectCursor.class）}）@ Entitypublic class PortOutSelectCursor {@Id @Column（name =ID_POUT_ORDER）private int ID_POUT_ORDER; @Column（name =ID_LEC_USER）private int ID_LEC_USER; @Column（name =ID_SERVICE_REC）private int ID_SERVICE_REC; @Column（name =POUT_TN）private String POUT_TN; @Column（name =ID_IPTN）private int ID_IPTN; //添加了所有的属性//添加了默认构造函数//添加了setter和getter}
在 persistence.xml中添加了映射
$ b
< persistence-unit name =XOSS_PDB_PROCEDURE_PERSISTENCE_UNIT_1transaction-type =JTA> <提供商GT; org.hibernate.jpa.HibernatePersistenceProvider< /提供商GT; < JTA - 数据 - 源> myDataSource< / JTA - 数据 - 源> <映射-文件> META-INF / procedure.xml< /映射-文件> <类> com.amdocs.oss.alt.procedure.response.PortOutSelectCursor< /类> <性状> < property name =javax.persistence.jdbc.drivervalue =oracle.jdbc.driver.OracleDriver/> < property name =hibernate.show_sqlvalue =true/> < property name =hibernate.format_sqlvalue =true/> < property name =hibernate.dialectvalue =org.hibernate.dialect.Oracle10gDialect/> < property name =hibernate.hbm2ddl.autovalue =none/> < property name =hibernate.transaction.jta.platformvalue =org.hibernate.service.jta.platform.internal.WeblogicJtaPlatform/> < property name =hibernate.proc.param_null_passingvalue =true/> < property name =alt.procedure_namesvalue =insertEquipReturn，getPortOutSelect/> < property name =hibernate.archive.autodetectionvalue =class/> < /性状> < / persistence-unit>
/ p>
public void getPortOutSelect（）{try {List< Object []> a = this.em.createNamedQuery（functionCall）。getResultList（）; Object [] r = a.get（0）; catch（Exception e）{e.printStackTrace（）; } return null;}
仍在努力解决问题。请帮助解决方案
这是如何在Java类中获得结果（希望您可以使用此问题作为示例）：Oracle安装程序 strong>：
$ b $ pre $
CREATE FUNCTION get_ref_cursor RETURN SYS_REFCURSOR
IS
out_cursor SYS_REFCURSOR;
BEGIN
OPEN out_cursor FOR
SELECT 123 AS col1 FROM DUAL UNION ALL
SELECT 456 FROM DUAL UNION ALL
SELECT 789 FROM DUAL;
RETURN out_cursor;
END;
/
Java ：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
导入oracle.jdbc.OracleCallableStatement;
导入oracle.jdbc.OracleTypes;
public class GetRefCursorFromFunction
{
public static void main（final String [] args）{
try {
Class.forName（oracle.jdbc .OracleDriver）;
Connection con = DriverManager.getConnection（
jdbc：oracle：thin：@localhost：1521：orcl，
USERNAME，
PASSWORD
）;
OracleCallableStatement st =
（OracleCallableStatement）con.prepareCall（BEGIN：1：= get_Ref_Cursor（）; END;）;
st.registerOutParameter（1，OracleTypes.CURSOR）;
System.out.println（st.execute（））;
ResultSet rs = st.getCursor（1）;
while（rs.next（））
{
System.out.println（rs.getInt（1））;
}
st.close（）;
con.close（）;
} catch（ClassNotFoundException | SQLException ex）{
System.out.println（ex.getMessage（））;
ex.printStackTrace（）;
（注意： ）假设您正在使用Oracle的驱动程序连接到数据库。）
输出：
123
456
789
I am having a problem while calling oracle function,
the function look like bellow
CREATE OR REPLACE FUNCTION get_port_out_select
(pi_id_pout_order IN PORT_OUT_ORDER.ID_POUT_ORDER % TYPE)
RETURN sys_refcursor
IS
lc_cursor sys_refcursor;
BEGIN
OPEN lc_cursor FOR
SELECT ID_POUT_ORDER, ID_LEC_USER, ID_POUT_LEC, ID_SERVICE_REC, POUT_TN, ID_IPTN, POUT_LEC_OCN, POUT_LEC_SPID, POUT_ALI_CODE,
LEC_CONTACT_FNAME, LEC_CONTACT_LNAME, LEC_CONTACT_TN, LEC_CONTACT_EMAIL,
DL_LEC_OCN, CUST_ACCT, CUST_F_NAME, CUST_L_NAME,
SVC_ADDR_STR, SVC_ADDR_NUM, SVC_ADDR_CITY, SVC_ADDR_STATE, SVC_ADDR_ZIP,
POUT_LOA_DATE, POUT_DDD, POUT_FOC, E_MAIL_SENT_DATE, V_MAIL_SENT_DATE, ID_POUT_STATUS, CUSTOMER_TYPE, AUTHORIZATION_NAME, ID_MASTER_POUT_ORDER
FROM PORT_OUT_ORDER
WHERE ID_POUT_ORDER = pi_id_pout_order
AND ID_POUT_STATUS < 95;
RETURN lc_cursor;
END;
it return the value as cursor
Bellow is the result when i execute the query in DB.
{<
ID_POUT_ORDER=2004,
ID_LEC_USER=39,
ID_POUT_LEC=36,
ID_SERVICE_REC=20277159,
POUT_TN=7186846814,
ID_IPTN=3990,
POUT_LEC_OCN=9147,
POUT_LEC_SPID=9147,
POUT_ALI_CODE=9147,
LEC_CONTACT_FNAME=unique,
LEC_CONTACT_LNAME=user,
LEC_CONTACT_TN=5165551234,
LEC_CONTACT_EMAIL=michael21474@gmail.com,
DL_LEC_OCN=9147,
CUST_ACCT=0785852664101,
CUST_F_NAME=YYYYYYY,
CUST_L_NAME=ZZZZZZ,
SVC_ADDR_STR=10thav,
SVC_ADDR_NUM=812,
SVC_ADDR_CITY=levittown,
SVC_ADDR_STATE=NY,
SVC_ADDR_ZIP=11756,
POUT_LOA_DATE=11-APR-07,
POUT_DDD=11-APR-07,
POUT_FOC=11-APR-07,
E_MAIL_SENT_DATE=10-APR-07,
V_MAIL_SENT_DATE=null,
ID_POUT_STATUS=1,
CUSTOMER_TYPE=Residential,
AUTHORIZATION_NAME=unique user,
ID_MASTER_POUT_ORDER=null
>,
}
I am using this to call the function
Query query = this.em.createNativeQuery("SELECT get_port_out_select(2004) FROM DUAL");
query.getSingleResult();
I am having a problem in getting the result.
I have checked the connectivity to database all is fine.
How do I handle this
Update 1:
Created the entity class
@NamedNativeQueries(value = {
@NamedNativeQuery(
name = "functionCall",
query = "{ SELECT get_port_out_select(2004) FROM DUAL }",
resultClass = PortOutSelectCursor.class)
})
@Entity
public class PortOutSelectCursor{
@Id
@Column(name="ID_POUT_ORDER")
private int ID_POUT_ORDER;
@Column(name="ID_LEC_USER")
private int ID_LEC_USER;
@Column(name="ID_SERVICE_REC")
private int ID_SERVICE_REC;
@Column(name="POUT_TN")
private String POUT_TN;
@Column(name="ID_IPTN")
private int ID_IPTN;
// added all the attribute
// added default constructor
// added setter and getter
}
Added the mapping in persistence.xml
<persistence-unit name="XOSS_PDB_PROCEDURE_PERSISTENCE_UNIT_1"
transaction-type="JTA">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<jta-data-source>myDataSource</jta-data-source>
<mapping-file>META-INF/procedure.xml</mapping-file>
<class>com.amdocs.oss.alt.procedure.response.PortOutSelectCursor</class>
<properties>
<property name="javax.persistence.jdbc.driver" value="oracle.jdbc.driver.OracleDriver" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />
<property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
<property name="hibernate.hbm2ddl.auto" value="none" />
<property name="hibernate.transaction.jta.platform"
value="org.hibernate.service.jta.platform.internal.WeblogicJtaPlatform" />
<property name="hibernate.proc.param_null_passing" value="true" />
<property name="alt.procedure_names" value="insertEquipReturn,getPortOutSelect" />
<property name="hibernate.archive.autodetection" value="class"/>
</properties>
</persistence-unit>
Dao class look like this
public void getPortOutSelect() {
try {
List < Object[] > a = this.em.createNamedQuery("functionCall").getResultList();
Object[] r = a.get(0);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
Still struggling to solve the problem. please help
解决方案 This is how to get the result in a Java class (hopefully you can port it to Hibernate using this question as an exemplar):
Oracle Setup:
CREATE FUNCTION get_ref_cursor RETURN SYS_REFCURSOR
IS
out_cursor SYS_REFCURSOR;
BEGIN
OPEN out_cursor FOR
SELECT 123 AS col1 FROM DUAL UNION ALL
SELECT 456 FROM DUAL UNION ALL
SELECT 789 FROM DUAL;
RETURN out_cursor;
END;
/
Java:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class GetRefCursorFromFunction
{
public static void main( final String[] args ){
try{
Class.forName( "oracle.jdbc.OracleDriver" );
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"USERNAME",
"PASSWORD"
);
OracleCallableStatement st =
(OracleCallableStatement) con.prepareCall( "BEGIN :1 := get_Ref_Cursor(); END;" );
st.registerOutParameter( 1, OracleTypes.CURSOR );
System.out.println( st.execute() );
ResultSet rs = st.getCursor( 1 );
while ( rs.next() )
{
System.out.println( rs.getInt( 1 ) );
}
st.close();
con.close();
} catch (ClassNotFoundException | SQLException ex) {
System.out.println( ex.getMessage() );
ex.printStackTrace();
}
}
}
(Note: this assumes you are using Oracle's driver to connect to the database.)
Output:
123
456
789
这篇关于如何调用使用java + hibernate返回sys_refcursor的oracle函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
