B029-JDBC增強

- - PreparedStatement 查詢
  - - 1.sql注入
    - 2.Statement分析 (面試題)
    - 3.PreparedStatement (面試題)
  - 登錄功能的完善
  - 事務
  - 鏈接池
  - - 概念
    - 實現
    - - DBCP連接池實現
      - 第一種配置方式
        第二種配置方式
  - 返回主鍵
  - BaseDao的抽取

PreparedStatement 查詢

1.sql注入

就是在sql的字符串拼接的時候，加入了特定的條件判斷，

如：SELECT * FROM student where name=’ 小坤坤255255 ’ OR 1=1

代碼

public class StudentDaoImpl  implements IStudentDao{//Statement的寫法@Overridepublic Student login(String name, String Password) {//通過工具類獲取連接Connection conn = JDBCUtil.Instance().getconn();Statement State =null;ResultSet rs=null;Student student = new Student();try {State = conn.createStatement();rs = State.executeQuery("select * from student where name='"+name+"'and password ='"+Password+"'");while (rs.next()) {student.setId(rs.getInt("id"));student.setName(rs.getString("name"));student.setPassword(rs.getString("password"));} } catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil.Instance().close(rs, State, conn);}return student;}
}

public class JDBCTest {@Testpublic void testName() throws Exception {StudentDaoImpl studentDaoImpl = new  StudentDaoImpl();//正常的代碼
//		Student stu = studentDaoImpl.login("網通", "123");//sql注入的代碼Student stu = studentDaoImpl.login("網通", "123' or '1=1");System.out.println(stu);if(stu.getName()!=null){System.out.println("賬號存在登錄成功");}else{System.out.println("賬號不存在  ..登錄失敗");}}
}

2.Statement分析 (面試題)

1.通過上面sql注入的案例我們發現 Statement 它可能會導致sql注入的問題
2.通過這幾天的sql的書寫我發現 Statement 拼接sql相當復雜，稍微有一點點差錯就會導致sql語句有問題

解決方法:PreparedStatement

3.PreparedStatement (面試題)

PreparedStatement 很好的解決了Statement的問題
1.不用擔心注入問題(雙引號之內看成一個整體的字符串而不是兩個字符串和一個關鍵字)，
2.sql語句不用復雜拼接，
3.會預處理sql語句，執行速度也更快

代碼：
StudentDaoImpl

	//PreparedStatement寫法@Overridepublic Student login(String name, String Password) {Connection conn = JDBCUtil2.Instance().getconn();PreparedStatement ps=null;ResultSet rs =null;Student student = new Student();try {ps= conn.prepareStatement("select * from student  where name=? and password=?");ps.setString(1, name);ps.setString(2, Password);rs = ps.executeQuery();while(rs.next()){student.setId(rs.getInt("id"));student.setName(rs.getString("name"));student.setPassword(rs.getString("password"));}} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtil2.Instance().close(rs, ps, conn);}return student;}

JDBCTest

	@Testpublic void testName() throws Exception {StudentDaoImpl studentDaoImpl = new  StudentDaoImpl();//正常的代碼
//		Student stu = studentDaoImpl.login("網通", "123");//sql注入的代碼Student stu = studentDaoImpl.login("網通", "123' or '1=1");System.out.println(stu);if(stu.getName()!=null){System.out.println("賬號存在登錄成功");}else{System.out.println("賬號不存在  ..登錄失敗");}}

問題:PreparedStatement和Statement 不是同一個類為什么關資源的時候可以傳PreparedStatement
因為 PreparedStatement 繼承了 Statement，（多態）

PreparedStatement ：

  // 預處理 這時候就會把sql發送到數據庫了，只是這時還不會執行sqlselect * from student  where name=? and password=? 		//變量位置使用？先占住，（這時已經發了sql語句了）ps= conn.prepareStatement("select * from student  where name=? and password=?");// 把？替換成對應的值ps.setString(1, name);ps.setString(2, Password);// 執行sql  這時的執行就是一個執行命令，不會發sql語句(前面已發)rs = ps.executeQuery();

Statement：

  //創建 Statement 對象 State = conn.createStatement();// 發送并執行sql rs = State.executeQuery("select * from student where name='"+name+"'and password ='"+Password+"'");

PreparedStatement 是 Statement 子類，速度比Statement 快，能避免sql 注入，可以不用拼接sql語句

登錄功能的完善

StudentDaoImpl

	@Overridepublic Student QueryByUsername(String name) {Connection conn = JDBCUtil2.Instance().getconn();PreparedStatement ps = null;ResultSet rs = null;Student student = new Student();try {ps = conn.prepareStatement("select * from student where name=?");ps.setString(1, name);rs = ps.executeQuery();while (rs.next()) {student.setId(rs.getInt("id"));student.setName(rs.getString("name"));student.setPassword(rs.getString("password"));}} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtil2.Instance().close(rs, ps, conn);}return student;}

JDBCTest

