JDBC操作封装

这两天学习了一下jdbc的封装，依据的是下面这篇

http://wenku.baidu.com/link?url=FaFDmQouYkKO24ApATHYmA5QzUcj-UE-7RSSZaBWPqkKB8i13eYw2LGGEsgs_BRyBf7vB_zgB0vBxFXvhXhmLzBfBEPzPCvMvzMvesUwOzW

然后在它的基础上作了修改，简化参数，做了数据类型方面的休整。还不算完全成型，因为对“0”的处理还没有好的解决方案。下面贴出代码。

先贴一个Student实体类

  1 package com.some.entity;
  2 
  3 import java.io.Serializable;
  4 import java.sql.Date;
  5 
  6 public class Student implements Serializable {
  7     private int id;
  8     private String stuNumber;
  9     private String stuPassword;
 10     private String stuName;
 11     private String stuSex;
 12     private String stuIdentification;
 13     private String stuIsGat;
 14     private String stuPhone;
 15     private int departmentId;
 16     private int majorId;
 17     private String gradeNow;
 18     private int nationMark;
 19     private String bithday;// 格式为2015/10/13
 20     private String address;
 21     private String politics;
 22 
 23     public int getId() {
 24         return id;
 25     }
 26 
 27     public void setId(int id) {
 28         this.id = id;
 29     }
 30 
 31     public String getStuNumber() {
 32         return stuNumber;
 33     }
 34 
 35     public void setStuNumber(String stuNumber) {
 36         this.stuNumber = stuNumber;
 37     }
 38 
 39     public String getStuPassword() {
 40         return stuPassword;
 41     }
 42 
 43     public void setStuPassword(String stuPassword) {
 44         this.stuPassword = stuPassword;
 45     }
 46 
 47     public String getStuName() {
 48         return stuName;
 49     }
 50 
 51     public void setStuName(String stuName) {
 52         this.stuName = stuName;
 53     }
 54 
 55     public String getStuSex() {
 56         return stuSex;
 57     }
 58 
 59     public void setStuSex(String stuSex) {
 60         this.stuSex = stuSex;
 61     }
 62 
 63     public String getStuIdentification() {
 64         return stuIdentification;
 65     }
 66 
 67     public void setStuIdentification(String stuIdentification) {
 68         this.stuIdentification = stuIdentification;
 69     }
 70 
 71     public String getStuIsGat() {
 72         return stuIsGat;
 73     }
 74 
 75     public void setStuIsGat(String stuIsGat) {
 76         this.stuIsGat = stuIsGat;
 77     }
 78 
 79     public String getStuPhone() {
 80         return stuPhone;
 81     }
 82 
 83     public void setStuPhone(String stuPhone) {
 84         this.stuPhone = stuPhone;
 85     }
 86 
 87     public int getDepartmentId() {
 88         return departmentId;
 89     }
 90 
 91     public void setDepartmentId(int departmentId) {
 92         this.departmentId = departmentId;
 93     }
 94 
 95     public int getMajorId() {
 96         return majorId;
 97     }
 98 
 99     public void setMajorId(int majorId) {
100         this.majorId = majorId;
101     }
102 
103     public String getGradeNow() {
104         return gradeNow;
105     }
106 
107     public void setGradeNow(String gradeNow) {
108         this.gradeNow = gradeNow;
109     }
110 
111     public int getNationMark() {
112         return nationMark;
113     }
114 
115     public void setNationMark(int nationMark) {
116         this.nationMark = nationMark;
117     }
118 
119     public String getBithday() {
120         return bithday;
121     }
122 
123     public void setBithday(String bithday) {
124         this.bithday = bithday;
125     }
126 
127     public String getAddress() {
128         return address;
129     }
130 
131     public void setAddress(String address) {
132         this.address = address;
133     }
134 
135     public String getPolitics() {
136         return politics;
137     }
138 
139     public void setPolitics(String politics) {
140         this.politics = politics;
141     }
142 }

Student.java

然后是数据库连接工具类

 1 package com.sql.util;
 2 
 3 import java.sql.DriverManager;
 4 import java.sql.Connection;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 
 8 import com.mysql.jdbc.PreparedStatement;
 9 
