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文件放在工程根目录一直引用失败！！！！后续将继续解决路径问题！！！