本文實(shí)例講述了java調(diào)用mysql存儲(chǔ)過程的方法。分享給大家供大家參考。具體如下：
數(shù)據(jù)庫(kù)的測(cè)試代碼如下 :
1、新建表test
|
1
2
3
4
5
|
create table test(field1 int not null)TYPE=MyISAM ;insert into test(field1) values(1); |
2、刪除已存在的存儲(chǔ)過程：
|
1
2
3
|
-- 刪除儲(chǔ)存過程delimiter // -- 定義結(jié)束符號(hào)drop procedure p_test; |
3、mysql存儲(chǔ)過程定義：
|
1
2
3
4
5
6
|
create procedure p_test()begindeclare temp int;set temp = 0; update test set field1 = values(temp);end |
4、調(diào)用方法：
|
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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
CallableStatement cStmt = conn.prepareCall("{call p_test()}");cStmt.executeUpdate();import java.sql.*; /** iGoder */public class ProcedureTest { /* 表和存儲(chǔ)過程定義如下： delimiter // DROP TABLE if exists test // CREATE TABLE test( id int(11) NULL ) // drop procedure if existssp1 // create procedure sp1(in p int) comment 'insert into a int value' begin declare v1 int; set v1 = p; insert into test(id) values(v1); end // drop procedure if exists sp2 // create procedure sp2(out p int) begin select max(id) into p from test; end // drop procedure if exists sp6 // create procedure sp6() begin select * from test; end// */ public static void main(String[] args) { //callIn(111); //callOut(); callResult(); } /** * 調(diào)用帶有輸入?yún)?shù)的存儲(chǔ)過程 * @param in stored procedure input parametervalue */ public static void callIn(int in){ //獲取連接 Connection conn = ConnectDb.getConnection(); CallableStatement cs = null; try { //可以直接傳入?yún)?shù) //cs = conn.prepareCall("{call sp1(1)}"); //也可以用問號(hào)代替 cs = conn.prepareCall("{call sp1(?)}"); //設(shè)置第一個(gè)輸入?yún)?shù)的值為110 cs.setInt(1, in); cs.execute(); } catch (Exception e) { e.printStackTrace(); } finally { try { if(cs != null){ cs.close(); } if(conn != null){ conn.close(); } } catch (Exception ex) { ex.printStackTrace(); } } } /** * 調(diào)用帶有輸出參數(shù)的存儲(chǔ)過程 * */ public static void callOut() { Connection conn = ConnectDb.getConnection(); CallableStatement cs = null; try { cs = conn.prepareCall("{call sp2(?)}"); //第一個(gè)參數(shù)的類型為Int cs.registerOutParameter(1, Types.INTEGER); cs.execute(); //得到第一個(gè)值 int i = cs.getInt(1); System.out.println(i); } catch (Exception e) { e.printStackTrace(); } finally { try { if(cs != null){ cs.close(); } if(conn != null){ conn.close(); } } catch (Exception ex) { ex.printStackTrace(); } } } /** * 調(diào)用輸出結(jié)果集的存儲(chǔ)過程 */ public static void callResult(){ Connection conn = ConnectDb.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { cs = conn.prepareCall("{call sp6()}"); rs = cs.executeQuery(); //循環(huán)輸出結(jié)果 while(rs.next()){ System.out.println(rs.getString(1)); } } catch (Exception e) { e.printStackTrace(); } finally { try { if(rs != null){ rs.close(); } if(cs != null){ cs.close(); } if(conn != null){ conn.close(); } } catch (Exception ex) { ex.printStackTrace(); } } }} /** *獲取數(shù)據(jù)庫(kù)連接的類 */import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement; class ConnectDb { public static Connection getConnection(){ Connection conn = null; PreparedStatement preparedstatement = null; try { Class.forName("org.gjt.mm.mysql.Driver").newInstance(); String dbname = "test"; String url="jdbc:mysql://localhost/"+dbname+"?user=root&password=root&useUnicode=true&characterEncoding=8859_1"; conn= DriverManager.getConnection(url); } catch (Exception e) { e.printStackTrace(); } return conn; } } |
希望本文所述對(duì)大家的java程序設(shè)計(jì)有所幫助。
