java调用Oracle存储过程方式汇总.docx

1、java调用Oracle存储过程方式汇总spring调用Oracle存储过程，并用cursor返回结果集1. 创建表： Java代码 1. createtableTEST_USERS 2. ( 3. USER_IDVARCHAR2(10)notnull, 4. NAMEVARCHAR2(10)notnull, 5. PASSWORDVARCHAR2(20)notnull 6. )create table TEST_USERS ( USER_ID VARCHAR2(10) not null, NAME VARCHAR2(10) not null, PASSWORD VARCHAR2(20) no

2、t null )2. 创建存储过程： Java代码 1. createorreplacepackagedisplay_users_packageis 2. typesearch_resultsisrefcursor; 3. proceduredisplay_users_proc(results_outoutsearch_results,userIdintest_users.user_id%type); 4. enddisplay_users_package; 5. 6. createorreplacepackagebodydisplay_users_packageis 7. procedure

3、display_users_proc(results_outoutsearch_results,userIdintest_users.user_id%type) 8. is 9. begin 10. ifuserIdisnotnullthen 11. openresults_outforselect*fromtest_userswhereuser_idlikeuserId|%; 12. else 13. openresults_outforselect*fromtest_users; 14. endif; 15. enddisplay_users_proc; 16. enddisplay_us

4、ers_package;create or replace package display_users_package is type search_results is ref cursor; procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type); end display_users_package; create or replace package body display_users_package is procedure display_user

5、s_proc(results_out out search_results, userId in test_users.user_id%type) is begin if userId is not null then open results_out for select * from test_users where user_id like userId | %; else open results_out for select * from test_users; end if; end display_users_proc; end display_users_package;这个r

6、esults_out是一个游标类型，用来返回查找的结果集。 3.完整实现代码： Java代码 1. importjava.sql.CallableStatement; 2. importjava.sql.Connection; 3. importjava.sql.ResultSet; 4. importjava.sql.SQLException; 5. importjava.util.ArrayList; 6. importjava.util.HashMap; 7. importjava.util.List; 8. importjava.util.Map; 9. 10. importjavax

7、.sql.DataSource; 11. 12. importoracle.jdbc.OracleTypes; 13. 14. importorg.springframework.dao.DataAccessException; 15. importorg.springframework.jdbc.core.CallableStatementCallback; 16. importorg.springframework.jdbc.core.CallableStatementCreator; 17. importorg.springframework.jdbc.core.JdbcTemplate

8、; 18. 19. importcom.spring.stored.procedure.util.DataContextUtil; 20. 21. /* 22. *authorJaneJiao 23. * 24. */25. publicclassSpringStoredProce 26. 27. publicListexecute(StringstoredProc,Stringparams) 28. ListresultList=null; 29. try 30. finalDataSourceds=DataContextUtil.getInstance().getDataSource();

