实验六 DAO 和 MVC 模式

一、实验目的
熟悉采用 DAO 模式实现对数据库的访问方法，利用 Servlet 实现 MVC 模式，并为后续 Hibernate 框架的学习打下基础。
二、实验学时
4 学时
三、实验环境

1. 硬件环境：普通 PC 机
2. 软件环境：Windows 操作系统、Eclipse/MyEclipse、Tomcat、Mysql
   四、实验内容
   在实验五实现的功能中增加数据库访问功能。
   1、根据系统设计建立所需的数据库、数据表。
   2、确定项目的 DAO 模型，开发相应类，在该类中实现增删改查的相应操
   作。
   3、编写公共的数据库连接类 DBCon 类，在 DAO 中使用 DBCon 提供的数
   据库连接。
   4、编写控制器类 Servlet，在其中加入对 DAO 对象的调用，并根据传递参
   数的不同执行不同的操作，实现数据库的增删改查操作，即能够添加图书、修改
   图书信息以及删除图书、查询图书。
   五、实验参考界面

在该程序中可以输入图书数量，然后点击修改按钮来更新图书数量，点击删
除按钮则删除对应的图书。
六、实验报告书写要求
应在实验报告中说明如下事项：
（一）所开发系统数据库设计
简要描述系统数据库设计：数据表名称及用途，表中主要字段说明。
（二）在所开发系统中增加数据库访问功能
1、创建数据库及数据表后，导出数据库到 SQL 文件，摘抄 SQL 文件中建
表相关语句。
2、描述所设计的 DAO 模型，列出相应类。
3、简要描述所实现功能中对 DAO 对象的调用过程。
4、如果要求删除之前给用户一个提示信息，让用户确认是否删除该如何实
现？

项目结构如下：

Book.java

package book;

public class Book {
	public String bookName;
	public double bookPrice;
	public String bookWriter;
	public String bookPublisher;
	public int bookNumber;
	public int getBookNumber() {
		return bookNumber;
	}

	public double getBookPrice() {
		return bookPrice;
	}

	public void setBookPrice(double bookPrice) {
		this.bookPrice = bookPrice;
	}

	public String getBookWriter() {
		return bookWriter;
	}

	public void setBookWriter(String bookWriter) {
		this.bookWriter = bookWriter;
	}

	public void setBookNumber(int bookNumber) {
		this.bookNumber = bookNumber;
	}

	public String getBookName() {
		return bookName;
	}

	public void setBookName(String bookName) {
		this.bookName = bookName;
	}

	public String getBookPublisher() {
		return bookPublisher;
	}

	public void setBookPublisher(String bookPublisher) {
		this.bookPublisher = bookPublisher;
	}


}

BookDaoImpl.java

package book.dao;

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

import book.Book;
import dao.impl.IBookDao;
import utils.DbUtil;

