学习日志day42（2021-09-07）（1、实现分页功能 2、导出Excel 3、导入Excel 4、CSRF攻击）

最新推荐文章于 2023-11-09 12:08:15 发布

huzige_

最新推荐文章于 2023-11-09 12:08:15 发布

阅读量128

点赞数

分类专栏：学习日志文章标签： java sql

本文链接：https://blog.csdn.net/huzige_/article/details/120148285

版权

学习日志专栏收录该内容

73 篇文章 2 订阅

订阅专栏

这篇博客记录了作者在JavaWeb学习的第42天的内容，包括如何实现分页功能，使用Java进行Excel的导出和导入，以及对CSRF攻击的介绍和防御策略。

摘要由CSDN通过智能技术生成

学习内容：学习JavaWeb（Day42）

1、实现分页功能
2、导出Excel
3、导入Excel
4、CSRF攻击

1、实现分页功能

（1）创建jsp页面

<div class="container">
    <table class="table table-striped">
        <thead>
        <tr>
            <th>序号</th>
            <th>ID</th>
            <th>书籍名称</th>
            <th>作者</th>
            <th>出版社</th>
        </tr>

        </thead>
        <tbody>
        <c:forEach items="${pageBook.items}" var="book" varStatus="status">
            <tr>
                <td>${status.count}</td>
                <td>${book.id}</td>
                <td>${book.bookname}</td>
                <td>${book.author}</td>
                <td>${book.publisher}</td>
            </tr>
        </c:forEach>

        </tbody>

    </table>
    <nav aria-label="...">
        <ul class="pager">
            <li><a href="/book/list?p=1">首页</a></li>
            <li><a href="/book/list?p=${pageBook.pageNo - 1}">上一页</a></li>
            <c:if test="${pageBook.pageNo == pageBook.totalPage}">
                <li><a href="javascript:void(0)" disabled>下一页</a></li>
            </c:if>
            <c:if test="${pageBook.pageNo < pageBook.totalPage}">
                <li><a href="/book/list?p=${pageBook.pageNo + 1}">下一页</a></li>
            </c:if>
            <li><a href="/book/list?p=${pageBook.totalPage}">尾页</a></li>
        </ul>
    </nav>
</div>

（2）创建page工具类

public class Page<T> {

    private Integer pageSize = 10; //每页显示的条数
    private Integer totalSize; //当前总条数
    private Integer pageNo;  //当前页数
    private Integer start; //起始位置
    private List<T> items; //当前查询的结果集
    private Integer totalPage;

    public Page(Integer pageSize,Integer totalSize,Integer pageNo){
        this.pageSize = pageSize;
        setTotalSize(totalSize);
        setPageNo(pageNo);
    }

    /*
    * totalSize 和pageNo位置不能调换
    * */
    public Page(Integer totalSize,Integer pageNo){
        setTotalSize(totalSize);
        setPageNo(pageNo);
    }

    public Integer getPageSize() {
        return pageSize;
    }

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

    /*
    * 计算总页数
    * */
    public void setTotalSize(Integer totalSize) {
        totalPage = totalSize / pageSize;
        if(totalSize % pageSize != 0){
            totalPage++;
        }
        this.totalSize = totalSize;
    }

    public Integer getTotalPage() {
        return totalPage;
    }


    public Integer getPageNo() {
        return pageNo;
    }

    /**
     * 赋值当前页并计算起始位置
     * @param pageNo
     */
    public void setPageNo(Integer pageNo) {
        if(pageNo < 1){
            pageNo = 1;
        }
        if(pageNo > totalPage){
            pageNo = totalPage;
        }
        start = (pageNo - 1)*pageSize;
        this.pageNo = pageNo;
    }

    public Integer getStart() {
        return start;
    }

    public List<T> getItems() {
        return items;
    }

    public void setItems(List<T> items) {
        this.items = items;
    }
}

（3）创建Dao

public class BookDao {
    private DBHelp dbHelp = new DBHelp();
    public Page<Book> findAll(Integer pageNo,Integer pageSize){
        System.out.println(getTotalSize());
        Integer totalSize = getTotalSize().intValue();
        Page<Book> page = null;
        if(pageSize == null){
            page = new Page<Book>(totalSize,pageNo);
        }
        page = new Page<Book>(pageSize,totalSize,pageNo);
        String sql = "select id,bookname,author,publisher from book limit ?,?";
        List<Book> books = dbHelp.executeQueryForList(sql, new BeanRowMapper<Book>(Book.class), page.getStart(), page.getPageSize());
        page.setItems(books);
        return page;
    }

    public Long getTotalSize(){
        String sql = "select count(*) from book";

        Long result = dbHelp.executeQueryForObject(sql, new RowMapper<Long>() {
            @Override
            public Long mapperRow(ResultSet rs) throws SQLException {
                return rs.getLong(1);
            }
        });
        return result;
    }
}

（3）创建Service

public class BookService {
    private BookDao bookDao = new BookDao();
    public Page<Book> findAll(Integer pageNo){
        return bookDao.findAll(pageNo,null);
    }

    public Page<Book> findByPageSize(Integer pageNo,Integer pageSize){
        return bookDao.findAll(pageNo,pageSize);
    }
}

（4）创建Servlet

