JDBC mysql 相關內容筆記

解決亂碼： url字元串加上?useUnicode=true&characterEncoding=utf-8;

mysql資料庫無法插入中文數據問題：將mysql資料庫的編碼改為utf-8;

ResultSet中get(列名)方法不一定獲取的是資料庫欄位名，也有可能是自定義的別名，例如：select id,name,password pwd from user 此時resultSet.get("password")會拋出異常！

反射封裝JDBC：

 1 package test;
 2 
 3 import java.lang.reflect.Field;
 4 import java.sql.Connection;
 5 import java.sql.DriverManager;
 6 import java.sql.PreparedStatement;
 7 import java.sql.ResultSet;
 8 import java.sql.ResultSetMetaData;
 9 import java.sql.SQLException;
10 import java.util.ArrayList;
11 import java.util.List;
12 
13 public class TestReflectionJDBC<T> {
14 
15     private static Connection connection = null;
16     private static PreparedStatement preparedStatement = null;
17     private static ResultSet resultSet = null;
18 
19     private Connection getConnection() {
20         try {
21             Class.forName("com.mysql.jdbc.Driver");
22             if (connection == null) {
23                 connection = DriverManager.getConnection(
24                         "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123456");
25             }
26         } catch (Exception e) {
27             e.printStackTrace();
28         }
29         return connection;
30     }    
31 
32     public List<T> query(String sql,Class<T> clzz,Object...objects){
33         List<T> list=new ArrayList<>();
34         try {
35             preparedStatement=getConnection().prepareStatement(sql);
36             for(int i = 0;i<objects.length;i++){
37                 preparedStatement.setObject(i+1, objects[i]);
38             }
39             resultSet=preparedStatement.executeQuery();
40             if(resultSet!=null){
41                 ResultSetMetaData resultSetMetaData=null;                
42                 while(resultSet.next()){
43                     //if(resultSetMetaData==null){
44                         resultSetMetaData=resultSet.getMetaData();
45                         T t=clzz.newInstance();
46                         int count=resultSetMetaData.getColumnCount();
47                         for(int i=0;i<count;i++){
48                             String name=resultSetMetaData.getColumnName(i+1);
49                             Field field=clzz.getDeclaredField(name);
50                             field.setAccessible(true);
51                             Object value=resultSet.getObject(name);
52                             field.set(t, value);                            
53                         }
54                         list.add(t);
55                     //}
56                 }
57             }
58         } catch (Exception e) {
59             e.printStackTrace();
60         }        
61         return list;
62     }
63     public int update(String sql,Object...objects){
64         int result=0;
65         try {
66             preparedStatement=getConnection().prepareStatement(sql);
67             for(int i=0;i<objects.length;i++){
68                 preparedStatement.setObject(i+1, objects[i]);
69             }
70             result=preparedStatement.executeUpdate();
71         } catch (SQLException e) {
72             e.printStackTrace();
73         }
74         return result;
75     }
76 }