5.5 jsp查询操作

编写查询
需求：
1.在booklist.jsp页面需要显示所有图书类型
1.需要查询全部图书类型
Booklist.jsp

图书借阅系统

图书分类 全部   图书名称  是否借阅 请选择 未借阅 已借阅  

Servlet中查询的代码编写
查询图书类型

/**
 * 查询图书类型
 * @param request
 * @param response
 * @throws ServletException
 * @throws IOException
 */
protected void queryBookType(HttpServletRequest request, HttpServletResponse response)
		throws ServletException, IOException {
	//调用service层的方法
List<BookType> bookTypes=	bookTypeService.queryAll();
//存
HttpSession  session=request.getSession();
 session.setAttribute("bookTypes",bookTypes );
	
}

三层编写

public class BookTypeDaoImpl implements BookTypeDao {

private  QueryRunner  queryRunner=C3P0Util.getQueryRunner();

@Override
public List<BookType> queryAll() {
	String sql="SELECT  * from book_type";
	List<BookType> bookTypes=null;	
try {
	bookTypes=	queryRunner.query(sql,new BeanListHandler<BookType>(BookType.class));
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
	return bookTypes;
}

}

页面是带3个条件的分页的动态查询
在servlet中需要获取到3个参数及当前页码数
参数:
图书分类
图书名称
是否借阅
当前页码数

分析：
页面 需要显示当前页码数 和总页数
最终页面要显示的数据:
图书的集合
当前页码数
总页数
选择图书类型及按图书名称和状态查询后 在对应的位置参数要回显

#7行数数据 如果每页显示3行–》3页
#总页数=总记录数/每页显示的行数 (总记录数%每页显示的行数==0)
#总页数=总记录数/每页显示的行数+1 (总记录数%每页显示的行数!=0)

#第一页
SELECT * from book_info LIMIT 0,3
#第二页
SELECT * from book_info LIMIT 3,3
#第三页
SELECT * from book_info LIMIT 6,3
#第一个参数与当前页码数的关系 （当前页码数-1）*每页显示的行数

–》将分页需要的参数 封装成javaBean

/**

• 属性设置的顺序:1.pageSize(每页显示的行数) 2.rows(总行数) 3. pageNo(当前页码数)

• @author Administrator

• @param
  */
  public class PageBean {

  private List lists;// 存放当前页面 数据的集合
  private Integer pageNo = 1;// 当前页码数
  private Integer pageSize = 4; // 每页显示的行数
  private Integer totalPage;// 总页数
  private Integer rows;// 总行数

  public List getLists() {
  return lists;
  }

  public void setLists(List lists) {
  this.lists = lists;
  }

  public Integer getPageNo() {
  return pageNo;
  }

  public void setPageNo(Integer pageNo) {
  if (null == pageNo) {
  this.pageNo = 1;
  // 当前页码数 一直点击下一页 变成了 5了 但是数据库查询出来的总页数 只有3页
  // 这种情况 当前页码数=总页数(3)
  //如果在进行上述计算的时候 查询总页数的代码有问题 导致总页数=0
  //这个时候当前页码数=0
  } else if (pageNo > this.totalPage&&this.totalPage>0) { // --》totalPage有值–》pageSize(rows) 要预防多条件查询 总记录数为0 导致当前页码数为0 limit第一个参数为负数
  this.pageNo = this.totalPage;
  } else if(pageNo<=0){
  this.pageNo = 1;
  }else{
  this.pageNo=pageNo;
  }
  }

  public Integer getPageSize() {
  return pageSize;
  }

  public void setPageSize(Integer pageSize) {
  this.pageSize = pageSize;
  }

  public Integer getTotalPage() {
  return totalPage;
  }

  public Integer getRows() {
  return rows;
  }

