分頁(yè)改鲫、事務(wù)

列表分頁(yè)

1.將數(shù)據(jù)列表和當(dāng)前頁(yè)士骤、每頁(yè)大小稻据、總共頁(yè)數(shù)放在一個(gè)JavaBean中

public class PageBean<T> {
    private List<T> list;
    private int currentPage;
    private int pageSize;
    private int totalPage;

    public PageBean() {
    }

    public PageBean(List<T> list, int currentPage, int pageSize, int totalPage) {
        this.list = list;
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        this.totalPage = totalPage;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
}

2.修改DAO層實(shí)例中的findByDept()方法芬萍，返回PageBean對(duì)象

    public PageBean<Emp> findByDept(Dept dept, int page, int size) {
        ResultSet rs = DbSessionFactory.openSession().executeQuery(
                "select * from tb_emp where dno=? limit ?,?", 
                dept.getId(), (page - 1) * size, size);
        try {
            List<Emp> list = handleResultSet(rs, dept);
            rs = DbSessionFactory.openSession().executeQuery( 
                    "select count(eno) from tb_emp where dno=?", 
                    dept.getId());
            int total = rs.next() ? rs.getInt(1) : 0;
            int totalPage = total % size == 0 ? total / size : total / size + 1;
            return new PageBean<>(list, page, size, totalPage);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DbException("處理結(jié)果集時(shí)發(fā)生異常", e);
        }
    }

3.修改業(yè)務(wù)層biz實(shí)例的getEmpsByDeptId()方法代碼

(1)先根據(jù)部門(mén)ID得到部門(mén)對(duì)象
(2)根據(jù)部門(mén)對(duì)象執(zhí)行Dao層findByDept()方法得到pageBean對(duì)象

    public PageBean<Emp> getEmpsByDeptId(int deptId, int page, int size) {
        DbSessionFactory.openSession();
        Dept dept = deptdao.findById(deptId);
        PageBean<Emp> pageBean = dept != null ? 
                empdao.findByDept(dept, page, size) : null;
        DbSessionFactory.closeSession();
        return pageBean;
    }

4.創(chuàng)建serverlet，重寫(xiě)service()方法

(1)將每頁(yè)個(gè)數(shù)設(shè)置成常數(shù)
(2)根據(jù)req對(duì)象拿到部門(mén)id字符串宙橱，若不為空姨俩，用Integer.parseInt()轉(zhuǎn)換成int類(lèi)型
(3)創(chuàng)建業(yè)務(wù)EmpService對(duì)象
(4)根據(jù)req拿到page字符串，定義int page = 1养匈，判斷page是否為空哼勇，不為空 將page字符串轉(zhuǎn)為int類(lèi)型賦值給page
(5)用service對(duì)象調(diào)用getEmpsByDeptId()方法得到PageBean對(duì)象
(6)綁定數(shù)據(jù)
(7)跳轉(zhuǎn)

@WebServlet(urlPatterns="/show_emp_list.do", loadOnStartup=1)
public class ShowEmpListServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private static final int DEFAULT_PAGE_SIZE = 3;

    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String idStr = req.getParameter("id");
        if (idStr != null) {
            int deptId = Integer.parseInt(idStr);
            EmpService service = new EmpServiceImpl();
            String pageStr = req.getParameter("page");
            int page = 1;
            if (pageStr != null) {
                page = Integer.parseInt(pageStr);
            }
            PageBean<Emp> pageBean = service.getEmpsByDeptId(deptId, page, DEFAULT_PAGE_SIZE);
            req.setAttribute("id", idStr);
            req.setAttribute("deptEmpList", pageBean.getList());
            req.setAttribute("currentPage", pageBean.getCurrentPage());
            req.setAttribute("totalPage", pageBean.getTotalPage());
            req.getRequestDispatcher("emp_deptid.jsp").forward(req, resp);
        }
    }

}

5.jsp代碼

        <div class="center">
            <a href="show_emp_list.do?id=${id}&page=1">首頁(yè)</a>
            <c:if test="${currentPage > 1}">
            <a href="show_emp_list.do?id=${id}&page=${currentPage - 1}">上一頁(yè)</a>
            </c:if>
            <c:if test="${currentPage < totalPage}">
            <a href="show_emp_list.do?id=${id}&page=${currentPage + 1}">下一頁(yè)</a>
            </c:if>
            <a href="show_emp_list.do?id=${id}&page=${totalPage}">末頁(yè)</a>
        </div>

