20170416总结
JDBC通用DAO：
1 public <T> T get(Class<T> clazz,String sql,Object ... args) throws SQLException { 2 T entity = null; 3 4 Connection connection = null; 5 PreparedStatement preparedStatement = null; 6 ResultSet resultSet = null; 7 8 jdbcUtils = new JDBCUtils(); 9 try { 10 //1.得到ResultSet对象 11 connection = jdbcUtils.getConnection(); 12 preparedStatement = connection.prepareStatement(sql); 13 for (int i=0;i<args.length;i++){ 14 preparedStatement.setObject(i+1,args[i]); 15 } 16 resultSet = preparedStatement.executeQuery(); 17 //2.得到ResultSetMetaData对象 18 ResultSetMetaData rsmd = resultSet.getMetaData(); 19 //3.创建一个Map<String,Object> 对象，键：SQL查询的列的别名 值：列的值 20 Map<String,Object> map = new HashMap<>(); 21 //4.处理结果集，利用ResultSetMetaData填充 3 对应的Map对象 22 while (resultSet.next()){ 23 for (int i=0;i<rsmd.getColumnCount();i++){ 24 String columnLable = rsmd.getColumnLabel(i + 1); 25 Object columnValues = resultSet.getObject(columnLable); 26 27 map.put(columnLable,columnValues); 28 } 29 } 30 31 //5.若Map不为空集，利用反射创建clazz对应的对象 32 if (map.size() > 0){ 33 entity = clazz.newInstance(); 34 //5.遍历Map对象，利用反射未Class对象的对应的属性赋值 35 for (Map.Entry<String,Object> entry : map.entrySet()){ 36 String filedName = entry.getKey(); 37 Object value = entry.getValue(); 38 ReflectionUtils.setFieldValue(entity,filedName,value); 39 } 40 } 41 } catch (Exception e) { 42 e.printStackTrace(); 43 }finally { 44 jdbcUtils.releaseDB(resultSet,preparedStatement,connection); 45 } 46 // System.out.println("传入的sql值为：" + sql); 47 48 return entity; 49 }
JDBC工具类：
1 package com.carzyit.jdbc; 2 3 import java.io.FileInputStream; 4 import java.io.IOException; 5 import java.io.InputStream; 6 import java.sql.*; 7 import java.util.Properties; 8 9 import static com.sun.org.apache.xalan.internal.utils.SecuritySupport.getContextClassLoader; 10 11 /** 12 * Created by lenovo on 2017/4/11. 13 */ 14 public class JDBCUtils { 15 private String driver; 16 private String url; 17 private String user; 18 private String pass; 19 public Connection getConnection() throws Exception { 20 21 InputStream in = getClass().getResourceAsStream("mysql.properties"); 22 Properties properties = new Properties(); 23 properties.load(in); 24 driver = properties.getProperty("driver"); 25 url = properties.getProperty("url"); 26 user = properties.getProperty("user"); 27 pass = properties.getProperty("pass"); 28 29 //加载驱动 30 Class.forName(driver); 31 Connection connection = DriverManager.getConnection(url,user,pass); 32 33 return connection; 34 } 35 36 /** 37 * 执行SQL语句，使用PreparedStatement，使用可变参数进行输入 38 * @param sql 39 * @param args 40 * @throws SQLException 41 */ 42 public void update(String sql,Object... args) throws SQLException { 43 Connection connection = null; 44 PreparedStatement preparedStatement = null; 45 try { 46 connection = getConnection(); 47 preparedStatement = connection.prepareStatement(sql); 48 for (int i=0;i<args.length;i++){ 49 preparedStatement.setObject(i + 1,args[i]); 50 } 51 preparedStatement.executeUpdate(); 52 } catch (Exception e) { 53 e.printStackTrace(); 54 }finally { 55 releaseDB(null,preparedStatement,connection); 56 } 57 } 58 59 public ResultSet query(String sql) throws Exception { 60 Connection connection = null; 61 Statement statement = null; 62 ResultSet resultSet = null; 63 64 connection = getConnection(); 65 statement = connection.createStatement(); 66 resultSet = statement.executeQuery(sql); 67 68 return resultSet; 69 } 70 71 public int update(String sql) throws Exception { 72 73 Connection connection = null; 74 Statement statement = null; 75 76 connection = getConnection(); 77 statement = connection.createStatement(); 78 79 return statement.executeUpdate(sql); 80 } 81 82 public static void releaseDB(ResultSet resultSet, Statement statement, Connection connection) throws SQLException { 83 if (resultSet != null){ 84 resultSet.close(); 85 } 86 87 if (statement != null){ 88 statement.close(); 89 } 90 91 if (connection != null){ 92 connection.close(); 93 } 94 } 95 }
mysql.properties文件
1 driver=com.mysql.jdbc.Driver 2 url=jdbc:mysql://localhost:3306/array 3 user=root 4 pass=root
mysql其他测试操作
1 package com.carzyit.jdbc; 2 3 4 import com.sun.corba.se.spi.ior.ObjectKey; 5 6 import java.sql.*; 7 import java.util.HashMap; 8 import java.util.Map; 9 import java.util.Scanner; 10 11 /** 12 * 常用数据库操作集合 13 * Created by lenovo on 2017/4/11. 14 */ 15 public class JDBCTest { 16 private JDBCUtils jdbcUtils; 17 private Student student; 18 19 /** 20 * 使用ResultSetMetaData获取JDBC元数据对象 21 * @throws SQLException 22 */ 23 public void testResultSetMetaData() throws SQLException { 24 25 Connection connection = null; 26 PreparedStatement preparedStatement = null; 27 ResultSet resultSet = null; 28 String sql = "SELECT flow_id flowId,type,id_card idCard,exam_card examCard,student_name studentName," + 29 "location,grade From testgrad WHERE flow_id = ?"; 30 31 jdbcUtils = new JDBCUtils(); 32 try { 33 34 connection = jdbcUtils.getConnection(); 35 preparedStatement = connection.prepareStatement(sql); 36 preparedStatement.setInt(1,3); 37 38 resultSet = preparedStatement.executeQuery(); 39 40 Map<String,Object> map = new HashMap<String,Object>(); 41 42 //1.得到ResultSetMetaData对象 43 ResultSetMetaData rsmd = resultSet.getMetaData(); 44 while (resultSet.next()){ 45 //2.打印每一列的列名，放入map集合中 46 for (int i=0;i<rsmd.getColumnCount();i++){ 47 String columnLable = rsmd.getColumnLabel(i + 1); 48 Object columnValue = resultSet.getObject(columnLable);//getObject不用指定类型 49 50 map.put(columnLable,columnValue); 51 } 52 } 53 Class clazz = Student.class;//实例化为Student对象 54 Object object = clazz.newInstance(); 55 for (Map.Entry<String,Object> entry : map.entrySet()){ 56 String filedName = entry.getKey(); 57 Object fileValues = entry.getValue(); 58 //利用反射机制获取集合元素 59 ReflectionUtils.setFieldValue(object,filedName,fileValues); 60 } 61 System.out.println(object);//输出实例化对象的集合 62 } catch (Exception e) { 63 e.printStackTrace(); 64 }finally { 65 jdbcUtils.releaseDB(resultSet,preparedStatement,connection); 66 } 67 } 68 69 /** 70 * *********掌握************ 71 * 目前最通用的查询方法！！！！！！！！利用反射机制得到类的属性 72 * @param clazz 73 * @param sql 74 * @param args 75 * @param <T> 76 * @return 77 * @throws SQLException 78 */ 79 public <T> T get(Class<T> clazz,String sql,Object ... args) throws SQLException { 80 T entity = null; 81 82 Connection connection = null; 83 PreparedStatement preparedStatement = null; 84 ResultSet resultSet = null; 85 86 jdbcUtils = new JDBCUtils(); 87 try { 88 //1.得到ResultSet对象 89 connection = jdbcUtils.getConnection(); 90 preparedStatement = connection.prepareStatement(sql); 91 for (int i=0;i<args.length;i++){ 92 preparedStatement.setObject(i+1,args[i]); 93 } 94 resultSet = preparedStatement.executeQuery(); 95 //2.得到ResultSetMetaData对象 96 ResultSetMetaData rsmd = resultSet.getMetaData(); 97 //3.创建一个Map<String,Object> 对象，键：SQL查询的列的别名 值：列的值 98 Map<String,Object> map = new HashMap<>(); 99 //4.处理结果集，利用ResultSetMetaData填充 3 对应的Map对象 100 while (resultSet.next()){ 101 for (int i=0;i<rsmd.getColumnCount();i++){ 102 String columnLable = rsmd.getColumnLabel(i + 1); 103 Object columnValues = resultSet.getObject(columnLable); 104 105 map.put(columnLable,columnValues); 106 } 107 } 108 109 //5.若Map不为空集，利用反射创建clazz对应的对象 110 if (map.size() > 0){ 111 entity = clazz.newInstance(); 112 //5.遍历Map对象，利用反射未Class对象的对应的属性赋值 113 for (Map.Entry<String,Object> entry : map.entrySet()){ 114 String filedName = entry.getKey(); 115 Object value = entry.getValue(); 116 ReflectionUtils.setFieldValue(entity,filedName,value); 117 } 118 } 119 } catch (Exception e) { 120 e.printStackTrace(); 121 }finally { 122 jdbcUtils.releaseDB(resultSet,preparedStatement,connection); 123 } 124 // System.out.println("传入的sql值为：" + sql); 125 126 return entity; 127 } 128 129 public Customers getCustomers(String sql,Object... args) throws SQLException { 130 Customers customers = null; 131 132 Connection connection = null; 133 PreparedStatement preparedStatement = null; 134 ResultSet resultSet = null; 135 136 jdbcUtils = new JDBCUtils(); 137 try { 138 connection = jdbcUtils.getConnection(); 139 preparedStatement = connection.prepareStatement(sql); 140 for (int i=0;i<args.length;i++){ 141 preparedStatement.setObject(i+1,args[i]); 142 } 143 resultSet = preparedStatement.executeQuery(); 144 while (resultSet.next()){ 145 customers = new Customers(); 146 customers.setId(resultSet.getInt(1)); 147 customers.setName(resultSet.getString(2)); 148 customers.setEmail(resultSet.getString(3)); 149 customers.setBirth(resultSet.getDate(4)); 150 } 151 } catch (Exception e) { 152 e.printStackTrace(); 153 }finally { 154 jdbcUtils.releaseDB(resultSet,preparedStatement,connection); 155 } 156 System.out.println("传入的sql值为：" + sql); 157 return customers; 158 } 159 160 /** 161 * 使用PrepareStatement进行登录验证，将有效解决SQL注入问题 162 */ 163 public String testSQLInjection2(String username,String password) throws SQLException { 164 String flag = null; 165 String sql = "SELECT * FROM zengjiqiang WHERE username = ? AND password = ?";//符号记得别用中文，否则出错很难找到！！！！ 166 System.out.println(sql); 167 jdbcUtils = new JDBCUtils(); 168 169 Connection connection = null; 170 PreparedStatement preparedStatement = null; 171 ResultSet resultSet = null; 172 173 try { 174 connection = jdbcUtils.getConnection(); 175 preparedStatement = connection.prepareStatement(sql); 176 177 preparedStatement.setString(1,username); 178 preparedStatement.setString(2,password); 179 resultSet = preparedStatement.executeQuery(); 180 if (resultSet.next()){ 181 System.out.println("登录成功！"); 182 flag = "登录成功!!!"; 183 }else { 184 System.out.println("用户名或者密码错误！"); 185 flag = "用户名或者密码错误！"; 186 } 187 } catch (Exception e) { 188 e.printStackTrace(); 189 }finally { 190 jdbcUtils.releaseDB(resultSet,preparedStatement,connection); 191 } 192 return flag; 193 } 194 195 /** 196 * 使用Statement进行登录验证，可以进行SQL注入，不安全 197 * @throws SQLException 198 */ 199 public void testSQLInjection() throws SQLException { 200 String username = "a' or password = "; 201 String password = " or '1'='1 "; 202 String sql = "select * from zengjiqiang where username= '" + username + "' and password = '"+ password + "'"; 203 System.out.println(sql); 204 jdbcUtils = new JDBCUtils(); 205 206 Connection connection = null; 207 Statement statement = null; 208 ResultSet resultSet = null; 209 210 try { 211 connection = jdbcUtils.getConnection(); 212 statement = connection.createStatement(); 213 resultSet = statement.executeQuery(sql); 214 if (resultSet.next()){ 215 System.out.println("登录成功！"); 216 }else { 217 System.out.println("用户名或者密码错误！"); 218 } 219 } catch (Exception e) { 220 e.printStackTrace(); 221 }finally { 222 jdbcUtils.releaseDB(resultSet,statement,connection); 223 } 224 } 225 226 public String registerTest(String username,String password) throws SQLException { 227 String flag = null; 228 Connection connection = null; 229 PreparedStatement preparedStatement = null; 230 String sql = "insert into zengjiqiang(username,password) values(?,?)"; 231 jdbcUtils = new JDBCUtils(); 232 try { 233 connection = jdbcUtils.getConnection(); 234 preparedStatement = connection.prepareStatement(sql); 235 preparedStatement.setString(1,username); 236 preparedStatement.setString(2,password); 237 preparedStatement.executeUpdate(); 238 flag = "注册成功！"; 239 } catch (Exception e) { 240 e.printStackTrace(); 241 flag = "注册失败！"; 242 }finally { 243 jdbcUtils.releaseDB(null,preparedStatement,connection); 244 } 245 return flag; 246 } 247 248 249 /** 250 * 使用PreparedStatement执行插入 251 * @throws SQLException 252 */ 253 public void testPrepareStatement() throws SQLException { 254 Connection connection = null; 255 PreparedStatement preparedStatement = null; 256 String sql = "insert into customers(name,email,birth) values(?,?,?)"; 257 jdbcUtils = new JDBCUtils(); 258 try { 259 connection = jdbcUtils.getConnection(); 260 preparedStatement = connection.prepareStatement(sql); 261 preparedStatement.setString(1,"李达康"); 262 preparedStatement.setString(2,"12345@163.com"); 263 preparedStatement.setDate(3,new Date(new java.util.Date().getTime()));//传入时间的时候要特别注意 264 preparedStatement.executeUpdate(); 265 } catch (Exception e) { 266 e.printStackTrace(); 267 }finally { 268 jdbcUtils.releaseDB(null,preparedStatement,connection); 269 } 270 } 271 272 /** 273 * 根据某一列的信息查询所有对象的数据；例如：根据准考证号或者身份证号查询成绩 274 * @throws Exception 275 */ 276 public void getStudentInfo() throws Exception { 277 //1.得到查询类型 278 int searchType = getSearchTypeFromConsole(); 279 //2.具体查询学生信息 280 Student student = searchStudent(searchType); 281 //3.打印学生信息 282 printStudent(student); 283 } 284 285 /** 286 * 打印学生信息：存在打印具体信息， 287 * @param student 288 */ 289 private void printStudent(Student student) { 290 if (student != null){ 291 System.out.println(student); 292 }else { 293 System.out.println("无查找结果！"); 294 } 295 296 } 297 298 /** 299 * 具体查询学生信息的，返回一个Student对象，若不存在，则返回null 300 * @param searchType 301 * @return 302 */ 303 private Student searchStudent(int searchType) throws Exception { 304 String sql = "SELECT * FROM TestGrad WHERE "; 305 // String sql = "SELECT flowid, type, idcard, examcard,studentname, location, grade FROM TestGrad WHERE "; 306 Scanner scanner = new Scanner(System.in); 307 //1.根据searchType，提示用户输入 308 //1.1若searchType为1，提示：请输入身份证号，若为2：请输入准考证号 309 if (searchType == 1){ 310 System.out.print("请输入准考证号："); 311 String examCard = scanner.next(); 312 sql = sql + "examcard = '" + examCard + "'"; 313 }else { 314 System.out.print("请输入身份证号:"); 315 String idCard = scanner.next(); 316 sql = sql + "idCard = '" + idCard + "'"; 317 } 318 //2.执行查询 319 Student student = getStudent(sql); 320 321 //3、存在返回查询结果 322 return student; 323 } 324 325 /** 326 * 使用PrepareStatement进行查询操作 327 * 根据传入的Sql 返回student对象, 328 * @param sql 329 * @param args 330 * @return 331 */ 332 private Student getStudent(String sql,Object... args) throws SQLException { 333 Student student = null; 334 335 Connection connection = null; 336 PreparedStatement preparedStatement = null; 337 ResultSet resultSet = null; 338 339 jdbcUtils = new JDBCUtils(); 340 try { 341 connection = jdbcUtils.getConnection(); 342 preparedStatement = connection.prepareStatement(sql); 343 for (int i=0;i<args.length;i++){ 344 preparedStatement.setObject(i+1,args[i]); 345 } 346 resultSet = preparedStatement.executeQuery(); 347 while (resultSet.next()){ 348 student = new Student(); 349 student.setFlowId(resultSet.getInt(1)); 350 student.setType(resultSet.getInt(2)); 351 student.setIdCard(resultSet.getString(3)); 352 student.setExamCard(resultSet.getString(4)); 353 student.setStudentName(resultSet.getString(5)); 354 student.setLocation(resultSet.getString(6)); 355 student.setGrade(resultSet.getInt(7)); 356 } 357 } catch (Exception e) { 358 e.printStackTrace(); 359 }finally { 360 jdbcUtils.releaseDB(resultSet,preparedStatement,connection); 361 } 362 System.out.println("传入的sql值为：" + sql); 363 return student; 364 } 365 366 /** 367 * 使用Statement进行查询操作 368 * 根据传入的Sql 返回student对象, 369 * @return 370 */ 371 private Student getStudent(String sql) throws SQLException { 372 373 Student student = null; 374 375 Connection connection = null; 376 Statement statement = null; 377 ResultSet resultSet = null; 378 379 jdbcUtils = new JDBCUtils(); 380 try { 381 connection = jdbcUtils.getConnection(); 382 statement = connection.createStatement(); 383 resultSet = statement.executeQuery(sql); 384 while (resultSet.next()){ 385 student = new Student( 386 resultSet.getInt(1), 387 resultSet.getInt(2), 388 resultSet.getString(3), 389 resultSet.getString(4), 390 resultSet.getString(5), 391 resultSet.getString(6), 392 resultSet.getInt(7)); 393 } 394 } catch (Exception e) { 395 e.printStackTrace(); 396 }finally { 397 jdbcUtils.releaseDB(resultSet,statement,connection); 398 } 399 System.out.println("传入的sql值为：" + sql); 400 return student; 401 } 402 403 /** 404 * 从控制台读入一个整数，确定要查询的类型 405 * @return 1.用身份证查询 2.用准考证号查询， 其他的无效，并提示用户重新输入 406 */ 407 private int getSearchTypeFromConsole() { 408 System.out.println("1.用准考证号查询 2.用身份证查询"); 409 Scanner scanner = new Scanner(System.in); 410 int type = scanner.nextInt(); 411 if (type != 1 && type != 2){ 412 System.out.print("输入错误请重新输入"); 413 throw new RuntimeException(); 414 } 415 return type; 416 } 417 418 /** 419 * 使用Statement执行插入语句；非常麻烦，注意与PrepareStatement执行插入的区别 420 * @throws Exception 421 */ 422 public void addNewStudent() throws Exception { 423 student = getStudentFromConsole(); 424 // addNewStudent(student); 425 addNewStudent2(student); 426 } 427 428 private Student getStudentFromConsole(){ 429 430 Scanner scanner = new Scanner(System.in); 431 student = new Student(); 432 433 System.out.print("FlowId: "); 434 student.setFlowId(scanner.nextInt()); 435 436 System.out.print("Type: "); 437 student.setType(scanner.nextInt()); 438 439 System.out.print("IdCard:"); 440 student.setIdCard(scanner.next()); 441 442 System.out.print("ExamCard:"); 443 student.setExamCard(scanner.next()); 444 445 System.out.print("StudentName: "); 446 student.setStudentName(scanner.next()); 447 448 System.out.print("Location:"); 449 student.setLocation(scanner.next()); 450 451 System.out.print("Grade: "); 452 student.setGrade(scanner.nextInt()); 453 454 return student; 455 } 456 457 public void addNewStudent(Student student) throws Exception { 458 //1.准备一条sql语句： 459 String sql = "INSERT INTO TestGrad VALUES(" + student.getFlowId() 460 + "," + student.getType() + ",'" + student.getIdCard() + "','" 461 + student.getExamCard() + "','" + student.getStudentName() 462 + "','" + student.getLocation() + "'," + student.getGrade() 463 + ")"; 464 465 jdbcUtils.update(sql); 466 } 467 468 /** 469 * 使用PrepareStatement 470 * @param student 471 * @throws SQLException 472 */ 473 public void addNewStudent2(Student student) throws SQLException { 474 jdbcUtils = new JDBCUtils(); 475 String sql = "insert into testgrad(flowid,type,idcard,examcard,studentname,location,grade) values(?,?,?,?,?,?,?)"; 476 jdbcUtils.update(sql,student.getFlowId(),student.getType(), 477 student.getIdCard(),student.getExamCard(), student.getStudentName(), 478 student.getLocation(),student.getGrade()); 479 } 480 481 /** 482 * 使用Statement查询数据库的左右信息（查询四六级所有信息） 483 * @throws SQLException 484 */ 485 public void allTotalInfo() throws SQLException { 486 String sql = null; 487 Connection connection = null; 488 Statement statement = null; 489 ResultSet resultSet = null; 490 jdbcUtils = new JDBCUtils(); 491 // sql = "delete from customers where id = 1";//删除第一条记录 492 // sql = "update customers set name = 'Tom' where id = 2";//查询id为2的记录 493 sql = "select flowid ,type,idcard,examcard,studentname,location,grade from testgrad";//查询所有结果 494 495 try { 496 connection = jdbcUtils.getConnection(); 497 statement = connection.createStatement(); 498 resultSet = statement.executeQuery(sql); 499 while (resultSet.next()){ 500 int id = resultSet.getInt("flowid"); 501 int grad = resultSet.getInt("type"); 502 String idnumber = resultSet.getString("idcard"); 503 String ticketnumber = resultSet.getString("examcard"); 504 String name = resultSet.getString("studentname"); 505 String area = resultSet.getString("location"); 506 int score = resultSet.getInt("grade"); 507 System.out.println("流水号：" + id); 508 System.out.println("四级/六级：" + grad); 509 System.out.println("身份证号：" + idnumber); 510 System.out.println("准考证号：" + ticketnumber); 511 System.out.println("姓名：" + name); 512 System.out.println("区域：" + area); 513 System.out.println("得分：" + score); 514 System.out.println(); 515 } 516 } catch (Exception e) { 517 e.printStackTrace(); 518 }finally { 519 jdbcUtils.releaseDB(resultSet,statement,connection); 520 } 521 } 522 }
关于properties文件存放的位置：
一般放在和引用此properties文件的类的包下，然后使用如下代码引用：（相对路径的应用方法）
1 InputStream in = getClass().getResourceAsStream("mysql.properties"); 2 Properties properties = new Properties(); 3 properties.load(in);
使用绝对路径引用一直出错！！！！！将properties文件放在工程根目录一直引用失败！！！！后续将继续解决路径问题！！！