  public void setRows(Integer rows) {
  this.rows = rows;
  //自动计算出总页数
  this.totalPage=rows%this.pageSize==0?rows/this.pageSize:rows/this.pageSize+1;
  }

}

动态条件查询分页代码编写:

分页动态查询Servlet
protected void queryBook(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 取调存转
String booktypeid = request.getParameter(“booktypeid”);
String bookname = request.getParameter(“bookname”);
String borrow = request.getParameter(“borrow”);
String pageno = request.getParameter(“pageno”);

	// 查询总行数
	int count = bookService.bookCount(Integer.parseInt(booktypeid), bookname, Integer.parseInt(borrow));
	// 创建分页工具类的对象
	PageBean<Book> pageBean = new PageBean<Book>();
	pageBean.setPageSize(3);// 每页显示的行数
	pageBean.setRows(count);
	// null 注意 pageno要检测是否为null 为null 报 NumberFromatException
	pageBean.setPageNo(Integer.parseInt(pageno));
	// 获取分页的第一个参数
	int first = (pageBean.getPageNo() - 1) * pageBean.getPageSize();
	// 调用service层 将 查询的条件及 limit的2个参数传递过去
List<Book> books=	bookService.queryBook(Integer.parseInt(booktypeid), bookname, Integer.parseInt(borrow), first,
			pageBean.getPageSize());

}

Service

public class BookServiceImpl implements BookService {

private BookDao bookdao = new BookDaoImpl();

@Override
public int addBook(Book book) {

	return bookdao.addBook(book);
}

@Override
public List<Book> queryBook(int booktype, String bookname, int borrow, int frist, int end) {
	// TODO Auto-generated method stub
	return bookdao.queryBook(booktype, bookname, borrow, frist, end);
}

@Override
public int bookCount(int booktype, String bookname, int borrow) {
	// TODO Auto-generated method stub
	return 0;
}

}

Dao
public interface BookDao {

int addBook(Book  book);

int bookCount(int booktype,String bookname, int borrow);
/**
 *  动态条件分页查询
 * @param booktype  图书类型id  0代码查询全部
 * @param bookname  图书名称
 * @param borrow    是否借阅  -1代码不按状态查询
 * @param frist    分页参数1
 * @param end      分页参数2
 * @return
 */
List<Book>  queryBook(int booktype,String bookname, int borrow,int frist,int end);

}

public class BookDaoImpl implements BookDao {

private QueryRunner queryRunner = C3P0Util.getQueryRunner();

@Override
public int addBook(Book book) {
	String sql = "insert into book_info(book_code,book_name,book_type,book_author,publish_press,publish_date,is_borrow) VALUES(?,?,?,?,?,?,?) ";
	int num = 0;
	try {
		num = queryRunner.update(sql, book.getBook_code(), book.getBook_name(), book.getBook_type(),
				book.getBook_author(), book.getPublish_press(), book.getPublish_date(), book.getIs_borrow());
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}

	return num;
}

@SuppressWarnings({ "unchecked", "rawtypes", "null" })
@Override
public List<Book> queryBook(int booktype, String bookname, int borrow, int frist, int end) {
	StringBuffer sb = new StringBuffer();
	List par = new ArrayList();
	List<Book> books = null;
	sb.append(
			"SELECT book_info.book_id,book_info.book_code,book_info.book_name,book_info.book_type,book_info.book_author,book_info.publish_press,book_info.publish_date,book_info.is_borrow,book_info.path, book_type.type_name FROM  book_info INNER JOIN book_type ON book_info.book_type = book_type.id  where  1=1");
	if (booktype != 0) {
		sb.append(" and  book_type=? ");
		par.add(booktype);
	}
	if (bookname != null && !"".equals(bookname)) {

		sb.append("  and book_name like ?");
		par.add("%" + bookname + "%");
	}
	if (borrow != -1) {
		sb.append("  and  is_borrow=?");
		par.add(borrow);
	}
	sb.append("  limit ?,?");
	par.add(frist);
	par.add(end);
	try {
		books = queryRunner.query(sb.toString(), new BeanListHandler<Book>(Book.class), par.toArray());
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return books;
}

@SuppressWarnings({ "rawtypes", "unchecked" })
@Override
public int bookCount(int booktype, String bookname, int borrow) {
	StringBuffer sb = new StringBuffer();
	sb.append("select  COUNT(*) from book_info where  1=1 ");
	List par = new ArrayList();
	Long count = 0l;
	if (booktype != 0) {
		sb.append(" and  book_type=? ");
		par.add(booktype);
	}
	if (bookname != null && !"".equals(bookname)) {

		sb.append("   and book_name like ?");
		par.add("%" + bookname + "%");
	}
	if (borrow != -1) {
		sb.append("   and  is_borrow=?");
		par.add(borrow);
	}

	try {
		count = (Long) queryRunner.query(sb.toString(), new ScalarHandler(1), par.toArray());
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}

	return count.intValue();
}

}

测试
public class TestServlet {
public static void main(String[] args) {

	BookService bookService = new BookServiceImpl();
	// 取调存转
	String booktypeid = "1";
	String bookname = "我";
	String borrow = "0";
	String pageno = "1";

	// 查询总行数
	int count = bookService.bookCount(Integer.parseInt(booktypeid), bookname, Integer.parseInt(borrow));
	// 创建分页工具类的对象
	PageBean<Book> pageBean = new PageBean<Book>();
	pageBean.setPageSize(3);// 每页显示的行数
	pageBean.setRows(count);
	// null 注意 pageno要检测是否为null 为null 报 NumberFromatException
	pageBean.setPageNo(Integer.parseInt(pageno));
	// 获取分页的第一个参数
	int first = (pageBean.getPageNo() - 1) * pageBean.getPageSize();
	// 调用service层 将 查询的条件及 limit的2个参数传递过去
	List<Book> books = bookService.queryBook(Integer.parseInt(booktypeid), bookname, Integer.parseInt(borrow),
			first, pageBean.getPageSize());

	for (Book book : books) {
		System.out.println(book);
	}
}

}