1.分析需求:
2.实战
(导包 省略)
2.1创建库(db_select)/表
t_dept
t_user
2.2创建Maven工程,导入依赖,jquery类库
2.3创建实体类
cn.kgc.entity/Dept、User
Dept
public class Dept {
private Integer id;
private String deptName;
}//省略getter和setter方法
User
public class User {
private Integer id;
private String name;
private String pwd;
private String deptName;
}//省略getter和setter方法
省略了deptId 但一般与数据库对应,多添加的另算,具体根据业务
2.4创建dao
cn.kgc.dao/BaseDao、UserDao、UserDaoImpl
BaseDao
省略
UserDao
public interface UserDao {
//1.查询用户列表
List<User> findAllUsers(Integer deptId);
//2.查询部门列表
List<Dept> findAllDept();
}
UserDaoImpl
public class UserDaoImpl extends BaseDao implements UserDao {
@Override
public List<User> findAllUsers(Integer deptId) {
List<User> list = new ArrayList<>();
String sql = "SELECT a.id id,a.`name` name,a.pwd pwd,b.deptName deptName\n" +
" FROM t_user a LEFT JOIN t_dept b\n" +
" ON a.deptId = b.id WHERE 1 = 1";
StringBuffer sb = new StringBuffer(sql);
Object[] params = null;
if (deptId !=null && deptId!=-1){ //-1 全选 查询所有
sb.append(" and a.deptId = ?");
params = new Object[]{deptId};
}else {
sb.append("");
params = null;
}
rs = super.executeQuery(sb.toString(),params);
try {
while (rs.next()){
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
u.setPwd(rs.getString("pwd"));
u.setDeptName(rs.getString("deptName"));
list.add(u);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
super.closeAll();
}
return list;
}
@Override
public List<Dept> findAllDept() {
List<Dept> list = new ArrayList<>();
rs = super.executeQuery("select * from t_dept",null);
try {
while (rs.next()){
Dept dept = new Dept();
dept.setId(rs.getInt("id"));
dept.setDeptName(rs.getString("deptName"));
list.add(dept);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
super.closeAll();
}
return list;
}
}
2.5 创建sevice
cn.kgc.service/ UserService、UserServiceImpl
省略
2.6创建servlet
cn.kgc.servlet/ UserServlet
public class UserServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//修改字符集
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//获取action
String action = request.getParameter("action");
UserService userService = new UserServiceImpl();
if (action.equals("findAllUsers")){
String deptId = request.getParameter("deptId");
List<User> userList = null;
if (deptId != null && !"".equals(deptId) && !"-1".equals(deptId)
){
userList = userService.findAllUsers(Integer.parseInt(deptId));
}else {
userList = userService.findAllUsers(-1);
}
String s = JSON.toJSONString(userList);
response.getWriter().print(s);
}else if (action.equals("findAllDept")){
List<Dept> deptList = userService.findAllDept();
String json = JSON.toJSONString(deptList);
System.out.println(json);
response.getWriter().print(json);
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request,response);
}
}
2.7 web.xml
省略
2.8 创建list.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
<script type="text/javascript" src="js/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(function () {
// alert(11); 迭代测试
ajaxDate();
ajaxDate2();
});
function ajaxDate() {
var dept = $("#dept").val();
$.ajax({
"url": "http://localhost:8080/UserServlet",
"type": "post",
"data": {"action":"findAllDept"},
"dataType": "json",
async: false,
// 同步刷新,从上往下刷新
success:function (obj) {
//1.定义一个变量str
var str = "";
//2.每次执行ajax之前先清空列表中的数据
$(".remove").remove();// 会不断添加,所以每次要清空
if (dept!=null && dept!=""){
$("#dept").val(dept);
}else {
//初次访问list.jsp页面的时候,第一次是没有传递dept的值的,
//所以前端页面默认显示--请选择部门--
//3.拼接str字符串;添加下拉列表中的值option,默认的初始值
str += "<option value='-1' class='remove'>--请选择部门--</option>";// remove清空
// value 属性规定在表单被提交时被发送到服务器的值
// 注意:如果没有规定 value 属性,选项的值将设置为 <option> 标签中的内容。
}
//最后一步,将str拼接到select下拉列表中
$.each(obj,function (i) {
str += "<option value="+obj[i].id+" class='remove'>"+obj[i].deptName+"</option>";
});
//将组装的下拉列表中的值拼接到<select>下拉列表框中
$("#dept").append(str);
},
error:function () {}
})
}
function ajaxDate2() {
var dept = $("#dept").val();
$.ajax({
url: "http://localhost:8080/UserServlet",
data: {"action":"findAllUsers","deptId":dept},
type: "post",
dataType: "json",
async: false,
success:function (obj) {
var str="";
$(".remove2").remove();
$.each(obj,function (i) {
str += "<tr class='remove2'>";
str += "<td>"+obj[i].id+"</td>";
str += "<td>"+obj[i].name+"</td>";
str += "<td>"+obj[i].pwd+"</td>";
str += "<td>"+obj[i].deptName+"</td>";
str += "</tr>";
})
$("#tbody>table").append(str);
},
error:function () {}
})
}
</script>
</head>
<body>
<div align="center">
按部门查询<select name="deptId" id="dept"></select>
<button onclick="ajaxDate2()">查找</button>
</div>
<div id="tbody" align="center">
<table border="1px">
</table>
</div>
</body>
</html>
2.9 测试
http://localhost:8080/list.jsp
选择对应的会查询对应的,如是请选择部门则是查询所有。
题外话:
public static void main(String[] args) {
String s = null;
Integer a = Integer.parseInt(s);
System.out.println(a);
//java.lang.NumberFormatException: For input string: "null" 数字格式异常
//把 null 转换成数字类型时出错了,这样就很确切了。
//在传参的时候应该是Integer类型,而我在后台用String类型接收,
//最后又将String类型给强制转换成Integer类型,在没有值得情况下自然是转不了了,所以就有null值了。
}
----2021.11.20&11.23