一、.固定条件,直接查询数据库,把结果进行分页显示出来;
见博客
https://blog.csdn.net/qq_37591637/article/details/82951647
二、条件不定,查询数据库,把查询结果进行分页显示
如下图;包含四个条件,用户在选择的时候可以选择一个条件、任意两个条件、三个条件、全部条件进行查询,
思路:
1,jjsp把用户选择的条件参数传递到A.servlet中;
2 在A.servlet中用户选择条件,根据条件组成一个sql语句,把这个没有进行limit ?,?分页的数据传递到一个中介的B.servlet中
3.在B.servlet中,把这个sql语句进行分页查询
遇到的难点:
1.乱码,:
整个过程涉及到:A.servlet传递参数到B.servlet 、 B.servlet传递参数到C.jsp 、C.jsp 传递参数到B.servlet 三个过程
其中 A.servlet传递参数到B.servlet C.jsp 传递参数到B.servlet 总有一个乱码,怎么处理都不行?总有一个正常一个乱码?
解决方式:
一样的参数,接受的时候用了两个字符串接受,分开处理;
A.servlet
package cn.com.servlet;
import java.io.IOException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.com.pack.*;
public class SearchCondition extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//这个servlet的主要作用是根据用户的选择才查询出想要的结果
String name=request.getParameter("name");
if(name!=null&&name!=""){
name = URLEncoder.encode(name, "ISO-8859-1");
name = URLDecoder.decode(name, "UTF-8");
}
String number=request.getParameter("num");
if(number!=null&&number!=""){
number = URLEncoder.encode(number, "ISO-8859-1");
number = URLDecoder.decode(number, "UTF-8");
}
String state=request.getParameter("states");
state = URLEncoder.encode(state, "ISO-8859-1");
state = URLDecoder.decode(state, "UTF-8");
String part=request.getParameter("organs");
part = URLEncoder.encode(part, "ISO-8859-1");
part = URLDecoder.decode(part, "UTF-8");
//1.1把收到的信息都存储到一个集合里面,如果是空的就筛选掉,放进一个新的集合
//penson(假如你的数据放在对象中)
Map<String,String> map =new HashMap<String,String>();
String sql =" select * from person_info where 1=1 ";
Person_info person=new Person_info();
if(name!= null&&name!=""){
map.put("name",name);
}else if(number!=null&&number!=""){
map.put("number",number);
}else if(part!=null&&part!=""){
if(part.equals("全部")){
}else{
map.put("part",part);
}
}
else if(state!=null&&state!=""){
if(state.equals("全部")){
}else{
map.put("state",state);
}
}
Set<String> key = map.keySet();
for (String i : key) {
sql=sql+"and "+i+"= '"+map.get(i)+"'";
}
request.setCharacterEncoding("utf-8");
System.out.println("第一次传过去的sql:"+sql);
request.getRequestDispatcher("/SearchCondition_Media?sql="+sql).forward(request, response);
}
}
B.servlet
package cn.com.servlet;
import java.io.IOException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.com.jdbc.JdbcUtils;
import cn.com.pack.Person_info;
@SuppressWarnings("serial")
public class SearchCondition_Media extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 条件查询媒介
// 1.数据库里面数据的总条数;
int countpage = 0;
request.setCharacterEncoding("utf-8");
String sql = request.getParameter("sql");
String sql1=request.getParameter("sqls");
if(sql1!=null&&!("".equals(sql1))){
sql1 = new String (sql1.getBytes("ISO-8859-1"),"UTF-8");
System.out.println("sql1:"+sql1);
}
if(sql==null||"".equals(sql)){
sql=sql1;
}
System.out.println("sql:" + sql);
ResultSet rs0 = JdbcUtils.select(sql, null);
List<Person_info> list = new ArrayList<Person_info>();
// 分页查询
try {
while (rs0.next()) {
countpage++;
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
// 每页显示多少行
int limt = 10;
// 2.如果一页是10条数据的话,那么就是all页
int all = (countpage / limt) + 1;
// 这个是从前台获取的数据,是上一页还是下一页
int cpage = 1;
// 这个是上一页还是下一页的数据
String currentpage = request.getParameter("currentpage");
if (currentpage == null || currentpage == "") {
cpage = 1;
} else {
cpage = Integer.parseInt(currentpage);
// 如果到了最后一页,用户再点击下一页的时候跳转到第一页
if (cpage == (all + 1)) {
cpage = 1;
// 如果到了第一页,用户再点击下一页的时候跳转到最后一页
} else if (cpage == 0) {
cpage = all;
}
}
// 3.开始查询的数据
int start = limt * (cpage - 1);
// 4.结束查询的数据
int end = start + (limt - 1);
// 这个代码是查询数据库里面的person_info,然后循环遍历显示在页面上
// sql语句的意思就是limit 索引,显示索引以后多少行
String sqls = sql + " limit ?,?";
// 如果到了最后一页的时候,
if (end > countpage) {
limt = countpage - start;
}
int sz[] = { start, limt };
ResultSet rs = JdbcUtils.selectint(sqls, sz);
try {
while (rs.next()) {
Person_info pin = new Person_info(rs.getString(1),
rs.getString(2), rs.getString(3), rs.getString(4),
rs.getString(5), rs.getString(6));
list.add(pin);
}
request.setAttribute("list", list);
request.setAttribute("cpage", cpage);
request.setAttribute("all", all);
request.setAttribute("address", "/Socket/SearchCondition_Media");
request.getRequestDispatcher("/Personnel_definition.jsp?sql="+sql).forward(
request, response);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 1.2根据新的集合的来进行查询
}
}
C.jsp
<div id="center-right">
<!-- 人员信息界面-->
<form action="/Socket/SearchCondition" method="post">
<br>
<span class="peo_left">人员名称:<input type="text" name="name"/></span>
<span class="peo_left" style="margin-left: 117px;">人员编号:<input type="text" name="num"/></span><br>
<span class="peo_left">所属机构:
<select name="organ" id="organ">
<option onclick="organ_se();">所有</option>
<option onclick="organ_se();">公司</option>
<option onclick="organ_se();">新龙项目部</option>
<option onclick="organ_se();">中矿</option>
</select>
</span>
<span class="peo_left">启用状态:
<select id="state">
<option onclick="state_se();">全部</option>
<option onclick="state_se();">启用</option>
<option onclick="state_se();">禁用</option>
</select>
<span><input type="text" id="organs" name="organs" style="display: none;"/>
<input type="text" id="states" name="states" style="display: none;"/></span>
</span>
<span id="button">
<input type="submit" value="查询" class="select"/>
<input type="reset" value="重置" class="select"/>
<input type="button" value="人员报表" class="select"/>
</span>
</form>
<br>
<div id="peo_info" style="height: 259px;">
<table style="width: 99%;">
<tr><td>人员名称</td><td>人员编号</td><td>所属部门</td><td>所属工种</td><td>职务</td><td>启用状态</td><td><a href="Personnel_add.jsp">【添 加】</a></td></tr>
<c:forEach items="${list}" var="i" varStatus="status">
<c:if test="${status.index % 2 == 0}">
<tr style="background-color: white"><td class="list_p">${i.name}</td><td class="list_p">${i.number}</td><td class="list_p">${i.part}</td><td class="list_p">${i.kinds}</td><td class="list_p">${i.duty}</td><td class="list_p">${i.state}</td><td class="list_p"><a href="/Socket/Person_edit?name=${i.name}&&number=${i.number}">【编辑</a>|<a href="/Socket/Person_delete?name=${i.name}&&number=${i.number}">删除</a>】</td></tr>
</c:if>
<c:if test="${status.index % 2 == 1}">
<tr style="background-color: #f2f7fa"><td class="list_p">${i.name}</td><td class="list_p">${i.number}</td><td class="list_p">${i.part}</td><td class="list_p">${i.kinds}</td><td class="list_p">${i.duty}</td><td class="list_p">${i.state}</td><td class="list_p"><a href="/Socket/Person_edit?name=${i.name}&&number=${i.number}">【编辑</a>|<a href="/Socket/Person_delete?name=${i.name}&&number=${i.number}">删除</a>】</td></tr>
</c:if>
</c:forEach>
<tr><td>
<a href="<%=request.getAttribute("address") %>?currentpage=${cpage-1}&&sqls=<%=request.getParameter("sql")%>">上一页</a></td><td>第${cpage}页</td><td><a href="<%=request.getAttribute("address") %>?currentpage=${cpage+1}&&sqls=<%=request.getParameter("sql")%>">下一页</a></td><td>总共<%=request.getAttribute("all") %>页</td></tr>
</table>
</div>
</div>