	// 登錄的第二種實現方式@Testpublic void login() throws Exception {StudentDaoImpl studentDaoImpl = new StudentDaoImpl();// 查詢是小坤坤(用戶名)的信息，這個用戶名 應該是前臺(瀏覽器) 用戶 傳過來的 -- 模擬Student student = studentDaoImpl.QueryByUsername("小坤坤");// 判斷用戶名是否存在if (student.getName() == null) {System.out.println("賬號不存在");}// else 就是賬號存在else {// 判斷這個賬號的密碼是否正確 (這個密碼應該是前臺(瀏覽器) 用戶 傳過來的)if (!"8848".equals(student.getPassword())) {System.err.println("密碼錯誤");} else {System.out.println("登錄成功");}}}

事務

	@Testpublic void Testtrans() throws Exception {Connection connection = null;PreparedStatement ps = null;PreparedStatement ps2 = null;try {connection = JDBCUtil2.Instance().getconn();// 不提交事務 (sql執行了，改變了數據庫的數據，但是后面沒有寫提交事務數據庫就不能有變化)，connection.setAutoCommit(false);String sql = "update bank set money=money-1000 where name='過兒'";ps = connection.prepareStatement(sql);ps.execute();// 在這個位置 出現異常int a=0/0;String sql2 = "update bank set money=money+1000 where name='姑姑'";ps2 = connection.prepareStatement(sql2);ps2.execute();// 提交事物 （數據庫可以發生變化了）connection.commit();} catch (Exception e) {// 回滾 （你數據庫改變了之后我還是可以回滾）/*當我們把自動提交關閉,那sql就不是提交執行,于是我們一定要記住,當我們一個整體功能完成之后,自己要手動進行提交;--conn.commit但是失敗之后,要記住數據回滾*/connection.rollback();} finally {ps2.close();ps.close();connection.close();}}

ACID (面試)
事務 : 一組操作要么都成功要么都失敗
事務具有4個特征，分別是原子性、一致性、隔離性和持久性，簡稱事務的ACID特性；
原子性（atomicity) ：一個事務要么全部提交成功，要么全部失敗回滾，不能只執行其中的一部分操作
一致性（consistency) ：一個事務執行前后，數據庫都處于一致性狀態
隔離性（isolation）：每一個事務都是單獨的，事務和事務之間不影響
持久性（durability）：事務執行完了, 持久化到數據庫

鏈接池

概念

在這里插入圖片描述
你創建了一個池塘池塘里面你放了很多鏈接用完了就放回去 -->節省開關鏈接的時間

實現

在Java中，連接池使用javax.sql.DataSource接口來表示連接池，這里的DataSource就是連接池，連接池就是DataSource。
DataSource是接口，和JDBC一樣，是Sun公司開發的一套接口，需要各大廠商實現；
需要導入相應包—導包…
所以使用連接池，首先需要導包;

常用的DataSource的實現有下面兩種方式:
DBCP: Spring推薦的（Spring框架已經集成DBCP）
C3P0: Hibernate推薦的(早期)（Hibernate框架已經集成C3P0）持久層

DBCP連接池實現

1.導入jar包
commons-dbcp-1.3.jar，commons-pool-1.5.6.jar

2.代碼
BasicDataSource就是DBCP的連接池實現

第一種配置方式

public class JDBCUtil {// 構造方法私有化private JDBCUtil() {};private static JDBCUtil instance;// 一啟動就加載驅動 只執行一次static Properties ps = null;static BasicDataSource ds = null;static {ps = new Properties();try {ps.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));ds = new BasicDataSource();ds.setDriverClassName(ps.getProperty("dirverClassName"));ds.setUsername(ps.getProperty("username"));ds.setPassword(ps.getProperty("password"));ds.setUrl(ps.getProperty("url"));} catch (IOException e) {e.printStackTrace();}/*		 try {ps.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));Class.forName(ps.getProperty("dirverClassName"));} catch (Exception e) {e.printStackTrace();}*/instance = new JDBCUtil();}public static JDBCUtil Instance() {return instance;}// 寫加載數據庫的驅動public Connection getconn() {Connection connection = null;try {//換成新的獲取連接池的方式connection = ds.getConnection();// connection = DriverManager.getConnection(ps.getProperty("url"),// ps.getProperty("username"), ps.getProperty("password"));} catch (Exception e) {e.printStackTrace();}return connection;}// 關閉資源public void close(ResultSet rs, Statement State, Connection conn) {try {if (rs != null) {rs.close();}} catch (SQLException e) {e.printStackTrace();} finally {try {if (State != null) {State.close();}} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn != null) {conn.close();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}
}

public class test {public static void main(String[] args) {Connection connection = JDBCUtil.Instance().getconn();try {String sql = "update bank set money=money-500 where name='過兒'";PreparedStatement ps = connection.prepareStatement(sql);ps.execute();} catch (SQLException e) {e.printStackTrace();}}
}

第二種配置方式

public class JDBCUtil2 {// 構造方法私有化private JDBCUtil2() {};private static JDBCUtil2 instance;// 一啟動就加載驅動 只執行一次static Properties ps = null;static DataSource ds = null;static {ps = new Properties();try {ps.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));// 創建連接池ds = BasicDataSourceFactory.createDataSource(ps);} catch (Exception e) {e.printStackTrace();}// try {// 		ps.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));// 		Class.forName(ps.getProperty("dirverClassName"));// } catch (Exception e) {// 		e.printStackTrace();// }instance = new JDBCUtil2();}public static JDBCUtil2 Instance() {return instance;}// 寫加載數據庫的驅動public Connection getconn() {Connection connection = null;try {//換成新的獲取連接池的方式connection = ds.getConnection();// connection = DriverManager.getConnection(ps.getProperty("url"),// ps.getProperty("username"), ps.getProperty("password"));} catch (Exception e) {e.printStackTrace();}return connection;}// 關閉資源public void close(ResultSet rs, Statement State, Connection conn) {try {if (rs != null) {rs.close();}} catch (SQLException e) {e.printStackTrace();} finally {try {if (State != null) {State.close();}} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}}}
}

public class test {public static void main(String[] args) {Connection connection = JDBCUtil2.Instance().getconn();try {String sql = "update bank set money=money-500 where name='過兒'";PreparedStatement ps = connection.prepareStatement(sql);ps.execute();} catch (SQLException e) {e.printStackTrace();}}
}

返回主鍵

場景舉例：先后添加product和product_stock時，需要拿到product插入時自增的id存到product_stock的product_id里

看文檔做

StudentDaoImpl

