第七週總結

​ 這周主要是對JDBC的學習，是一套通用的java連線資料庫的標準，用於遮蔽資料庫之間的差異，使得程式設計師可以專注於處理資料而不需要考慮資料庫之間的差異。

入門案例

第一個JDBC程式:

實現新增一條固定的資料到表格中

// 1.載入資料庫驅動
Class.forName("com.mysql.jdbc.Driver");

// 2.獲取資料庫連線物件
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

// 3.獲取處理sql語句的物件
Statement stat = conn.createStatement();

// 4.執行sql(當執行結果為resultset（執行select操作時）時返回值為true，其他任何時候都是false)
boolean f = stat.execute("insert into user(username,password,nickname) values('softeem','soft123','軟帝')");

// 5.處理結果
System.out.println("執行結果："+f);

// 6.關閉資源
stat.close();
conn.close();

JDBC常見類與介面

DriverManager

DriverManager是java.sql包中提供的一個用於進行資料庫驅動管理的工具類，對於驅動的註冊，以及連線的獲取都可以通過DriverManager來完成，另外從jdbc4開始，不再需要顯示的使用Class.forName("com.mysql.jdbc.Driver")載入驅動；因為DriverManager會自動尋找驅動包裡面的以下檔案:

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-nO0eKKCE-1607845200064)(E:\Java學習\Java學習\20201209\筆記\assets\1607485243871.png)]

DriverManager提供的常見方法：

• getConnection(String url,String user,String password)

• getConnection(String url)

  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?user=root&password=123456");
  

• getConnection(String url,Properties prop)

  Properties info = new Properties();
  info.put("user","root");
  info.put("password","123456");
  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",info);
  

Connection

Connection是JDBC中的核心介面位於java.sql包，用於實現應用程式和資料庫管理系統（DBMS）進行通訊一個通道，通過該介面可以向資料庫傳送可以執行sql指令碼，以及將執行之後的結果返回到程式中。connection即資料庫與應用程式之間的一個會話(session)

常用方法：

• createStatement() :獲取一個用於執行sql語句的執行物件，主要用於執行靜態的sql（存在SQL隱碼攻擊風險）
• parepareStatement(String sql) :獲取一個預處理sql語句的執行物件，預先對sql語句編譯，一般用於執行動態sql（解決SQL隱碼攻擊風險）
• prepareCall(String sql)：獲取一個用於預處理sql儲存過程的執行物件，主要用於執行儲存過程（procedure）
• setAutoCommit(boolean b)：設定事物是否自動提交
• commit()：提交事務
• rollback()：事物回滾
• getMetaData(): 獲取資料庫的後設資料物件（DatabaseMetaData），包含了資料庫的詳細資訊

Statement與PreparedStatement

Statement

Statement是來自java.sql包中用於執行sql語句的處理介面物件，該介面宣告瞭一些用於執行sql語句的相關方法，常見如下：

• execute(String sql)：用於執行任何的sql語句
• executeQuery(String sql)：主要用於執行sql查詢(Select)操作
• executeUpdate(String sql)：主要用於執行sql更新(insert,update,delete)操作以及DDL操作
• addBatch(String sql)：用於將sql語句新增到當前Statement物件的命令列表中用於批量執行sql
• executeBatch()：用於將一批（多條sql）sql命令同時執行
• getResultSet()：獲取執行當前SQL語句之後的結果集物件（ResultSet）
• getGeneratedKeys()：獲取自動生成的鍵所在結果集

具體使用如下:

public static void insert(String name, String pwd, String nickname) throws ClassNotFoundException, SQLException {
    //準備sql語句
    String sql = "insert into user(username,password,nickname) values('" + name + "','" + pwd + "','" + nickname + "')";
    // 1.載入驅動
    Class.forName("com.mysql.jdbc.Driver");
    // 2.獲取連線
    Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
    // 3.獲取處理sql語句的執行物件
    Statement stat = conn.createStatement();
    // 4.執行sql語句
    boolean b = stat.execute(sql);
    // 5.處理結果
    System.out.println("是否有ResultSet：" + b);
    // 6.關閉資源
    stat.close();
    conn.close();
}

對以上程式在main方法中執行

public static void main(String[] args){
     insert("rose","rose123","肉絲");
}

通過對以上程式碼的觀察發現，如果需要實現動態的資料操作，則需要對sql語句進行字串拼接，使得sql語句編寫起來比較麻煩，並且使用過程中存在SQL隱碼攻擊的風險：

