EnterpriseJavaEE-Test1

介绍

本文是写给某位小朋友用来准备课堂测试的，为了防止他一直纠缠我，索性将那样浪费的时间用来写成一个教程。

项目要求文档与资料：https://github.com/MarK-YANG/EnterpriseJavaEE-Test1/tree/master/BookStore

项目源码：https://github.com/MarK-YANG/EnterpriseJavaEE-Test1/tree/master/myBook

主要是关于JSTL，EL和Connection Pool的一个练习。

操作系统：MacOS Yosemite

IDE： IntelliJ IDEA 14

服务器： Tomcat 8.0.21

详细步骤

一、将Book.sql文件导入到数据库中：（用MySQLWorkbench或IDE中Database工具）

进入System Prefenrences选择MySQL打开Start MySQL Server开关，开启3306端口
打开MySQLWorkbench,选择Local instance 3306进入Workbench，选择create a new schema，新建一个database,名称为boostore（或用 IntelliJ中集成的Database工具）。
然后选择中的Open a SQL Script，导入目录下的bookstore.sql。
⌘+A全选bookstore.sql中的内容，点击执行，数据导入部分完成

二、创建JavaBeans

Books：按数据库的字段创建private的私有变量，然后生成Getter和Setter方法。
Sources：按数据库的字段创建private的私有变量，然后生成Getter和Setter方法。

三、创建JDBC

将MySQL Driver的jar包导入项目中，或放入tomcat目录下的lib文件夹中。
创建数据库连接工具类：

public class DBUtil {

    private final String DB_URL = "jdbc:mysql://localhost:3306/bookstore?useUnicode=true&characterEncoding=utf8";
    private final String DB_USER = "root";
    private final String DB_PASSWORD = "yang";
    private Connection con;