事務(wù)

事務(wù)（transaction） - ACID

A - atomic - 原子性 所有操作要么一起成功，要么一起失敗呕乎，一組不可分割的操作
C - consistency - 一致性 事務(wù)前后對(duì)象狀態(tài)是一致的
I - isolation - 隔離性 兩個(gè)不同的事務(wù) 不要看到對(duì)方的中間狀態(tài)
D - duration - 持久性

1.事務(wù)的邊界不在持久層，在業(yè)務(wù)層
2.通過(guò)ThreadLocal類(lèi)將線程綁定資源
3.用工廠來(lái)創(chuàng)建對(duì)象

案例：銀行轉(zhuǎn)賬

public class AccountTest {
    
    public static void main(String[] args) {
        Connection con = DbUtil.getConnection();
        try {
            // 設(shè)置不要自動(dòng)提交事務(wù)(開(kāi)啟事務(wù)環(huán)境)
            con.setAutoCommit(false);
            int affectedRows = DbUtil.executeUpdate(con, 
                    "update tb_account set balance=? where accid=?", 
                    900, "1234");
            if (affectedRows == 1) {
                System.out.println(1 / 0);
                DbUtil.executeUpdate(con, 
                        "update tb_account set balance=? where accid=?",
                        1100, "4321");
            }
            // 如果所有操作全部成功就手動(dòng)提交事務(wù)
            con.commit();
            System.out.println("轉(zhuǎn)賬操作已經(jīng)完成!");
        } catch (Exception e) {
            try {
                System.out.println("轉(zhuǎn)賬操作未能完成!");
                // 如果發(fā)生了異常狀況就回滾事務(wù)(撤銷(xiāo))
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DbUtil.closeConnection(con);            
        }
    }
}

優(yōu)化持久層

1.將原本工具類(lèi)DbUtil改名為DbConfig陨晶，分離數(shù)據(jù)庫(kù)查改操作猬仁，增加關(guān)閉sql語(yǔ)句和關(guān)閉ResultSet結(jié)果集的方法

public final class DbConfig {
    private static final String JDBC_DRV = "com.mysql.jdbc.Driver"; 
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/hr?useUnicode=true&characterEncoding=utf-8";
    private static final String JDBC_UID = "root";
    private static final String JDBC_PWD = "123456";
    
    static {
        try {
            Class.forName(JDBC_DRV);
        } catch (ClassNotFoundException e) {
            throw new DbException("加載數(shù)據(jù)庫(kù)驅(qū)動(dòng)失敗", e);
        }
    }
    
    private DbConfig() {
        throw new AssertionError();
    }
    
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(JDBC_URL, JDBC_UID, JDBC_PWD);
        } catch (SQLException e) {
            throw new DbException("創(chuàng)建數(shù)據(jù)庫(kù)連接失敗", e);
        }
    }
    
    public static void closeConnection(Connection con) {
        try {
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException e) {
            throw new DbException("關(guān)閉數(shù)據(jù)庫(kù)連接失敗", e);
        }
    }
    
    public static void closeStatement(Statement stmt) {
        try {
            if (stmt != null && !stmt.isClosed()) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DbException("關(guān)閉語(yǔ)句失敗", e);
        }
    }
    
    public static void closeResultSet(ResultSet rs) {
        try {
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DbException("關(guān)閉結(jié)果集失敗", e);
        }
    }
    
}

2.將用戶(hù)進(jìn)行數(shù)據(jù)庫(kù)相關(guān)業(yè)務(wù)的所有操作統(tǒng)一定義為一個(gè)會(huì)話(huà)

(1)實(shí)現(xiàn)打開(kāi)和關(guān)閉會(huì)話(huà)的方法
(2)提供事務(wù)管理的方法

• 開(kāi)始事務(wù)
• 提交事務(wù)
• 回滾事務(wù)

(3)數(shù)據(jù)庫(kù)的更新和查詢(xún)方法

public class DbSession {
    private Connection con;
    
    public void open() {
        if (con == null) {
            con = DbConfig.getConnection();
        }
    }
    
    public void close() {
        DbConfig.closeConnection(con);
        con = null;
    }
    