SQL隱碼攻擊

觀察以下程式碼

public static boolean login(String username, String password) throws ClassNotFoundException, SQLException {
    String sql = "select * from user where username='" + username + "' and password='" + password + "'";
    Connection conn = null;
    Statement stat = null;
    try {
        conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
        stat = conn.createStatement();
        //執行查詢獲取結果集
        ResultSet rs = stat.executeQuery(sql);
        //判斷結果集中是否存在可用結果行
        if (rs.next()) {
            return true;
        }
    } finally {
        conn.close();
        stat.close();
    }
    return false;
}

若呼叫該方法時按如下格式提供方法引數值：

//select * from user where username='asdfasdf' and password='' or '1'='1'
boolean b = login("asdfasdf", "' or '1'='1 ");
System.out.println(b ? "登入成功" : "登入失敗");

分析得知，無論賬號如何提供，子需要密碼按照第二個引數的寫法，即可繞過登入過程直接輸出如下結果:

登入成功

因為使用Statement不僅會存在字串拼接的複雜問題，還會導致SQL隱碼攻擊的風險；因此JDBC中針對該問題從Statement介面還擴充套件了一個子介面java.sql.PraparedStatement用於解決以上問題:

PreparedStatement

PreparedStatement是從Statement介面繼承而來，使用該介面可以有效的避免sql注入以及字串拼接的問題:

public class JDBCDemo3 {

    public static boolean login(String username,String password) throws SQLException {
        //程式碼移動 alt+shift+up/down
        String sql = "select * from user where username=? and password=?";
        ResultSet rs = null;
        //JDK1.7新增
        try(
            Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
            //獲取預編譯sql的物件（已經完成的sql語句的預編譯）
            PreparedStatement ps = conn.prepareStatement(sql);
        ){
            //預處理(向sql語句指定位置的佔位符填充實際值)
            ps.setString(1,username);
            ps.setString(2,password);
            //執行
            rs = ps.executeQuery();
            if(rs.next()){
                return true;
            }
        }finally{
            rs.close();
        }
        return false;
    }

    public static void main(String[] args) throws SQLException {
        boolean b = login("admin", "123456");
        System.out.println(b ? "登入成功" : "登入失敗");
    }
}

ResultSet

結果集物件，用於表示對於查詢語句執行之後的結果，內部的資料可能是一條也可以是多條

ResultSet常見方法：

• boolean next() ： 判斷是否有下一個結果行
• getXXX(int index)：根據列索引獲取列值
• getXXX(String columnLabel)：根據列標籤（預設列名）獲取值
• ResultSetMetaData getMetaData()：返回結果集的後設資料

ResultSet的基本使用：

String sql = "select * from user";
//獲取連線
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
//獲取執行sql語句的物件
PreparedStatement ps = conn.prepareStatement(sql);
//執行查詢
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    //根據列索引獲取當前列值				 // 根據列標籤獲取指定列值
    int uid = rs.getInt(1);             // rs.getInt("id")
    String username = rs.getString(2);  // rs.getString("username")
    String password = rs.getString(3);  // rs.getString("password")
    int status = rs.getInt(4);          // rs.getInt("status")
    String nickname = rs.getString(5);  // rs.getString("nickname")

    System.out.println(uid + "/" + username + "/" + password + "/" + status + "/" + nickname);
}
rs.close();
ps.close();
conn.close();

CRUD操作

/**
* 新增資料
*/
public static void insert(String name, String pwd, String nickname) throws ClassNotFoundException, SQLException {
    //準備sql語句
    String sql = "insert into user(username,password,nickname) values(?,?,?)";
    // 1.載入驅動
    Class.forName("com.mysql.jdbc.Driver");
    // 2.獲取連線
    Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
    // 3.獲取處理sql語句的執行物件
    PreparedStatement ps = conn.prepareStatement();
    //預處理
    ps.setString(1,name);
    ps.setString(2,pwd);
    ps.setString(3,nickname);
    // 4.執行sql語句
    int i = stat.executeUpdate();
    // 5.處理結果
    System.out.println("受影響程式碼行：" + i);
    // 6.關閉資源
    stat.close();
    conn.close();
}

/**
 * 根據提供的使用者id刪除使用者
 */
