jdbc连接数据库工具类
1 import java.lang.reflect.Field; 2 import java.sql.Connection; 3 import java.sql.DriverManager; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.ResultSetMetaData; 7 import java.sql.SQLException; 8 import java.util.ArrayList; 9 import java.util.HashMap; 10 import java.util.List; 11 import java.util.Map; 12 13 public class DBUtils { 14 15 // 表示定义数据库的用户名 16 private static String USERNAME = "root"; 17 // 定义数据库的密码 18 private static String PASSWORD = "root"; 19 // 定义数据库的驱动信息 20 private static String DRIVER = "com.mysql.jdbc.Driver"; 21 // 定义访问数据库的地址 22 private static String URL = "jdbc:mysql://localhost:3306/db"; 23 24 private static DBUtils per = null; 25 // 定义数据库的链接 26 private Connection con = null; 27 // 定义sql语句的执行对象 28 private PreparedStatement pstmt = null; 29 // 定义查询返回的结果集合 30 private ResultSet resultSet = null; 31 32 private DBUtils() { 33 34 } 35 36 /** 37 * 单例模式，获得工具类的一个对象 38 * 39 * @return 40 */ 41 public static DBUtils getInstance() { 42 if (per == null) { 43 per = new DBUtils(); 44 per.registeredDriver(); 45 } 46 return per; 47 } 48 49 private void registeredDriver() { 50 // TODO Auto-generated method stub 51 try { 52 Class.forName(DRIVER); 53 System.out.println("注册驱动成功！"); 54 } catch (ClassNotFoundException e) { 55 // TODO Auto-generated catch block 56 e.printStackTrace(); 57 } 58 } 59 60 /** 61 * 获得数据库的连接 62 * 63 * @return 64 */ 65 public Connection getConnection() { 66 try { 67 con = DriverManager.getConnection(URL, USERNAME, PASSWORD); 68 } catch (SQLException e) { 69 // TODO Auto-generated catch block 70 e.printStackTrace(); 71 } 72 System.out.println("连接数据库成功!!"); 73 return con; 74 } 75 76 /** 77 * 完成对数据库的表的添加删除和修改的操作 78 * 79 * @param sql 80 * @param params 81 * @return 82 * @throws SQLException 83 */ 84 public boolean executeUpdate(String sql, List<Object> params) 85 throws SQLException { 86 87 boolean flag = false; 88 89 int result = -1; // 表示当用户执行添加删除和修改的时候所影响数据库的行数 90 91 pstmt = con.prepareStatement(sql); 92 93 if (params != null && !params.isEmpty()) { 94 int index = 1; 95 for (int i = 0; i < params.size(); i++) { 96 pstmt.setObject(index++, i); 97 } 98 } 99 100 result = pstmt.executeUpdate(); 101 flag = result > 0 ? true : false; 102 103 return flag; 104 } 105 106 /** 107 * 从数据库中查询数据 108 * 109 * @param sql 110 * @param params 111 * @return 112 * @throws SQLException 113 */ 114 public List<Map<String, Object>> executeQuery(String sql, 115 List<Object> params) throws SQLException { 116 List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); 117 int index = 1; 118 pstmt = con.prepareStatement(sql); 119 if (params != null && !params.isEmpty()) { 120 for (int i = 0; i < params.size(); i++) { 121 pstmt.setObject(index++, params.get(i)); 122 } 123 } 124 resultSet = pstmt.executeQuery(); 125 ResultSetMetaData metaData = resultSet.getMetaData(); 126 int cols_len = metaData.getColumnCount(); 127 while (resultSet.next()) { 128 Map<String, Object> map = new HashMap<String, Object>(); 129 for (int i = 0; i < cols_len; i++) { 130 String cols_name = metaData.getColumnName(i + 1); 131 Object cols_value = resultSet.getObject(cols_name); 132 if (cols_value == null) { 133 cols_value = ""; 134 } 135 map.put(cols_name, cols_value); 136 } 137 list.add(map); 138 } 139 return list; 140 141 } 142 143 /** 144 * jdbc的封装可以用反射机制来封装,把从数据库中获取的数据封装到一个类的对象里 145 * 146 * @param sql 147 * @param params 148 * @param cls 149 * @return 150 * @throws Exception 151 */ 152 public <T> List<T> executeQueryByRef(String sql, List<Object> params, 153 Class<T> cls) throws Exception { 154 List<T> list = new ArrayList<T>(); 155 int index = 1; 156 pstmt = con.prepareStatement(sql); 157 if (params != null && !params.isEmpty()) { 158 for (int i = 0; i < params.size(); i++) { 159 pstmt.setObject(index++, params.get(i)); 160 } 161 } 162 resultSet = pstmt.executeQuery(); 163 ResultSetMetaData metaData = resultSet.getMetaData(); 164 int cols_len = metaData.getColumnCount(); 165 while (resultSet.next()) { 166 T resultObject = cls.newInstance(); // 通过反射机制创建实例 167 for (int i = 0; i < cols_len; i++) { 168 String cols_name = metaData.getColumnName(i + 1); 169 Object cols_value = resultSet.getObject(cols_name); 170 if (cols_value == null) { 171 cols_value = ""; 172 } 173 Field field = cls.getDeclaredField(cols_name); 174 field.setAccessible(true); // 打开javabean的访问private权限 175 field.set(resultObject, cols_value); 176 } 177 list.add(resultObject); 178 } 179 return list; 180 181 } 182 183 public void closeDB() { 184 if (resultSet != null) { 185 try { 186 resultSet.close(); 187 } catch (SQLException e) { 188 // TODO Auto-generated catch block 189 e.printStackTrace(); 190 } 191 } 192 if (pstmt != null) { 193 try { 194 pstmt.close(); 195 } catch (SQLException e) { 196 // TODO Auto-generated catch block 197 e.printStackTrace(); 198 } 199 } 200 if (con != null) { 201 try { 202 con.close(); 203 } catch (SQLException e) { 204 // TODO Auto-generated catch block 205 e.printStackTrace(); 206 } 207 } 208 } 209 210 211 212 }
测试：
1 import java.sql.SQLException; 2 import java.util.ArrayList; 3 import java.util.HashSet; 4 import java.util.List; 5 import java.util.Map; 6 import java.util.Set; 7 8 9 public class Test { 10 11 /** 12 * @param args 13 */ 14 public static void main(String[] args) { 15 // TODO Auto-generated method stub 16 17 DBUtils db = DBUtils.getInstance(); 18 db.getConnection(); 19 String sql = "SELECT name,introduction,location FROM activity WHERE id IN(SELECT activityId from mem_act_collect where memberId = ?)"; 20 List<Activity> reslist = new ArrayList<Activity>(); 21 List<Object> list = new ArrayList<Object>(); 22 list.add(2014303342); 23 24 try { 25 reslist = db.executeQueryByRef(sql,list,Activity.class); 26 27 for(Activity ac:reslist){ 28 System.out.println(ac.toString()); 29 } 30 31 32 } catch (Exception e) { 33 // TODO Auto-generated catch block 34 e.printStackTrace(); 35 }finally{ 36 db.closeDB(); 37 } 38 39 } 40 41 }
