java调用Oracle存储过程方式汇总.docx
- 文档编号:8933254
- 上传时间:2023-05-16
- 格式:DOCX
- 页数:34
- 大小:33.25KB
java调用Oracle存储过程方式汇总.docx
《java调用Oracle存储过程方式汇总.docx》由会员分享,可在线阅读,更多相关《java调用Oracle存储过程方式汇总.docx(34页珍藏版)》请在冰点文库上搜索。
java调用Oracle存储过程方式汇总
spring调用Oracle存储过程,并用cursor返回结果集
1.创建表:
Java代码
1.create table TEST_USERS
2.(
3. USER_ID VARCHAR2(10) not null,
4. NAME VARCHAR2(10) not null,
5. PASSWORD VARCHAR2(20) not null
6.)
createtableTEST_USERS
(
USER_IDVARCHAR2(10)notnull,
NAMEVARCHAR2(10)notnull,
PASSWORDVARCHAR2(20)notnull
)
2.创建存储过程:
Java代码
1.create or replace package display_users_package is
2. type search_results is ref cursor;
3. procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type);
4.end display_users_package;
5.
6.create or replace package body display_users_package is
7. procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type)
8. is
9. begin
10. if userId is not null then
11. open results_out for select * from test_users where user_id like userId || '%';
12. else
13. open results_out for select * from test_users;
14. end if;
15. end display_users_proc;
16.end display_users_package;
createorreplacepackagedisplay_users_packageis
typesearch_resultsisrefcursor;
proceduredisplay_users_proc(results_outoutsearch_results,userIdintest_users.user_id%type);
enddisplay_users_package;
createorreplacepackagebodydisplay_users_packageis
proceduredisplay_users_proc(results_outoutsearch_results,userIdintest_users.user_id%type)
is
begin
ifuserIdisnotnullthen
openresults_outforselect*fromtest_userswhereuser_idlikeuserId||'%';
else
openresults_outforselect*fromtest_users;
endif;
enddisplay_users_proc;
enddisplay_users_package;
这个results_out是一个游标类型,用来返回查找的结果集。
3. 完整实现代码:
Java代码
1.import java.sql.CallableStatement;
2.import java.sql.Connection;
3.import java.sql.ResultSet;
4.import java.sql.SQLException;
5.import java.util.ArrayList;
6.import java.util.HashMap;
7.import java.util.List;
8.import java.util.Map;
9.
10.import javax.sql.DataSource;
11.
12.import oracle.jdbc.OracleTypes;
13.
14.import org.springframework.dao.DataAccessException;
15.import org.springframework.jdbc.core.CallableStatementCallback;
16.import org.springframework.jdbc.core.CallableStatementCreator;
17.import org.springframework.jdbc.core.JdbcTemplate;
18.
19.import com.spring.stored.procedure.util.DataContextUtil;
20.
21./**
22. * @author Jane Jiao
23. *
24. */
25.public class SpringStoredProce {
26.
27. public List
28. List
29. try{
30. final DataSource ds = DataContextUtil.getInstance().getDataSource();
31. final JdbcTemplate template = new JdbcTemplate(ds);
32. resultList = (List
33. new ProcCallableStatementCallback());
34. }catch(DataAccessException e){
35. throw new RuntimeException("execute method error :
DataAccessException " + e.getMessage());
36. }
37. return resultList;
38. }
39.
40.
41. /**
42. * Create a callable statement in this connection.
43. */
44. private class ProcCallableStatementCreator implements CallableStatementCreator {
45. private String storedProc;
46. private String params;
47.
48.
49. /**
50. * Constructs a callable statement.
51. * @param storedProc The stored procedure's name.
52. * @param params Input parameters.
53. * @param outResultCount count of output result set.
54. */
55. public ProcCallableStatementCreator(String storedProc, String params) {
56. this.params = params;
57. this.storedProc = storedProc;
58. }
59.
60. /**
61. * Returns a callable statement
62. * @param conn Connection to use to create statement
63. * @return cs A callable statement
64. */
65. public CallableStatement createCallableStatement(Connection conn) {
66. StringBuffer storedProcName = new StringBuffer("call ");
67. storedProcName.append(storedProc + "(");
68. //set output parameters
69. storedProcName.append("?
");
70. storedProcName.append(", ");
71.
72. //set input parameters
73. storedProcName.append("?
");
74. storedProcName.append(")");
75.
76. CallableStatement cs = null;
77. try {
78. // set the first parameter is OracleTyep.CURSOR for oracel stored procedure
79. cs = conn.prepareCall(storedProcName.toString());
80. cs.registerOutParameter (1, OracleTypes.CURSOR);
81. // set the sencond paramter
82. cs.setObject(2, params);
83. } catch (SQLException e) {
84. throw new RuntimeException("createCallableStatement method Error :
SQLException " + e.getMessage());
85. }
86. return cs;
87. }
88.
89. }
90.
91. /**
92. *
93. * The ProcCallableStatementCallback return a result object,
94. * for example a collection of domain objects.
95. *
96. */
97. private class ProcCallableStatementCallback implements CallableStatementCallback {
98.
99. /**
100. * Constructs a ProcCallableStatementCallback.
101. */
102. public ProcCallableStatementCallback() {
103. }
104.
105. /**
106. * Returns a List(Map) collection.
107. * @param cs object that can create a CallableStatement given a Connection
108. * @return resultsList a result object returned by the action, or null
109. */
110. public Object doInCallableStatement(CallableStatement cs){
111. List
112. try {
113. cs.execute();
114. ResultSet rs = (ResultSet) cs.getObject
(1);
115. while (rs.next()) {
116. Map
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(SQLException e) {
124. throw new RuntimeException("doInCallableStatement method error :
SQLException " + e.getMessage());
125. }
126. return resultsMap;
127. }
128. }
129.}
importjava.sql.CallableStatement;
importjava.sql.Connection;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.HashMap;
importjava.util.List;
importjava.util.Map;
importjavax.sql.DataSource;
importoracle.jdbc.OracleTypes;
importorg.springframework.dao.DataAccessException;
importorg.springframework.jdbc.core.CallableStatementCallback;
importorg.springframework.jdbc.core.CallableStatementCreator;
importorg.springframework.jdbc.core.JdbcTemplate;
importcom.spring.stored.procedure.util.DataContextUtil;
/**
*@authorJaneJiao
*
*/
publicclassSpringStoredProce{
publicList
List
try{
finalDataSourceds=DataContextUtil.getInstance().getDataSource();
finalJdbcTemplatetemplate=newJdbcTemplate(ds);
resultList=(List
newProcCallableStatementCallback());
}catch(DataAccessExceptione){
thrownewRuntimeException("executemethoderror:
DataAccessException"+e.getMessage());
}
returnresultList;
}
/**
*Createacallablestatementinthisconnection.
*/
privateclassProcCallableStatementCreatorimplementsCallableStatementCreator{
privateStringstoredProc;
privateStringparams;
/**
*Constructsacallablestatement.
*@paramstoredProcThestoredprocedure'sname.
*@paramparamsInputparameters.
*@paramoutResultCountcountofoutputresultset.
*/
publicProcCallableStatementCreator(StringstoredProc,Stringparams){
this.params=params;
this.storedProc=storedProc;
}
/**
*Returnsacallablestatement
*@paramconnConnectiontousetocreatestatement
*@returncsAcallablestatement
*/
publicCallableStatementcreateCallableStatement(Connectionconn){
StringBufferstoredProcName=newStringBuffer("call");
storedProcName.append(storedProc+"(");
//setoutputparameters
storedProcName.append("?
");
storedProcName.append(",");
//setinputparameters
storedProcName.append("?
");
storedProcName.append(")");
CallableStatementcs=null;
try{
//setthefirstparameterisOracleTyep.CURSORfor
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- java 调用 Oracle 存储 过程 方式 汇总