public static boolean delete(int id) throws SQLException {
    String sql = "delete from user where id=?";
    try(
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
        //獲取預編譯sql的物件（已經完成的sql語句的預編譯）
        PreparedStatement ps = conn.prepareStatement(sql);
    ){
        ps.setInt(1,id);
        return ps.executeUpdate() > 0;
    }
}

/**
* 根據提供的使用者id修改使用者密碼為指定值
*/
public static boolean update(int id,String newPwd) throws SQLException {
    String sql = "update user set password=? where id=?";
    try(
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
        PreparedStatement ps = conn.prepareStatement(sql);
    ){
        ps.setString(1,newPwd);
        ps.setInt(2,id);
        return ps.executeUpdate() > 0;
    }
}

/**
* 根據id查詢資料
* @param id
* @throws SQLException
 */
public static void queryById(int id) throws SQLException {
    String sql = "select * from user where id=?";
    //獲取連線
    Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
    //獲取執行sql語句的物件
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setInt(1, id);
    //執行查詢
    ResultSet rs = ps.executeQuery();
    if (rs.next()) {
        int uid = rs.getInt(1);             // rs.getInt("id")
        String username = rs.getString(2);  // rs.getString("username")
        String password = rs.getString(3);  // rs.getString("password")
        int status = rs.getInt(4);          // rs.getInt("status")
        String nickname = rs.getString(5);  // rs.getString("nickname")

        System.out.println(uid + "/" + username + "/" + password + "/" + status + "/" + nickname);
    }
    rs.close();
    ps.close();
    conn.close();
}

JDBC封裝1.0

JDBC操作常見的六個步驟中存在大量的重複操作，通過以下封裝可以實現對其中三個步驟完成統一封裝處理：

1. 載入驅動
2. 獲取連線
3. 獲取執行sql語句的物件
4. 執行
5. 處理結果
6. 關閉資源

/**
 * @Author mrchai 2020/12/10 9:16
 */
public class DBUtils {

    /**驅動類路徑*/
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    /**URL地址*/
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    /**登入資料庫伺服器的賬號*/
    private static final String USER = "root";
    /**登入資料庫伺服器的密碼*/
    private static final String PASSWORD = "123456";

    static{
        try {
            // 1.載入驅動
            Class.forName(DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 返回資料庫連線物件
     * @return
     */
    public static Connection getConn(){
        try {
            return DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 關閉資源
     * @param rs    結果集
     * @param stat  處理SQL的執行物件
     * @param conn  資料庫連線
     */
    public static void close(ResultSet rs, Statement stat,Connection conn){
        //ctrl+alt+t
        try {
            if(rs != null){
                rs.close();
            }
            if(stat != null){
                stat.close();
            }
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
}

使用以上封裝完成基本的查詢操作：

String sql = "select * from user";
//通過工具類獲取連線
Connection conn = DBUtils.getConn();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    int id = rs.getInt("id");
    String username = rs.getString("username");
    String password = rs.getString("password");
    String nickname = rs.getString("nickname");
    int status = rs.getInt("status");
    System.out.println(id + "/" + username + "/" + password + "/" + nickname + "/" + status);
}
//使用工具類關閉資源
DBUtils.close(rs,ps,conn);

JDBC查詢詳解

單行單列查詢

/**
 * 查詢單行單列資料
 * @Author mrchai 2020/12/10 10:25
 */
public class Query01 {

    public static void main(String[] args) throws SQLException {
        String sql = "select count(*) as num from user";
        //獲取連線
        Connection conn = DBUtils.getConn();
        //獲取預處理sql的物件
        PreparedStatement ps = conn.prepareStatement(sql);
        //執行查詢
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            int count = rs.getInt("num");
            //int count = rs.getInt(1);
            System.out.println("總資料條數："+count);
        }
        //關閉資源
        DBUtils.close(rs,ps,conn);
    }
}

分頁查詢

/**
 * @Author mrchai 2020/12/10 10:30
 */
public class Query02 {

    /**
     *
     * @param offset  查詢偏移量（起始查詢的資料位置）
     * @param limit   查詢結果集限制行（每頁最大顯示資料行）
     */
    public static List<User> queryLimit(int offset, int limit) throws SQLException {
        List<User> list = new ArrayList<>();
        //引數1：開始查詢的位置
        //引數2：查詢的資料條數
        String sql = "select * from user limit ?,?";
        Connection conn = DBUtils.getConn();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,offset);
        ps.setInt(2,limit);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String nickname = rs.getString("nickname");
            int status = rs.getInt("status");
            int gid = rs.getInt("gid");
            //每一次迴圈，產生一個user物件
            list.add(new User(id,username,password,status,nickname,gid));
        }
        DBUtils.close(rs,ps,conn);
        return list;
    }

    public static void main(String[] args) throws SQLException {
        //真分頁（select * from XXX limit ?,?） ：物理分頁，從資料庫查詢多少條顯示多少條
        //假分頁（select * from XXX）：邏輯分頁，一次性將資料全部從資料庫查詢出來，然後再記憶體中通過subList擷取部分顯示

        //當前頁碼
        int pageNow = 1;
        //每頁資料條數
        int pageSize = 10;
        //計算起始查詢位置
        int offset = (pageNow - 1) * pageSize;
        List<User> users = queryLimit(offset, pageSize);
        //遍歷輸出結果
        users.forEach(u-> System.out.println(u));
    }
}

多表聯合查詢

public class Query03 {

    public static void main(String[] args) throws SQLException {

        String sql = "select " +
            "u.id _uid," +
            "u.username _username," +
            "u.password _password," +
            "u.nickname _nickname," +
            "u.status _status," +
            "g.id _gid," +
            "g.gname _gname " +
            "from user u left join groups g on u.gid=g.id";

        Connection conn = DBUtils.getConn();
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int uid = rs.getInt("_uid");
            String username = rs.getString("_username");
            String password = rs.getString("_password");
            String nickname = rs.getString("_nickname");
            int status = rs.getInt("_status");
            int gid = rs.getInt("_gid");
            String gname = rs.getString("_gname");
            System.out.println(uid + "/" + username + "/" + password + "/" + nickname + "/" + status + "/" + gid + "/" + gname);
        }
        DBUtils.close(rs,ps,conn);
    }
}

DAO與DTO(Entity,VO,PO,POJO)

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-6CCyAHnb-1607845200069)(D:\帶班資料\2020\j2009\j2009課程資料\part5-資料庫與JDBC\20201210\筆記\assets\1607648152774.png)]

