需求:
- 查询全部
- 模糊查询
- 删除用户
- 添加用户
数据库脚本:
--
-- Table structure for table `book`
--
CREATE DATABASE JYKS04
DROP TABLE IF EXISTS `book`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `book` (
`book_id` INT NOT NULL AUTO_INCREMENT,
`book_ISBN` VARCHAR(50) DEFAULT NULL,
`book_name` VARCHAR(50) DEFAULT NULL,
`book_author` VARCHAR(50) DEFAULT NULL,
`book_pub` VARCHAR(50) DEFAULT NULL,
`book_sort` INT DEFAULT NULL,
`book_abstract` VARCHAR(50) DEFAULT NULL,
`book_total` INT DEFAULT NULL,
`book_photo` VARCHAR(50) DEFAULT NULL,
`book_photo_location` VARCHAR(50) DEFAULT NULL,
`book_photo_year` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`book_id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `book`
--
LOCK TABLES `book` WRITE;
/*!40000 ALTER TABLE `book` DISABLE KEYS */;
INSERT INTO `book` VALUES (1,'114-5-141-9198-1','java从入门到入土','七海nana7mi','VirtuaReal',1,'java教程',30,'image/1.jpg','上海','2077'),(2,'114-5-123-9198-1','Android从入门到入坟','东雪莲','菠萝包',1,'Android教程',30,'image/2.jpg','日本','2042'),(3,'114-5-123-2310-1','Red Dead','杰克马斯顿','不知道',2,'自传',30,'image/3.jpg','外星','1899');
/*!40000 ALTER TABLE `book` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `sort`
--
DROP TABLE IF EXISTS `sort`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sort` (
`sort_id` INT NOT NULL AUTO_INCREMENT,
`sort_name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`sort_id`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `sort`
--
LOCK TABLES `sort` WRITE;
/*!40000 ALTER TABLE `sort` DISABLE KEYS */;
INSERT INTO `sort` VALUES (1,'工具书'),(2,'自传');
/*!40000 ALTER TABLE `sort` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
Dao层:
01:BookDao:
public interface BookDao {
//查询全部
List<Book> getAll();
//模糊查询
List<Book> getBookByName(String name);
//根据id删除
int deleteBookById(int id);
//增加书籍信息
int addBook(Book book);
//根据类别查询
List<Book> getBookBySortId(int sortId);
//修改信息
int updateBook(Book book);
}
02:SortDao
public interface SortDao {
///查询所有类别
List<Sort> getAll();
}
Dao层(Impl)层:
01:BookDaoImpl
public class BookDaoImpl extends BaseDao implements BookDao {
@Override
public List<Book> getAll() {
String sql="SELECT * FROM book,sort WHERE book.`book_sort`=sort.`sort_id`";
ResultSet rs = this.executeQuery(sql, null);
List<Book> books=new ArrayList<>();
try {
while (rs.next()){
Book book=new Book();
book.setBook_id(rs.getInt("book_id"));
book.setBook_ISBN(rs.getString("book_ISBN"));
book.setBook_name(rs.getString("book_name"));
book.setBook_author(rs.getString("book_author"));
book.setBook_pub(rs.getString("book_pub"));
book.setBook_sort(rs.getInt("book_sort"));
book.setBook_abstract(rs.getString("book_abstract"));
book.setBook_total(rs.getInt("book_total"));
book.setBook_photo(rs.getString("book_photo"));
book.setBook_photo_location(rs.getString("book_photo_location"));
book.setBook_photo_year(rs.getString("book_photo_year"));
book.setSortName(rs.getString("sort_name"));
books.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
return books;
}
@Override
public List<Book> getBookByName(String name) {
String sql="select * from book,sort WHERE book.`book_sort`=sort.`sort_id` AND book.`book_name` like ?";
name="%"+name+"%";
Object [] objects={name};
ResultSet rs = this.executeQuery(sql, objects);
List<Book> books=new ArrayList<>();
try {
while (rs.next()){
Book book=new Book();
book.setBook_id(rs.getInt("book_id"));
book.setBook_ISBN(rs.getString("book_ISBN"));
book.setBook_name(rs.getString("book_name"));
book.setBook_author(rs.getString("book_author"));
book.setBook_pub(rs.getString("book_pub"));
book.setBook_sort(rs.getInt("book_sort"));
book.setBook_abstract(rs.getString("book_abstract"));
book.setBook_total(rs.getInt("book_total"));
book.setBook_photo(rs.getString("book_photo"));
book.setBook_photo_location(rs.getString("book_photo_location"));
book.setBook_photo_year(rs.getString("book_photo_year"));
book.setSortName(rs.getString("sort_name"));
books.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
return books;
}
@Override
public int deleteBookById(int id) {
String sql="delete from book where book_id=?";
Object [] objects={id};
return this.executeUpdate(sql,objects);
}
@Override
public int addBook(Book book) {
String sql="INSERT INTO `book`(book_ISBN,book_name,book_author,book_pub,book_sort,book_abstract,book_total,book_photo,book_photo_location,book_photo_year) VALUES (?,?,?,?,?,?,?,?,?,?)";
Object [] objects={book.getBook_ISBN(),book.getBook_name(),book.getBook_author(),book.getBook_pub(),book.getBook_sort(),book.getBook_abstract(),book.getBook_total(),book.getBook_photo(),book.getBook_photo_location(),book.getBook_photo_year()};
return this.executeUpdate(sql,objects);
}
@Override
public List<Book> getBookBySortId(int sortId) {
String sql="SELECT * FROM book,sort WHERE book.`book_sort`=sort.`sort_id` AND book.`book_sort`=?";
Object [] objects={sortId};
ResultSet rs = this.executeQuery(sql, objects);
List<Book> books=new ArrayList<>();
try {
while (rs.next()){
Book book=new Book();
book.setBook_id(rs.getInt("book_id"));
book.setBook_ISBN(rs.getString("book_ISBN"));
book.setBook_name(rs.getString("book_name"));
book.setBook_author(rs.getString("book_author"));
book.setBook_pub(rs.getString("book_pub"));
book.setBook_sort(rs.getInt("book_sort"));
book.setBook_abstract(rs.getString("book_abstract"));
book.setBook_total(rs.getInt("book_total"));
book.setBook_photo(rs.getString("book_photo"));
book.setBook_photo_location(rs.getString("book_photo_location"));
book.setBook_photo_year(rs.getString("book_photo_year"));
book.setSortName(rs.getString("sort_name"));
books.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
return books;
}
@Override
public int updateBook(Book book) {
String sql="UPDATE book SET book_ISBN=?, book_name=?,book_author=?,book_pub=?,book_sort=?,book_abstract=?,book_total=?,book_photo=?,book_photo_location=?,book_photo_year=? WHERE book_id=?";
Object [] objects={book.getBook_ISBN(),book.getBook_name(),book.getBook_author(),book.getBook_pub(),book.getBook_sort(),book.getBook_abstract(),book.getBook_total(),book.getBook_photo(),book.getBook_photo_location(),book.getBook_photo_year(),book.getBook_id()};
return this.executeUpdate(sql,objects);
}
}
02:SortDaoImpl
public class SortDaoImpl extends BaseDao implements SortDao {
@Override
public List<Sort> getAll() {
String sql = "select * from sort";
ResultSet rs = this.executeQuery(sql, null);
List<Sort> sorts = new ArrayList<>();
try {
while(rs.next()) {
Sort sort=new Sort();
sort.setSort_id(rs.getInt("sort_id"));
sort.setSort_name(rs.getString("sort_name"));
sorts.add(sort);
};
} catch (SQLException e) {
e.printStackTrace();
}
return sorts;
}
}
Service层:
01:接口:(BookService)/(SortService):
接口中的方法和Dao层中的 一样 但是 如果是int类型的只需要修成boolean类型即可
02:实现类(BookServiceImpl)/(SortServiceImpl)
实现类中只需要调用Dao层的方法 并且返回方法 ,是int类型的只需要使用if加以判断就ok
Servlet层:
@WebServlet("/BookServlet")
@MultipartConfig(maxFileSize = 1024*1024*2)//标识Servlet支持文件上传 文件最大值为2MB
public class BookServlet extends HttpServlet {
//获取注解里设置的值
private static MultipartConfig config=BookServlet.class.getAnnotation(MultipartConfig.class);
//设置允许上传的图片类型
private String TYPE="image/gif;image/png;image/jpg";
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req,resp);
}
//从HTTP header中提取上传文件的名称
private String getFileName(Part part){
//获取头信息
String con=part.getHeader("content-disposition");
String [] items=con.split(";");
for (String str:items){
if(str.trim().startsWith("filename")){
return str.substring(str.indexOf("=")+2,str.length()-1);
}
}
return "";
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setCharacterEncoding("utf-8");
resp.setContentType("text/html; charset=utf-8");
String path=req.getParameter("path");
BookService bs=new BookServiceImpl();
SortService ss=new SortServiceImpl();
HttpSession session=req.getSession();
if(path==null){//查询全部
List<Book> books = bs.getAll();//查询所有书籍
//序列化json格式字符串
session.setAttribute("books",books);
resp.sendRedirect("index.jsp");
}else if(path.equals("sort")){//查询所有类别
List<Sort> sorts = ss.getAll();
String jsonString = JSON.toJSONString(sorts);
session.setAttribute("sorts",sorts);
resp.setCharacterEncoding("utf-8");
resp.getWriter().print(jsonString);
}else if(path.equals("ss")){//种类查询
String idStr = req.getParameter("sortId");
int id = Integer.valueOf(idStr);
List<Book> books=new ArrayList<>();
if(id!=-1){
//有id
books = bs.getBookBySortId(id);
}else{
books=bs.getAll();
}
session.setAttribute("books",books);
resp.sendRedirect("jsp/booklist.jsp");
}else if(path.equals("bName")){//模糊查询
String bookName = req.getParameter("bookName");
List<Book> books = bs.getBookByName(bookName);
session.setAttribute("books",books);
resp.sendRedirect("jsp/booklist.jsp");
}else if(path.equals("add")){//添加
String isbn = req.getParameter("isbn");
String bookName = req.getParameter("bookName");
String bookAuthor = req.getParameter("bookAuthor");
String bookPublisher = req.getParameter("bookPublisher");
String bookPubYear = req.getParameter("bookPubYear");
String bookSort = req.getParameter("bookSort");
String bookPubLocation = req.getParameter("bookPubLocation");
String bookNums = req.getParameter("bookNums");
Book book=new Book();
book.setBook_name(bookName);
book.setBook_ISBN(isbn);
book.setBook_author(bookAuthor);
book.setBook_pub(bookPublisher);
book.setBook_photo_year(bookPubYear);
book.setBook_sort(Integer.valueOf(bookSort));
book.setBook_photo_location(bookPubLocation);
book.setBook_total(Integer.valueOf(bookNums));
resp.setCharacterEncoding("utf-8");
boolean flag = bs.addBook(book);
String msg="";
if(flag){
msg="添加成功!";
}else{
msg="添加失败!";
}
resp.getWriter().print(msg);
}else if(path.equals("delBook")){//单独删除
String idStr=req.getParameter("id");
boolean flag = bs.deleteBookById(Integer.valueOf(idStr));
String msg="删除失败";
if(flag){msg="删除成功";}
resp.getWriter().print(msg);
}else if(path.equals("delBooks")){//批量删除
String idList=req.getParameter("ids");
//分割字符串
String[] ids = idList.split(",");
boolean flag=false;
for (int i = 0; i <ids.length ; i++) {//循环删除
flag= bs.deleteBookById(Integer.valueOf(ids[i]));
}
String msg="删除失败";
if(flag){msg="删除成功";}
resp.getWriter().print(msg);
}else if(path.equals("update")){
//获取表单信息
String id = req.getParameter("id");
String bookName = req.getParameter("bookName");
String isbn = req.getParameter("isbn");
String author = req.getParameter("author");
String pub = req.getParameter("pub");
String sortId = req.getParameter("sortName");
String abs= req.getParameter("abstract");
String total = req.getParameter("total");
String photo = "";
String location = req.getParameter("location");
String year=req.getParameter("year");
boolean falg=false;//标注文件上传成功
//文件上传
//服务器文件系统上的绝对路径
String filePath=req.getSession().getServletContext().getRealPath("upload/");
//如果没有这个目录,则新建一个
File file=new File(filePath);
if(!file.exists()){
file.mkdir();
}
String message;
try {
//获取part对象
Part part=req.getPart("bookPhoto");
String type=part.getContentType();
//Arrays.asList 把数组转换成集合
List<String> types= Arrays.asList(TYPE.split(";"));
//类型在允许范围内
if(!(type==null || "".equals(type.trim())) && types.contains(type) ){
//提取文件名
String name = getFileName(part);
photo=name;
//将文件写入路径中
part.write(filePath+File.separator+name);
message="上传成功,文件名为"+name+",文件大小为:"+part.getSize()+"bytes!";
falg=true;
}else{
message="上传失败,类型不支持!";
}
}catch (IllegalStateException i){
message="上传失败!,文件过大,文件的最大限制为"+config.maxFileSize()+"bytes!";
}catch (Exception e){
e.printStackTrace();
message=e.getMessage();
}
if(falg){
Book book=new Book();
book.setBook_id(Integer.valueOf(id));
book.setBook_ISBN(isbn);
book.setBook_name(bookName);
book.setBook_author(author);
book.setBook_pub(pub);
book.setBook_sort(Integer.valueOf(sortId));
book.setBook_abstract(abs);
book.setBook_total(Integer.valueOf(total));
book.setBook_photo(photo);
book.setBook_photo_location(location);
book.setBook_photo_year(year);
//提交修改请求
boolean flag = bs.updateBook(book);
if(falg){
resp.getWriter().print("<script type='text/javascript'>alert('修改成功!');window.location('jsp/booklist.jsp')</script>");
}else{
resp.getWriter().print("<script type='text/javascript'>alert('修改失败!');window.location('jsp/booklist.jsp')</script>");
}
}
}
}
}
页面:(jsp)
页面的关键代码:
booklist.jsp
/*批量选中*/
function selectAll(obj){
if($(obj).is(':checked')){
$("input:checkbox").each(function(){
$(this).prop("checked",true);
})
}else{
$("input:checkbox").each(function(){
$(this).prop("checked",false);
})
}
}
/*批量删除*/
function deleteBooks(id){
var idList = [];//定义一个空数组
if(typeof(id) == "undefined"){
//批量删除
if(confirm("您确定要批量删除吗?")){
$("input[name='selectBook']:checked").each(function(i){//把所有被选中的复选框的值存入数组
idList[i] =$(this).val();
});
}else{
return;
}
}else{
if(confirm("请确定要删除该书籍吗?")){
//删除指定的书籍
idList[0] = id;
}else{
return;
}
}
$.ajax({
type:'post',
url:'../deleteBook' ,
dataType: 'json',
traditional:true,
data: {"ids":idList},
success:function(data){
alert(data.message);
if(data.result==1){
initBookList(1);
}
}
});
}
function delBook(id) {
$.post("<%=request.getContextPath()%>/BookServlet","path=delBook&id="+id,function (data) {
alert(data);
if(data=="删除成功"){
//使用jquery删除当前的ul
$("#"+id).parents("ul").remove();
}
},"text");
}
function delBooks() {
var idList=[];
$("input[name='select']:checked").each(function(i){
idList[i] =$(this).val();//获取id存入数组中
});
$.post("<%=request.getContextPath()%>/BookServlet","path=delBooks&ids="+idList,function (data) {
alert(data);
if(data=="删除成功"){
//使用jquery删除当前的ul
$("input[name='select']:checked").each(function(i){
$(this).parents("ul").remove();//循环删除
});
}
});
}
</script>
</head>
<body style="background:#fff;">
<div class="list">
<div id="ztai">
<form action="<%=request.getContextPath()%>/BookServlet?path=bName" method="post">
<div id="cx"><ul>
<li>书籍名称:</li>
<li id="wenben"><input type="text" id="bookName" name="bookName"/></li>
<li><input type="submit" value="查询" class="ann" /></li>
</ul></div>
</form>
<div class="ins">
<ul>
<a href="bookinfoadd.jsp" target="_self"><li id="dele">新增</li></a>
<a href="javascript:;" onclick="delBooks()"><li>批量删除</li></a>
</ul>
</div>
</div>
<ul id="biti">
<li id="tit"> <input type="checkbox" name="selectall" value="" onclick="selectAll(this)"/></li>
<li>序号</li>
<li>书籍名称</li>
<li>ISBN</li>
<li>书籍著者</li>
<li>分类号</li>
<li>分类名称</li>
<li>出版单位</li>
<li>发行地</li>
<li>出版时间</li>
<li>馆藏数</li>
<li id="caoz">基本操作</li>
</ul>
<div id="bookList">
<!--书籍列表-->
<c:forEach items="${books}" var="book">
<ul>
<li id="tit">
<input type="checkbox" name="select" value="${book.book_id}">
</li>
<li>${book.book_id}</li>
<li>${book.book_name}</li>
<li>${book.book_ISBN}</li>
<li>${book.book_author}</li>
<li>${book.book_sort}</li>
<li>${book.sortName}</li>
<li>${book.book_pub}</li>
<li>${book.book_photo_location}</li>
<li>${book.book_total}</li>
<li id="caoz">
<a href="bookinfoupdate.jsp?id=${book.book_id}" id="${book.book_id}" >
<img src="../images/edt.gif" width="16" height="16" />
<span>修改</span>
</a>
<a href="javascript:;" id="${book.book_id}" onclick="delBook(${book.book_id})" >
<img src="../images/del.gif" width="16" height="16" />
<span>删除</span>
</a>
</li>
</ul>
</c:forEach>
</div>
<div id="ztai2">
<!--分页-->
</div>
</body>
bookinfoupdate.jsp
<--<title>书籍信息新增修改</title>-->
<body style="background:#fff;color: #2b7dbb">
<%
String id = request.getParameter("id");
session.setAttribute("id",Integer.valueOf(id));
%>
<div id="tabbox" >
<form action="<%=request.getContextPath()%>/BookServlet?path=update" method="post" enctype="multipart/form-data">
<table align="center" border="1">
<tr><td colspan="4"><h3 align="center">书籍信息</h3></td></tr>
<c:forEach items="${books}" var="book">
<c:if test="${book.book_id==sessionScope.id}">
<tr>
<td>编号</td>
<td><input type="text" name="id" value="${book.book_id}"></td>
<td>书籍名称</td>
<td><input type="text" name="bookName" value="${book.book_name}"></td>
</tr>
<tr>
<td>ISBN</td>
<td><input type="text" name="isbn" value="${book.book_ISBN}"></td>
<td>作者</td>
<td><input type="text" name="author" value="${book.book_author}"></td>
</tr>
<tr>
<td>出版社</td>
<td><input type="text" name="pub" value="${book.book_pub}"></td>
<td>书籍类别</td>
<td><select name="sortName" id="">
<c:set var="count" value="0"/>
<c:forEach items="${sorts}" var="sort" >
<c:if test="${sort.sort_id==book.book_sort}">
<option value="${sort.sort_id}" selected>${sort.sort_name}</option>
</c:if>
<option value="${sort.sort_id}" >${sort.sort_name}</option>
</c:forEach>
</select></td>
</tr>
<tr>
<td>简介</td>
<td><input type="text" name="abstract" value="${book.book_abstract}"></td>
<td>馆藏数</td>
<td><input type="text" name="total" value="${book.book_total}"></td>
</tr>
<tr>
<td>图片</td>
<td><input type="text" name="photo" value="${book.book_photo}"><br><input type="file" name="bookPhoto" ></td>
<td>发行地</td>
<td><input type="text" name="location" value="${book.book_photo_location}"></td>
</tr>
<tr>
<td colspan="2">发行年限</td>
<td colspan="2"><input type="text" name="year" value="${book.book_photo_year}"> </td></tr>
<tr>
<td colspan="4" align="center"><input type="submit" value="提交"></td>
</tr>
</c:if>
</c:forEach>
</table>
</form>
</div>
</body>