    public void beginTx() {
        try {
            if (con != null && !con.isClosed()) {
                con.setAutoCommit(false);
            }
        } catch (SQLException e) {
            throw new DbException("開(kāi)啟事務(wù)失敗", e);
        }
    }
    
    public void commitTx() {
        try {
            if (con != null && !con.isClosed()) {
                con.commit();
            }
        } catch (SQLException e) {
            throw new DbException("提交事務(wù)失敗", e);
        }
    }
    
    public void rollbackTx() {
        try {
            if (con != null && !con.isClosed()) {
                con.rollback();
            }
        } catch (SQLException e) {
            throw new DbException("回滾事務(wù)失敗", e);
        }
    }
    
    public int executeUpdate(String sql, Object... params) {
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                stmt.setObject(i + 1, params[i]);
            }
            return stmt.executeUpdate();
        } catch (SQLException e) {
            throw new DbException("執(zhí)行SQL語(yǔ)句時(shí)出錯(cuò)", e);
        }
    }
    
    public ResultSet executeQuery(String sql, Object... params) {
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                stmt.setObject(i + 1, params[i]);
            }
            return stmt.executeQuery();
        } catch (SQLException e) {
            throw new DbException("執(zhí)行SQL語(yǔ)句時(shí)出錯(cuò)", e);
        }       
    }
}

3.實(shí)現(xiàn)會(huì)話(huà)工廠帝璧，不再是自己創(chuàng)建對(duì)象，自己去管理對(duì)象的其他操作湿刽，而是用一個(gè)工廠來(lái)管理對(duì)象的烁。需要使用對(duì)象就從工廠中拿

(1)因?yàn)橛脩?hù)對(duì)數(shù)據(jù)庫(kù)操作只需要使用一個(gè)connection對(duì)象連接數(shù)據(jù)庫(kù)，所以最好通過(guò)ThreadLocal類(lèi)將線程綁定資源诈闺，這樣不會(huì)造成重復(fù)創(chuàng)建資源
(2)提供打開(kāi)會(huì)話(huà)和關(guān)閉會(huì)話(huà)的靜態(tài)方法

public class DbSessionFactory {
    // 把資源跟線程綁定起來(lái)
    private static ThreadLocal<DbSession> threadLocal = new ThreadLocal<>();
    
    public static DbSession openSession() {
        DbSession session = threadLocal.get();
        if (session == null) {
            session = new DbSession();
            threadLocal.set(session);
        }
        session.open();
        return session;
    }
    
    public static void closeSession() {
        DbSession session = threadLocal.get();
        if (session != null) {
            session.close();
            threadLocal.set(null);
        }
    }
}

4.修改DAO層實(shí)例中方法渴庆，不用再在方法里創(chuàng)建connection對(duì)象連接和關(guān)閉，使用DbSessionFactory.openSession()得到會(huì)話(huà)對(duì)象雅镊，然后進(jìn)行相關(guān)數(shù)據(jù)庫(kù)操作

    public boolean update(Dept dept) {
        return DbSessionFactory.openSession().executeUpdate(
                "update tb_dept set dname=?, dloc=? where dno=?", 
                dept.getName(), dept.getLoc(), dept.getId()) == 1;
    }

5.修改業(yè)務(wù)層

如果要進(jìn)行多表復(fù)雜的增刪改操作一般代碼格式為getAllDepts()方法襟雷。
查詢(xún)數(shù)據(jù)庫(kù)不用進(jìn)行事務(wù)管理，一般只需要先打開(kāi)會(huì)話(huà)仁烹，操作完成耸弄，關(guān)閉會(huì)話(huà)

public class DeptServiceImpl implements DeptService {
    private DeptDao deptDao = new DeptDaoDbImpl();

    @Override
    public List<Dept> getAllDepts() {
        DbSession session = DbSessionFactory.openSession();
        List<Dept> list = null;
        try {
            session.beginTx();
            list = deptDao.findAll();
            session.commitTx();
        } catch (Exception e) {
            session.rollbackTx();
            e.printStackTrace();
        } finally {
            DbSessionFactory.closeSession();
        }
        return list;
    }

    @Override
    public boolean editDept(Dept dept) {
        DbSessionFactory.openSession();
        boolean flag = deptDao.update(dept);
        DbSessionFactory.closeSession();
        return flag;
    }
}