Oracle數據庫先創建一個表和添加一些數據
1.先在Oracle數據庫中創建一個student表:
|
1
2
3
4
5
6
7
8
|
create table student( id number(11) not null primary key, stu_name varchar(16) not null, gender number(11) default null, age number(11) default null, address varchar(128) default null); |
2.向表中增添一些數據
insert into student values('1','王小軍','1','17','北京市和平里七區30號樓7門102')
MyEclipse里編寫java代碼
1.將ojdbc6.jar導入項目中
先創建一個項目，然后在鼠標移到項目上右鍵-->new-->folder;folder name:lib;這樣就在項目中創建了一個文件夾lib;然后將ojdbc6.jar包導入該文件夾中
該包下載地址鏈接：ojdbc6.rar
鼠標移到該包上;右鍵-->build path-->add to build path;
2.創建一個類,開始編碼
|
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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
|
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;public class OperateOracle { // 定義連接所需的字符串 // 192.168.0.X是本機地址(要改成自己的IP地址)，1521端口號，XE是精簡版Oracle的默認數據庫名 private static String USERNAMR = "orcl"; private static String PASSWORD = "orcl"; private static String DRVIER = "oracle.jdbc.OracleDriver"; private static String URL = "jdbc:oracle:thin:@192.168.0.X:1521:xe"; // 創建一個數據庫連接 Connection connection = null; // 創建預編譯語句對象，一般都是用這個而不用Statement PreparedStatement pstm = null; // 創建一個結果集對象 ResultSet rs = null; /** * 向數據庫中增加數據 * 首先獲取表內數據總數,總數+1為新增數據的id值 * @param stuName:學生姓名 * @param gender:學生性別,1表示男性,2表示女性 * @param age:學生年齡 * @param address:學生住址 */ public void AddData(String stuName, int gender, int age, String address) { connection = getConnection(); // String sql = // "insert into student values('1','王小軍','1','17','北京市和平里七區30號樓7門102')"; String sql = "select count(*) from student where 1 = 1"; String sqlStr = "insert into student values(?,?,?,?,?)"; int count = 0; try { // 計算數據庫student表中數據總數 pstm = connection.prepareStatement(sql); rs = pstm.executeQuery(); while (rs.next()) { count = rs.getInt(1) + 1; System.out.println(rs.getInt(1)); } // 執行插入數據操作 pstm = connection.prepareStatement(sqlStr); pstm.setInt(1, count); pstm.setString(2, stuName); pstm.setInt(3, gender); pstm.setInt(4, age); pstm.setString(5, address); pstm.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 向數據庫中刪除數據 * @param stuName:根據姓名刪除數據 */ public void DeleteData(String stuName) { connection = getConnection(); String sqlStr = "delete from student where stu_name=?"; System.out.println(stuName); try { // 執行刪除數據操作 pstm = connection.prepareStatement(sqlStr); pstm.setString(1, stuName); pstm.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 向數據庫中修改數據 * @param stuName:學生姓名,根據此值查詢要修改的某行值 * @param gender * @param age * @param address */ public void UpdateData(String stuName, int gender, int age, String address) { connection = getConnection(); String sql = "select id from student where 1 = 1 and stu_name = ?"; String sqlStr = "update student set stu_name=?,gender=?,age=?,address=? where id=?"; int count = 0; try { // 計算數據庫student表中數據總數 pstm = connection.prepareStatement(sql); pstm.setString(1, stuName); rs = pstm.executeQuery(); while (rs.next()) { count = rs.getInt(1); System.out.println(rs.getInt(1)); } // 執行插入數據操作 pstm = connection.prepareStatement(sqlStr); pstm.setString(1, stuName); pstm.setInt(2, gender); pstm.setInt(3, age); pstm.setString(4, address); pstm.setInt(5, count); pstm.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 向數據庫中查詢數據 */ public void SelectData() { connection = getConnection(); String sql = "select * from student where 1 = 1"; try { pstm = connection.prepareStatement(sql); rs = pstm.executeQuery(); while (rs.next()) { String id = rs.getString("id"); String name = rs.getString("stu_name"); String gender = rs.getString("gender"); String age = rs.getString("age"); String address = rs.getString("address"); System.out.println(id + "\t" + name + "\t" + gender + "\t" + age + "\t" + address); } } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 使用ResultSetMetaData計算列數 */ public void SelectData2() { connection = getConnection(); String sql = "select * from employees where 1 = 1"; int count = 0; try { pstm = connection.prepareStatement(sql); rs = pstm.executeQuery(); while (rs.next()) { count++; } ResultSetMetaData rsmd = rs.getMetaData(); int cols_len = rsmd.getColumnCount(); System.out.println("count=" + count + "\tcols_len=" + cols_len); } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 獲取Connection對象 * * @return */ public Connection getConnection() { try { Class.forName(DRVIER); connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD); System.out.println("成功連接數據庫"); } catch (ClassNotFoundException e) { throw new RuntimeException("class not find !", e); } catch (SQLException e) { throw new RuntimeException("get connection error!", e); } return connection; } /** * 釋放資源 */ public void ReleaseResource() { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstm != null) { try { pstm.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } }} |
3.創建一個測試類
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
public class Test { public static void main(String[] args) { /** * 增刪改查完成,但是有一定局限性 * 1.增 問題不大 * 2.刪 要給出一個值去刪除(可能值不存在-->沒有處理機制,值不唯一怎么處理?) * 3.改 同刪的問題 * 4.查 問題不大 */ //創建OperateOracle對象 OperateOracle oo=new OperateOracle(); //測試增加數據操作 //oo.AddData("孫中山",1,25,"北京市海淀區紅旗路111號"); //測試刪除數據操作 //oo.DeleteData("孫中山"); //測試更新數據操作 oo.UpdateData("孫中山",1,30,"北京市東城區岳山路11號"); //測試查詢數據操作 //oo.SelectData(); //測試ResultSetMetaData類 //oo.SelectData2(); }} |
正如測試類中所注釋的,此處只可按照正確的方式去連接Oracle數據庫,操作增刪改查操作,但是對于一些錯誤操作的處理機制還不夠完善。
以上就是本文的全部內容，希望本文的內容對大家的學習或者工作能帶來一定的幫助，同時也希望多多支持服務器之家！
原文鏈接：http://www.cnblogs.com/cainiaomahua/p/6183339.html