10 public class DatabaseConnection {
11     private static final String driver = "com.mysql.jdbc.Driver";// 数据库驱动
12     private static final String url = "jdbc:mysql://localhost:3306/sports_test";// 链接数据库名称
13                                                                             // ,localhost替换成域名
14     private static final String user = "root";// mysql的登陆用户名
15     private static final String password = "1234";// mysql的登陆密码
16     private Connection conn = null;
17 
18     public DatabaseConnection() throws Exception {// 在构造方法中进行数据库连接
19         try {
20             Class.forName(driver);//实现静态方法
21             this.conn = DriverManager.getConnection(url, user, password);
22         } catch (Exception e) {
23             e.printStackTrace();
24         }
25     }
26 
27     public Connection getConnection() {
28         return this.conn;
29     }
30 
31     public static void closeConnection(Connection conn) {
32         if (conn != null) {
33             try {
34                 conn.close();
35             } catch (SQLException e) {
36                 e.printStackTrace();
37             }
38         }
39     }
40 
41     public static void closePreparedStatement(PreparedStatement pstmt) {
42         if (pstmt != null) {
43             try {
44                 pstmt.close();
45             } catch (SQLException e) {
46                 e.printStackTrace();
47             }
48         }
49     }
50 
51     public static void closeResultSet(ResultSet rs) {
52         if (rs != null) {
53             try {
54                 rs.close();
55             } catch (SQLException e) {
56                 e.printStackTrace();
57             }
58         }
59     }
60 }

DatabaseConnection.java

顺便贴一个简易hash函数

 1 package com.change.util;
 2 
 3 import java.security.MessageDigest;
 4 import java.security.NoSuchAlgorithmException;
 5 
 6 public final class GetHash {
 7     public static String getMD5(String str){
 8         String hashedStr=null;
 9         try{
10             MessageDigest md=MessageDigest.getInstance("MD5");
11             md.update(str.getBytes());
12             byte ss[]=md.digest();
13             hashedStr=bytes2String(ss);
14         }catch(NoSuchAlgorithmException e){
15             
16         }
17         return hashedStr;
18     }
19     
20     private static String bytes2String(byte[] aa){
21         String hash="";
22         for(int i=0;i<aa.length;i++){
23             int temp;
24             if(aa[i]<0){
25                 temp=256+aa[i];
26             }else{
27                 temp=aa[i];
28             }
29             if(temp<16){
30                 hash+="0";
31             }
32             hash+=Integer.toString(temp,16);
33         }
34         hash=hash.toUpperCase();
35         return hash;
36     }
37     
38 //    public static void main(String[] args) {
39 //        String a="123456";
40 //        String b="1234567";
41 //        String c="fast";
42 //        String hashA=GetHash.getMD5(a);
43 //        String hashB=GetHash.getMD5(b);
44 //        String hashC=GetHash.getMD5(c);
45 //        System.out.println(hashA);
46 //        System.out.println(hashB);
47 //        System.out.println(hashC);
48 //        //E10ADC3949BA59ABBE56E057F20F883E
49 //        //FCEA920F7412B5DA7BE0CF42B8C93759
50 //        //31D4541B8E926A24F0C9B835B68CFDF3
51 //    }
52 }

GetHash.java

顺便贴一个转换编码的类，有时做下载功能的时候需要用到

 1 package com.change.util;
 2 
 3 import java.io.UnsupportedEncodingException;
 4 
 5 public class ChangeISO {
 6     public static String parseGBK(String sIn) {
 7         if ((sIn == null) || (sIn.equals(""))) {
 8             return sIn;
 9         }
10         try {
11             return new String(sIn.getBytes("GBK"), "ISO-8859-1");
12         } catch (UnsupportedEncodingException usex) {
13         }
14         return sIn;
15     }
16 
17     public static String parseUTF8(String sIn) {
18         if ((sIn == null) || (sIn.equals(""))) {
19             return sIn;
20         }
21         try {
22             return new String(sIn.getBytes("UTF-8"), "ISO-8859-1");
23         } catch (UnsupportedEncodingException usex) {
24         }
25         return sIn;
26     }
27 }

Change