	@Overridepublic void insert(Student stu) {Connection conn = JDBCUtil.Instance().getconn();PreparedStatement ps = null;try {String sql = "insert into student(name,password) values(?,?)";ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);ps.setString(1, stu.getName());ps.setString(2, stu.getPassword());ps.executeUpdate();ResultSet rs = ps.getGeneratedKeys();while (rs.next()) {System.err.println(rs.getInt(1));}} catch (SQLException e) {e.printStackTrace();}}

JDBCTest

	@Testpublic void addStudent() throws Exception {StudentDaoImpl studentDaoImpl = new StudentDaoImpl();Student stu = new Student();stu.setName("小波波");stu.setPassword("857857958958");studentDaoImpl.insert(stu);}

BaseDao的抽取

BaseDao

public class BaseDao {public void excuteUpdate(String sql, Object... objects) {Connection conn = JDBCUtil2.Instance().getconn();PreparedStatement ps = null;try {ps = conn.prepareStatement(sql);for (int i = 0; i < objects.length; i++) {ps.setObject(i + 1, objects[i]);}ps.execute();} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtil2.Instance().close(null, ps, conn);}}
}

實現類：

public class StudentDaoImpl extends BaseDao  implements IStudentDao{@Overridepublic void insert(Student stu) {String  sql="insert into student(name,password) values(?,?)";excuteUpdate(sql, stu.getName(),stu.getPassword());}@Overridepublic void update(Student stu) {String sql = "update student set name=?,password=? where id =?";excuteUpdate(sql, stu.getName(),stu.getPassword(),stu.getId());}@Overridepublic void delete(Student stu) {String sql = "delete from student where id = ?";excuteUpdate(sql, stu.getId());}
}

JDBCTest

	@Testpublic void addStudent() throws Exception {StudentDaoImpl studentDaoImpl = new StudentDaoImpl();Student stu = new Student();stu.setName("小波波");stu.setPassword("857857");stu.setId(254172);
//		studentDaoImpl.insert(stu);
//		studentDaoImpl.delete(stu);studentDaoImpl.update(stu);}