簡單的Java數(shù)據(jù)庫連接和關(guān)閉工具類
寫JDBC應(yīng)用的人常常為關(guān)閉資源而頭痛不已，這些代碼枯燥無味，如何才能用簡單的代碼進(jìn)行關(guān)閉呢，下面我寫了一個方法，可以解除你的痛苦：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
/** * 關(guān)閉所有可關(guān)閉資源 * * @param objs 可關(guān)閉的資源對象有Connection、Statement、ResultSet，別的類型資源自動忽略 */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); } } |
這個方法，帶了“...”參數(shù)，這個實(shí)際上是Java5中的可變參數(shù)方法。可以不論順序，不論個數(shù)，調(diào)用時候直接關(guān)閉想要關(guān)閉的資源對象就ok了。例如：
|
1
2
3
4
5
|
catch (SQLException e) { e.printStackTrace(); } finally { DBTools.closeAll(stmt, pstmt1, pstmt2, conn); } |
下面給出這個類完整的寫法：
|
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; /** * 簡單的Java數(shù)據(jù)庫連接和關(guān)閉工具類 * * @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)建一個JDBC連接 * * @return 一個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)閉的資源對象有Connection、Statement、ResultSet，別的類型資源自動忽略 */ 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); } } } |
因為是在寫工具，連接用到的次數(shù)很少，所以這里采用jdbc模式創(chuàng)建，而沒有用到連接池。關(guān)閉方法用起來很爽，減少了代碼量，也提高了程序的可靠性和質(zhì)量。
一個簡單的JDBC通用工具
支持多種數(shù)據(jù)庫，統(tǒng)一方式產(chǎn)生連接，最優(yōu)化、最簡單方式釋放資源。
歡迎拍磚！
|
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ù)庫操作工具，提供數(shù)據(jù)庫連接獲取、SQL執(zhí)行、資源關(guān)閉等功能，支持的數(shù)據(jù)庫為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ù)庫驅(qū)動發(fā)生錯誤！"); e.printStackTrace(); } } /** * 創(chuàng)建一個數(shù)據(jù)庫連接 * * @param url 數(shù)據(jù)庫連接URL串 * @param properties 作為連接參數(shù)的任意字符串標(biāo)記/值對的列表；通常至少應(yīng)該包括 "user" 和 "password" 屬性 * @return 一個JDBC的數(shù)據(jù)庫連接 * @throws SQLException 獲取連接失敗時候拋出 */ 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ù)庫連接發(fā)生異常", e); throw e; } return conn; } /** * 在一個數(shù)據(jù)庫連接上執(zhí)行一個靜態(tài)SQL語句查詢 * * @param conn 數(shù)據(jù)庫連接 * @param staticSql 靜態(tài)SQL語句字符串 * @return 返回查詢結(jié)果集ResultSet對象 * @throws SQLException 執(zhí)行異常時候拋出 */ public static ResultSet executeQuery(Connection conn, String staticSql) throws SQLException { ResultSet rs = null; try { //創(chuàng)建執(zhí)行SQL的對象 Statement stmt = conn.createStatement(); //執(zhí)行SQL，并獲取返回結(jié)果 rs = stmt.executeQuery(staticSql); } catch (SQLException e) { log.error("執(zhí)行SQL語句出錯，請檢查！\n" + staticSql); throw e; } return rs; } /** * 在一個數(shù)據(jù)庫連接上執(zhí)行一個靜態(tài)SQL語句 * * @param conn 數(shù)據(jù)庫連接 * @param staticSql 靜態(tài)SQL語句字符串 * @throws SQLException 執(zhí)行異常時候拋出 */ public static void executeSQL(Connection conn, String staticSql) throws SQLException { Statement stmt = null; try { //創(chuàng)建執(zhí)行SQL的對象 stmt = conn.createStatement(); //執(zhí)行SQL，并獲取返回結(jié)果 stmt.execute(staticSql); } catch (SQLException e) { log.error("執(zhí)行SQL語句出錯，請檢查！\n" + staticSql); throw e; } finally { close(stmt); } } /** * 在一個數(shù)據(jù)庫連接上執(zhí)行一批靜態(tài)SQL語句 * * @param conn 數(shù)據(jù)庫連接 * @param sqlList 靜態(tài)SQL語句字符串集合 * @throws SQLException 執(zhí)行異常時候拋出 */ public static void executeBatchSQL(Connection conn, List<String> sqlList) throws SQLException { try { //創(chuàng)建執(zhí)行SQL的對象 Statement stmt = conn.createStatement(); for (String sql : sqlList) { stmt.addBatch(sql); } //執(zhí)行SQL，并獲取返回結(jié)果 stmt.executeBatch(); } catch (SQLException e) { log.error("執(zhí)行批量SQL語句出錯，請檢查！"); throw e; } } /** * 獲取Oracle數(shù)據(jù)一個指定的Sequence下一個值 * * @param conn 數(shù)據(jù)庫連接 * @param seq_name Sequence名稱 * @return Sequence下一個值 */ public static long sequenceNextval(Connection conn, String seq_name) { long val = -1L; Statement stmt = null; ResultSet rs = null; try { //創(chuàng)建執(zhí)行SQL的對象 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值出錯，請檢查！\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)閉的資源對象有Connection、Statement、ResultSet，別的類型資源自動忽略 */ 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ù)庫連接發(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語句發(fā)生異常！"); } } /** * 測試代碼，沒用 * * @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(); } } |
