分页、事务

列表分页

1.将数据列表和当前页、每页大小、总共页数放在一个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层实例中的findByDept()方法，返回PageBean对象

    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("处理结果集时发生异常", e);
        }
    }

3.修改业务层biz实例的getEmpsByDeptId()方法代码

(1)先根据部门ID得到部门对象
(2)根据部门对象执行Dao层findByDept()方法得到pageBean对象

    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.创建serverlet，重写service()方法

(1)将每页个数设置成常数
(2)根据req对象拿到部门id字符串，若不为空，用Integer.parseInt()转换成int类型
(3)创建业务EmpService对象
(4)根据req拿到page字符串，定义int page = 1，判断page是否为空，不为空 将page字符串转为int类型赋值给page
(5)用service对象调用getEmpsByDeptId()方法得到PageBean对象
(6)绑定数据
(7)跳转

@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">首页</a>
            <c:if test="${currentPage > 1}">
            <a href="show_emp_list.do?id=${id}&page=${currentPage - 1}">上一页</a>
            </c:if>
            <c:if test="${currentPage < totalPage}">
            <a href="show_emp_list.do?id=${id}&page=${currentPage + 1}">下一页</a>
            </c:if>
            <a href="show_emp_list.do?id=${id}&page=${totalPage}">末页</a>
        </div>

事务

事务（transaction） - ACID

A - atomic - 原子性 所有操作要么一起成功，要么一起失败，一组不可分割的操作
C - consistency - 一致性 事务前后对象状态是一致的
I - isolation - 隔离性 两个不同的事务 不要看到对方的中间状态
D - duration - 持久性

1.事务的边界不在持久层，在业务层
2.通过ThreadLocal类将线程绑定资源
3.用工厂来创建对象

案例：银行转账

public class AccountTest {
    
    public static void main(String[] args) {
        Connection con = DbUtil.getConnection();
        try {
            // 设置不要自动提交事务(开启事务环境)
            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");
            }
            // 如果所有操作全部成功就手动提交事务
            con.commit();
            System.out.println("转账操作已经完成!");
        } catch (Exception e) {
            try {
                System.out.println("转账操作未能完成!");
                // 如果发生了异常状况就回滚事务(撤销)
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DbUtil.closeConnection(con);            
        }
    }
}

优化持久层

1.将原本工具类DbUtil改名为DbConfig，分离数据库查改操作，增加关闭sql语句和关闭ResultSet结果集的方法

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("加载数据库驱动失败", 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("创建数据库连接失败", e);
        }
    }
    
    public static void closeConnection(Connection con) {
        try {
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException e) {
            throw new DbException("关闭数据库连接失败", e);
        }
    }
    
    public static void closeStatement(Statement stmt) {
        try {
            if (stmt != null && !stmt.isClosed()) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DbException("关闭语句失败", e);
        }
    }
    
    public static void closeResultSet(ResultSet rs) {
        try {
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DbException("关闭结果集失败", e);
        }
    }
    
}

2.将用户进行数据库相关业务的所有操作统一定义为一个会话

(1)实现打开和关闭会话的方法
(2)提供事务管理的方法

• 开始事务
• 提交事务
• 回滚事务

(3)数据库的更新和查询方法

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("开启事务失败", e);
        }
    }
    
    public void commitTx() {
        try {
            if (con != null && !con.isClosed()) {
                con.commit();
            }
        } catch (SQLException e) {
            throw new DbException("提交事务失败", e);
        }
    }
    
    public void rollbackTx() {
        try {
            if (con != null && !con.isClosed()) {
                con.rollback();
            }
        } catch (SQLException e) {
            throw new DbException("回滚事务失败", 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("执行SQL语句时出错", 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("执行SQL语句时出错", e);
        }       
    }
}

3.实现会话工厂，不再是自己创建对象，自己去管理对象的其他操作，而是用一个工厂来管理对象。需要使用对象就从工厂中拿

(1)因为用户对数据库操作只需要使用一个connection对象连接数据库，所以最好通过ThreadLocal类将线程绑定资源，这样不会造成重复创建资源
(2)提供打开会话和关闭会话的静态方法

public class DbSessionFactory {
    // 把资源跟线程绑定起来
    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层实例中方法，不用再在方法里创建connection对象连接和关闭，使用DbSessionFactory.openSession()得到会话对象，然后进行相关数据库操作

    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.修改业务层

如果要进行多表复杂的增删改操作一般代码格式为getAllDepts()方法。
查询数据库不用进行事务管理，一般只需要先打开会话，操作完成，关闭会话

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;
    }
}