9、 31. finalJdbcTemplatetemplate=newJdbcTemplate(ds); 32. resultList=(List)template.execute(newProcCallableStatementCreator(storedProc,params), 33. newProcCallableStatementCallback(); 34. catch(DataAccessExceptione) 35. thrownewRuntimeException(executemethoderror:DataAccessException+e.getMessage(); 36

10、. 37. returnresultList; 38. 39. 40. 41. /* 42. *Createacallablestatementinthisconnection. 43. */44. privateclassProcCallableStatementCreatorimplementsCallableStatementCreator 45. privateStringstoredProc; 46. privateStringparams; 47. 48. 49. /* 50. *Constructsacallablestatement. 51. *paramstoredProcT

11、hestoredproceduresname. 52. *paramparamsInputparameters. 53. *paramoutResultCountcountofoutputresultset. 54. */55. publicProcCallableStatementCreator(StringstoredProc,Stringparams) 56. this.params=params; 57. this.storedProc=storedProc; 58. 59. 60. /* 61. *Returnsacallablestatement 62. *paramconnCon

12、nectiontousetocreatestatement 63. *returncsAcallablestatement 64. */65. publicCallableStatementcreateCallableStatement(Connectionconn) 66. StringBufferstoredProcName=newStringBuffer(call); 67. storedProcName.append(storedProc+(); 68. /setoutputparameters 69. storedProcName.append(?); 70. storedProcN

13、ame.append(,); 71. 72. /setinputparameters 73. storedProcName.append(?); 74. storedProcName.append(); 75. 76. CallableStatementcs=null; 77. try 78. /setthefirstparameterisOracleTyep.CURSORfororacelstoredprocedure 79. cs=conn.prepareCall(storedProcName.toString(); 80. cs.registerOutParameter(1,Oracle

14、Types.CURSOR); 81. /setthesencondparamter 82. cs.setObject(2,params); 83. catch(SQLExceptione) 84. thrownewRuntimeException(createCallableStatementmethodError:SQLException+e.getMessage(); 85. 86. returncs; 87. 88. 89. 90. 91. /* 92. * 93. *TheProcCallableStatementCallbackreturnaresultobject, 94. *fo

15、rexampleacollectionofdomainobjects. 95. * 96. */97. privateclassProcCallableStatementCallbackimplementsCallableStatementCallback 98. 99. /* 100. *ConstructsaProcCallableStatementCallback. 101. */102. publicProcCallableStatementCallback() 103. 104. 105. /* 106. *ReturnsaList(Map)collection. 107. *par

16、amcsobjectthatcancreateaCallableStatementgivenaConnection 108. *returnresultsListaresultobjectreturnedbytheaction,ornull 109. */110. publicObjectdoInCallableStatement(CallableStatementcs) 111. ListresultsMap=newArrayList(); 112. try 113. cs.execute(); 114. ResultSetrs=(ResultSet)cs.getObject(1); 115

17、. while(rs.next() 116. MaprowMap=newHashMap(); 117. rowMap.put(userId,rs.getString(USER_ID); 118. rowMap.put(name,rs.getString(NAME); 119. rowMap.put(password,rs.getString(PASSWORD); 120. resultsMap.add(rowMap); 121. 122. rs.close(); 123. catch(SQLExceptione) 124. thrownewRuntimeException(doInCallab

18、leStatementmethoderror:SQLException+e.getMessage(); 125. 126. returnresultsMap; 127. 128. 129. import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.uti

19、l.Map;import javax.sql.DataSource;import oracle.jdbc.OracleTypes;import org.springframework.dao.DataAccessException;import org.springframework.jdbc.core.CallableStatementCallback;import org.springframework.jdbc.core.CallableStatementCreator;import org.springframework.jdbc.core.JdbcTemplate;import co

20、m.spring.stored.procedure.util.DataContextUtil;/* * author Jane Jiao * */public class SpringStoredProce public List execute(String storedProc, String params) List resultList = null; try final DataSource ds = DataContextUtil.getInstance().getDataSource(); final JdbcTemplate template = new JdbcTemplat

21、e(ds); resultList = (List)template.execute(new ProcCallableStatementCreator(storedProc, params), new ProcCallableStatementCallback(); catch(DataAccessException e) throw new RuntimeException(execute method error : DataAccessException + e.getMessage(); return resultList; /* * Create a callable stateme

22、nt in this connection. */ private class ProcCallableStatementCreator implements CallableStatementCreator private String storedProc; private String params; /* * Constructs a callable statement. * param storedProc The stored procedures name. * param params Input parameters. * param outResultCount coun

23、t of output result set. */ public ProcCallableStatementCreator(String storedProc, String params) this.params = params; this.storedProc = storedProc; /* * Returns a callable statement * param conn Connection to use to create statement * return cs A callable statement */ public CallableStatement creat

24、eCallableStatement(Connection conn) StringBuffer storedProcName = new StringBuffer(call ); storedProcName.append(storedProc + (); /set output parameters storedProcName.append(?); storedProcName.append(, ); /set input parameters storedProcName.append(?); storedProcName.append(); CallableStatement cs = null; try / set the first parameter is OracleTyep.CURSOR for