java web多条件查询_【JavaWeb】实现动态多条件查询

实体类对象：

package com.ysw.web.entity;

public class Book {

private Integer id; //编号

private String name; //书名

private Double price; //价格

private String category; //类型

private Integer pnum; //库存

private String imgurl; //图片路径

private String description; //描述

private String author; //作者

private Integer sales; //销售量

public Book() {

}

public Book(String name, Double price, String category, Integer pnum, String imgurl, String description, String author, Integer sales) {

this.name = name;

this.price = price;

this.category = category;

this.pnum = pnum;

this.imgurl = imgurl;

this.description = description;

this.author = author;

this.sales = sales;

}

public Book(Integer id, String name, Double price, String category, Integer pnum, String imgurl, String description, String author, Integer sales) {

this.id = id;

this.name = name;

this.price = price;

this.category = category;

this.pnum = pnum;

this.imgurl = imgurl;

this.description = description;

this.author = author;

this.sales = sales;

}

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 Double getPrice() {

return price;

}

public void setPrice(Double price) {

this.price = price;

}

public String getCategory() {

return category;

}

public void setCategory(String category) {

this.category = category;

}

public Integer getPnum() {

return pnum;

}

public void setPnum(Integer pnum) {

this.pnum = pnum;

}

public String getImgurl() {

return imgurl;

}

public void setImgurl(String imgurl) {

this.imgurl = imgurl;

}

public String getDescription() {

return description;

}

public void setDescription(String description) {

this.description = description;

}

public String getAuthor() {

return author;

}

public void setAuthor(String author) {

this.author = author;

}

public Integer getSales() {

return sales;

}

public void setSales(Integer sales) {

this.sales = sales;

}

@Override

public String toString() {

final StringBuffer sb = new StringBuffer("Book{");

sb.append("id=").append(id);

sb.append(", name='").append(name).append('\'');

sb.append(", price=").append(price);

sb.append(", category='").append(category).append('\'');

sb.append(", pnum=").append(pnum);

sb.append(", imgurl='").append(imgurl).append('\'');

sb.append(", description='").append(description).append('\'');

sb.append(", author='").append(author).append('\'');

sb.append(", sales=").append(sales);

sb.append('}');

return sb.toString();

}

}

Service层：

//多条件动态查询

public Listsearch(Integer id, String name, Double maxPrice, Double minPrice,

String category, Integer maxPnum, Integer minPnum, String imgurl,

String description, String author, Integer maxSales,Integer minSales) {

return bookDao.search(id, name, maxPrice, minPrice, category, maxPnum, minPnum, imgurl,

description, author, maxSales, minSales);

}

Dao层：

//复杂条件查询

public Listsearch(Integer id, String name, Double maxPrice, Double minPrice,

String category, Integer maxPnum, Integer minPnum, String imgurl,

String description, String author, Integer maxSales,Integer minSales){

//这个是用于存储查询的结果的

Listbooks = new ArrayList();

//这个用于存储查询的条件参数的

List list = new ArrayList();

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

//创建资源链接对象

conn = getConnection();

//定义sql语句

String sql = "select * from book where 1 = 1";

//书本编号不为空的时候

if (id != 0) {

sql = sql + " and id = ?";

list.add(id);

}

//去除掉姓名的空白位置

if (!"".equals(name.trim())){

sql = sql + " and name like ?";

list.add("%" + name.trim() + "%");

}

//最高价格

if (maxPrice != 0.0) {

sql = sql + " and price < ?";

list.add(maxPrice);

}

//最低价格

if (minPrice != 0.0) {

sql = sql + " and price > ?";

list.add(minPrice);

}

//如果类别名不为空

if (!"".equals(category.trim())) {

sql = sql + " and category like ?";

list.add("%" + category.trim() + "%");

}

//最大库存

if (maxPnum != 0) {

sql = sql + " and pnum < ?";

list.add(maxPnum);

}

//最小库存

if (minPnum != 0) {

sql = sql + " and pnum > ?";

list.add(minPnum);

}

//作品封面

if (!"".equals(imgurl.trim())) {

sql = sql + "and imgurl like ?";

list.add("%" + imgurl.trim() + "%");

}

//作品描述

if (!"".equals(description.trim())){

sql = sql + " and description like ?";

list.add("%" + description.trim() + "%");

}

//作者

if (!"".equals(author.trim())){

sql = sql + " and author like ?";

list.add("%" + author.trim() + "%");

}

//最大销量

if (maxSales != 0){

sql = sql + " and sales < ?";

list.add(maxSales);

}

//最低销量

if (minSales != 0){

sql = sql + " and sales > ?";

list.add(minSales);

}

//创建sql执行对象

pstmt = conn.prepareStatement(sql);

//给?参数进行赋值

if (list.size() > 0) {

for (int i = 0; i < list.size(); i++) {

pstmt.setObject(i+1,list.get(i));

}

}

//执行sql

rs = pstmt.executeQuery();

//遍历查询

while (rs.next()){

Book book = new Book();

book.setId(rs.getInt("id"));

book.setName(rs.getString("name"));

book.setPrice(rs.getDouble("price"));

book.setCategory(rs.getString("category"));

book.setPnum(rs.getInt("pnum"));

book.setImgurl(rs.getString("imgurl"));

book.setDescription(rs.getString("description"));

book.setAuthor(rs.getString("author"));

book.setSales(rs.getInt("sales"));

books.add(book);

}

} catch (Exception e) {

e.printStackTrace();

} finally {

//关闭资源链接对象

close(rs,pstmt,conn);

}

//返回一个带有参数的list集合

return books;

}

Jsp页面：

国际图书商城

国际图书商城

多条件动态查询

编号:

书名:

最高价格:

最低价格:

类别:

最大库存:

最小库存:

封面:

描述:

作者:

最高售量:

最低售量:

编号

书名

价格

类别

库存

封面

描述

作者

售量

--%>

${vs.count}

${book.name}

${book.price}

${book.category}

${book.pnum}

${book.imgurl}

${book.description}

${book.author}

${book.sales}

请选择操作：

共${pageBean.count}条记录,共${pageBean.totalPage}页