@WebServlet("/book/list")
public class BookServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String pageNo = req.getParameter("p");
        String pageSize = req.getParameter("pageSize");
        BookService bookService = new BookService();
        //Page<Book> pageBook = bookService.findAll(Integer.valueOf(pageNo));
        Page<Book> pageBook2 = bookService.findByPageSize(Integer.valueOf(pageNo),Integer.valueOf(pageSize));
        req.setAttribute("pageBook", pageBook2);
        req.getRequestDispatcher("/WEB-INF/views/list.jsp").forward(req, resp);
    }
}

2、导出Excel

（1）Excel的导入和导出需要用到的包
poi.jar
commons-math.jar

（2）创建Dao

public class BookDao {
    private DBHelp dbHelp = new DBHelp();
    public List<Book> findAll(){

        String sql = "select id,bookname,author,publisher from book";
        return dbHelp.executeQueryForList(sql, new BeanRowMapper<Book>(Book.class));

    }
    public void save(Book book) {
        String sql = "insert into book (id,bookname,author,publisher) values(?,?,?,?)";
        dbHelp.executeUpdate(sql,book.getId(),book.getBookname(),book.getAuthor(),book.getPublisher());
    }
}

（2）导出Excel，创建ExportBooksServlet.java

@WebServlet("/export")
public class ExportBooksServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        BookService bookService = new BookService();
        List<Book> bookList = bookService.findAll();
        HSSFWorkbook workBook = new HSSFWorkbook();

        Sheet sheet = workBook.createSheet("书籍列表");

        Row row = sheet.createRow(0);
        Cell c0 = row.createCell(0);
        c0.setCellValue("序号");
        Cell c1 = row.createCell(1);
        c1.setCellValue("ID");
        Cell c2 = row.createCell(2);
        c2.setCellValue("书籍名称");
        Cell c3 = row.createCell(3);
        c3.setCellValue("作者");
        Cell c4 = row.createCell(4);
        c4.setCellValue("出版社");


        for (int i = 0; i < bookList.size(); i++) {
            Row r = sheet.createRow(i + 1);
            Cell cell0 = r.createCell(0);
            cell0.setCellValue(i+1);
            Cell cell1 = r.createCell(1);
            cell1.setCellValue(bookList.get(i).getId());
            Cell cell2 = r.createCell(2);
            cell2.setCellValue(bookList.get(i).getBookname());
            Cell cell3 = r.createCell(3);
            cell3.setCellValue(bookList.get(i).getAuthor());
            Cell cell4 = r.createCell(4);
            cell4.setCellValue(bookList.get(i).getPublisher());
        }


        response.setContentType("application/vnd.ms-excel");
        String name = new String("书籍列表.xls".getBytes("UTF-8"),"ISO8859-1");
        response.setHeader("Content-Disposition", "attachment; filename=\" " + name + "\"" );
        OutputStream os = response.getOutputStream();
        workBook.write(os);

        os.close();
        workBook.close();

    }
}

3、导入Excel

（1）创建jsp页面，第一步先下载模板，第二步上传数据

<body>

    <div class = "container">
        <div>
            <h3>第一步：请下载导入模板</h3>
            <a class = "btn btn-info" href="/downloadTemp">下载模板</a>
        </div>

        <div>
            <h3>第二步：请上传数据</h3>
            <form action="/import" method="post" enctype="multipart/form-data">
                点击上传文件：<input type="file" name="temp" id=""><br>
                <input class = "btn btn-success" type="submit" value="上传">
            </form>
        </div>
    </div>

</body>

（2）创建下载模板的servlet

@WebServlet("/downloadTemp")
public class DownloadTemplateServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("application/vnd.ms-excel");

        response.setHeader("Content-Disposition", "attachment; filename=\"temp.xls\"" );
        File file = new File("d:/temp/temp.xls");
        response.setContentLength(Long.valueOf(file.length()).intValue());

        InputStream is = new FileInputStream(file);
        OutputStream os = response.getOutputStream();
        IOUtils.copy(is,os);
    }
}

（3）创建ImportBooksServlet.java

@WebServlet("/import")
@MultipartConfig
public class ImportBookServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.getRequestDispatcher("WEB-INF/views/import.jsp").forward(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setHeader("xx","yyy");
        BookService bookService = new BookService();

        Part temp = request.getPart("temp");

        InputStream is = temp.getInputStream();

        Workbook wb = new HSSFWorkbook(is);
        Sheet sheet = wb.getSheetAt(0);

        //getLastRowNum() 获取最后一行行号
        for(int i = 1;i <= sheet.getLastRowNum();i++){
            Row row = sheet.getRow(i);
            Book book = new Book();

            short lastCellNum = row.getLastCellNum();
            for(int j = 0;j < lastCellNum;j++){
                Cell cell = row.getCell(j);

                if(j == 1){
                    int id = (int) cell.getNumericCellValue();
                    book.setId(id);
                }
                if(j == 2){
                    String bookName = cell.getStringCellValue();
                    book.setBookname(bookName);
                }
                if(j == 3){
                    String author = cell.getStringCellValue();
                    book.setAuthor(author);
                }
                if(j == 4){
                    String publisher = cell.getStringCellValue();
                    book.setPublisher(publisher);
                }
            }
            bookService.save(book);
        }
    }
}