【jsp案例】通过select查询父类子类数据表

有了无线级联的下拉列表，我们可以根据option的value 值来获得Book表的集合

http://blog.csdn.net/czk_love_wyq/article/details/42077417 无线级联思路

下面是效果图

可以看到当我们在下拉列表中选择不同的选项时查询的数据也不相同，并且当查询的数据为父类时，要连同子类的数据一起查询

先创建一张Book表（除了字段信息以外还要一个categoryId用来表示此书的类别）

逻辑

View层->通过select的option（之前jsp案例说过，选择不同选项时触发onchange事件，刷新本页）

控制层->获得相应的value值后，调用查询此类别是父类还是子类，如果是0是根类其他类是子类（无限级联）

数据访问层->如果是根类，则要用子查询，先查询类别表有哪些类别的父类Id和传进来的iD相同，然后查询book表 当categoryid in这些id

如果是子类，直接查询book表的categoryId即可

数据访问层<-将查询到的集合返回给控制层

控制层<-返回给View

View层<-如果集合不为空，则遍历集合，显示列表信息

以下是代码

bean

package cn.mybookshop.bean;public class Book {private int id;private String isbn;private String title;private String titleDir;private String author;private double utilPrice;private Category category;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public String getTitleDir() {return titleDir;}public void setTitleDir(String titleDir) {this.titleDir = titleDir;}public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}public double getUtilPrice() {return utilPrice;}public void setUtilPrice(double utilPrice) {this.utilPrice = utilPrice;}public Category getCategory() {return category;}public void setCategory(Category category) {this.category = category;}public String getIsbn() {return isbn;}public void setIsbn(String isbn) {this.isbn = isbn;}}

daoImpl

package cn.mybookshop.dao.Impl;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import cn.mybookshop.bean.Book;import cn.mybookshop.bean.Category;import cn.mybookshop.biz.Impl.CategoryBizImpl;import cn.mybookshop.dao.BookDao;import cn.mybookshop.dao.DB.DBHelper;public class BookDaoImpl implements BookDao{@Overridepublic List<Book> findBooksByCategoryId(int categoryId) {String sql=String.format("select Id,ISBN,Title,TitleDir,Author,UtilPrice,CategoryId from Book where CategoryId='%d'", categoryId);System.out.println(sql);DBHelper.open();ResultSet rs=DBHelper.executeQuery(sql);if(rs!=null){return ResultSetToList(rs);}return null;}@Overridepublic List<Book> findBooksByParentCategoryId(int categoryId) {String sql=String.format("select Id,ISBN,Title,TitleDir,Author,UtilPrice,CategoryId from Book where CategoryId in (select id from categroy where parentId='%d') ",categoryId);System.out.println(sql);DBHelper.open();ResultSet rs=DBHelper.executeQuery(sql);if(rs!=null){return ResultSetToList(rs);}return null;}public Book ResultSetToObject(ResultSet rs){Book book=new Book();Category category=null;try {category = new CategroyDaoImpl().findCategoryById(rs.getInt("CategoryId"));book.setId(rs.getInt("Id"));book.setAuthor(rs.getString("author"));book.setIsbn(rs.getString("Isbn"));book.setTitle(rs.getString("Title"));book.setTitleDir(rs.getString("TitleDir"));book.setUtilPrice(rs.getDouble("utilPrice"));book.setCategory(category);return book;} catch (SQLException e) {e.printStackTrace();}return null;}public List<Book> ResultSetToList(ResultSet rs){if(rs!=null){List<Book>  list=new ArrayList<Book>();try {while(rs.next()){Book book=ResultSetToObject(rs);list.add(book);}} catch (SQLException e) {e.printStackTrace();}return list;}return null;}}

package cn.mybookshop.biz.Impl;import java.util.List;import cn.mybookshop.bean.Book;import cn.mybookshop.bean.Category;import cn.mybookshop.biz.BookBiz;import cn.mybookshop.dao.BookDao;import cn.mybookshop.dao.CategoryDao;import cn.mybookshop.dao.Impl.BookDaoImpl;import cn.mybookshop.dao.Impl.CategroyDaoImpl;public class BookBizImpl implements BookBiz {private BookDao bookDao = null;private CategoryDao categoryDao = null;public BookBizImpl(){if(bookDao==null){this.bookDao=new BookDaoImpl();}if(categoryDao==null){this.categoryDao=new CategroyDaoImpl();}}@Overridepublic void setCategoryDao(CategoryDao categoryDao) {this.categoryDao=categoryDao;}@Overridepublic void setBookDao(BookDao bookDao) {this.bookDao=bookDao;}@Overridepublic CategoryDao getCategoryDao() {return categoryDao;}@Overridepublic BookDao getBookDao() {return bookDao;}@Overridepublic List<Book> findBooksByCategoryId(int categoryId) {Category category=categoryDao.findCategoryById(categoryId);return findBooksByCategoryId(category);}@Overridepublic List<Book> findBooksByCategoryId(Category category) {int id=category.getId();int parentId=category.getParentId();List<Book> list=null;if(parentId!=0){list=bookDao.findBooksByCategoryId(id);}else{list=bookDao.findBooksByParentCategoryId(id);}return list;}}