最后就是jdbc增删查改的封装了，有了这个感觉方便很多，不需要像以前一个操作写一大坨函数，我真是太笨了= =

  1 package com.sql.util;
  2 
  3 import java.security.Timestamp;
  4 import java.sql.Connection;
  5 import java.sql.PreparedStatement;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 import java.util.ArrayList;
  9 import java.util.Date;
 10 import java.util.List;
 11 import java.lang.reflect.Field;
 12 import java.lang.reflect.Method;
 13 import java.math.BigDecimal;
 14 
 15 import com.change.util.GetHash;
 16 import com.some.entity.Student;
 17 
 18 /**
 19  * @see 这个类用于封装sql操作
 20  */
 21 public class DatabaseOperator {
 22 
 23     /**
 24      * @param conn
 25      *            Connection对象
 26      * @param obj
 27      *            vo对象(实体)
 28      * @param sql
 29      *            StringBuilder 对象
 30      * @return List集合
 31      * @throws Exception
 32      */
 33 
 34     public static List<?> getSelectList(Connection conn, Object obj,
 35             StringBuilder sql) {
 36         int isFirstParameter = 0;
 37         // 用于装入vo对象中设置属性值的真实属性值
 38         List<Object> fieldValue = new ArrayList<>();
 39         // 返回一个list集合，装的是执行过查询的集合，集合resultList装的一般是vo对象
 40         List<Object> resultList = new ArrayList<>();
 41 
 42         try {
 43             // 获取对象的所属的类
 44             Class<?> c = obj.getClass();
 45             // 取得本类的全部属性
 46             Field[] f = c.getDeclaredFields();
 47 
 48             for (int i = 0; i < f.length; i++) {
 49                 String name = f[i].getName();
 50                 String get = DatabaseOperator.getGos("get", name);
 51 
 52                 // Java 反射机制中 getMethod()和getDeclaredMethod()区别
 53                 // getMethods()和getDeclaredMethods()区别
 54                 // 前者只返回共有方法 后者返回全部3种（不包括继承）
 55                 Method m = c.getDeclaredMethod(get);
 56                 Object value = m.invoke(obj);
 57                 if (value != null && value != ""
 58                         && !String.valueOf(value).equals("0")) {
 59                     System.out.println(value);
 60                     // 如果是第一个条件参数，不需要加上and
 61                     if (isFirstParameter == 0) {
 62                         sql.append(" " + name + " = ?");
 63                         isFirstParameter++;
 64                         // 填入要附加的条件参数
 65                         fieldValue.add(value);
 66                     } else {
 67                         sql.append(" and " + name + " = ?");
 68                         // 填入要附加的条件参数
 69                         fieldValue.add(value);
 70                     }
 71 
 72                 }
 73             }
 74             System.out.println(sql);
 75             PreparedStatement ps = conn.prepareStatement(sql.toString());
 76             for (int i = 0; i < fieldValue.size(); i++) {
 77                 // System.out.println(fieldValue.size());
 78                 // System.out.println(fieldValue.get(i));
 79                 ps.setObject(i + 1, fieldValue.get(i));
 80             }
 81             // 定义结果集
 82             ResultSet rs = ps.executeQuery();
 83             while (rs.next()) {
 84                 Object o = c.newInstance();
 85                 // f是本类全部属性
 86                 for (int i = 0; i < f.length; i++) {
 87                     String name = f[i].getName();
 88                     // System.out.println(name);
 89                     String set = DatabaseOperator.getGos("set", name);
 90                     // System.out.println(set);
 91                     // 获取类的set方法
 92                     // System.out.println(f[i].getType());
 93                     Method m = c.getDeclaredMethod(set, f[i].getType());
 94                     m.setAccessible(true);
 95                     // System.out.println(m.getName());
 96                     Object value = rs.getObject(i + 1);
 97                     // System.out.println(value + "======");
 98                     if (value != null) {
 99                         // String r = f[i].getType().toString();
100                         // System.out.println(r);
101                         String rsType = value.getClass().getSimpleName();
102                         // System.out.println(rsType + "++++");
103 
104                         // 判断从数据库读出的数据的数据类型，这里比较奇怪
105                         if (rsType.equals("Long")) {
106                             m.invoke(o, Integer.parseInt(value.toString()));
107                         } else if (rsType.equals("Integer")) {
108                             m.invoke(o, value);
109                         } else if (rsType.equals("Double")) {
110                             m.invoke(o, ((BigDecimal) value).doubleValue());
111                         } else if (rsType.equals("Float")) {
112                             m.invoke(o, ((BigDecimal) value).floatValue());
113                         } else if (rsType.equals("Date")
114                                 || rsType.equals("Timestamp")) {
115                             // System.out.println("da");
116                             if (rsType.equals("Date")) {
117                                 m.invoke(o, ((Date) value).toString());
118                             } else {
119                                 m.invoke(o, ((Timestamp) value).toString());
120                             }
121                         } else if (rsType.equals("String")) {
122                             m.invoke(o, value);
123                         } else {
124                             // System.out.println("未知");
125                         }
126                     }
127                 }
128                 resultList.add(o);
129             }
130             return resultList;
131 
132         } catch (Exception e) {
133             e.printStackTrace();
134         }
135 
136         return resultList;
137     }
138 
139     /**
140      * 
141      * @param conn
142      *            Connection对象
143      * @param sql1
144      *            StringBuilder 可以增加长度，用于拼接指定的需要插入的列名
145      * @param obj
146      *            vo对象
147      * @return int 0，1执行结果
148      * @throws Exception
149      */
150     public static int doInsert(Connection conn, StringBuilder sql1, Object obj)
151             throws Exception {
152         Class<?> c = obj.getClass();
153         Field[] f = c.getDeclaredFields();
154         // StringBuilder 可以增加长度，用于拼接values后面的属性值
155         StringBuilder sql2 = new StringBuilder();
156         // 用于装入vo对象中设置属性值的真实属性值
157         List<Object> fieldValue = new ArrayList<>();
158 
159         sql1.append("(");
160         sql2.append("values(");
161         for (int i = 0; i < f.length; i++) {
162             String name = f[i].getName();
163             String get = getGos("get", name);
164             Method m = c.getDeclaredMethod(get);
165             Object value = m.invoke(obj);
166 
167             if (value != null && value != ""
168                     && !String.valueOf(value).equals("0")) {
169                 sql1.append(name + ",");
170                 sql2.append("?,");
171                 fieldValue.add(value);
172             }
173         }
174 
175         int j = sql1.length() - 1;
176         if (sql1.lastIndexOf(",") == j) {
177             int start = sql1.length() - 1;
178             int end = sql1.length();
179             sql1.replace(start, end, "");
180         }
181         int k = sql2.length() - 1;
182         if (sql2.lastIndexOf(",") == k) {
183             int start = sql2.length() - 1;
184             int end = sql2.length();
185             sql2.replace(start, end, "");
186         }
187         sql1.append(")");
188         sql2.append(")");
189         StringBuilder sql = sql1.append(sql2);
190         System.out.println(sql);
191 
192         PreparedStatement ps = conn.prepareStatement(sql.toString());
193         // System.out.println(fieldValue.size());
194         for (int i = 0; i < fieldValue.size(); i++) {
195             // System.out.println(fieldValue.get(i));
196             ps.setObject(i + 1, fieldValue.get(i));
197         }
198         // 执行
199         int result = ps.executeUpdate();
200         return result;
201     }
202 
203     /**
204      * @param conn
205      *            Connection对象
206      * @param sql
207      *            删除的sql语句(index:delete from student)
208      * @param id
209      *            删除的主键
210      * @return int 0，1执行结果
211      */
212     public static int doDelete(Connection conn, String sql, String primaryKey,
213             int id) {
214         sql = sql + " where " + primaryKey + " = ?";
215         int result = 0;
216         try {
217             System.out.println(sql);
218             PreparedStatement ps = conn.prepareStatement(sql);
219             ps.setInt(1, id);
220             result = ps.executeUpdate();
221         } catch (SQLException e) {
222             e.printStackTrace();
223         }
224         return result;
225     }
226 
227     /**
228      * @param conn
229      *            Connection对象
230      * @param sql
231      *            要执行的sql语句
232      * @param obj
233      *            一般是vo对象
234      * @param primaryKey
235      *            表中的主键， 更新是基于主键更新的
236      * @return int 0，1执行结果
237      * @throws
238      */
239     public static int doUpdate(Connection conn, StringBuilder sql, Object obj,
240             String primaryKey, Integer id) throws Exception {
241         Class<?> c = obj.getClass();
242         Field[] f = c.getDeclaredFields();
243         // list类型，用于在程序计数传入的属性值
244         List<Object> fieldValue = new ArrayList<>();
245 
246         sql.append(" set");
247 
248         for (int i = 0; i < f.length; i++) {
249             String name = f[i].getName();
250             String get = getGos("get", name);
251             Method m = c.getDeclaredMethod(get);
252             Object value = m.invoke(obj);
253 
254             int j = name.toLowerCase().indexOf(primaryKey.toLowerCase());
255             if (value != null && value != "" && j < 0) {
256                 // System.out.println(f[i].getName());
257                 sql.append(" " + name + " = ?,");
258                 fieldValue.add(value);
259             }
260         }
261 
262         // 去掉最后一个逗号
263         int k = sql.length() - 1;
264         if (sql.lastIndexOf(",") == k) {
265             int start = sql.length() - 1;
266             int end = sql.length();
267             sql.replace(start, end, "");
268         }
269 
270         if (id != null) {
271             sql.append(" where " + primaryKey + " = ?");
272         }
273 
274         System.out.println(sql);
275         PreparedStatement ps = conn.prepareStatement(sql.toString());
276         for (int i = 0; i < fieldValue.size(); i++) {
277             // System.out.println(fieldValue.get(i));
278             ps.setObject(i + 1, fieldValue.get(i));
279         }
280         if (id != null) {
281             ps.setObject(fieldValue.size() + 1, id);
282             System.out.println(id);
283         }
284         int result = ps.executeUpdate();
285         System.out.println(result);
286         return result;
287     }
288 
289     /**
290      *  @see getGos是获取get或者set，用于得到一个属性的get和set方法，例如getName()  
291      *  @param s:set 或者 get    @param name:属性
292      * 
293      * @return String类型   
294      */
295     public static String getGos(String s, String name) {
296         String str = s + name.substring(0, 1).toUpperCase() + name.substring(1);
297         return str;
298     }
299 
300     public static void main(String[] args) {
301         DatabaseConnection dc = null;
302         try {
303             dc = new DatabaseConnection();
304             Connection conn = dc.getConnection();
305 
306             // // 查询
307             // Student student1 = new Student();
308             // // student1.setStuNumber("14020031096");
309             // StringBuilder sql1 = new StringBuilder();
310             // // sql1.append("select * from student where");
311             // sql1.append("select * from student");
312             // @SuppressWarnings("unchecked")
313             // List<Student> stuList = (List<Student>) DatabaseOperator
314             // .getSelectList(conn, student1, sql1);
315             // System.out.println(stuList.get(0).getStuNumber());
316             // System.out.println(stuList.get(0).getId());
317             // for (int i = 0; i < stuList.size(); i++) {
318             // System.out.println(stuList.get(i).getStuName());
319             // }
320 
321             // 增加
322             // Student student2 = new Student();
323             // student2.setStuName("萌重宝宝");
324             // student2.setStuNumber("22222222222");
325             // student2.setStuPassword(GetHash.getMD5("123456"));
326             // StringBuilder sql2 = new StringBuilder();
327             // sql2.append("insert into student");
328             // int result=doInsert(conn, sql2, student2);
329             // if(result==1){
330             // System.out.println("insert成功");
331             // }else{
332             // System.out.println("insert失败");
333             // }
334 
335             // 删除
336             // String sql3 = "delete from student";
337             // String primaryKey = "id";
338             // int id = 15458;
339             // int result=doDelete(conn, sql3, primaryKey, id);
340             // if(result==1){
341             // System.out.println("delete成功");
342             // }else{
343             // System.out.println("delete失败");
344             // }
345 
346             // 更新
347             // Student student4 = new Student();
348             // student4.setStuName("萌重儿子");
349             // StringBuilder sql4 = new StringBuilder();
350             // sql4.append("update student");
351             // String primaryKey = "id";
352             // int id = 15456;
353             // DatabaseOperator.doUpdate(conn, sql4, student4, primaryKey, id);
354         } catch (Exception e) {
355             e.printStackTrace();
356         }
357 
358     }
359 }

DatabaseOperator

主要是4个函数，还有一个拼接字符串函数，主函数是使用范例。

啦啦啦跑步去