    public DBUtil(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public Connection getCon(){
        return con;
    }

    public void closeCon(){
        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

四、创建控制器

创建名ShowBooks的Servlet

@WebServlet(name = "ShowBooks", urlPatterns = "/ShowBooks")
//添加urlPatterns，为form表单action所填项

public class ShowBooks extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String action = request.getParameter("action");
        if (action == null ){
            action = "list";
        }

        switch (action){
          //根据输入的书名检索
            case "find":
                String title = request.getParameter("title");
                if(title != null){
                    findBooks(title, request, response);
                }else{
                    listAllBooks(request, response);
                }
                break;
            //检索同一作者    
            case "author":
                String author = request.getParameter("author");
                if(author != null){
                    searchAuthor(author, request, response);
                }else{
                    listAllBooks(request, response);
                }
                break;
            //检索同一出版商    
            case "publisher":
                String publisher = request.getParameter("publisher");
                if(publisher != null){
                    searchPublisher(publisher, request, response);
                }else{
                    listAllBooks(request, response);
                }
                break;
            //列出全部书籍    
            case "list":
            default:
                listAllBooks(request, response);
                break;
        }
    }
    
  //同上
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String action = request.getParameter("action");
        if (action == null ){
            action = "list";
        }

        switch (action){
            case "find":
                String title = request.getParameter("title");
                if(title != null){
                    findBooks(title, request, response);
                }else{
                    listAllBooks(request, response);
                }
                break;
            case "author":
                String author = request.getParameter("author");
                if(author != null){
                    searchAuthor(author, request, response);
                }else{
                    listAllBooks(request, response);
                }
                break;
            case "publisher":
                String publisher = request.getParameter("publisher");
                if(publisher != null){
                    searchPublisher(publisher, request, response);
                }else{
                    listAllBooks(request, response);
                }
                break;
            case "list":
            default:
                listAllBooks(request, response);
                break;
        }
    }
    

添加listAllBooks(request, response)方法

private void listAllBooks(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException{
        
        //建立JDBC连接    
        DBUtil util = new DBUtil();
        Connection connection = util.getCon();
      
      //用于存放所有的JavaBeans
        ArrayList<Books> books = new ArrayList<>();

        try {
            Statement stmt = connection.createStatement();
            String sql = "SELECT * FROM Books";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                Books myBook = new Books();
                
                //获取数据库中数据，一般来说rs.getString(1)的方式相比rs.getString("isbn")效率更高一些
                myBook.setIsbn(rs.getString(1));
                myBook.setAuthor_name(rs.getString(2));
                myBook.setTitle(rs.getString(3));
                myBook.setPublisher_name(rs.getString(4));
                myBook.setPublication_year(rs.getInt(5));
                myBook.setBinding(rs.getString(6));
                myBook.setSource_numb(rs.getInt(7));
                myBook.setRetail_price(rs.getDouble(8));
                myBook.setNumber_on_hand(rs.getInt(9));
                books.add(myBook);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        util.closeCon();
        
      //将存有JavaBeans的List传入下一个View中
        request.setAttribute("books", books);
        request.getRequestDispatcher("BookList.jsp").forward(request, response);
        
    }

添加findBooks(title, request, response)方法

private void findBooks(String title, HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException{
            
        //建立JDBC连接    
        DBUtil util = new DBUtil();
        Connection connection = util.getCon();
        
        //用SQL语句中Like的方法建立模糊查询
        String sql = "SELECT * FROM Books WHERE title LIKE ?";
        ArrayList<Books> books = new ArrayList<>();
        try {
        
          //使用preparedstatement
            PreparedStatement pstmt = connection.prepareStatement(sql);
            
            //设置传入的参数，ps，从1开始
            pstmt.setString(1,  "%"+title+"%");

            ResultSet rs = pstmt.executeQuery();

            while(rs.next()){
                Books myBook = new Books();
                myBook.setIsbn(rs.getString(1));
                myBook.setAuthor_name(rs.getString(2));
                myBook.setTitle(rs.getString(3));
                myBook.setPublisher_name(rs.getString(4));
                myBook.setPublication_year(rs.getInt(5));
                myBook.setBinding(rs.getString(6));
                myBook.setSource_numb(rs.getInt(7));
                myBook.setRetail_price(rs.getDouble(8));
                myBook.setNumber_on_hand(rs.getInt(9));
                books.add(myBook);
            }

            request.setAttribute("books", books);
            request.getRequestDispatcher("BookList.jsp").forward(request, response);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        util.closeCon();
    }

添加searchAuthor(author, request, response)方法

private void searchAuthor(String author, HttpServletRequest request, HttpServletResponse response)
            throws ServletException,IOException{
            
        //获取JDBC连接    
        DBUtil util = new DBUtil();
        Connection connection = util.getCon();
        
        String sql = "SELECT * FROM Books WHERE author_name = ?";
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, author);
            ArrayList<Books> books = new ArrayList<>();
            ResultSet rs = pstmt.executeQuery();
            while(rs.next())
            {
                Books myBook = new Books();
                myBook.setIsbn(rs.getString(1));
                myBook.setAuthor_name(rs.getString(2));
                myBook.setTitle(rs.getString(3));
                myBook.setPublisher_name(rs.getString(4));
                myBook.setPublication_year(rs.getInt(5));
                myBook.setBinding(rs.getString(6));
                myBook.setSource_numb(rs.getInt(7));
                myBook.setRetail_price(rs.getDouble(8));
                myBook.setNumber_on_hand(rs.getInt(9));
                books.add(myBook);
            }

            request.setAttribute("books", books);
            request.getRequestDispatcher("BookList.jsp").forward(request, response);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        util.closeCon();

    }

添加searchPublisher(publisher, request, response)方法

private void searchPublisher(String publisher, HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException{
            
        //建立JDBC连接    
        DBUtil util = new DBUtil();
        Connection connection = util.getCon();
        
        String sql = "SELECT * FROM Books WHERE publisher_name = ?";
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, publisher);
            ArrayList<Books> books = new ArrayList<>();
            ResultSet rs = pstmt.executeQuery();
            while(rs.next())
            {
                Books myBook = new Books();
                myBook.setIsbn(rs.getString(1));
                myBook.setAuthor_name(rs.getString(2));
                myBook.setTitle(rs.getString(3));
                myBook.setPublisher_name(rs.getString(4));
                myBook.setPublication_year(rs.getInt(5));
                myBook.setBinding(rs.getString(6));
                myBook.setSource_numb(rs.getInt(7));
                myBook.setRetail_price(rs.getDouble(8));
                myBook.setNumber_on_hand(rs.getInt(9));
                books.add(myBook);
            }
            System.out.println(books.size());
            request.setAttribute("books", books);
            request.getRequestDispatcher("BookList.jsp").forward(request, response);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        util.closeCon();
    }

五、创建BookList.jsp(View)

首先，在目录己经提供了现成的BookList.html模板，可将其中的主要内容复制过来
我们要以table的方式列出所有书籍的信息，而每一本书的信息都封装在名为Books类的JavaBean中，所有的JavaBean又存储在一个名为books的ArrayList中（从Servlet中传入）。
明白了这些，我们首要工作就是要遍历名为books的ArrayList，于是我们用到了JSTL中的<c:foreach>标签。<c:foreach>标签的简要使用方法是：
- item项为要遍历的数据结构
- var项为迭代器所指向的当前变量
- e.g.
  1 2 3
  <c:foreach item=“${books}” var=“var”> <td>${var}</td> </c:foreach>
其次，如果某书籍可外借数量不足5本，我们需要用另一种方式展示出来，这里就用到了JSTL的<c:if>标签。<c:if>标签的简单使用方法是：
- test项为判断条件
- 标签内为条件成功执行代码
- e.g.
  1 2 3
  <c:if test=“{var == 5}”> <td>${var}</td> </c:if>
最后，我们需要将table中的某列的内容以HyperLink的方式展示，可以使用JSTL中的<c:url>标签。<c:url>标签的简单使用方法是：
- value项是HyperLink的地址
- <c:param name=“” value=“”></c:param>是通过get方法传入的值
- e.g.
  1 2 3 4
  <c:url value=“ShowBooks”> <c:param name=“action” value=“author”></c:param> <c:param name=“author” value=“${var.author_name}”></c:param> </c:url>
注，使用JSTL时，要将jstl-1.2-2.jar和standard-1.1.2-2.jar导放到项目目录下，或将他们导入tomcat目录的lib文件夹下
最后，我们的BookList.jsp的body部分是这样的：

<body >
<form action="ShowBooks?action=find" method="post">
  Input book title: <input type="text" name="title" value="" />
  <input type="submit" value="find" />
</form>

<table id="table-2">
  <thead>
  <tr>
    <th>ISBN</th>
    <th>Title</th>
    <th>Author</th>
    <th>Price</th>
    <th>Publisher</th>
    <th>Source</th>
    <th>Quantity</th>
  </tr>
  </thead>
  <tbody>
  <c:forEach items="${books}" var="var">
    <c:if test="${var.number_on_hand > 5}">
      <tr>
        <td>${var.isbn}</td>
        <td>${var.title}</td>
        <td><a href="
  <c:url value="ShowBooks">
  <c:param name="action" value="author"></c:param>
  <c:param name="author" value="${var.author_name}"></c:param>
  </c:url>
">${var.author_name}</a></td>
        <td>${var.retail_price}</td>
        <td><a href="<c:url value="ShowBooks">
  <c:param name="action" value="publisher"></c:param>
  <c:param name="publisher" value="${var.publisher_name}"></c:param>
</c:url> ">${var.publisher_name}</a></td>
        <td>${var.source_numb}</td>
        <td>${var.number_on_hand}</td>
      </tr>
    </c:if>
    <c:if test="${var.number_on_hand <= 5}">
      <tr bgcolor="lime">
        <td>${var.isbn}</td>
        <td>${var.title}</td>
        <td><a href="
  <c:url value="ShowBooks">
  <c:param name="action" value="author"></c:param>
  <c:param name="author" value="${var.author_name}"></c:param>
  </c:url>
">${var.author_name}</a></td>
        <td>${var.retail_price}</td>
        <td><a href="<c:url value="ShowBooks">
  <c:param name="action" value="publisher"></c:param>
  <c:param name="publisher" value="${var.publisher_name}"></c:param>
</c:url> ">${var.publisher_name}</a></td>
        <td>${var.source_numb}</td>
        <td>${var.number_on_hand}</td>
      </tr>
    </c:if>

  </c:forEach>

  </tbody>
</table>


</body>

六、打开tomact服务器，在浏览器中输入http://localhost:8080/项目名称/ShowBooks(ServletURLPattern)测试一下。

MarK’s Blog

Rules for happiness: something to do, someone to love, something to hope for.

介绍

目录

详细步骤

Comments