簡(jiǎn)單的Java數(shù)據(jù)庫(kù)連接和關(guān)閉工具類(lèi)
寫(xiě)JDBC應(yīng)用的人常常為關(guān)閉資源而頭痛不已，這些代碼枯燥無(wú)味，如何才能用簡(jiǎn)單的代碼進(jìn)行關(guān)閉呢，下面我寫(xiě)了一個(gè)方法，可以解除你的痛苦：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
/** * 關(guān)閉所有可關(guān)閉資源 * * @param objs 可關(guān)閉的資源對(duì)象有Connection、Statement、ResultSet，別的類(lèi)型資源自動(dòng)忽略 */public static void closeAll(Object... objs) { for (Object obj : objs) { if (obj instanceof Connection) close((Connection) obj); if (obj instanceof Statement) close((Statement) obj); if (obj instanceof ResultSet) close((ResultSet) obj); } } |
這個(gè)方法，帶了“...”參數(shù)，這個(gè)實(shí)際上是Java5中的可變參數(shù)方法。可以不論順序，不論個(gè)數(shù)，調(diào)用時(shí)候直接關(guān)閉想要關(guān)閉的資源對(duì)象就ok了。例如：
|
1
2
3
4
5
|
catch (SQLException e) { e.printStackTrace(); } finally { DBTools.closeAll(stmt, pstmt1, pstmt2, conn); } |
下面給出這個(gè)類(lèi)完整的寫(xiě)法：
|
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
|
package com.lavasoft.ibatistools.common; import com.lavasoft.ibatistools.bean.Table; import com.lavasoft.ibatistools.metadata.DataSourceMetaData; import com.lavasoft.ibatistools.metadata.MySQLDataSourceMetaData; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.List; import java.util.Properties; /** * 簡(jiǎn)單的Java數(shù)據(jù)庫(kù)連接和關(guān)閉工具類(lèi) * * @author leizhimin 11-12-20 下午4:32 */public class DBTools { private static String driverClassName, url, user, password; static { init(); } private static void init() { InputStream in = DBTools.class.getResourceAsStream("/com/lavasoft/ibatistools/jdbc.properties"); Properties preps = new Properties(); try { preps.load(in); driverClassName = preps.getProperty("jdbc.driver"); url = preps.getProperty("jdbc.url"); user = preps.getProperty("jdbc.username"); password = preps.getProperty("jdbc.password"); } catch (IOException e) { e.printStackTrace(); } } /** * 創(chuàng)建一個(gè)JDBC連接 * * @return 一個(gè)JDBC連接 */ public static Connection makeConnection() { Connection conn = null; try { Class.forName(driverClassName); conn = DriverManager.getConnection(url, user, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } public static void close(Connection conn) { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } public static void close(ResultSet rs) { if (rs != null) try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } public static void close(Statement stmt) { if (stmt != null) try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 關(guān)閉所有可關(guān)閉資源 * * @param objs 可關(guān)閉的資源對(duì)象有Connection、Statement、ResultSet，別的類(lèi)型資源自動(dòng)忽略 */ public static void closeAll(Object... objs) { for (Object obj : objs) { if (obj instanceof Connection) close((Connection) obj); if (obj instanceof Statement) close((Statement) obj); if (obj instanceof ResultSet) close((ResultSet) obj); } } public static void main(String[] args) { DataSourceMetaData dbmd = MySQLDataSourceMetaData.instatnce(); List<Table> tableList = dbmd.getAllTableMetaData(DBTools.makeConnection()); for (Table table : tableList) { System.out.println(table); } } } |
因?yàn)槭窃趯?xiě)工具，連接用到的次數(shù)很少，所以這里采用jdbc模式創(chuàng)建，而沒(méi)有用到連接池。關(guān)閉方法用起來(lái)很爽，減少了代碼量，也提高了程序的可靠性和質(zhì)量。
一個(gè)簡(jiǎn)單的JDBC通用工具
支持多種數(shù)據(jù)庫(kù)，統(tǒng)一方式產(chǎn)生連接，最優(yōu)化、最簡(jiǎn)單方式釋放資源。
歡迎拍磚！
|
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
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
|
import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import java.sql.*; import java.util.List; import java.util.Properties; /** * 通用數(shù)據(jù)庫(kù)操作工具，提供數(shù)據(jù)庫(kù)連接獲取、SQL執(zhí)行、資源關(guān)閉等功能，支持的數(shù)據(jù)庫(kù)為Oracle10g、MySQL5.x。</P> * * @author leizhimin 2012-03-05 11:22 */public class DBToolkit { private static Log log = LogFactory.getLog(DBToolkit.class); static { try { Class.forName("oracle.jdbc.driver.OracleDriver"); Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { log.error("加載數(shù)據(jù)庫(kù)驅(qū)動(dòng)發(fā)生錯(cuò)誤！"); e.printStackTrace(); } } /** * 創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)連接 * * @param url 數(shù)據(jù)庫(kù)連接URL串 * @param properties 作為連接參數(shù)的任意字符串標(biāo)記/值對(duì)的列表；通常至少應(yīng)該包括 "user" 和 "password" 屬性 * @return 一個(gè)JDBC的數(shù)據(jù)庫(kù)連接 * @throws SQLException 獲取連接失敗時(shí)候拋出 */ public static Connection makeConnection(String url, Properties properties) throws SQLException { Connection conn = null; try { conn = DriverManager.getConnection(url, properties); } catch (SQLException e) { log.error("獲取數(shù)據(jù)庫(kù)連接發(fā)生異常", e); throw e; } return conn; } /** * 在一個(gè)數(shù)據(jù)庫(kù)連接上執(zhí)行一個(gè)靜態(tài)SQL語(yǔ)句查詢(xún) * * @param conn 數(shù)據(jù)庫(kù)連接 * @param staticSql 靜態(tài)SQL語(yǔ)句字符串 * @return 返回查詢(xún)結(jié)果集ResultSet對(duì)象 * @throws SQLException 執(zhí)行異常時(shí)候拋出 */ public static ResultSet executeQuery(Connection conn, String staticSql) throws SQLException { ResultSet rs = null; try { //創(chuàng)建執(zhí)行SQL的對(duì)象 Statement stmt = conn.createStatement(); //執(zhí)行SQL，并獲取返回結(jié)果 rs = stmt.executeQuery(staticSql); } catch (SQLException e) { log.error("執(zhí)行SQL語(yǔ)句出錯(cuò)，請(qǐng)檢查！\n" + staticSql); throw e; } return rs; } /** * 在一個(gè)數(shù)據(jù)庫(kù)連接上執(zhí)行一個(gè)靜態(tài)SQL語(yǔ)句 * * @param conn 數(shù)據(jù)庫(kù)連接 * @param staticSql 靜態(tài)SQL語(yǔ)句字符串 * @throws SQLException 執(zhí)行異常時(shí)候拋出 */ public static void executeSQL(Connection conn, String staticSql) throws SQLException { Statement stmt = null; try { //創(chuàng)建執(zhí)行SQL的對(duì)象 stmt = conn.createStatement(); //執(zhí)行SQL，并獲取返回結(jié)果 stmt.execute(staticSql); } catch (SQLException e) { log.error("執(zhí)行SQL語(yǔ)句出錯(cuò)，請(qǐng)檢查！\n" + staticSql); throw e; } finally { close(stmt); } } /** * 在一個(gè)數(shù)據(jù)庫(kù)連接上執(zhí)行一批靜態(tài)SQL語(yǔ)句 * * @param conn 數(shù)據(jù)庫(kù)連接 * @param sqlList 靜態(tài)SQL語(yǔ)句字符串集合 * @throws SQLException 執(zhí)行異常時(shí)候拋出 */ public static void executeBatchSQL(Connection conn, List<String> sqlList) throws SQLException { try { //創(chuàng)建執(zhí)行SQL的對(duì)象 Statement stmt = conn.createStatement(); for (String sql : sqlList) { stmt.addBatch(sql); } //執(zhí)行SQL，并獲取返回結(jié)果 stmt.executeBatch(); } catch (SQLException e) { log.error("執(zhí)行批量SQL語(yǔ)句出錯(cuò)，請(qǐng)檢查！"); throw e; } } /** * 獲取Oracle數(shù)據(jù)一個(gè)指定的Sequence下一個(gè)值 * * @param conn 數(shù)據(jù)庫(kù)連接 * @param seq_name Sequence名稱(chēng) * @return Sequence下一個(gè)值 */ public static long sequenceNextval(Connection conn, String seq_name) { long val = -1L; Statement stmt = null; ResultSet rs = null; try { //創(chuàng)建執(zhí)行SQL的對(duì)象 stmt = conn.createStatement(); //執(zhí)行SQL，并獲取返回結(jié)果 rs = stmt.executeQuery("select " + seq_name + ".nextval from dual"); if (rs.next()) val = rs.getLong(1); } catch (SQLException e) { log.error("#ERROR# :獲取Sequence值出錯(cuò)，請(qǐng)檢查！\n" + seq_name); e.printStackTrace(); throw new RuntimeException(e); } finally { close(rs); close(stmt); } return val; } /** * 關(guān)閉所有可關(guān)閉的JDBC資源，不論先后順序，總能以正確的順序執(zhí)行 * * @param objs 可關(guān)閉的資源對(duì)象有Connection、Statement、ResultSet，別的類(lèi)型資源自動(dòng)忽略 */ public static void closeAll(Object... objs) { for (Object obj : objs) if (obj instanceof ResultSet) close((ResultSet) obj); for (Object obj : objs) if (obj instanceof Statement) close((Statement) obj); for (Object obj : objs) if (obj instanceof Connection) close((Connection) obj); } private static void close(Connection conn) { if (conn != null) try { conn.close(); } catch (SQLException e) { log.error("關(guān)閉數(shù)據(jù)庫(kù)連接發(fā)生異常！"); } } private static void close(ResultSet rs) { if (rs != null) try { rs.close(); } catch (SQLException e) { log.error("關(guān)閉結(jié)果集發(fā)生異常！"); } } private static void close(Statement stmt) { if (stmt != null) try { stmt.close(); } catch (SQLException e) { log.error("關(guān)閉SQL語(yǔ)句發(fā)生異常！"); } } /** * 測(cè)試代碼，沒(méi)用 * * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { String tns = "jdbc:oracle:thin:@\n" + "(description= \n" + "\t(ADDRESS_LIST =\n" + "\t\t(address=(protocol=tcp)(host=10.87.30.44)(port=1521))\n" + "\t\t(address=(protocol=tcp)(host=10.87.30.45)(port=1521))\n" + "\t\t(address=(protocol=tcp)(host=10.87.30.46)(port=1521))\n" + "\t\t(load_balance=yes)\n" + "\t)\n" + "\t(connect_data =\n" + "\t\t(service_name=KFCS)\n" + "\t\t(failover_mode =\n" + "\t\t\t(type=session)\n" + "\t\t\t(method=basic)\n" + "\t\t\t(retries=5)\n" + "\t\t\t(delay=15)\n" + "\t\t)\n" + "\t)\n" + ")"; Properties p_ora = new Properties(); p_ora.put("user", "base"); p_ora.put("password", "1qaz!QAZ"); p_ora.put("internal_logon", "normal"); Connection ora_conn = makeConnection(tns, p_ora); ResultSet rs1 = ora_conn.createStatement().executeQuery("select count(1) from base.cfg_static_data"); rs1.next(); System.out.println(rs1.getInt(1)); rs1.close(); ora_conn.close(); Properties p_mysql = new Properties(); p_mysql.put("user", "root"); p_mysql.put("password", "leizm"); String url = "jdbc:mysql://localhost:3306/tdmc"; Connection mysql_conn = makeConnection(url, p_mysql); ResultSet rs2 = mysql_conn.createStatement().executeQuery("select count(1) from cfg_code"); rs2.next(); System.out.println(rs2.getInt(1)); rs2.close(); mysql_conn.close(); } } |
