一、数据库设计　　

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 getAll(){
List bookList=new ArrayList();
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 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

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"> booksys listServlet edu.aeon.booksys.servlet.ListServlet listServlet /listBookServlet index.html

　　2.9测试结果

　　　　

三、添加书籍

　　3.1 添加书籍页面addBook.jsp

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>

添加书籍
书名:
价格:
作者:
日期:

　　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@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@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 getAll(){
List bookList=new ArrayList();
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 bookList=bookDao.getAll();
for (Book book : bookList) {
System.out.println(book.getBookId()+"\t"+book.getBookName());
}
}
}

　　3.5 web.xml

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">
booksys
listServlet edu.aeon.booksys.servlet.ListServlet
listServlet /listBookServlet

addBookServlet edu.aeon.booksys.servlet.AddBookServlet
addBookServlet /addBookServlet

index.html

　　3.6 addBook.jsp

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>

添加书籍
书名:
价格:
作者:
日期:

　　3.7 添加页面显示

　　　　

四、重定向

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