sxt_(011_014)_显示添加书籍

一、数据库设计　　

create table book(
    bookid int auto_increment primary key,
    bookname varchar(16),
    bookprice double,
    bookauthor varchar(10),
    bookdate date

);


insert into book(bookname,bookprice,bookauthor,bookdate) values('java1',88.01,'akr1','2018-6-1');
insert into book(bookname,bookprice,bookauthor,bookdate) values('java2',88.02,'akr2','2018-6-2');
insert into book(bookname,bookprice,bookauthor,bookdate) values('java3',88.03,'akr3','2018-6-3');
insert into book(bookname,bookprice,bookauthor,bookdate) values('java4',88.04,'akr4','2018-6-4');
insert into book(bookname,bookprice,bookauthor,bookdate) values('java5',88.05,'akr5','2018-6-5');

二、显示书籍demo

　　2.1 新建一个web项目

　　2.2 导入所需要的jar包

　　2.3 book实体类

package edu.aeon.booksys.entity;

import java.util.Date;
/**
 * [说明]:book实体类
 * @author qq1584875179
 *
 */
public class Book {
    private int bookId;
    private String bookName;
    private double bookPrice;
    private String bookAuthor;
    private Date bookDate;
    public Book() {
    }
    
    public Book(String bookName, double bookPrice, String bookAuthor, Date bookDate) {
        super();
        this.bookName = bookName;
        this.bookPrice = bookPrice;
        this.bookAuthor = bookAuthor;
        this.bookDate = bookDate;
    }
    
    
    public Book(int bookId, String bookName, double bookPrice, String bookAuthor, Date bookDate) {
        super();
        this.bookId = bookId;
        this.bookName = bookName;
        this.bookPrice = bookPrice;
        this.bookAuthor = bookAuthor;
        this.bookDate = bookDate;
    }

    public int getBookId() {
        return bookId;
    }
    public void setBookId(int bookId) {
        this.bookId = bookId;
    }
    public String getBookName() {
        return bookName;
    }
    public void setBookName(String bookName) {
        this.bookName = bookName;
    }
    public double getBookPrice() {
        return bookPrice;
    }
    public void setBookPrice(double bookPrice) {
        this.bookPrice = bookPrice;
    }
    public String getBookAuthor() {
        return bookAuthor;
    }
    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }
    public Date getBookDate() {
        return bookDate;
    }
    public void setBookDate(Date bookDate) {
        this.bookDate = bookDate;
    }
    
}

 　　2.4 DBUtils工具类

package edu.aeon.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * [说明]:jdbc工具类
 * 封装了jdbc里面的重复步骤:数据库的连接和数据库资源的释放
 * @author qq1584875179
 * @version 1.2(该版本将连接数据库的各种数据库配置信息(用户名、密码、驱动及url)单独提取到配置文件中)
 */
public class DBUtils {
    private static String username;
    private static String password;
    private static String driverClass;
    private static String url;
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;
    /**
     * 静态代码块处理读取之前的数据
     */
    static{
        InputStream inputStream = DBUtils.class.getClassLoader().getResourceAsStream("config/database/database.properties");
        Properties properties=new Properties();
        try {
            properties.load(inputStream);
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
        } catch (IOException e) {
            System.out.println("初始化读取数据库配置文件--->database.properties失败！");
            e.printStackTrace();
        }
    }
    /**
     * 连接数据库
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public  void getMySqlConnection(){
       try {
             Class.forName(driverClass);
             connection=DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * [说明]:更新：(增加、删除、改)
     * @param sql sql语句
     * @param objects 可变参数数组
     * @return updateNum:所更新后影响的行数
     */
    public int executeUpdate(String sql,Object...objects){
        this.getMySqlConnection();
        int updateNum = 0;
        try {
            preparedStatement=connection.prepareStatement(sql);
            if(objects!=null){
                for(int i=0;i<objects.length;i++){
                    preparedStatement.setObject((i+1), objects[i]);
                }
            }
            updateNum=preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeDB(resultSet, preparedStatement, connection);
        }
        return updateNum;
    }
    
