繼MySQL之后的技術-JDBC-從淺到深-02

概念

編程六部曲

SQL注入和statement

工具類的封裝

JDBC事務

模糊查詢

批處理

數據庫連接池

Apache-DBUtils

BasicDao

概念

JDBC為訪問不同的數據庫提供了統一的接口，為使用者屏蔽了細節問題。

Java程序員使用JDBC，可以連接任何提供了JDBC驅動程序得到數據庫系統，從而完成對數據庫的各種操作。

解耦合：降低程序的耦合度，提高程序的擴展力。

編程六部曲

第一步，注冊驅動

第二步，獲取連接

第三步，獲取數據庫操作對象

第四步，執行sql語句

第五步，處理查詢結果集

第六步，釋放資源

public static void main(String[] args) throws Exception{//類加載Class.forName("com.mysql.jdbc.Driver");//獲取連接String url = "jdbc:mysql://localhost:3306/mydata";Connection connection = DriverManager.getConnection(url, "root", "123456");//獲取數據庫對象Statement statement = connection.createStatement();//執行sql語句String sql = "select no,name from student";ResultSet resultSet = statement.executeQuery(sql);//處理結果集while(resultSet.next()){int no = resultSet.getInt(1);String name = resultSet.getString(2);System.out.println(name + ":\t" + no);}//關閉流resultSet.close();statement.close();connection.close();
}

SQL注入和statement

目前存在的問題：用戶輸入的信息中患有sql語句的關鍵字，并且這些關鍵字參與sql語句的編譯過程導致sql語句的愿意被扭曲，進而達到sql注入。

如何解決sql注入問題？

1、主要用戶提供的信息不參與sql語句的編譯過程，問題就解決了。

2、即使用戶提供的信息中含有sql語句的關鍵字，但是沒有參與編譯，不起作用。

3、想要用戶信息不參與sql語句的編譯，那么必須使用java.sql.PreparedStatement

4、PreparedStatement接口繼承了java.sql.Statement

5、PreparedStatement是屬于預編譯的數據庫操作對象

6、PreparedStatement的原理是：預先對SQL語句的框架進行編譯，然后再給SQL語句傳值

解決SQL注入的關鍵是什么？

用戶提供的信息中即使含有sql語句的關鍵字，但是這些關鍵字并沒有參與編譯，不起作用。

?對比Statement和PreparedStatement

Statement存在sql注入問題，PreparedStatement解決了SQL注入問題。

Statement是編譯一次執行一次。

PreparedStatement是編譯一次，可執行N次，PreparedStatement效率較高。

PreparedStatement使用較多，凡是業務方面要求是需要進行sql語句拼接的，必須使用Statement。

工具類的封裝