​ 以上時序圖是常見的web專案架構模式，其中對於頻繁的資料訪問操作，實際開發中通常會抽取一層DAO(Data Access Object)層;DAO層主要負責訪問資料庫，對資料表進行CRUD操作，DAO層並不關注具體的業務邏輯，因此稱之為資料訪問層。

常見DAO層的定義語法:

public class EmpDAO{

	public boolean insert(Emp d) {
		// 執行新增操作
		return false;
	}

	public boolean delete(int id) {
		// 執行刪除操作
		return false;
	}

	public boolean update(Emp d) {
		// 執行更新操作
		return false;
	}

	public Emp findById(int id) {
		// 根據id查詢
		return null;
	}
    	
    public List<Emp> findByAll(int id) {
		// 查詢所有
		return null;
	}
}

JDBC封裝2.0 - 封裝通用的更新操作

觀察以下操作相關程式碼：

/**
     * ctrl + shift + -/+ 語句塊展開收縮
     * 使用者新增
     * @param user
     * @return
     */
public boolean insert(User user){
    String sql = "insert into user_info(uname,upass,sex,birth,phone) values(?,?,?,?,?)";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBUtils.getConn();
        ps = conn.prepareStatement(sql);
        ps.setString(1,user.getUname());
        ps.setString(2,user.getUpass());
        ps.setString(3,user.getSex());
        ps.setDate(4,user.getBirth());
        ps.setString(5,user.getPhone());
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

/**
     * 根據提供使用者資訊修改使用者密碼
     * @param user
     * @return
     */
public boolean updatePwd(User user){
    String sql = "update user_info set upass=? where uid=?";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBUtils.getConn();
        ps = conn.prepareStatement(sql);
        ps.setString(1,user.getUpass());
        ps.setInt(2,user.getUid());
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

/**
     * 根據提供的使用者資訊修改使用者狀態
     * @param user
     * @return
     */
public boolean updateStatus(User user){
    String sql = "update user_info set status=? where uid=?";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBUtils.getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1,user.getStatus());
        ps.setInt(2,user.getUid());
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

/**
     * 根據id刪除使用者
     * @param uid
     * @return
     */
public boolean deleteByUid(int uid){
    String sql = "delete from user_info where uid=?";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBUtils.getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1,uid);
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

以上程式碼都是實現對於資料庫的更新操作，其中所有功能的實現，除了SQL語句不一樣（對應預處理時需要指定的引數隨之變化）之外，其餘程式碼基本是相同的，因此以上所有的增刪改（insert,update,delte）功能可以通過一個封裝方法統一完成:

在DBUtils.java中新增以下方法:

/**
     * 封裝通用的更新操作（即通過該方法實現對於任意資料表的insert,update,delete操作）
     * @param sql       需要被執行sql語句
     * @param params    執行sql語句時需要傳遞進去引數
     * @return          執行結果
     */
public static boolean exeUpdate(String sql,Object... params){
    Connection conn = getConn();
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sql);
        //當傳入的引數不為null時執行預處理
        if(Objects.nonNull(params)){
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1,params[i]);
            }
        }
        //執行更新
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    } finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