    public ResultSet getAll(String sql,Object...objects){
        this.getMySqlConnection();
        try {
            preparedStatement=connection.prepareStatement(sql);
            if(objects!=null){
                for(int i=0;i<objects.length;i++){
                    preparedStatement.setObject((i+1), objects[i]);
                }
            }
            resultSet=preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }/*finally {//为什么不能关掉:因为关掉之后查不到数据了
            this.closeDB(resultSet, preparedStatement, connection);
        }*/
        return resultSet;
    }
    /**
     * 释放数据库资源
     * @param resultSet 结果集
     * @param statement 执行sql语句的对象
     * @param connection 数据库连接对象
     */
    public static void closeDB(ResultSet resultSet,Statement statement,Connection connection){
        if(null!=resultSet){
            try {
                resultSet.close();
            } catch (SQLException e) {
                System.out.println("释放数据库资源失败！--->resultSet");
                e.printStackTrace();
            }
        }
        if(null!=statement){
            try {
                statement.close();
            } catch (SQLException e) {
                System.out.println("释放数据库资源失败！--->statement");
                e.printStackTrace();
            }
        }
        if(null!=connection){
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println("释放数据库资源失败！--->connection");
                e.printStackTrace();
            }
        }
    }
}

　　2.5database.properties　　

username=root
password=root
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/booksys

　　2.6 BookDao　　

package edu.aeon.booksys.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import edu.aeon.booksys.entity.Book;
import edu.aeon.utils.DBUtils;
/**
 * [说明]:dao
 * @author qq:1584875179
 *
 */