public class JDBCUtil{private static String url;private static String user;private static String password;private static String driver;static{try{Properties properties = new Properties();properties.load(new FileInputStream("mysql.properties"));driver = properties.getProperty("driver");url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");//注冊驅動Class.forName(driver);}catch (Exception e){throw new RuntimeException(e);}}//創建連接方法public static Connection getConnection(){try{return DriverManager.getConnection(url,user,password);} catch(SQLException throwables) {throw new RuntimeException(throwables);}}//關閉流public static void close(ResultSet rs, Statement ps,Connection c){if(rs != null){try{rs.close();} catch(SQLException throwables){throw new RuntimeException(throwables);}}if(ps != null){try{ps.close();} catch(SQLException throwables){throw new RuntimeException(throwables);}}if(c != null){try{c.close();} catch(SQLException throwables){throw new RuntimeException(throwables);}}}public static void main(String[] args){Connection connection = JDBCUtil.getConnection();PreparedStatement preparedStatement = null;String sql = "insert into t1 values('李四',2)";try{preparedStatement = connection.prepareStatement(sql);//執行sql語句preparedStatement.executeUpdate();} catch(SQLException throwables){throwables.printStackTrace();} finally{JDBCUtil.close(null,preparedStatement,connection);}}}

JDBC事務

JDBC中的事務是自動提交的，什么時候自動提交？

只要執行任意一條DML預計，則自動提交一次，這是JDBC默認的事務行為。但是在實際的業務當中，通常都是N條DML語句共同聯合才能完成的，必須保證他們這些DML語句在同一個事務中同時成功或者同時失敗。

//重點三行代碼
connection.setAutoCommit(false);
connection.commit();
connection.rollback();

public static void main(String[] args){Connection connection = JDBCUtil.getConnection();PreparedStatement preparedStatement = null;try {//設置為不自動提交，即開啟事務connection.setAutoCommit(false);String sql = "update t1 set no = no - 100 where name = '馬云'" ;preparedStatement = connection.prepareStatement(sql);preparedStatement.executeUpdate();String sql2 = "update t1 set no = no + 100 where name = '馬化騰'";PreparedStatement preparedStatement1 = connection.prepareStatement(sql2);preparedStatement1.executeUpdate();//提交事務connection.commit();} catch (SQLException throwables){try{//程序執行到此處，說明程序報錯了connection.rollback();} catch (SQLException e){e.printStackTrace();}throwables.printStackTrace();} finally{JDBCUtil.close(null, preparedStatement, connection);}
}

模糊查詢

public static void main(String[] args){Connection connection = JDBCUtil.getConnection();PreparedStatement preparedStatement = null;ResultSet resultSet = null;try{String sql = "select name fron t1 where name like ?";preparedStatement = connection.prepareStatement(sql);//模糊查詢preparedStatement.setString(1,"馬%");resultSet = preparedStatement.executeQuery();while(resultSet.next()){String name = resultSet.getString("name");System.out.println(name);}} catch(SQLException throwables){throwables.printStackTrace();} finally{JDBCUtil.close(resultSet, preparedStatement, connection);}
}

批處理

1、當需要成批插入或者更新記錄時，可以采用java的批量更新機制，這一機制允許多條語句一次性提交給數據庫批量處理。

2、JDBC的批量處理語句包括以下方法：

? ? ? ? addBatch(); //添加需要批量處理的SQL語句或參數

? ? ? ? executeBatch(); //執行批量處理語句

? ? ? ? clearBatch(); // 清空批處理包下的語句

3、JDBC連接MySQL時，如果要使用批處理功能，請在url后添加：

? ? ? ? ?rewriteBatchedStatements=true

4、批處理往往和PreparedStatement一起搭配使用，減少變異次數，減少運行次數。

//以下是傳統方法批量傳入大量數據
public static void main(String[] args){Connection connection = JDBCUtil.getConnection();PreparedStatement preparedStatement = null;try{String sql = "insert into t1 values(?,?)";preparedStatement = connection.prepareStatement(sql);long start = System.currentTimeMillis();//批量插入數據for(int i = 0;i < 5000;i++){prepreadStatement.setString(1,"凍梨");preparedStatement.setInt(2,i);preparedStatement.executeUpdate();}long end = System.currentTimeMillis();} catch(SQLException throwables){throwables.printStackTrace();} finally{JDBCUtil.close(null, preparedStatement, connection);}
}

//以下是批處理方法
public static void main(String[] args){Connection connection = JDBCUtil.getConnection();PreparedStatement preparedStatement = null;try {String sql = "insert into t1 values(?,?)";preparedStatement = connection.preparedStatement(sql);long start = System.currentTimeMillis();//批量插入數據for(int i = 15000; i< 20000;i++){preparedStatement.setString(1,"凍梨");preparedStatement.setInt(2,i);//將sql語句加入批處理包內preparedStatement.addBatch();//達到一千條，在進行處理if((i + 1) % 1000 == 0){preparedStatement.executeBatch();preparedStatement.clearBatch();}}long end = System.currentTimeMillis();} catch (SQLException throwables){throwables.printStackTrace();} finally{JDBCUtil.close(null,preparedStatement, connection);}
}

數據庫連接池

1、傳統的JDBC數據庫連接使用DriverManager來獲取，每次向數據庫連接的時候都要將Connection加載到內容中，再驗證IP地址，用戶名和密碼需要數據庫連接，會占用很多系統資源。

2、每一次數據庫連接，使用完后都得斷開，如果程序出現異常而未能關閉，將導致數據庫內存泄漏，最終將導致重啟數據庫。

3、傳統獲取連接的方式，不能控制創建的連接數量，如連接過多，也可能導致內存泄漏，MySQL崩潰。

4、解決傳統開發中的數據庫連接問題，可以采用數據庫連接池技術。

數據庫連接池基本介紹：

????????1、預先在緩沖池放入一定數據的連接，當需要建立數據庫連接時，只需從緩沖池中取出一個，使用完畢之后再放回去。

? ? ? ? 2、數據庫連接池負責分配，管理和釋放數據庫連接，他允許應用程序重復使用一個現有的數據庫連接，而不是重新建立一個。

? ? ? ? 3、當應用程序向連接池請求的連接數超過最大連接數據時，需要等待。

JDBC的數據庫連接池使用javax.sql.DataSource來表示，DataSource只是一個接口。

數據庫連接池種類：

? ? ? ? 1、C3P0：速度慢，穩定

? ? ? ? 2、DBCP：較快，不穩定

? ? ? ? 3、Proxool：有監控連接池窗臺的功能，不穩定

? ? ? ? 4、BonCP：數據快

? ? ? ? 5、Druid：集以上優點于一身

//傳統方式
public static void main(String[] args) throws Exception{long start = System.currentTimeMillis();for(int i = 0;i < 5000;i++){Connection connection = JDBCUtil.getConnection();connection.close();}long end = System.currentTimeMillis();}

//連接池，需要加入對應的jar和配置文件
public static void main(String[] args) throws Exception{//獲取數據源對象ComboPooledDataSource comboPoolDataSource = new ComboPooledDataSource();//根據配置文件獲取信息Properties properties = new Properties();properties.load(new FileInputStream("src//mysql.properties"));String driver = properties.getProperty('driver');String url = properties.getProperty('url');String user = properties.getProperty('user');String password = properties.getProperty('password');//給數據源設置參數comboPooledDataSource.setDriverClass(driver);comboPooledDataSource.setJdbcUrl(url);comboPooledDataSource.setUser(user);comboPooledDataSource.setPassword(password);//初始化連接數comboPooledDataSource.setInitialPoolSize(10);//設置最大連接數comboPooledDataSource.setMaxPoolSize(50);//獲取連接for(int i = 0;i < 5000;i++){Connection connection = comboPooledDataSource.getConnection();connection.close();}}

//比如50w，Druid要快
public static void main(String[] args) throws Exception{Properties properties = new Properties();properties.load(new FileInputStream("src//druid.properties"));//創建一個Druid連接DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);for(int i = 0;i < 5000;i++){Connection connection = dataSource.getConnection();connection.close();}}

//關于Druid連接池的工具類
public class JDBCUtilsByDruid{private static DataSource ds;static {try{Properties properties = new Properties();properties.load(new FileInputStream("src//druid.properties"));ds = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e){throw new RuntimeException(e);}}//創建連接public static Connection getConnection(){try{return ds.getConnection();} catch(SQLException throwables){throw new RuntimeException(throwables);}}//不是真正的關閉，而是放回連接池public static void close(ResultSet rs, Statement ps, Connection c){if(rs != null){try{rs.close();} catch(SQLException throwables){throw new RuntimeException(throwables);}}if(ps != null){try{ps.close();} catch(SQLException throwables){throw new RuntimeException(throwables);}}if(c!= null){try{c.close();} catch(SQLException throwables){throw new RuntimeException(throwables);}}}
}

Apache-DBUtils

//以下是土方法把resultSet集合里的數據，封裝到list集合中
public class T2{private Integer no;private String name;public T2(){}public T2(Integer no, String name){this.no = no;this.name = name;}public Integer getNo(){return no;}public static void main(String[] args){Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;String sql = "select * from t2";//存儲結果ArrayList<T2> list = new ArrayList<>();try{connection = JDBCUtilsByDruid.getConnection();preparedStatement = connection.prepareStatement(sql);resultSet = preparedStatement.executeQuery();while(resultSet.next()){int no = resultSet.getInt(1);String name = resultSet.getString(2);list.add(new T2(no,name));}} catch(SQLExceotion throwables){throwables.printStackTrace();} finally{JDBCUtilByDruid.close(resultSet, preparedStatement, connection);}}
}

Apache-DBUtils：是Apache組織提供的一個開源JDBC工具類庫，是對JDBC的封裝，極大簡化JDBC工作量。

Dbutils類：

1、QueryRunner類：該類封裝了SQL的執行，是線程安全的。

2、ResultSetHandler接口：該接口用于處理java.sql.ResultSet，將數據按要求轉換為另一種形式。

ArrayHandler：把結果集中的第一行數據轉成對象數組

ArrayListJamdler：把結果集中的每一行數據都換成一個數組，存放到List中

BeanHandler：將結果集中的第一行數據封裝到一個對應的JavaBean實例中

BeanListHandler：將結果集中的第一行數據都封裝到一個對應得到JavaBean實例中，存放到List

ColumnListHandler：將結果集中某一列的數據存放到List中

KeyedHandler：將結果集中的每行數據都封裝到Map里，再把這些map再存放到一個map里，其key為指定的key

MapHandler：將結果集中的第一行數據封裝到一個Map里，key是列名，value就是對應的值

MapListHandler：將結果集中的每一行數據都封裝到一個Map里，然后再存放到List

public static void main(String[] args) throws SQLException{Connection connection = JDBCUtilsByDruid.getConnection();QueryRunner queryRunner = new QueryRunner();String sql = "select * from t2";/*第一個參數：一個連接對象第二個參數：sql語句第三個參數：new BeanListHandler<>(T2.class)在將resultSet封裝成T2對象，然后加入list集合中底層使用反射機制，去獲取T2類的屬性，進行封裝*/List<T2> list = queryRunner.query(connection, sql, new BeanListHandler<>(T2.class));for(T2 t: list){System.out.println(t);}JDBCUtilsByDruid.close(null,null,connection);
}

public static void main(String[] args) throws SQLException{Connection connection = JDBCUtilsByDruid.getConnection();QueryRunner queryRunner = new QueryRunner();String sql = "delete from t2 where no = 3";int update = queryRunner.update(connection, sql);JDBCUtilsByDruid.close(null,null,connection);
}

BasicDao

DAO和增上改查通用方法——BasicDao

1、DAO：data access object 數據訪問對象

2、這樣的通用類，成為BasicDao，是專門和數據庫交互的，即完成對數據表的crud操作

3、在BasicDao的基礎上，實現一張表對應一個Dao，更好的完成功能

Customemer表——Customer.java——CustomerDao,java

Apache-dbutils+Druid簡化了JDBC開發，但還有不足：

1、SQL語句是固定的，不能通過參數傳入，通用性不好，需要進行改進，更方便執行CRUD

2、對于select操作，如果有返回值，返回類型不能固定，需要使用泛型

3、將來的表很多，業務需求復雜，不可能只靠一個java類完成

public class BasicDAO<T> {private QueryRunner qr = new QueryRunner();//dml操作語句public int update(String sql, Object... parameters){Connection connection = null;try {connection = JDBCUtilsByDruid.getConnection();return qr.update(connection, sql, parameters);} catch (SQLException e){throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(null,null,connection);}}//根據傳入的Class對象，返回對應泛型的集合public List<T> query(String ssql, Class<T> clazz, Object... parameters){Connection connection = null;try {connection = JDBCUtilsByDruid.getConnection();return qr.query(connection, sql, new BeanListHandler<T>(clazz),parameters);} catch (SQLException e){throw new RuntimeException(e);} finally{JDBCUtilsByDruid.close(null,null,connection);}}//查詢單行結果public T querySingle(String sql, Class<T> clazz,Object... parameters){Connection connection = null;try{conenction = JDBCUtilsByDruid.getConnection();return qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);} catch (SQLException e){throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(null,null,connection);}}//查詢單行單列結果public Object queryScalar(String sql, Object... parameters){Connection connection = null;try{conenction = JDBCUtilsByDruid.getConnection();return qr.query(connection, sql, new ScalarHandler(), parameters);} catch (SQLException e){throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(null,null,connection);}}}

public class T2DAO extends BasicDAO<T2>{}public class T2{private Integer no;private String name;public T2(){}public T2(Integer no, String name){this.no = no;this.name = name;}public Integer getNo(){ return no;}public void setNo(Integer no) {this.no = no;}public String getName() {return name;}public void setName(String name){this.name = name;}@Overridepublic String toString(){}
}public class TestDao{public static void main(String[] args){T2DAO t2DAO = new T2DAO();//查詢多行String sql = "select * from t2";List<T2> query = t2DAO.query(sql, T2.class);for(T2 t: query){System.out.println(t);}//查詢單行    String sql3 = "select * from t2 where no = 2";T2 t2 = t2DAO.querySingle(sql3, T2.class);//查詢單行單列String sql4 = "select name fron t2 where no = 2";Object o = t2DAO.queryScalar(sql4);//dml操作String sql2 = "insert into t2 values(4, '擦原配')";int update = t2DAO.update(sql2);}
}

public class JDBCUtilsByDruid{private static DataSource ds;static{try{Properties properties = new Properties();properties.load(new FileInputStream("src//druid.properties"));ds = DruidDataSourceFactory.createDataSource(Properties);} catch (Exception e){throw new RuntimeException(e);}}public static Connection getConnection(){try{return ds.getConnection();} catch (SQLException throwables){throw new RuntimeException(throwables);}}
}