通過對以上程式碼呼叫，之前的程式可以優化為以下程式碼：

/**
     * ctrl + shift + -/+ 語句塊展開收縮
     * 使用者新增
     *
     * @param user
     * @return
     */
public boolean insert(User user) {
    String sql = "insert into user_info(uname,upass,sex,birth,phone) values(?,?,?,?,?)";
    return DBUtils.exeUpdate(sql, user.getUname(), user.getUpass(), user.getSex(), user.getBirth(), user.getPhone());
}

/**
     * 根據提供使用者資訊修改使用者密碼
     *
     * @param user
     * @return
     */
public boolean updatePwd(User user) {
    String sql = "update user_info set upass=? where uid=?";
    return DBUtils.exeUpdate(sql, user.getUpass(), user.getUid());
}

/**
     * 根據提供的使用者資訊修改使用者狀態
     *
     * @param user
     * @return
     */
public boolean updateStatus(User user) {
    String sql = "update user_info set status=? where uid=?";
    return DBUtils.exeUpdate(sql, user.getStatus(), user.getUid());
}

/**
     * 根據id刪除使用者
     *
     * @param uid
     * @return
     */
public boolean deleteByUid(int uid) {
    return DBUtils.exeUpdate("delete from user_info where uid=?", uid);
}

程式碼量瞬間減少，世界如此清淨！！！

getGeneratedKeys獲取自動生成鍵

​ 在實際開發中，可能會遇到如下需求：比如商品新增的功能，同時需要涉及到商品圖片的上傳，此時商品資訊和圖片資訊通常會儲存在不同的資料表中，而圖片表中的商品圖片應該要關聯商品表的商品ID，由於資料庫商品表中商品id列可設定為自動遞增，此時需要為商品圖片指定所關聯的商品id，因此JDBC中提供了一個功能，允許在新增資料時獲取自動生成的鍵：

/**
     * 向資料庫插入資料的同時，獲取自動為當前資料行生成的鍵值（前提是主鍵列是自增列）
     *
     * @param user
     * @return   返回自動生成的鍵
     */
public int insertAndGetKeys(User user) {
    String sql = "insert into user_info(uname,upass,sex,birth,phone) values(?,?,?,?,?)";
    Connection conn = DBUtils.getConn();
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        //獲取預處理sql的執行物件，並設定其允許獲取自動生成的鍵
        ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, user.getUname());
        ps.setString(2, user.getUpass());
        ps.setString(3, user.getSex());
        ps.setDate(4, user.getBirth());
        ps.setString(5, user.getPhone());
        int i = ps.executeUpdate();
        if (i > 0) {
            //獲取自動生成的鍵
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBUtils.close(rs, ps, conn);
    }
    return -1;
}

SQL語句批處理操作（1）

如何同時執行多條不同型別的sql語句？

public class BatchDemo {

    public static void main(String[] args) throws SQLException {

        Connection conn = DBUtils.getConn();
        Statement stat = conn.createStatement();
        //新增sql語句到Statement執行佇列中
        stat.addBatch("create table tbinfo(id int primary key auto_increment,msg varchar(30) not null)");
        stat.addBatch("insert into tbinfo(msg) values('資料新增')");
        stat.addBatch("insert into tbinfo(msg) values('資料新增')");
        stat.addBatch("update tbinfo set msg='資料更新' where id=1");
        //執行批量操作，返回每一條sql語句執行影響的資料行數
        int[] results = stat.executeBatch();
        for(int i:results){
            System.out.println(i);
        }

    }
}

JDBC高階

JDBC批處理（2）