public class BookDao extends DBUtils{
    public List<Book> getAll(){
        List<Book> bookList=new ArrayList<Book>();
        ResultSet resultSet=this.getAll("select * from book");
        try {
            while(resultSet.next()){
                bookList.add(new Book(resultSet.getInt(1), resultSet.getString(2), resultSet.getDouble(3), resultSet.getString(4), resultSet.getDate(5)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return bookList;
    }
    //测试
    public static void main(String[] args) {
        BookDao bookDao=new BookDao();
        List<Book> bookList=bookDao.getAll();
        for (Book book : bookList) {
            System.out.println(book.getBookId()+"\t"+book.getBookName());
        }
    }
}

　　2.7 显示书籍列表的servlet:ListBookServlet　　

package edu.aeon.booksys.servlet;

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

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import edu.aeon.booksys.dao.BookDao;
import edu.aeon.booksys.entity.Book;
/**
 * [说明]:查询书籍列表的servlet
 * @author qq:1584875179
 *
 */
public class ListServlet extends HttpServlet {
    
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        BookDao bookDao=new BookDao();
        List<Book> bookList=bookDao.getAll();
        response.setContentType("text/html;charset=utf-8");
        PrintWriter printWriter=response.getWriter();
        printWriter.print("<html>");
        printWriter.print("<head><title>显示书籍列表</title></head>");
        printWriter.print("<body>");
        printWriter.print("<table border='1' align='center' width='60%'>");
        printWriter.print("<th>书籍号</th>");
        printWriter.print("<th>书籍名</th>");
        printWriter.print("<th>书籍价格</th>");
        printWriter.print("<th>书籍作者</th>");
        printWriter.print("<th>书籍出版日期</th>");
        for(int i=0;i<bookList.size();i++){
            printWriter.print("<tr>");
            printWriter.print("<td>"+bookList.get(i).getBookId()+"</td>");
            printWriter.print("<td>"+bookList.get(i).getBookName()+"</td>");
            printWriter.print("<td>"+bookList.get(i).getBookPrice()+"</td>");
            printWriter.print("<td>"+bookList.get(i).getBookAuthor()+"</td>");
            printWriter.print("<td>"+bookList.get(i).getBookDate()+"</td>");
            printWriter.print("</tr>");
        }
        printWriter.print("</table");
        printWriter.print("</body>");
        printWriter.print("</html>");
    }
}

　　2.8 在web.xml中配置servlet

　　

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>booksys</display-name>
  <servlet>
      <servlet-name>listServlet</servlet-name>
      <servlet-class>edu.aeon.booksys.servlet.ListServlet</servlet-class>
  </servlet>
  <servlet-mapping>
      <servlet-name>listServlet</servlet-name>
      <url-pattern>/listBookServlet</url-pattern>
  </servlet-mapping>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
  </welcome-file-list>
</web-app>

 

　　2.9测试结果

　　　　

 三、添加书籍

　　3.1 添加书籍页面addBook.jsp

　　

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>添加书籍</title>
</head>
<body>
    <form action="addBookServlet">
        <table border="1" align="center">
            <tr><td colspan="2" align="center">添加书籍</td></tr>
            <tr>
                <td>书名:</td><td><input type="text" name="bookName"/></td>
            </tr>
            <tr>
                <td>价格:</td><td><input type="text" name="bookPrice"/></td>
            </tr>
            <tr>
                <td>作者:</td><td><input type="text" name="bookAuthor"/></td>
            </tr>
            <tr>
                <td>日期:</td><td><input type="text" name="bookDate"/></td>
            </tr>
            <tr>
                <td colspan="2" align="center"><input type="submit" value="提交"><input type="reset" value="全部撤销"></td>
            </tr>
        </table>
    </form>
</body>
</html>

　　3.2  DBUtils 

　　

package edu.aeon.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import edu.aeon.booksys.entity.Book;

/**
 * [说明]:jdbc工具类
 * 封装了jdbc里面的重复步骤:数据库的连接、资源的释放及通用操作(增删改查)。
 * @author qq:1584875179
 * @version 1.3(该版本将连接数据库的各种数据库配置信息(用户名、密码、驱动及url)单独提取到配置文件中)
 */
public class DBUtils {
    private static String username;
    private static String password;
    private static String driverClass;
    private static String url;
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;
    /**
     * 静态代码块处理读取之前的数据
     */
    static{
        InputStream inputStream = DBUtils.class.getClassLoader().getResourceAsStream("config/database/database.properties");
        Properties properties=new Properties();
        try {
            properties.load(inputStream);
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
        } catch (IOException e) {
            System.out.println("初始化读取数据库配置文件--->database.properties失败！");
            e.printStackTrace();
        }
    }
    /**
     * 连接数据库
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public  void getMySqlConnection(){
       try {
             Class.forName(driverClass);
             connection=DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * [说明]:更新：(增加、删除、改)
     * @param sql sql语句
     * @param objects 可变参数数组
     * @return updateNum:所更新后影响的行数
     */
    public int executeUpdate(String sql,Object...objects){
        this.getMySqlConnection();
        int updateNum = 0;
        try {
            preparedStatement=connection.prepareStatement(sql);
            if(objects!=null){
                for(int i=0;i<objects.length;i++){
                    preparedStatement.setObject((i+1), objects[i]);
                }
            }
            updateNum=preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeDB(resultSet, preparedStatement, connection);
        }
        return updateNum;
    }
    /**
     * 
     * @param sql sql语句
     * @param objects 可变参数
     * @return resultSet 查询结果集
     */
    public ResultSet getAll(String sql,Object...objects){
        this.getMySqlConnection();
        try {
            preparedStatement=connection.prepareStatement(sql);
            if(objects!=null){
                for(int i=0;i<objects.length;i++){
                    preparedStatement.setObject((i+1), objects[i]);
                }
            }
            resultSet=preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }/*finally {//为什么不能关掉:因为关掉之后查不到数据了
            this.closeDB(resultSet, preparedStatement, connection);
        }*/
        return resultSet;
    }
    public int addBook(Book book){
        this.getMySqlConnection();
        int updateNum = 0;
        String sql="insert into book(bookName,bookPrice,bookAuthor,bookDate) values(?,?,?,?);";
        try {
            preparedStatement=connection.prepareStatement(sql);
            if(book!=null){
                    preparedStatement.setObject(1, book.getBookName());
                    preparedStatement.setObject(2, book.getBookPrice());
                    preparedStatement.setObject(3, book.getBookAuthor());
                    preparedStatement.setObject(4, book.getBookDate());
            }
            updateNum=preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeDB(resultSet, preparedStatement, connection);
        }
        return updateNum;
    }
    /**
     * 释放数据库资源
     * @param resultSet 结果集
     * @param statement 执行sql语句的对象
     * @param connection 数据库连接对象
     */
    public static void closeDB(ResultSet resultSet,Statement statement,Connection connection){
        if(null!=resultSet){
            try {
                resultSet.close();
            } catch (SQLException e) {
                System.out.println("释放数据库资源失败！--->resultSet");
                e.printStackTrace();
            }
        }
        if(null!=statement){
            try {
                statement.close();
            } catch (SQLException e) {
                System.out.println("释放数据库资源失败！--->statement");
                e.printStackTrace();
            }
        }
        if(null!=connection){
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println("释放数据库资源失败！--->connection");
                e.printStackTrace();
            }
        }
    }
}

 

　　3.3 AddBookServlet

package edu.aeon.booksys.servlet;

import java.io.IOException;
import java.text.SimpleDateFormat;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import edu.aeon.booksys.dao.BookDao;
import edu.aeon.booksys.entity.Book;
/**
 * [说明]:添加书籍servlet
 * @author qq:1584875179
 *
 */
public class AddBookServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        BookDao bookDao=new BookDao();
        response.setContentType("text/html;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        String bookName=request.getParameter("bookName");
        String bookPrice=request.getParameter("bookPrice");
        String bookAuthor=request.getParameter("bookAuthor");
        String bookDate=request.getParameter("bookDate");
        try {
            Book book=new Book(bookName, Double.parseDouble(bookPrice), bookAuthor, new SimpleDateFormat("yyyy-MM-dd").parse(bookDate));
            int updateNum=bookDao.addBook(book);
            if(updateNum>0){
                response.getWriter().print("添加成功！");
            }else{
                response.getWriter().print("添加失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

}

　　3.4  BookDao 

package edu.aeon.booksys.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import edu.aeon.booksys.entity.Book;
import edu.aeon.utils.DBUtils;
/**
 * [说明]:dao
 * @author qq:1584875179
 *
 */
public class BookDao extends DBUtils{
    public List<Book> getAll(){
        List<Book> bookList=new ArrayList<Book>();
        ResultSet resultSet=this.getAll("select * from book");
        try {
            while(resultSet.next()){
                bookList.add(new Book(resultSet.getInt(1), resultSet.getString(2), resultSet.getDouble(3), resultSet.getString(4), resultSet.getDate(5)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return bookList;
    }
    public int add(Book book){
        int updateNum=this.addBook(book);
        return updateNum;
    }
    //测试
    public static void main(String[] args) {
        BookDao bookDao=new BookDao();
        List<Book> bookList=bookDao.getAll();
        for (Book book : bookList) {
            System.out.println(book.getBookId()+"\t"+book.getBookName());
        }
    }
}

　　3.5 web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>booksys</display-name>
  <servlet>
    <servlet-name>listServlet</servlet-name>
    <servlet-class>edu.aeon.booksys.servlet.ListServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>listServlet</servlet-name>
    <url-pattern>/listBookServlet</url-pattern>
  </servlet-mapping>
  
  
  <servlet>
    <servlet-name>addBookServlet</servlet-name>
    <servlet-class>edu.aeon.booksys.servlet.AddBookServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>addBookServlet</servlet-name>
    <url-pattern>/addBookServlet</url-pattern>
  </servlet-mapping>
  
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
  </welcome-file-list>
</web-app>

　　3.6 addBook.jsp

　　

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>添加书籍</title>
</head>
<body>
    <form action="addBookServlet">
        <table border="1" align="center">
            <tr><td colspan="2" align="center">添加书籍</td></tr>
            <tr>
                <td>书名:</td><td><input type="text" name="bookName"/></td>
            </tr>
            <tr>
                <td>价格:</td><td><input type="text" name="bookPrice"/></td>
            </tr>
            <tr>
                <td>作者:</td><td><input type="text" name="bookAuthor"/></td>
            </tr>
            <tr>
                <td>日期:</td><td><input type="text" name="bookDate"/></td>
            </tr>
            <tr>
                <td colspan="2" align="center"><input type="submit" value="提交"><input type="reset" value="全部撤销"></td>
            </tr>
        </table>
    </form>
</body>
</html>

　　3.7 添加页面显示

　　　　

四、重定向

　　4.1重定向：由response.sendRendirect("xxxServlet");实现
　　　　当使用重定向时，服务器会将重定向的地址("xxxServlet")交给浏览器。浏览器根据新的url,重新发起一次请求。
　　4.2 重定向的简图：