(1)多组合条件模糊查询页面:query.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>高级搜索</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<h3 align="center">高级搜索</h3>
<form action="<c:url value='/servlet/CustomerServlet'/>" method=get>
<input type="hidden" name="method" value="query" />
<table border="0" align="center" width="40%" style="margin-left: 100px;">
<tr>
<td width="100px">客户名称</td>
<td width="40%">
<input type="text" name="cname"/>
</td>
</tr>
<tr>
<td>客户性别</td>
<td>
<select name="gender">
<option value="">==请选择性别==</option>
<option value="1">男</option>
<option value="0">女</option>
</select>
</td>
</tr>
<tr>
<td>手机</td>
<td>
<input type="text" name="cellphone"/>
</td>
<td>
<label id="cellphoneError" class="error"> </label>
</td>
</tr>
<tr>
<td>邮箱</td>
<td>
<input type="text" name="email"/>
</td>
<td>
<label id="emailError" class="error"> </label>
</td>
</tr>
<tr>
<td> </td>
<td>
<input type="submit" value="搜索"/>
<input type="reset" value="重置"/>
</td>
<td> </td>
</tr>
</table>
</form>
</body>
</html>
(2)列表结果页面:list.jsp(可通过查询按钮进入,也可通过模糊查询按钮进入)
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="com.cstm.domain.Customer" import="com.cstm.util.TransferUtil"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>客户列表</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<h3 align="center">客户列表</h3>
<table border="1" width="70%" align="center">
<tr>
<th>客户姓名</th>
<th>性别</th>
<th>生日</th>
<th>手机</th>
<th>邮箱</th>
<th>描述</th>
<th>操作</th>
</tr>
<c:forEach items="${pb.beanList }" var="cus">
<tr>
<td>${cus.cname }</td>
<td>
<c:if test="${cus.gender eq '1'}">
<c:out value="男" />
</c:if>
<c:if test="${cus.gender eq '0'}">
<c:out value="女" />
</c:if>
</td>
<td>${cus.birthday }</td>
<td>${cus.cellphone }</td>
<td>${cus.email }</td>
<td>${cus.description }</td>
<td>
<a href="<c:url value='/servlet/CustomerServlet?method=findByCid&cid=${cus.cid }'/>">编辑</a>
<a href="<c:url value='/servlet/CustomerServlet?method=delete&cid=${cus.cid }'/>">删除</a>
</td>
</tr>
</c:forEach>
</table>
<br />
<!-- 给出分页相关的链接 -->
<center>
第${pb.pc }页/共${pb.tp }页
<a href="${pb.url }&pc=1">首页</a>
<c:if test="${pb.pc>1 }">
<a href="${pb.url }&pc=${pb.pc-1 }">上一页</a>
</c:if>
<!--
总结中间页面列表的显示:
1,如果页面总数小于等于10,那么全部显示出来。begin=1,end=10
2,如果页面总数大于10,那么会有公式:begin=当前页面-5,end=当前页面+4
如果begin<1,那么设置begin=1,end=10
如果end>总页面数,那么设置begin=总页面数-9,end=总页面数。
-->
<c:choose>
<c:when test="${pb.tp<=10 }">
<c:set var="begin" value="1" />
<c:set var="end" value="${pb.tp }" />
</c:when>
<c:otherwise>
<c:set var="begin" value="${pb.pc-5 }" />
<c:set var="end" value="${pb.pc+4 }"/>
<!-- 如果头溢出 -->
<c:if test="${begin<1 }">
<c:set var="begin" value="1" />
<c:set var="end" value="10"/>
</c:if>
<!-- 如果尾溢出 -->
<c:if test="${end>pb.tp }">
<c:set var="begin" value="${pb.tp-9 }" />
<c:set var="end" value="${pb.tp }"/>
</c:if>
</c:otherwise>
</c:choose>
<!-- 循环显示 -->
<c:forEach var="i" begin="${begin }" end="${end }">
<c:choose>
<c:when test="${i eq pb.pc }">
[${i }]
</c:when>
<c:otherwise>
<a href="${pb.url }&pc=${i }">[${i }]</a>
</c:otherwise>
</c:choose>
</c:forEach>
<c:if test="${pb.pc<pb.tp }">
<a href="${pb.url }&pc=${pb.pc+1 }">下一页</a>
</c:if>
<a href="${pb.url }&pc=${pb.tp }">尾页</a>
</center>
</body>
</html>
(3)servlet
/**
* 多条件组合查询
* @throws UnsupportedEncodingException
*
* */
public String query(HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException{
Customer cus=CommonUtils.toBean(request.getParameterMap(), Customer.class);
/**
* 处理GET请求方式的编码问题
*
* */
cus=encoding(cus);
int pc=getPc(request);
int ps=10;//给定ps的值,每页10行
PageBean<Customer> pb=service.query(cus,pc,ps);
String url=getUrl(request);
pb.setUrl(url);
request.setAttribute("pb", pb);
return "/list.jsp";
}
private Customer encoding(Customer cus) throws UnsupportedEncodingException {
String cname=cus.getCname();
String gender=cus.getGender();
String cellphone=cus.getCellphone();
String email=cus.getEmail();
if(cname!=null && !cname.trim().isEmpty()){
cname=new String(cname.getBytes("ISO-8859-1"),"utf-8");
cus.setCname(cname);
}
if(gender!=null && !gender.trim().isEmpty()){
gender=new String(gender.getBytes("ISO-8859-1"),"utf-8");
cus.setGender(gender);
}
if(cellphone!=null && !cellphone.trim().isEmpty()){
cellphone=new String(cellphone.getBytes("ISO-8859-1"),"utf-8");
cus.setCellphone(cellphone);
}
if(email!=null && !email.trim().isEmpty()){
email=new String(email.getBytes("ISO-8859-1"),"utf-8");
cus.setEmail(email);
}
return cus;
}
private String getUrl(HttpServletRequest request){
String contextPath=request.getContextPath();//项目名
String servletPath=request.getServletPath();//servlet路径
String queryString=request.getQueryString();//参数字符串
if(queryString.contains("&pc=")){
int index=queryString.lastIndexOf("&pc=");//不要pc了。
queryString=queryString.substring(0, index);
}
return contextPath+servletPath+"?"+queryString;
}
public String findAll(HttpServletRequest request,HttpServletResponse response){
/*
* 获取页面传递的pc
* 给定ps的值
* 使用pc和ps调用service方法,得到PageBean,保存到request域。
* */
int pc=getPc(request);
int ps=10;//给定ps的值,每页10行
PageBean<Customer> pb=service.findAll(pc,ps);
pb.setUrl(getUrl(request));
request.setAttribute("pb", pb);
return "/list.jsp";
}
private int getPc(HttpServletRequest request){
String value=request.getParameter("pc");
if(value==null || value.trim().isEmpty()){
return 1;
}
else{
return Integer.parseInt(value);
}
}
(4)Dao
/**
* @author Demetria
* 多条件组合查询
* query(Customer cus)
* @throws SQLException
*/
public PageBean<Customer> query(Customer cus,int pc,int ps) throws SQLException{
PageBean<Customer> pb=new PageBean<Customer>();
QueryRunner qr=new TXQueryRunner();
StringBuilder sb=new StringBuilder("select * from t_customer where 1=1");
StringBuilder sql=new StringBuilder("select count(*) from t_customer where 1=1");
List<Object> params=new ArrayList<Object>();
//cname
String cname=cus.getCname();
if(cname!=null && !cname.trim().isEmpty()){
sb.append(" and cname like ?");
sql.append(" and cname like ?");
params.add("%"+cname+"%");
}
//gender
String gender=cus.getGender();
if(gender!=null && !gender.trim().isEmpty()){
sb.append(" and gender = ?");
sql.append(" and gender = ?");
params.add(gender);
}
//cellphone
String cellphone=cus.getCellphone();
if(cellphone!=null && !cellphone.trim().isEmpty()){
sb.append(" and cellphone like ?");
sql.append(" and cellphone like ?");
params.add("%"+cellphone+"%");
}
//email
String email=cus.getEmail();
if(email!=null && !email.trim().isEmpty()){
sb.append(" and email like ?");
sql.append(" and email like ?");
params.add("%"+email+"%");
}
Number number=(Number)qr.query(sql.toString(), new ScalarHandler(),params.toArray());
int tr=number.intValue();
pb.setTr(tr);
sb.append(" limit ?,?");
params.add(pc);
params.add(ps);
pb.setPc(pc);
pb.setPs(ps);
List<Customer> list=qr.query(sb.toString(), new BeanListHandler<Customer>(Customer.class),params.toArray());
pb.setBeanList(list);
return pb;
}
public PageBean<Customer> findAll(int pc,int ps){
/**
* 得到PageBean对象
* 设置pc
* 设置ps
* 得到tr并设置
* 得到beanList,并赋值。
* 返回pb
* */
try{
PageBean<Customer> pb=new PageBean<Customer>();
pb.setPc(pc);
pb.setPs(ps);
QueryRunner qr=new TXQueryRunner();
StringBuilder sql=new StringBuilder("select * from t_customer order by cname limit ?,?");
Object[] params={(pc-1)*ps,ps};
String sql1="select count(*) from t_customer";
Number number=(Number) qr.query(sql1,new ScalarHandler());
int tr=number.intValue();
pb.setTr(tr);
List<Customer> list=qr.query(sql.toString(), new BeanListHandler<Customer>(Customer.class),params);
pb.setBeanList(list);
return pb;
}catch(SQLException e){
throw new RuntimeException(e);
}
}