VIEW

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><%@page import="cn.mybookshop.bean.*"%><%@page import="cn.mybookshop.bean.Book"%><%@page import="cn.mybookshop.biz.*"%><%@page import="cn.mybookshop.biz.Impl.*"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><% String CurrId=request.getParameter("id");if(CurrId!=null){System.out.println("CurrId:"+CurrId+"得到");}%><%List<Book> books=null;if(CurrId!=null){int Id=Integer.parseInt(CurrId);BookBiz bookBiz=new BookBizImpl();books=bookBiz.findBooksByCategoryId(Id);if(null!=books){System.out.println("booksize-------"+books.size());}} %><html><head><title>My JSP 'BookManager.jsp' starting page</title><script type="text/javascript">function search(oSearch){if(oSearch.value=="-1"){alert("请输入你想查询的类别");return;}location.href="BookManager.jsp?id="+oSearch.value;}</script></head><body><div  style="margin-right: auto; text-align: center; margin-left: auto; margin-bottom: 0px; margin-top: 0px"><select style="width: 165px; " onChange="search(this)"><option value="-1">-----请选择-----</option><%CategoryBiz cb=new CategoryBizImpl();List<Map<Category,List<Category>>> list=cb.findAllCategory();for(Map<Category,List<Category>> map:list){Set<Category> set=map.keySet();for(Category category:set){  System.out.println(null!=CurrId&&CurrId==String.valueOf(category.getId()));String option="<option value='%d' %s>%s</option>";if(null!=CurrId&&CurrId.equals(String.valueOf(category.getId()))){out.println(String.format(option,category.getId(),"selected=selected",category.getCategoryName()));}else{out.println(String.format(option,category.getId()," ",category.getCategoryName()));}List<Category> childList=map.get(category);for(Category cate:childList){if(null!=CurrId&&CurrId.equals(String.valueOf(cate.getId()))){out.println(String.format(option,cate.getId(),"selected=selected","-----"+cate.getCategoryName()));}else{out.println(String.format(option,cate.getId()," ","-----"+cate.getCategoryName()));}}}} %> </select> </div> <!--    <c:if test="${not empty books}" >              <table border="1"  style="width:500px">               <tr><td>编号</td><td>图书标题</td><td>作者</td><td>封面</td><td></td></tr>               <c:forEach var="book" items="${books}" varStatus="status">                  <tr><td>${status.index+1}</td><td>${book.title}</td><td>${book.author}</td><td><img src='images/convers/${book.isbn}.jpg' alt='${book.title}' style="width:80px;height:150px"/></td><td><a href="admin/book/BookDetail.jsp?bookid=${book.id}">详细</a> <a href="admin/book/UpdateBookInfo.jsp?bookid=${book.id}"">修改</a> <a href="admin/book/DelBookInfo.jsp?bookid=${book.id}"">删除</a></td></tr>               </c:forEach>             </table>            </c:if>--><%if(null!=books&&books.size()>0){out.println("<table style='width:90%'>");out.println("<tr><td>编号</td><td>图书标题</td><td>作者</td><td>封面</td><td>操作</td></tr>");int i=1;for(Book b:books){%><tr><td><%=i %></td><td><%=b.getTitle() %></td><td><%=b.getAuthor() %></td><td><img src='../images/<%=b.getIsbn() %>.jpg' alt=<%=b.getTitleDir() %> style="width:80px;height:100px"/></td><td><a href='admin/book/BookDetail.jsp?bookid=<%=b.getId()%>'>详细</a> <a href='admin/book/UpdateBookInfo.jsp?bookid=<%=b.getId()%>'>修改</a> <a href='admin/book/DelBookInfo.jsp?bookid=<%=b.getId()%>'>删除</a></td></tr><%++i;}out.println("</table>");} %> </body></html>