java dbhelper

?

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

import javax.naming.spi.DirStateFactory; import java.lang.reflect.Field; import java.sql.*; import java.util.ArrayList; import java.util.List;   public   class DBHelper {   //  private static final String DRIVER = "com.mysql.jdbc.Driver";    // private static final String URL = "jdbc:mysql://101.12.102.10:3306/test";     //private static final String USER = "root";     //private static final String PASSWORD = "123456";       private static final String DRIVER = "com.mysql.jdbc.Driver";     private static final String URL = "jdbc:mysql://rm-2ze66k2qxc95j13nl.mysql.rds.aliyuncs.com:3306/longfor_mdm";     private static final String USER = "wydb003_rw";     private static final String PASSWORD = "vzXYZ5h3MiDntZDtuCFE";       private static DBHelper ins;       public static DBHelper ins() {         if (ins == null) {             ins = new DBHelper();         }         return ins;     }         /**      * 连接数据库      *      * @return 链接数据库对象      */     public Connection getConnection() {         Connection conn = null;         try {             Class.forName(DRIVER);         } catch (ClassNotFoundException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }         try {             conn = DriverManager.getConnection(URL, USER, PASSWORD);         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }         return conn;     }       /**      * 释放相应的资源      *      * @param rs      * @param pstmt      * @param conn      */     public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection conn) {         try {             if (rs != null) {                 rs.close();             }             if (pstmt != null) {                 pstmt.close();             }             if (conn != null) {                 conn.close();             }         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }     }       /**      * 此方法可以完成增删改所有的操作      *      * @param sql      * @param params      * @return true or false      */     public boolean excuteUpdate(String sql, List<Object> params) {         int res = 0;//受影响的行数         Connection conn = null;         PreparedStatement pstmt = null;         ResultSet rs = null;         try {             conn = getConnection();             pstmt = conn.prepareStatement(sql);//装载sql语句             if (params != null) {                 //加入有？占位符，在执行之前把？占位符替换掉                 for (int i = 0; i < params.size(); i++) {                     pstmt.setObject(i + 1, params.get(i));                 }             }             res = pstmt.executeUpdate();           } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } finally {             closeAll(rs, pstmt, conn);         }         return res > 0 ? true : false;     }       /**      * 使用泛型方法和反射机制进行封装      *      * @param sql      * @param params      * @param cls      * @return      */     public <T> List<T> executeQuery(String sql, List<Object> params, Class<T> cls) throws Exception {         Connection conn = null;         PreparedStatement pstmt = null;         ResultSet rs = null;         System.out.println("123");           List<T> data = new ArrayList<T>();         try {             conn = getConnection();             pstmt = conn.prepareStatement(sql);//装载sql语句             if (params != null) {                 //加入有？占位符，在执行之前把？占位符替换掉                 for (int i = 0; i < params.size(); i++) {                     pstmt.setObject(i + 1, params.get(i));                 }             }             rs = pstmt.executeQuery();             System.out.println("456");               //把查询出来的记录封装成对应的实体类对象             ResultSetMetaData rsd = rs.getMetaData();//获得列对象,通过此对象可以得到表的结构，包括，列名，列的个数，列的数据类型             while (rs.next()) {                 T m = cls.newInstance();                 for (int i = 0; i < rsd.getColumnCount(); i++) {                     String col_name = rsd.getColumnName(i + 1);//获得列名                     Object value = rs.getObject(col_name);//获得列所对应的值                     col_name=col_name.toLowerCase();                         Field field = cls.getDeclaredField(col_name);                     field.setAccessible(true);//给私有属性设置可访问权                     field.set(m, value);//给对象的私有属性赋值                 }                 data.add(m);             }           } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } finally {             closeAll(rs, pstmt, conn);         }         return data;     }         public ResultSet executeQuerySql(String sql) {         DirStateFactory.Result result = null;         ResultSet rst = null;         PreparedStatement pst = null;         Connection con=this.getConnection();         try {               pst = this.getConnection().prepareStatement(sql);               rst = pst.executeQuery();             } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } finally {         //    closeAll(rst, pst, con);         }         return rst;     } }