在實際開發需求中，經常會涉及到將一張excel檔案中的所有資料匯入到資料庫的需求，此時在JDBC中就必然會執行多次新增操作，傳統的方式可以對excel檔案中的每一條資料進行讀取，然後沒讀取一次執行一次insert操作，但是這種效率十分低，因此JDBC中提供了對於資料批量新增的功能，通過批處理功能可以實現高效的資料匯入，如下:

public class BatchDemo2 {

    //傳統的新增方式：迴圈新增
    public static void add1() throws SQLException {
        for (int i = 0; i < 5000; i++) {
            String sql = "insert into tbinfo(msg) values(?)";
            Connection conn = DBUtils.getConn();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "資料新增" + i);
            DBUtils.close(null, ps, conn);
        }
    }

    //批處理方式
    public static void add2() throws SQLException {
        String sql = "insert into tbinfo(msg) values(?)";
        Connection conn = DBUtils.getConn();
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < 5000; i++) {
            ps.setString(1, "資料新增" + i);
            ps.addBatch();
            if (i % 10 == 0) {
                ps.clearBatch();
            }
        }
        int[] r = ps.executeBatch();
        DBUtils.close(null, ps, conn);
    }

    public static void main(String[] args) throws SQLException {
        long start = System.currentTimeMillis();
        //add1();
        add2();
        long end = System.currentTimeMillis();
        System.out.println("耗時：" + (end - start));
    }
}

結果：

// 耗時：6917  //非批處理方式
耗時：469		//批處理

JDBC事務操作

事務即在對資料庫表中資料同時進行多次更新操作時，要麼同時成功，要麼同時失敗，這一整個邏輯操作稱之為事務，事務的四大特徵（ACID）:

• 原子性
• 一致性
• 隔離性
• 永續性

在沒有引入事務時可能出現以下問題：

public class TxDemo {

    /**
     * 從指定賬號向另一個賬號轉出指定金額
     * @param a 賬號a
     * @param b 賬號b
     */
    public static void change(int a, int b){
        String sql = "update account set money=money-1000 where id=?";
        //減少
        DBUtils.exeUpdate(sql,a);
        //丟擲一個算術異常
        System.out.println(10/0);
        //增加
        sql = "update account set money=money+1000 where id=?";
        DBUtils.exeUpdate(sql,b);
    }

    public static void main(String[] args) {
        change(1,2);
    }
}

以上程式為一個簡易的轉賬邏輯，最終結果會導致一個賬號中金額減少，但是另一個賬號中金額沒有增加，從而最終結果不一致。

JDBC中使用事務

/**
     * 從指定賬號向另一個賬號轉出指定金額
     */
public static void change(){
    String sql1 = "update account set money=money-1000 where id=1";
    String sql2 = "update account set money=money+1000 where id=2";
    Connection conn = DBUtils.getConn();
    PreparedStatement ps = null;
    try {
        //關閉事務的自動提交
        conn.setAutoCommit(false);
        ps = conn.prepareStatement(sql1);
        int i = ps.executeUpdate();
        //            System.out.println(10/0);
        ps = conn.prepareStatement(sql2);
        int j = ps.executeUpdate();
        if(i > 0 && j > 0){
            //提交事務
            conn.commit();
        }
    } catch (Exception e) {
        e.printStackTrace();
        try {
            //事務回滾
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }finally{
        DBUtils.close(null,ps,conn);
    }
}

DBUtils中新增通用更新過載方法

保證多次更新在同一個事務中的前提是必須使所有更新操作都使用同一個資料庫連線，因此exeUpdate方法中不能每次都獲取一個新的連線，我們可以設定為從外部傳入連線，這樣一來只要呼叫該方法時傳入的是同一個Connection即可進行事務管理：

/**
     * 實現通用更新操作的方法，連線是需要外部提供的，事務可以在外部操作
     * 注意事項：該方法中出現異常必須丟擲不能捕獲，否則一旦出現異常，在當前方法就已經被catch，
     * 從而導致下一級呼叫者無法發現該異常，而導致事務無法回滾
     * @param conn
     * @param sql
     * @param params
     * @return
     */
public static boolean exeUpdate(Connection conn,String sql,Object... params) throws SQLException {
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sql);
        //當傳入的引數不為null時執行預處理
        if(Objects.nonNull(params)){
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1,params[i]);
            }
        }
        //執行更新
        return ps.executeUpdate() > 0;
    } finally{
        DBUtils.close(null,ps,null);
    }
}

使用：

