1、实验目的：
熟悉采用 DAO 模式实现对数据库的访问方法，利用 Servlet 实现 MVC 模式，并为后续 Hibernate 框架的学习打下基础。
2、实验内容：
在实验五实现的功能中增加数据库访问功能。
1、根据系统设计建立所需的数据库、数据表。
2、确定项目的 DAO 模型，开发相应类，在该类中实现增删改查的相应操作。3、编写公共的数据库连接类 DBCon 类，在 DAO 中使用 DBCon 提供的数据库连接。
4、编写控制器类 Servlet，在其中加入对 DAO 对象的调用，并根据传递参数的不同执行不同的操作，实现数据库的增删改查操作，即能够添加图书、修改图书信息以及删除图书、查询图书。
实现界面：
代码实现：
目录：
jsp
add.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>增加图书</title>
<script type="text/javascript">
function check(form) {
with (form) {
if (name.value == "") {
alert("用户名不能为空");
return false;
}
}
}
</script>
</head>
<body>
<form action="add" method="post" onsubmit="check(this)">
<table align="center" width="450">
<tr>
<td align="center" colspan="2">
<h2>添加图书信息</h2>
<hr>
</td>
</tr>
<tr>
<td align="right">书名：</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td align="right">图书价格：</td>
<td><input type="text" name="price"></td>
</tr>
<tr>
<td align="right">作者：</td>
<td><input type="text" name="author"></td>
</tr>
<tr>
<td align="right">出版社：</td>
<td><input type="text" name="press"></td>
</tr>
<tr>
<td align="right">图书数量：</td>
<td><input type="text" name="number"></td>
</tr>
<tr>
<td align="center" colspan="2">
<input type="submit" value="添 加">
</td>
</tr>
</table>
</form>
</body>
</html>
list.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">
<%@page import="java.util.List"%>
<%@page import="bean.Book"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>所有用户</title>
<style type="text/css">
td {
font-size: 12px;
}
h2 {
margin: 0px
}
</style>
<script type="text/javascript">
</script>
</head>
<body>
<h2 align="center">
<a href="add.jsp">添加图书</a>
</h2>
<br>
<table align="center" width="800" border="1" height="300"
bordercolor="white" bgcolor="pink" cellpadding="1" cellspacing="1">
<tr bgcolor="white">
<td align="center" colspan="9">
<h2>所有图书信息</h2>
</td>
</tr>
<tr align="center" bgcolor="#e1ffc1">
<td><b>ID</b></td>
<td><b>书名</b></td>
<td><b>价格</b></td>
<td><b>作者</b></td>
<td><b>出版社</b></td>
<td><b>数量</b></td>
<td colspan="4"><b>操作</b></td>
</tr>
<%
// 获取用户信息集合
List<Book> list = (List<Book>) request.getAttribute("bookInfoList");
// 判断是否有数据
if (list == null || list.size() < 1) {
%>
<tr bgcolor="white"><td colspan="5" ><h4 align="center">没有数据</h4></td></tr>
<%
} else {
// 遍历用户集合中的数据
for (Book book : list) {
%>
<tr align="center" bgcolor="white">
<td><%=book.getId()%></td>
<td><%=book.getName()%></td>
<td><%=book.getPrice()%></td>
<td><%=book.getAuthor()%></td>
<td><%=book.getPress()%></td>
<td><%=book.getNumber()%></td>
<td >
<a href="update?id=<%=book.getId()%>">修改</a>
</td>
<td>
<a href="delete?id=<%=book.getId()%>" onclick="return confirm('您确定删除吗')">删除</a>
</td>
</tr>
<%
}
}
%>
</table>
</body>
</html>
update.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="bean.Book"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>修改图书信息页面</title>
</head>
<body>
<div>
<table>
<thead><tr><td><h1>修改图书数量</h1></td></tr></thead>
<tbody>
<form action="update" method="post">
<tr>
<td>图书ID：</td>
<td><input type="text" name="id" value="${book.id}"
readonly="readonly" /></td>
</tr>
<tr>
<td>图书名：</td>
<td><input type="text" name="name" value="${book.name}"
/></td>
</tr>
<tr>
<td>价格：</td>
<td><input type="text" name="price" value="${book.price}"
/></td>
</tr>
<tr>
<td>作者：</td>
<td><input type="text" name="author" value="${book.author}"
/></td>
</tr>
<tr>
<td>出版社：</td>
<td><input type="text" name="press" value="${book.press}"
/></td>
</tr>
<tr>
<td>数量：</td>
<td><input type="text" name="number" value="${book.number}" /></td>
</tr>
<tr>
<td><input class="btn" type="submit" value="提交" /> <input
class="btn" type="reset" value="重置" /></td>
</tr>
</tbody>
</form>
</table>
</div>
</body>
</html>
JAVA类：
Book.java
package bean;
public class Book {
private Integer id;
private String name;
private String press;
private String author;
private Float price;
private Integer number;
public Integer getNumber() {
return number;
}
public void setNumber(Integer number) {
this.number = number;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Float getPrice() {
return price;
}
public void setPrice(Float price) {
this.price = price;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPress() {
return press;
}
public void setPress(String press) {
this.press = press;
}
}
AddServlet.java
package Control;
import java.io.IOException;
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 bean.Book;
import dao.BookDao;
@WebServlet("/add")
public class AddServlet extends HttpServlet {
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String name = req.getParameter("name");
Float price = Float.valueOf(req.getParameter("price"));
String author = req.getParameter("author");
String press =req.getParameter("press");
Integer number=Integer.valueOf(req.getParameter("number"));
Book book = new Book();//创建user对象
book.setName(name);
book.setPrice(price);
book.setAuthor(author);
book.setPress(press);
book.setNumber(number);
BookDao dao = new BookDao();
dao.addBook(book);//添加到数据库中
req.getRequestDispatcher("list").forward(req, resp);
}
}
DeleteServlet.java
package Control;
import java.io.IOException;
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 bean.Book;
import dao.BookDao;
@WebServlet("/delete")
public class DeleteServlet extends HttpServlet{
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
Book book=new Book();
String idStr = req.getParameter("id"); // 删除数据的ID，根据ID删除
if (idStr != null && !idStr.equals("")) {
int id = Integer.valueOf(idStr);
BookDao dao = new BookDao();
dao.deleteBook(id);
}
req.getRequestDispatcher("list").forward(req, resp);
}
}
ListServlet.java
package Control;
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 bean.Book;
import dao.BookDao;
@WebServlet("/list")
//上面注释的是WebServlet3.0的使用方式，通过这样的注解，不需要配置web.xml也可运行程序
public class ListServlet extends HttpServlet {
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
BookDao dao = new BookDao();
List<Book> list = dao.getAllBooks();
req.setAttribute("bookInfoList", list);
req.getRequestDispatcher("list.jsp").forward(req, resp);
}
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doGet(req, resp);
}
}
UpdateServlet.java
package Control;
import java.io.IOException;
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 bean.Book;
import dao.BookDao;
@WebServlet("/update")
public class UpdateServlet extends HttpServlet {
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String idStr = req.getParameter("id");
if (idStr != null && !idStr.equals("")) {
int id = Integer.valueOf(idStr);
BookDao dao = new BookDao();
Book book = dao.selectBookById(id);
req.setAttribute("book", book);
}
req.getRequestDispatcher("update.jsp").forward(req, resp);
}
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String idStr = req.getParameter("id");
if (idStr != null && !idStr.equals("")) {
int id = Integer.valueOf(idStr);
String name=req.getParameter("name");
String press=req.getParameter("press");
Float price=Float.valueOf(req.getParameter("price"));
String author=req.getParameter("author");
Integer number=Integer.valueOf(req.getParameter("number"));
Book book = new Book();
book.setNumber(number);
book.setAuthor(author);
book.setPrice(price);
book.setPress(press);
book.setName(name);
book.setId(id);
BookDao dao = new BookDao();
dao.updateBook(book);
}
req.getRequestDispatcher("list").forward(req, resp);
}
}
BookDao.java
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import bean.Book;
import model.DbHelper;
public class BookDao {
public List<Book> getAllBooks(){
List<Book> list = new ArrayList<Book>();
Connection conn = DbHelper.getConnection();//连接数据库
String sql = "select * from book";
try {
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rst = pst.executeQuery();
while (rst.next()) {
Book book = new Book();
book.setId(rst.getInt("id"));
book.setName(rst.getString("name"));
book.setPrice(rst.getFloat("price"));
book.setAuthor(rst.getString("author"));
book.setPress(rst.getString("press"));
book.setNumber(rst.getInt("number"));
list.add(book);
}
rst.close();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public boolean addBook(Book book){
String sql = "INSERT INTO `book`(`name`,`price`,`author`,`press`,`number`) VALUES (?,?,?,?,?)";
Connection conn = DbHelper.getConnection();
try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, book.getName());
pst.setFloat(2, book.getPrice());
pst.setString(3, book.getAuthor());
pst.setString(4, book.getPress());
pst.setInt(5, book.getNumber());
int count = pst.executeUpdate();
pst.close();
return count>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public boolean updateBook(Book book){
String sql = "UPDATE `book` SET `name`=?,`price`=?,`author`=?,`press`=?,`number`=? WHERE `id` = ?";
Connection conn = DbHelper.getConnection();
try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1,book.getName());
pst.setFloat(2, book.getPrice());
pst.setString(3,book.getAuthor());
pst.setString(4, book.getPress());
pst.setInt(5, book.getNumber());
pst.setInt(6, book.getId());
int count = pst.executeUpdate();
pst.close();
return count>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public boolean deleteBook(int id){
String sql = "delete from book where id = ?";
Connection conn = DbHelper.getConnection();
try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setInt(1,id);
int count = pst.executeUpdate();
pst.close();
return count>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public Book selectBookById(int id){
Connection conn = DbHelper.getConnection();
String sql = "select * from book where id = "+id;
Book book = null;
try {
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rst = pst.executeQuery();
while (rst.next()) {
book = new Book();
book.setId(rst.getInt("id"));
book.setName(rst.getString("name"));
book.setPrice(rst.getFloat("price"));
book.setAuthor(rst.getString("author"));
book.setPress(rst.getString("press"));
book.setNumber(rst.getInt("number"));
}
rst.close();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
return book;
}
}
CharacterEncodingFilter.java
package Filter;
import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
/**
* Servlet Filter implementation class CharacterEncodingFilter
*/
@WebFilter(
urlPatterns= {"/*"},
initParams= {
@WebInitParam(name="encoding",value="utf-8")
}
)
public class CharacterEncodingFilter implements Filter {
protected String encoding ;
/**
* Default constructor.
*/
public CharacterEncodingFilter() {
// TODO Auto-generated constructor stub
}
/**
* @see Filter#destroy()
*/
public void destroy() {
// TODO Auto-generated method stub
}
/**
* @see Filter#doFilter(ServletRequest, ServletResponse, FilterChain)
*/
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
// TODO Auto-generated method stub
// place your code here
if( encoding !=null) {
request.setCharacterEncoding(encoding);
}
// pass the request along the filter chain
chain.doFilter(request, response);
}
/**
* @see Filter#init(FilterConfig)
*/
public void init(FilterConfig fConfig) throws ServletException {
// TODO Auto-generated method stub
this.encoding=fConfig.getInitParameter("encoding");
}
}
DbHelper.java
package model;
import java.sql.Connection;
import java.sql.DriverManager;
public class DbHelper {
private static String url = "jdbc:mysql://localhost:3308/lab6?useUnicode=true&characterEncoding=utf-8"; //数据库地址
private static String userName = "root"; //数据库用户名
private static String passWord = null; //数据库密码
private static Connection conn = null;
private DbHelper(){
}
public static Connection getConnection(){
if(null == conn){
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, userName, passWord);
} catch (Exception e) {
e.printStackTrace();
}
}
return conn;
}
public static void main(String[] args) { //测试数据库是否连通
System.out.println(getConnection());
}
}
扫一扫
772
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
