这里我们对一个列表的信息进行搜索，根据姓名 部门
用到的工具和jsp的位置
jsp页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>Title</title>
<style type="text/css">
table{
background: dimgrey;
margin: auto;
width: 40%;
}
th,td{
background: white;
}
</style>
</head>
<body>
<table>
<tr>
<td colspan="6">
<form action="${pageContext.request.contextPath}/FindServlet" method="post" style="margin: auto">
姓名：<input type="text" name="name">
部门：<select id="dept" name="deptno">
<option value="0">请选择</option>
</select>
<input type="submit" value="搜索">
</form>
</td>
</tr>
<tr>
<th>id</th>
<th>姓名</th>
<th>工作</th>
<th>部门编号</th>
<th>部门名称</th>
<th>操作</th>
</tr>
<c:forEach items="${emps}" var="e">
<tr align="center">
<td>${e.empno}</td>
<td>${e.ename}</td>
<td>${e.job}</td>
<td>${e.dept.deptno}</td>
<td>${e.dept.dname}</td>
<td><a href="javascript:del(${e.empno})">删除</a>
<a href="${pageContext.request.contextPath}/Servlet?method=edit&id=${e.empno}">修改</a> </td>
</tr>
</c:forEach>
</table>
</body>
<script type="text/javascript" src="${pageContext.request.contextPath}/js/jquery-3.1.0.min.js" ></script>
<script type="text/javascript">
文档加载时,就执行 该函数
$(function () {//这里是部门下拉框的数据显示
//alert(11);
$.ajax({
url:'${pageContext.request.contextPath}/Servlet?method=findDepts',
type:'get',
dataType:'json',
success:function (data) { //data保存回调数据
//alert(data);
//[{},{},{}]
$(data).each(function (index,item) { // index索引 ,item 被查询出来的对象 dept
$("#dept").append("<option value='"+item.deptno+"'>"+item.dname+"</option>")
});
}
});
});
//列表的删除
function del(id) {
if(confirm("确认删除吗")){
window.location.href="${pageContext.request.contextPath}/Servlet?method=del&id="+id;
}
}
</script>
</html>
Servlet
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
EmpService es=new EmpServiceImpl();
//查询所有部门的数据
List<Dept> d=es.fineD();//这里在数据库里获取所有部门信息，用于部门下拉显示
String json = JSON.toJSONString(d);
//将json写入客户端
PrintWriter writer = response.getWriter();
writer.print(json);//传到前端
}DaoImpl
public List<Dept> findD() {
Connection conn = db.getConnection();//获取数据库的信息
List<Dept> depts=new ArrayList<>();//新建集合 用于接收部门信息
try {
PreparedStatement ps = conn.prepareStatement("select * FROM dept");//根据SQL查询部门表
ResultSet rs = ps.executeQuery();
while (rs.next()){
Dept d=new Dept();
d.setDeptno(rs.getInt("deptno"));
d.setDname(rs.getString("dname"));
d.setLoc(rs.getString("loc"));
depts.add(d);
}
return depts;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}以上做的是搜索框下拉选项的数据显示
以下是关于搜索的显示
比如当只输入了名字没有选择部门(只根据姓名模糊查询)
没有输入名字只选择了部门(只根据部门查找)
两个都没有输入(查询所有)
两个都输入(根据姓名和部门查找)
查询的Servlet
@WebServlet("/FindServlet")
public class FindServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
String name = request.getParameter("name");
Integer deptno=Integer.valueOf(request.getParameter("deptno"));
EmpService es=new EmpServiceImpl();
boolean b = StrUtil.isEmpty(name);//判断字符串name是否为空 如果为空返回true 不为空返回false
if(b==true&&deptno==0){//搜索框都为空时，搜索全部
//这里直接调用的查询所有的Servlet
request.getRequestDispatcher("/Servlet?method=login").forward(request,response);
}else if (b==false&&deptno==0){//根据名字搜索
List<Emp> list= es.findByEname(name);//查询方法
//将数据传到前台
request.setAttribute("emps",list);
//跳转的页面
request.getRequestDispatcher("WEB-INF/jsp/elist.jsp").forward(request,response);
}else if(b==true&&deptno>0){//根据deptno搜索
List<Emp> list= es.findByDeptno(deptno);
//将数据传到前台
request.setAttribute("emps",list);
//跳转的页面
request.getRequestDispatcher("WEB-INF/jsp/elist.jsp").forward(request,response);
}else{//两个都输入
List<Emp> list= es.findByAll(name,deptno);
//将数据传到前台
request.setAttribute("emps",list);
//跳转的页面
request.getRequestDispatcher("WEB-INF/jsp/elist.jsp").forward(request,response);
}
}
}
DaoImpl
根据名字模糊查询
/**
* 根据名字进行模糊查询
* 搜索框的搜索名字
* @param name
* @return
*/
@Override
public List<Emp> findByEname(String name) {
Connection conn = db.getConnection();
List<Emp> emps=new ArrayList<>();
try {
PreparedStatement ps = conn.prepareStatement("select * from emp e,dept d where e.deptno=d.deptno and e.ename like ?");
ps.setString(1,"%"+name+"%");
ResultSet rs = ps.executeQuery();
while (rs.next()){
System.out.println();
Emp e=new Emp();
e.setEmpno(rs.getInt("empno"));
e.setEname(rs.getString("ename"));
e.setJob(rs.getString("job"));
Dept d=new Dept();
d.setDeptno(rs.getInt("deptno"));
d.setDname(rs.getString("dname"));
e.setDept(d);
emps.add(e);
}
return emps;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}根据部门id查询
/**
* 根据部门的id进行查询
* 用于搜索框
* @param deptno
* @return
*/
@Override
public List<Emp> findByDeptno(Integer deptno) {
Connection conn = db.getConnection();
List<Emp> emps=new ArrayList<>();
try {
PreparedStatement ps = conn.prepareStatement("select * from emp e,dept d where e.deptno=d.deptno and d.deptno=?");
ps.setInt(1,deptno);
ResultSet rs = ps.executeQuery();
while (rs.next()){
System.out.println();
Emp e=new Emp();
e.setEmpno(rs.getInt("empno"));
e.setEname(rs.getString("ename"));
e.setJob(rs.getString("job"));
Dept d=new Dept();
d.setDeptno(rs.getInt("deptno"));
d.setDname(rs.getString("dname"));
e.setDept(d);
emps.add(e);
}
return emps;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}根据姓名和部门查找
/**
* 根据部门和姓名查找
* 用于搜索框回显
* @param name
* @param deptno
* @return
*/
@Override
public List<Emp> findByAll(String name, Integer deptno) {
Connection conn = db.getConnection();
List<Emp> emps=new ArrayList<>();
try {
PreparedStatement ps = conn.prepareStatement("select * from emp e,dept d where e.deptno=d.deptno and e.ename like ? and d.deptno=?");
ps.setString(1,"%"+name+"%");
ps.setInt(2,deptno);
ResultSet rs = ps.executeQuery();
while (rs.next()){
System.out.println();
Emp e=new Emp();
e.setEmpno(rs.getInt("empno"));
e.setEname(rs.getString("ename"));
e.setJob(rs.getString("job"));
Dept d=new Dept();
d.setDeptno(rs.getInt("deptno"));
d.setDname(rs.getString("dname"));
e.setDept(d);
emps.add(e);
}
return emps;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
两张表的信息 由于数据太多查询只简单的获取了几个字段
5062
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
