Java調(diào)用Oracle存儲(chǔ)過(guò)程詳解
步驟：
1、編寫(xiě)Oracle存儲(chǔ)過(guò)程
2、編寫(xiě)數(shù)據(jù)庫(kù)獲取連接工具類(lèi)
3、編寫(xiě)簡(jiǎn)單應(yīng)用調(diào)用存儲(chǔ)過(guò)程
實(shí)現(xiàn)：
1、Oracle存儲(chǔ)過(guò)程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
/*測(cè)試表*/create table test( id varchar2(32), name varchar2(32));/*存儲(chǔ)過(guò)程 插入數(shù)據(jù)*/ CREATE OR REPLACE PROCEDURE insert_procedure( PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) ASBEGIN INSERT INTO test (id, name) VALUES (PARA1, PARA2);END insert_procedure;/*存儲(chǔ)過(guò)程 返回結(jié)果集*/CREATE OR REPLACE PROCEDURE select_procedure( para_id IN VARCHAR2, name OUT sys_refcursor /* 這個(gè)sys_refcursor類(lèi)型在SYS.STANDARD包中 */) ASBEGIN OPEN name FOR SELECT * FROM test WHERE id = para_id;END; |
2、JDBC工具類(lèi)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtil { public static final String DRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String URL = "jdbc:oracle:thin:@localhost:1521/orcl"; public static final String USERNAME = "pfm"; public static final String PASSWORD = "pfm"; /** * 通過(guò)靜態(tài)代碼塊 注冊(cè)數(shù)據(jù)庫(kù)驅(qū)動(dòng) */ static { try { Class.forName(DRIVER); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 獲得Connection * * @return */ public static Connection getConnection() { Connection conn = null; try { conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (SQLException e) { e.printStackTrace(); } return conn; } /** * 獲得Statement * * @return */ public static Statement getStatement() { Statement st = null; try { st = getConnection().createStatement(); } catch (SQLException e) { e.printStackTrace(); } return st; } /** * 關(guān)閉ResultSet * * @param rs */ public static void closeResultSet(ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 關(guān)閉Statement * * @param st */ public static void closeStatement(Statement st) { if (st != null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 關(guān)閉Connection * * @param conn */ public static void closeConnection(Connection conn) { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 關(guān)閉全部 * * @param rs * @param sta * @param conn */ public static void closeAll(ResultSet rs, Statement sta, Connection conn) { closeResultSet(rs); closeStatement(sta); closeConnection(conn); }} |
3、調(diào)用存儲(chǔ)過(guò)程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import oracle.jdbc.driver.OracleTypes;/** * 測(cè)試調(diào)用存儲(chǔ)過(guò)程 * */public class StoredTest { public static void main(String[] args) { insert_call(); //select_call(); } /** * 執(zhí)行存儲(chǔ)過(guò)程 插入數(shù)據(jù) */ public static void insert_call() { Connection conn = DBUtil.getConnection(); PreparedStatement pst = null; CallableStatement proc = null; // 創(chuàng)建執(zhí)行存儲(chǔ)過(guò)程的對(duì)象 try { proc = conn.prepareCall("{ call insert_procedure(?,?) }"); proc.setString(1, "1"); // 設(shè)置第一個(gè)輸入?yún)?shù) proc.setString(2, "hello call"); // 設(shè)置第一個(gè)輸入?yún)?shù) proc.execute();// 執(zhí)行 } catch (SQLException e) { e.printStackTrace(); } finally { try { // 關(guān)閉IO流 proc.close(); DBUtil.closeAll(null, pst, conn); } catch (Exception e) { e.printStackTrace(); } } } /** * 執(zhí)行存儲(chǔ)過(guò)程 查詢數(shù)據(jù) */ public static void select_call() { Connection conn = DBUtil.getConnection(); CallableStatement stmt; try { stmt = conn.prepareCall("{ call select_procedure(?, ?) }"); // 用此調(diào)用方法不能實(shí)現(xiàn)多行語(yǔ)法 stmt.setString(1, "1"); stmt.registerOutParameter(2, OracleTypes.CURSOR); stmt.execute(); ResultSet rs = (ResultSet) stmt.getObject(2); while (rs.next()) { System.out.println(rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.closeConnection(conn); } }} |
感謝閱讀，希望能幫助到大家，謝謝大家對(duì)本站的支持！
原文鏈接：http://blog.csdn.net/itmyhome1990/article/details/49818531
