1 package dao;  2   3 import java.lang.reflect.Field;  4 import java.lang.reflect.Method;  5 import java.sql.Connection;  6 import java.sql.DriverManager;  7 import java.sql.PreparedStatement;  8 import java.sql.ResultSet;  9 import java.sql.SQLException; 10 import java.util.ArrayList; 11 import java.util.List; 12  13 public class BaseDAO { 14  15     // 驱动类全名（包名.类名） 16     private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; 17     // 连接的URL 18     private static final String URL = "jdbc:sqlserver://localhost\\sqlexpress:1433;DatabaseName=bookdb"; 19     // 登录SQLserver用户名和密码 20     private static final String USERNAME = "sa"; 21     private static final String PWD = "ghp418512"; 22  23     // 数据库对象声明 24     private Connection con = null; 25     private PreparedStatement pst = null; 26     private ResultSet rs = null; 27  28     /** 29      * 加载驱动,建立连接 30      *  31      * @throws ClassNotFoundException 32      * @throws SQLException 33      */ 34     private void getConnection() throws ClassNotFoundException, SQLException { 35         // 加载驱动 36         Class.forName(DRIVER); 37         // 建立连接 38         con = DriverManager.getConnection(URL, USERNAME, PWD); 39     } 40  41     /** 42      * 执行查询 43      *  44      * @param sql 45      *            执行的参数化SQL语句 select * from where name=? and pwd=? 46      * @param params 47      *            Object数组，封装所有SQL语句参数，顺序与SQL语句的参数顺序一致,new Object[]{name,pwd} 48      * @return ResultSet 返回执行后的结果集 49      */ 50     public ResultSet execQuery(String sql, Object[] params) { 51         try { 52             getConnection(); 53  54             pst = con.prepareStatement(sql); 55  56             setPrepareStatementParams(params); 57  58             rs = pst.executeQuery(); 59  60         } catch (SQLException e) { 61             e.printStackTrace(); 62         } catch (ClassNotFoundException e) { 63             e.printStackTrace(); 64         } 65         return rs; 66     } 67  68     /** 69      * 执行SQL查询语句，并返回泛型集合List<T> 调用示例：<br/> 70      * String sql="select * from book"; <br /> 71      * List<Book> bookList=super.execQueryAndPackagingList(sql, null, new 72      * Book()); 73      *  74      * @param sql 75      *            要执行的SQL查询语句，必须有结果返回，并与实体类型T匹配 76      * @param params 77      *            SQL语句的参数 78      * @param t 79      *            封装的实体类型对象 80      * @return 泛型集合List<T> 81      */ 82     public <T> List<T> execQueryAndPackagingList(String sql, Object[] params, 83             T t) { 84         rs = execQuery(sql, params); 85         List<T> tlist = null; 86         try { 87  88             tlist = setAllSetters(t, rs); 89  90         } catch (SQLException e) { 91             e.printStackTrace(); 92         } finally { 93             closeAll(); 94         } 95         return tlist; 96     } 97  98     /** 99      * 执行增加、删除、修改SQL操作100      * 101      * @param sql102      *            执行的参数化SQL语句103      * @param params104      *            Object数组，封装所有SQL语句参数105      * @return int 受影响的行数，-1表示出现异常106      */107     public int execUpdate(String sql, Object[] params) {108 109         try {110             getConnection();111             pst = con.prepareStatement(sql);112 113             setPrepareStatementParams(params);114 115             int affectedRows = pst.executeUpdate();116             return affectedRows;117         } catch (ClassNotFoundException e) {118             e.printStackTrace();119         } catch (SQLException e) {120             e.printStackTrace();121         } finally {122             closeAll();123         }124         return -1;125 126     }127 128     /**129      * 为PrepareStatement设置参数130      * 131      * @param params132      *            参数数组133      * @throws SQLException134      */135     private void setPrepareStatementParams(Object[] params) throws SQLException {136         if (params != null) {137             for (int i = 0; i < params.length; i++) {138                 pst.setObject(i + 1, params[i]);139             }140         }141     }142 143     /**144      * 关闭Connection,PrepareStatement,Result145      */146     public void closeAll() {147         try {148 149             if (rs != null) {150                 rs.close();151             }152             if (pst != null) {153                 pst.close();154             }155             if (con != null) {156                 con.close();157             }158 159         } catch (SQLException e) {160             e.printStackTrace();161         }162     }163 164     /**165      * 将查询结果(ResultSet)自动封装为泛型集合（List<T>）166      * 167      * @param ob168      *            封装的实体，其属性名必须与数据字段名一致，并提供有set***,get***方法169      * @param rs170      *            要封装的ResultSet171      * @return 泛型List集合（List<T>172      * @throws SQLException173      *             此方法可能抛出SQLException异常174      */175     private <T> List<T> setAllSetters(T ob, ResultSet rs) throws SQLException {176 177         Class cls = ob.getClass();178         List<T> tlist = new ArrayList<T>();179         T t = null;180 181         while (rs.next()) {182 183             try {184                 t = (T) ob.getClass().newInstance();185             } catch (InstantiationException e1) {186 187                 e1.printStackTrace();188             } catch (IllegalAccessException e1) {189 190                 e1.printStackTrace();191             }192 193             for (Field field : cls.getDeclaredFields()) {194                 for (Method method : cls.getMethods()) {195                     if ((method.getName().startsWith("set"))196                             && (method.getName().length() == (field.getName()197                                     .length() + 3))) {198                         if (method.getName().toLowerCase()199                                 .endsWith(field.getName().toLowerCase())) {200                             try {201                                 method.setAccessible(true);202                                 if (field.getType().getSimpleName()203                                         .toLowerCase().endsWith("integer"))204                                     method.invoke(t, rs.getInt(field.getName()205                                             .toLowerCase()));206                                 else if (field.getType().getSimpleName()207                                         .toLowerCase().endsWith("long"))208                                     method.invoke(t, rs.getLong(field.getName()209                                             .toLowerCase()));210                                 else if (field.getType().getSimpleName()211                                         .toLowerCase().endsWith("string"))212                                     method.invoke(t, rs.getString(field213                                             .getName().toLowerCase()));214                                 else if (field.getType().getSimpleName()215                                         .toLowerCase().endsWith("boolean"))216                                     method.invoke(t, rs.getBoolean(field217                                             .getName().toLowerCase()));218                                 else if (field.getType().getSimpleName()219                                         .toLowerCase().endsWith("timestamp"))220                                     method.invoke(t, rs.getTimestamp(field221                                             .getName().toLowerCase()));222                                 else if (field.getType().getSimpleName()223                                         .toLowerCase().endsWith("date"))224                                     method.invoke(t, rs.getDate(field.getName()225                                             .toLowerCase()));226                                 else if (field.getType().getSimpleName()227                                         .toLowerCase().endsWith("double"))228                                     method.invoke(t, rs.getDouble(field229                                             .getName().toLowerCase()));230                                 else if (field.getType().getSimpleName()231                                         .toLowerCase().endsWith("float"))232                                     method.invoke(t, rs.getFloat(field233                                             .getName().toLowerCase()));234                                 else if (field.getType().getSimpleName()235                                         .toLowerCase().endsWith("time"))236                                     method.invoke(t, rs.getTime(field.getName()237                                             .toLowerCase()));238                                 else239                                     method.invoke(t, rs.getObject(field240                                             .getName().toLowerCase()));241                             } catch (Exception e) {242                                 e.printStackTrace();243                                 ;244                             }245                         }246                     }247                 }248             }249             tlist.add(t);250         }251         return tlist;252     }253 }