哎，最近很好久沒(méi)寫(xiě)點(diǎn)東西了，由于工作的原因，接觸公司自己研發(fā)的底層orm框架，偶然發(fā)現(xiàn)該框架在調(diào)用jdbc操作的時(shí)候參考的是hibernate 里面的SimpleJdbcTemplate,這里我想到了在大學(xué)的時(shí)候自己用過(guò)的一個(gè)簡(jiǎn)單的jdbc封裝，現(xiàn)在我將代碼貼出來(lái)，和大家一起分享：
Config類：讀取同一包下的數(shù)據(jù)庫(kù)連接配置文件，這樣是為了更好的通用性考慮
|
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
|
package com.tly.dbutil;import java.io.IOException;import java.util.Properties;public class Config { private static Properties prop = new Properties(); static{ try { //加載dbconfig.properties配置文件 prop.load(Config.class.getResourceAsStream("dbconfig.properties")); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //設(shè)置常量 public static final String CLASS_NAME = prop.getProperty("CLASS_NAME"); public static final String DATABASE_URL = prop.getProperty("DATABASE_URL"); public static final String SERVER_IP = prop.getProperty("SERVER_IP"); public static final String SERVER_PORT = prop.getProperty("SERVER_PORT"); public static final String DATABASE_SID = prop.getProperty("DATABASE_SID"); public static final String USERNAME = prop.getProperty("USERNAME"); public static final String PASSWORD = prop.getProperty("PASSWORD"); } |
dbconfig.properties：數(shù)據(jù)庫(kù)配置文件，你也可以用xml格式等，注意Config類里面該文件的調(diào)用位置
|
1
2
3
4
5
6
7
|
CLASS_NAME=com.mysql.jdbc.DriverDATABASE_URL=jdbc:mysqlSERVER_IP=localhostSERVER_PORT=3306DATABASE_SID=employeesUSERNAME=rootPASSWORD=1 |
接下來(lái)就是數(shù)據(jù)庫(kù)連接輔助類DBConn了
|
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
|
package com.employees.dbutil;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DBConn { //三屬性、四方法 //三大核心接口 private Connection conn = null; private PreparedStatement pstmt = null; private ResultSet rs = null; //四個(gè)方法 //method1: 創(chuàng)建數(shù)據(jù)庫(kù)的連接 public Connection getConntion(){ try { //1: 加載連接驅(qū)動(dòng)，Java反射原理 Class.forName(Config.CLASS_NAME); //2:創(chuàng)建Connection接口對(duì)象，用于獲取MySQL數(shù)據(jù)庫(kù)的連接對(duì)象。三個(gè)參數(shù)：url連接字符串 賬號(hào) 密碼 String url = Config.DATABASE_URL+"://"+Config.SERVER_IP+":"+Config.SERVER_PORT+"/"+Config.DATABASE_SID; conn = DriverManager.getConnection(url,Config.USERNAME,Config.PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } //method2：關(guān)閉數(shù)據(jù)庫(kù)的方法 public void closeConn(){ if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(pstmt!=null){ try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } //method3: 專門用于發(fā)送增刪改語(yǔ)句的方法 public int execOther(PreparedStatement pstmt){ try { //1、使用Statement對(duì)象發(fā)送SQL語(yǔ)句 int affectedRows = pstmt.executeUpdate(); //2、返回結(jié)果 return affectedRows; } catch (SQLException e) { e.printStackTrace(); return -1; } } //method4: 專門用于發(fā)送查詢語(yǔ)句 public ResultSet execQuery(PreparedStatement pstmt){ try { //1、使用Statement對(duì)象發(fā)送SQL語(yǔ)句 rs = pstmt.executeQuery(); //2、返回結(jié)果 return rs; } catch (SQLException e) { e.printStackTrace(); return null; } }} |
平時(shí)的用上面的代碼能夠解決一些簡(jiǎn)單的CRUD的應(yīng)用了，但是還有很多限制，比如每次程序拿連接都要new,這樣就給系統(tǒng)加大了負(fù)擔(dān)，沒(méi)有事務(wù)，沒(méi)有dataSource等等，今天看見(jiàn)一哥們?cè)趫@里面寫(xiě)的一篇用反射解決直接以對(duì)象參數(shù)的方式CRUD,這個(gè)我以前也寫(xiě)過(guò)，沒(méi)寫(xiě)完，主要是自己想寫(xiě)一個(gè)通用的DButil,最后研究來(lái)研究去，發(fā)現(xiàn)越來(lái)越和hibernate里面的simpleJdbcTemplate接近了，所以就直接去看hibernate的源碼了，加上那段時(shí)間有些事，沒(méi)有時(shí)間，就將這件事閑置起來(lái)了，現(xiàn)在把這個(gè)東西補(bǔ)上，也給自己回顧一下下
BaseDao類
|
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
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
|
package com.employees.dao;import java.io.InputStream;import java.lang.reflect.Method;import java.lang.reflect.ParameterizedType;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import com.employees.dbutil.DBConn;public class BaseDAO<T> { DBConn conn = new DBConn(); private Connection connection = null; @SuppressWarnings("unused") private Class<T> persistentClass; @SuppressWarnings("unchecked") public BaseDAO() { initConnection(); //獲得參數(shù)化類型 ParameterizedType type = (ParameterizedType)getClass().getGenericSuperclass(); persistentClass = (Class<T>)type.getActualTypeArguments()[0]; } /** * 獲得數(shù)據(jù)庫(kù)連接 */ public void initConnection() { connection = conn.getConntion(); } /** * 保存 */ public void save(T entity) throws Exception{ //SQL語(yǔ)句,insert into table name ( String sql = "insert into " + entity.getClass().getSimpleName().toLowerCase() + "("; //獲得帶有字符串get的所有方法的對(duì)象 List<Method> list = this.matchPojoMethods(entity,"get"); Iterator<Method> iter = list.iterator(); //拼接字段順序 insert into table name(id,name,email, while(iter.hasNext()) { Method method = iter.next(); sql += method.getName().substring(3).toLowerCase() + ","; } //去掉最后一個(gè),符號(hào)insert insert into table name(id,name,email) values( sql = sql.substring(0, sql.lastIndexOf(",")) + ") values("; //拼裝預(yù)編譯SQL語(yǔ)句insert insert into table name(id,name,email) values(?,?,?, for(int j = 0; j < list.size(); j++) { sql += "?,"; } //去掉SQL語(yǔ)句最后一個(gè),符號(hào)insert insert into table name(id,name,email) values(?,?,?); sql = sql.substring(0, sql.lastIndexOf(",")) + ")"; //到此SQL語(yǔ)句拼接完成,打印SQL語(yǔ)句 System.out.println(sql); //獲得預(yù)編譯對(duì)象的引用 PreparedStatement statement = connection.prepareStatement(sql); int i = 0; //把指向迭代器最后一行的指針移到第一行. iter = list.iterator(); while(iter.hasNext()) { Method method = iter.next(); //此初判斷返回值的類型,因?yàn)榇嫒霐?shù)據(jù)庫(kù)時(shí)有的字段值格式需要改變,比如String,SQL語(yǔ)句是'"+abc+"' if(method.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("Date") != -1){ statement.setDate(++i, this.getDate(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("InputStream") != -1) { statement.setAsciiStream(++i, this.getBlob(method, entity),1440); } else { statement.setInt(++i, this.getInt(method, entity)); } } //執(zhí)行 conn.execOther(statement); //關(guān)閉連接 conn.closeConn(); } /** * 修改 */ public void update(T entity) throws Exception{ String sql = "update " + entity.getClass().getSimpleName().toLowerCase() + " set "; //獲得該類所有g(shù)et方法對(duì)象集合 List<Method> list = this.matchPojoMethods(entity,"get"); //臨時(shí)Method對(duì)象,負(fù)責(zé)迭代時(shí)裝method對(duì)象. Method tempMethod = null; //由于修改時(shí)不需要修改ID,所以按順序加參數(shù)則應(yīng)該把Id移到最后. Method idMethod = null; Iterator<Method> iter = list.iterator(); while(iter.hasNext()) { tempMethod = iter.next(); //如果方法名中帶有ID字符串并且長(zhǎng)度為2,則視為ID. if(tempMethod.getName().lastIndexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) { //把ID字段的對(duì)象存放到一個(gè)變量中,然后在集合中刪掉. idMethod = tempMethod; iter.remove(); //如果方法名去掉set/get字符串以后與pojo + "id"想符合(大小寫(xiě)不敏感),則視為ID } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))) { idMethod = tempMethod; iter.remove(); } } //把迭代指針移到第一位 iter = list.iterator(); while(iter.hasNext()) { tempMethod = iter.next(); sql += tempMethod.getName().substring(3).toLowerCase() + "= ?,"; } //去掉最后一個(gè),符號(hào) sql = sql.substring(0,sql.lastIndexOf(",")); //添加條件 sql += " where " + idMethod.getName().substring(3).toLowerCase() + " = ?"; //SQL拼接完成,打印SQL語(yǔ)句 System.out.println(sql); PreparedStatement statement = this.connection.prepareStatement(sql); int i = 0; iter = list.iterator(); while(iter.hasNext()) { Method method = iter.next(); //此初判斷返回值的類型,因?yàn)榇嫒霐?shù)據(jù)庫(kù)時(shí)有的字段值格式需要改變,比如String,SQL語(yǔ)句是'"+abc+"' if(method.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("Date") != -1){ statement.setDate(++i, this.getDate(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("InputStream") != -1) { statement.setAsciiStream(++i, this.getBlob(method, entity),1440); } else { statement.setInt(++i, this.getInt(method, entity)); } } //為Id字段添加值 if(idMethod.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(idMethod, entity)); } else { statement.setInt(++i, this.getInt(idMethod, entity)); } //執(zhí)行SQL語(yǔ)句 statement.executeUpdate(); //關(guān)閉預(yù)編譯對(duì)象 statement.close(); //關(guān)閉連接 connection.close(); } /** * 刪除 */ public void delete(T entity) throws Exception{ String sql = "delete from " + entity.getClass().getSimpleName().toLowerCase() + " where "; //存放字符串為"id"的字段對(duì)象 Method idMethod = null; //取得字符串為"id"的字段對(duì)象 List<Method> list = this.matchPojoMethods(entity, "get"); Iterator<Method> iter = list.iterator(); while(iter.hasNext()) { Method tempMethod = iter.next(); //如果方法名中帶有ID字符串并且長(zhǎng)度為2,則視為ID. if(tempMethod.getName().lastIndexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) { //把ID字段的對(duì)象存放到一個(gè)變量中,然后在集合中刪掉. idMethod = tempMethod; iter.remove(); //如果方法名去掉set/get字符串以后與pojo + "id"想符合(大小寫(xiě)不敏感),則視為ID } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))) { idMethod = tempMethod; iter.remove(); } } sql += idMethod.getName().substring(3).toLowerCase() + " = ?"; PreparedStatement statement = this.connection.prepareStatement(sql); //為Id字段添加值 int i = 0; if(idMethod.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(idMethod, entity)); } else { statement.setInt(++i, this.getInt(idMethod, entity)); } //執(zhí)行 conn.execOther(statement); //關(guān)閉連接 conn.closeConn(); } /** * 通過(guò)ID查詢 */ public T findById(Object object) throws Exception{ String sql = "select * from " + persistentClass.getSimpleName().toLowerCase() + " where "; //通過(guò)子類的構(gòu)造函數(shù),獲得參數(shù)化類型的具體類型.比如BaseDAO<T>也就是獲得T的具體類型 T entity = persistentClass.newInstance(); //存放Pojo(或被操作表)主鍵的方法對(duì)象 Method idMethod = null; List<Method> list = this.matchPojoMethods(entity, "set"); Iterator<Method> iter = list.iterator(); //過(guò)濾取得Method對(duì)象 while(iter.hasNext()) { Method tempMethod = iter.next(); if(tempMethod.getName().indexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) { idMethod = tempMethod; } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))){ idMethod = tempMethod; } } //第一個(gè)字母轉(zhuǎn)為小寫(xiě) sql += idMethod.getName().substring(3,4).toLowerCase()+idMethod.getName().substring(4) + " = ?"; //封裝語(yǔ)句完畢,打印sql語(yǔ)句 System.out.println(sql); //獲得連接 PreparedStatement statement = this.connection.prepareStatement(sql); //判斷id的類型 if(object instanceof Integer) { statement.setInt(1, (Integer)object); } else if(object instanceof String){ statement.setString(1, (String)object); } //執(zhí)行sql,取得查詢結(jié)果集. ResultSet rs = conn.execQuery(statement); //記數(shù)器,記錄循環(huán)到第幾個(gè)字段 int i = 0; //把指針指向迭代器第一行 iter = list.iterator(); //封裝 while(rs.next()) { while(iter.hasNext()) { Method method = iter.next(); if(method.getParameterTypes()[0].getSimpleName().indexOf("String") != -1) { //由于list集合中,method對(duì)象取出的方法順序與數(shù)據(jù)庫(kù)字段順序不一致(比如:list的第一個(gè)方法是setDate,而數(shù)據(jù)庫(kù)按順序取的是"123"值) //所以數(shù)據(jù)庫(kù)字段采用名字對(duì)應(yīng)的方式取. this.setString(method, entity, rs.getString(method.getName().substring(3).toLowerCase())); } else if(method.getParameterTypes()[0].getSimpleName().indexOf("Date") != -1){ this.setDate(method, entity, rs.getDate(method.getName().substring(3).toLowerCase())); } else if(method.getParameterTypes()[0].getSimpleName().indexOf("InputStream") != -1) { this.setBlob(method, entity, rs.getBlob(method.getName().substring(3).toLowerCase()).getBinaryStream()); } else { this.setInt(method, entity, rs.getInt(method.getName().substring(3).toLowerCase())); } } } //關(guān)閉結(jié)果集 rs.close(); //關(guān)閉預(yù)編譯對(duì)象 statement.close(); return entity; } /** * 過(guò)濾當(dāng)前Pojo類所有帶傳入字符串的Method對(duì)象,返回List集合. */ private List<Method> matchPojoMethods(T entity,String methodName) { //獲得當(dāng)前Pojo所有方法對(duì)象 Method[] methods = entity.getClass().getDeclaredMethods(); //List容器存放所有帶get字符串的Method對(duì)象 List<Method> list = new ArrayList<Method>(); //過(guò)濾當(dāng)前Pojo類所有帶get字符串的Method對(duì)象,存入List容器 for(int index = 0; index < methods.length; index++) { if(methods[index].getName().indexOf(methodName) != -1) { list.add(methods[index]); } } return list; } /** * 方法返回類型為int或Integer類型時(shí),返回的SQL語(yǔ)句值.對(duì)應(yīng)get */ public Integer getInt(Method method, T entity) throws Exception{ return (Integer)method.invoke(entity, new Object[]{}); } /** * 方法返回類型為String時(shí),返回的SQL語(yǔ)句拼裝值.比如'abc',對(duì)應(yīng)get */ public String getString(Method method, T entity) throws Exception{ return (String)method.invoke(entity, new Object[]{}); } /** * 方法返回類型為Blob時(shí),返回的SQL語(yǔ)句拼裝值.對(duì)應(yīng)get */ public InputStream getBlob(Method method, T entity) throws Exception{ return (InputStream)method.invoke(entity, new Object[]{}); } /** * 方法返回類型為Date時(shí),返回的SQL語(yǔ)句拼裝值,對(duì)應(yīng)get */ public Date getDate(Method method, T entity) throws Exception{ return (Date)method.invoke(entity, new Object[]{}); } /** * 參數(shù)類型為Integer或int時(shí),為entity字段設(shè)置參數(shù),對(duì)應(yīng)set */ public Integer setInt(Method method, T entity, Integer arg) throws Exception{ return (Integer)method.invoke(entity, new Object[]{arg}); } /** * 參數(shù)類型為String時(shí),為entity字段設(shè)置參數(shù),對(duì)應(yīng)set */ public String setString(Method method, T entity, String arg) throws Exception{ return (String)method.invoke(entity, new Object[]{arg}); } /** * 參數(shù)類型為InputStream時(shí),為entity字段設(shè)置參數(shù),對(duì)應(yīng)set */ public InputStream setBlob(Method method, T entity, InputStream arg) throws Exception{ return (InputStream)method.invoke(entity, new Object[]{arg}); } /** * 參數(shù)類型為Date時(shí),為entity字段設(shè)置參數(shù),對(duì)應(yīng)set */ public Date setDate(Method method, T entity, Date arg) throws Exception{ return (Date)method.invoke(entity, new Object[]{arg}); }} |
EmployeesDao繼承BaseDAO,可以直接使用父類的方法，增加了代碼的復(fù)用
|
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
|
package com.employees.dao;import java.util.ArrayList;import java.util.List;import com.employees.po.Employees;public class EmployeesDao extends BaseDAO<Employees> { // 添加員工信息的操作 public boolean addEmployees(final Employees employees) throws Exception { save(employees); return true; } // 將員工信息添加到表格中 public List<Employees> addEmployees(int id) throws Exception { List<Employees> lstEmployees = new ArrayList<Employees>(); Employees employees = findById(id); // 將當(dāng)前封轉(zhuǎn)好的數(shù)據(jù)裝入對(duì)象中 lstEmployees.add(employees); return lstEmployees; } public void deleteEmp(final Employees entity) throws Exception { this.delete(entity); } public void updateEmp(final Employees entity) throws Exception { this.update(entity); }} |
po層的代碼就不貼了，現(xiàn)在用junit4做一下測(cè)試
|
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
|
package com.employees.dao;import org.junit.Test;import com.employees.po.Employees;public class EmployeesDaoTest { @Test public void testAdd() throws Exception { Employees emp = new Employees(); emp.setPname("tly"); emp.setPsex("男"); emp.setPbeliefs("xxxxx"); emp.setPaddr("天河"); emp.setPhobby("打籃球"); emp.setPsubject("計(jì)算機(jī)"); emp.setPtel("123456"); EmployeesDao dao = new EmployeesDao(); dao.addEmployees(emp); } @Test public void testUpdate() throws Exception { EmployeesDao dao = new EmployeesDao(); Employees emp = dao.findById(14); emp.setPtel("999999"); dao.updateEmp(emp); } @Test public void testdelete() throws Exception { EmployeesDao dao = new EmployeesDao(); Employees emp = dao.findById(15); dao.deleteEmp(emp); }} |
經(jīng)過(guò)測(cè)試，這三個(gè)方法都能正常運(yùn)行，時(shí)間倉(cāng)促，有些代碼是參考其他哥們的，有些地方可能考慮的不是很全面或者有些代碼會(huì)有冗余，BaseDAO中做通用crud操作沒(méi)有寫(xiě)全，要是哪位小伙伴有興趣，可以接下去寫(xiě)寫(xiě)，比如查詢，批量化操作等等，如果測(cè)試通過(guò)的話，記得給我發(fā)一份啊，呵呵
以上這篇簡(jiǎn)單通用JDBC輔助類封裝(實(shí)例)就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持服務(wù)器之家。