public static void change2(){
    String sql1 = "update account set money=money-1000 where id=1";
    String sql2 = "update account set money=money+1000 where id=2";
    Connection conn = DBUtils.getConn();
    try {
        //關閉事務自動提交
        conn.setAutoCommit(false);
        DBUtils.exeUpdate(conn,sql1);
        //異常程式碼
        System.out.println(10/0);
        DBUtils.exeUpdate(conn,sql2);
        //提交事務
        conn.commit();
    } catch (Exception e) {
        e.printStackTrace();
        try {
            //事務回滾
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }finally{
        DBUtils.close(null,null,conn);
    }
}

資料庫連線池技術

​ 在之前進行任何資料庫操作的時候都必須要有一個可用的資料庫連線物件（Connection）；而這個連線物件的獲取需要通過：DriverManager.getConnection(url,user,password)並且連線的獲取是非常耗時(0.05~1s之間)的操作。因此傳統的連線獲取方式不滿足現在的高併發需求場景的，所以JDBC中引入了一種稱之為連線池(Connection Pool)的技術

​ 連線池的原理即，在程式啟動時，事先開啟並通過容器維護一定量的資料連線，從而為後續的資料持久化功能節省連線獲取的時間開銷，因為一旦dao需要使用連線時，只需要從連線池中直接獲取即可（類似從一個集合中取一個元素），並且使用完畢之後只需將連線歸還到連線池中即可，從程式的訪問效率上得到極大提升，因此，連線池技術，是服務端技術的重要組成部分。

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-2udNbu6x-1607845200074)(D:\帶班資料\2020\j2009\j2009課程資料\part5-資料庫與JDBC\20201211\筆記\assets\1607657203007.png)]

目前市面上提供了各種豐富的連線池解決方案：

• DBCP/DBCP2
• C3P0
• Proxool
• Druid(德魯伊：alibaba)
• HikraCP(SpringBoot推薦)

任何連線池的實現都必須要從javax.sql.DataSource進行實現

使用Druid連線池

1. 新增依賴

   druid-1.1.9.jar

2. 連線池建立並獲取連線

   //建立連線池物件
   DruidDataSource dataSource = new DruidDataSource();
   //設定連線資料庫的基本資訊（url,user,password）
   //        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
   dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
   dataSource.setUsername("root");
   dataSource.setPassword("123456");
   //設定初始連線數
   dataSource.setInitialSize(2);
   //設定最大連線數
   dataSource.setMaxActive(5);
   //設定連線獲取的最長等待時間（如果連線池中連線被耗盡時，等待重新獲取連線的時間超出時會丟擲異常）
   dataSource.setMaxWait(5000);
   //設定最小的閒置連線數
   dataSource.setMinIdle(1);
   
   //獲取連線
   Connection conn1 = dataSource.getConnection();
   

改造DBUtils

public class DBUtils2 {

    /**驅動類路徑*/
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    /**URL地址*/
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    /**登入資料庫伺服器的賬號*/
    private static final String USER = "root";
    /**登入資料庫伺服器的密碼*/
    private static final String PASSWORD = "123456";
    /**初始連線數*/
    private static final int INIT_SIZE = 1;
    /**最大活動連線數*/
    private static final int MAX_ACTIVE = 5;
    /**最長等待連線獲取時間*/
    private static final long MAX_WAIT = 5000;
    /**最小閒置連線數*/
    private static final int MIN_IDLE = 1;

    /**宣告資料來源*/
    private static DruidDataSource dataSource;

    static{
        dataSource = createDataSource();
    }

    /**
     * 建立連線池並返回
     * @return
     */
    private static DruidDataSource createDataSource(){
        //建立連線池物件
        dataSource = new DruidDataSource();
        //設定連線資料庫的基本字串
        dataSource.setUrl(URL);
        dataSource.setUsername(USER);
        dataSource.setPassword(PASSWORD);
        //設定連線池的初始引數
        dataSource.setInitialSize(INIT_SIZE);
        dataSource.setMaxActive(MAX_ACTIVE);
        dataSource.setMaxWait(MAX_WAIT);
        dataSource.setMinIdle(MIN_IDLE);
        return dataSource;
    }

    /**
     * 返回資料庫連線物件
     * @return
     */
    public static Connection getConn(){
        try {
            //當連線池物件為null或者連線池物件被關閉時重新建立連線池
            if(dataSource == null || dataSource.isClosed()){
                dataSource = createDataSource();
            }
            //返回連線
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
}

封裝通用查詢

ResultSetMetaData