public class BookDaoImpl implements IBookDao {

	
	@Override
	public void saveBook(Book b) {
		try {
			String sql = " INSERT INTO books VALUES(?,?,?,?,?) ";
			PreparedStatement ps = DbUtil.executePreparedStatement(sql);
			int result;
			ps.setString(1,b.bookName);
			ps.setDouble(2,b.bookPrice);
			ps.setString(3,b.bookWriter);
			ps.setString(4,b.bookPublisher);
			ps.setInt(5,b.bookNumber);
			result = ps.executeUpdate();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	public List<Book> findAll() {
		// TODO Auto-generated method stub
		String sql = " SELECT * FROM books ";
		List<Book> list = new ArrayList<Book>();
		ResultSet rs = DbUtil.executeQuery(sql);
		try {
			while(rs.next()) {
				Book b = new Book();
				b.setBookName(rs.getString("bookName"));
				b.setBookPrice(rs.getDouble("bookPrice"));
				b.setBookWriter(rs.getString("bookWriter"));
				b.setBookPublisher(rs.getString("bookPublisher"));
				b.setBookNumber(rs.getInt("bookNumber"));
				list.add(b);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		DbUtil.close();
		return list;
	}
	
	
	
	@Override
	public int delete(String bookName) {
		// TODO Auto-generated method stub
		String sql = "delete from books where bookName=?";
		PreparedStatement ps = DbUtil.executePreparedStatement(sql);
		int result = 0;
		try {
			ps.setString(1, bookName);
			result = ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		DbUtil.close();
		return result;
	}
	
	
	@Override
	public int update(int newBookNumber,String bookName) {
		// TODO Auto-generated method stub
		String sql = "update books set bookNumber=? where bookName=?";
		PreparedStatement ps = DbUtil.executePreparedStatement(sql);
		int result = 0;
		try {
			ps.setInt(1, newBookNumber);
			ps.setString(2, bookName);
			result = ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		DbUtil.close();
		return result;
	}
	
}

bookServlet.java

package com.servlet;

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

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

import book.Book;
import dao.factory.BookDaoFactory;
import dao.impl.IBookDao;

/**
 * Servlet implementation class bookServlet
 */
@WebServlet("/bookServlet")
public class bookServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public bookServlet() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String operation = request.getParameter("operation");
		IBookDao bdi = BookDaoFactory.getInterfaceFactory();
		
		switch (operation) {
		case "select": {
			try {
				List<Book> list = bdi.findAll(); // 执行dao方法
				request.setAttribute("list", list); 
				request.getRequestDispatcher("showBook.jsp").forward(request, response);

			} catch (Exception e) {
			}

			break;
		}
		case "add": {
			
			try {
				String bookName = request.getParameter("bookName");
				double bookPrice = Double.parseDouble(request.getParameter("bookPrice"));
				String bookWriter = request.getParameter("bookWriter");
				String bookPublisher = request.getParameter("bookPublisher");
				int bookNumber = Integer.parseInt(request.getParameter("bookNumber")) ;
				
				Book b = new Book();
				b.setBookName(bookName);
				b.setBookPrice(bookPrice);
				b.setBookPublisher(bookPublisher);
				b.setBookWriter(bookWriter);
				b.setBookNumber(bookNumber);
				
				bdi.saveBook(b);

				response.sendRedirect("index.jsp");

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

			break;
		}

		case "delete": {
			
			String bookName = request.getParameter("bookName");
			
			bdi.delete(bookName);
			response.sendRedirect("index.jsp");
			break;
		}
		case "update": {
			
			String bookName = request.getParameter("bookName");
			int newBookNumber = Integer.parseInt(request.getParameter("newBookNumber")) ;
			
			bdi.update(newBookNumber, bookName);
			response.sendRedirect("index.jsp");
			break;
		}
		default:
			response.sendRedirect("error.jsp");
		}
	}

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

}

BookDaoFactory.java

package dao.factory;

import book.dao.BookDaoImpl;
import dao.impl.IBookDao;

public class BookDaoFactory {
    public static IBookDao getInterfaceFactory(){
        return new BookDaoImpl();
    }
}

IBookDao.java

package dao.impl;

import java.util.List;

import book.Book;

public interface IBookDao {
	public void saveBook(Book b); //添加图书
	public List<Book> findAll(); //查找所有图书
	public int delete(String bookName); //删除图书
	public int update(int newBookNumber,String bookName); //更新图书存储数量
}

DbUtil.java

package utils;
import java.sql.*;

public class DbUtil {
	
	private static final String url = "jdbc:mysql://localhost:3306/mydb";
	private static final String user = "root";
	private static final String password = "";
	
	protected static Statement s = null;
	protected static ResultSet rs = null;
	protected static Connection conn = null;
	
	public static synchronized Connection getConnection()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url,user,password);
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
	
	public static int executeUpdate(String sql)
	{
		int result = 0;
		try {
			s = getConnection().createStatement();
			result = s.executeUpdate(sql);
		}catch(Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public static ResultSet executeQuery(String sql)
	{
		try {
			s = getConnection().createStatement();
			rs = s.executeQuery(sql);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		
		return rs;
	}
	
	public static PreparedStatement executePreparedStatement(String sql)
	{
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(sql);
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ps;
	}
	
	
	public static void close()
	{
		try {
			if(rs!=null)
				rs.close();
			if(s!=null)
				s.close();
			if(conn!=null)
				conn.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}

}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>Book</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>

addBook.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>提交书籍信息</title>
</head>
<body>
	
	<form action="bookServlet" method="get">
		图书名称：<input type="text" name="bookName"></br>
		价格：<input type="text" name="bookPrice"></br>
		作者：<input type="text" name="bookWriter"></br>
		出版社：<input type="text" name="bookPublisher"></br>
		库存：<input type="text" name="bookNumber"></br>
		<input type="hidden" value="add" name="operation">
		<input type="submit" value="提交">
	</form>
	

</body>
</html>

error.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>错误页面</title>
</head>
<body>
	<p>发生了错误</p>
</body>
</html>

index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<body>

	<% response.sendRedirect("/Book/bookServlet?operation=select"); %>
	
</body>
</html>

showBook.jsp

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

<%@ page import="book.Book" %>
<%@ page import="java.util.List" %>

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>显示图书信息</title>
	
</head>
<body>
	<table border="1" cellspacing="0" cellpadding="0" width="100%"
		style="align: center;">
		<tr bgcolor="ff9900" style="font-weight: bold;">
			<th>书名</th>
			<th>价格</th>
			<th>作者</th>
			<th>出版社</th>
			<th>库存</th>
			<th>修改库存</th>
			<th>删除</th>
		</tr>
		<%
		//循环显示数据
		List<Book> booklist = (List)request.getAttribute("list"); 
		if (booklist.size() != 0) {
			for (int i = 0; i < booklist.size(); i++) {
				pageContext.setAttribute("books", booklist.get(i));
				//保存到页面pageContext里面方便下面进行EL表达式调用
		%>
		<tr>
			<td>${books.bookName }</td> 
			<td>${books.bookPrice }</td> 
			<td>${books.bookWriter }</td>
			<td>${books.bookPublisher }</td>
			<td>${books.bookNumber }</td>
     		<td>
     			<form action="bookServlet" method="get">
	     			<input type="submit" value="修改库存">
	     			<input type="text" name="newBookNumber" >
	     			<input type="hidden" name="bookName" value="${books.bookName }">
	     			<input type="hidden" name="operation" value="update">
     			</form>
     		</td> 
     		<td><a href="/Book/bookServlet?bookName=${books.bookName }&operation=delete" onClick="return confirm('确定删除?');">删除</a>
     		</td>
		</tr>
		<%
			}
		} else {
		%>
		<tr>
			<td colspan="6">数据库中没有数据！</td>
		</tr>
		<%
		}
		%>
	</table>
	
	</br></br>
	<a href="addBook.jsp">添加图书信息</a>

</body>
</html>

mysql数据库名：mydb
表名：books
表结构如下