05_HttpServletResponse_响应Html文档_显示书籍列表

分析一个书籍管理系统

Book{
 Id,
 Name,
 Price,
 Author,
 pubDate
}

1. 建表：

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `author` varchar(50) DEFAULT NULL,
  `pubDate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 在项目中新建Book实体类：放于com.zhushen.entity包

package com.zhushen.entity;
import java.util.Date;

public class Book {
    private int id;
    private String name;
    private double price;
    private String author;
    private Date pubDate;
    public Book() {
    }

    public Book(String name, double price, String author, Date pubDate) {
        super();
        this.name = name;
        this.price = price;
        this.author = author;
        this.pubDate = pubDate;
    }

    public Book(int id, String name, double price, String author, Date pubDate) {
        super();
        this.id = id;
        this.name = name;
        this.price = price;
        this.author = author;
        this.pubDate = pubDate;
    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public double getPrice() {
        return price;
    }
    public void setPrice(double price) {
        this.price = price;
    }
    public String getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
    public Date getPubDate() {
        return pubDate;
    }
    public void setPubDate(Date pubDate) {
        this.pubDate = pubDate;
    }
}

3. 分析功能：先做显示所有书籍

• 先做书籍查询功能
  
  1. 由于对书籍的操作有很多：如增加、修改、查询及删除等于数据库交互的操作
  2. 所以将这些操作放于一个类中。取名BookDao
     DAO：data accesss object(数据访问对象)

4. 建一个util包:该包下存放连接数据库的工具类DBHelperForMysql

package com.zhushen.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBHelperForMysql {
    private String DBName;
    private String User;
    private String PassWord;
    private Connection conn;
    private PreparedStatement pst;
    private ResultSet rs;

    public DBHelperForMysql(String DBName, String User, String PassWord) {
        super();
        this.DBName = DBName;
        this.User = User;
        this.PassWord = PassWord;
    }
    /**
     * 获得数据库连接
     */
    private void getConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/"+DBName;
            conn=DriverManager.getConnection(url,User,PassWord);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace(); 
        }
    }
    /**
     * 查询方法
     * @param sql
     * @param objects
     * @return
     */
        public ResultSet executeQuery(String sql,Object...objects){
            try {
                this.getConnection();
                pst=conn.prepareStatement(sql);
                if(objects!=null){
                    for(int i=0;i<objects.length;i++){
                        pst.setObject(i+1, objects[i]);
                    }
                }
                return rs=pst.executeQuery();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
    /**
     * 更新--增加，修改，删除
     * @param sql
     * @param objects
     * @return
     */
    public int executeUpdate(String sql,Object...objects){
        try {
            this.getConnection();
            pst=conn.prepareStatement(sql);
            if(objects!=null){
                for(int i=0;i<objects.length;i++){
                    pst.setObject(i+1, objects[i]);
                }
            }
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            this.close();
        }
        return -1;
    }
    /**
     * 关闭数据库连接
     */
        public void close(){
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
}

5. 建一个dao包:该包下存放所有与数据库相关的操作的类BookDao

package com.zhushen.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zhushen.entity.Book;
import com.zhushen.util.DBHelperForMysql;

public class BookDao {
//查询所有书籍
    public List<Book> getAll(){
        List<Book> list=new ArrayList<Book>();
        //查询的操作
        DBHelperForMysql DBhelper=new DBHelperForMysql("booksys", "root", "Code531182");
        String sql="select * from book";
        try {
            ResultSet rs=DBhelper.executeQuery(sql);
            while(rs.next()){
                list.add(new Book(rs.getInt(1),rs.getString(2),rs.getDouble(3),
                        rs.getString(4 ),rs.getDate(5)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBhelper.close();
        }
        return list;
    }
}

6. 建一个servlet包，编写ListServlet类

package com.zhushen.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.zhushen.dao.BookDao;
import com.zhushen.entity.Book;

public class ListServlet extends HttpServlet{
    private static final long serialVersionUID = 1L;
    private BookDao bookdao=new BookDao();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
        throws ServletException, IOException {
    List<Book> list=bookdao.getAll();
    resp.setCharacterEncoding("utf-8");
    resp.setContentType("text/html;charset=utf-8");
    PrintWriter os=resp.getWriter();
    os.print("<html>");
    os.print("<head>");
    os.print("<title>");
    os.print("书籍列表");
    os.print("</title>");
    os.print("</head>");

    os.print("<body>");
    os.print("<table align='center' width='80%'>");
    os.print("<tr>");
        os.print("<td>");
        os.print("编号");
        os.print("</td>");
        os.print("<td>");
        os.print("书名");
        os.print("</td>");
        os.print("<td>");
        os.print("价格");
        os.print("</td>");
        os.print("<td>");
        os.print("作者");
        os.print("</td>");
        os.print("<td>");
        os.print("出版日期");
        os.print("</td>");
    os.print("</tr>");
    if(list!=null){
        for(int i=0;i<list.size();i++){
            os.print("<tr>");
            os.print("<td>");
            os.print(list.get(i).getId());
            os.print("</td>");
            os.print("<td>");
            os.print(list.get(i).getName());
            os.print("</td>");
            os.print("<td>");
            os.print(list.get(i).getPrice());
            os.print("</td>");
            os.print("<td>");
            os.print(list.get(i).getAuthor());
            os.print("</td>");
            os.print("<td>");
            os.print(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getPubDate()));
            os.print("</td>");
        os.print("</tr>");
        }
    }  
    os.print("</table>");
    os.print("</body>");
    os.print("</html>");
}

@Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        this.doGet(req, resp);
    }
}

7. 部署web.xml文件

<servlet>
        <servlet-name>listServlet</servlet-name>
        <servlet-class>com.zhushen.servlet.ListServlet</servlet-class>
     </servlet>
     <servlet-mapping>
        <servlet-name>listServlet</servlet-name>
        <url-pattern>/list</url-pattern>
     </servlet-mapping>

通过Http://localhost:8080/booksys/list访问