JavaWeb之——基本CRUD操作
综合练习需求
综合练习
- 简单功能
- 列表查询
- 登录
- 添加
- 删除
- 修改
- 复杂功能
- 删除选中
- 分页查询
- 好处：
- 减轻服务器内存的开销
- 提升用户体验
- 好处：
- 复杂条件查询
列表查询
@WebServlet("/UserListServlet")
public class UserListServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
UserService service = new UserServiceImpl();
List<User> users = service.findAll();
request.setAttribute("users", users);
request.getRequestDispatcher("/list.jsp").forward(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
Dao类
@Override
public List<User> findAll() {
String sql = "select * from user";
List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
return users;
}
添加功能
点击添加按钮，跳转到添加页面，填写信息后提交到添加servlet——AddUserServlet
AddUserServlet
- 设置编码
- 获取所有数据
- 封装对象
- 调用service完成保存
- 跳转到list页面，并更新。
@WebServlet("/AddUserServlet")
public class AddUserServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
Map<String, String[]> map = request.getParameterMap();
User u = new User();
try {
BeanUtils.populate(u, map);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
UserService service = new UserServiceImpl();
service.add(u);
response.sendRedirect(request.getContextPath() + "/UserListServlet");
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
@Override
public void add(User user) {
String sql = "insert into user values(null, ?, ?, ?, ?, ?, ?, null, null)";
template.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail());
}
删除功能
点击删除按钮，访问一个servlet，并传递记录的唯一标识。
DeleteUserServlet
- 获取参数
- 调用service删除
- 跳转查询所有servlet
@WebServlet("/DeleteSelectedServlet")
public class DeleteSelectedServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String[] id = request.getParameterValues("usernameId");
UserService service = new UserServiceImpl();
service.deleteSelectedUser(id);
response.sendRedirect(request.getContextPath() + "/UserListServlet");
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
@Override
public void delete(int id) {
String sql = "delete from user where id = ?";
template.update(sql, id);
}
修改功能
点击修改按钮，查询用户信息，通过findUserServlet找到信息，跳转到一个修改页面，填写信息后提交到修改servlet
小细节：
-
回显操作
-
修改操作
findUserServlet
@WebServlet("/FindUserServlet")
public class FindUserServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
UserService service = new UserServiceImpl();
User user = service.findUserById(id);
//将user存入request
request.setAttribute("user", user);
//转发
request.getRequestDispatcher("/update.jsp").forward(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
@WebServlet("/UpdateUserServlet")
public class UpdateUserServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
Map<String, String[]> map = request.getParameterMap();
User user = new User();
try {
BeanUtils.populate(user, map);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
UserService service = new UserServiceImpl();
service.updateUser(user);
response.sendRedirect(request.getContextPath() + "/UserListServlet");
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
@Override
public User findById(int parseInt) {
String sql = "select * from user where id = ?";
return template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), parseInt);
}
@Override
public void update(User user) {
String sql = "update user set name = ?, gender = ?, age = ?, address = ?, qq = ?, email = ? where id = ?";
template.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail(), user.getId());
}
删除选中功能
点击删除选中按钮，就把选择的记录删除。
重点：获取选中条目的id
@WebServlet("/DeleteSelectedServlet")
public class DeleteSelectedServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String[] id = request.getParameterValues("usernameId");
UserService service = new UserServiceImpl();
service.deleteSelectedUser(id);
response.sendRedirect(request.getContextPath() + "/UserListServlet");
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
使用表单来提交删除的编号
对于删除选中的条目，建立一个表单，表单的可选项checkbox如果选中，就提交这个id，如：id=1&id=2...
然后删除servlet通过这些参数进行删除操作
<div style="float: right;margin: 5px;">
<a class="btn btn-primary" href="${pageContext.request.contextPath}/add.jsp">添加联系人</a>
<a class="btn btn-primary" href="javascript:void(0);" id="deleteSelected">删除选中</a>
</div>
<form id="form" action="${pageContext.request.contextPath}/DeleteSelectedServlet" method="post">
<table border="1" class="table table-bordered table-hover">
<tr class="success">
<th><input type="checkbox" id="first"></th>
<th>编号</th>
<th>姓名</th>
</tr>
<c:forEach items="${p.list}" var="user" varStatus="s">
<tr>
<td><input type="checkbox" name="usernameId" value="${user.id}"></td>
<td>${s.count}</td>
<td>${user.name}</td>
<td><a class="btn btn-default btn-sm" href="${pageContext.request.contextPath}/FindUserServlet?id=${user.id}">修改</a>
<a class="btn btn-default btn-sm" href="javascript:deleteUser(${user.id});">删除</a></td>
</tr>
</c:forEach>
</table>
</form>
<script>
function deleteUser(id) {
//用户安全提示
if (confirm("您确定要删除吗？")) {
//访问路径
location.href="${pageContext.request.contextPath}/DeleteUserServlet?id="+id;
}
}
window.onload = function() {
//给删除选中按钮添加单击事件
document.getElementById("deleteSelected").onclick = function() {
//点击按钮之后，提交表单
if (confirm("您确定要删除选中条目吗？")) {
var flag = false;
//判断是否有选中条目,必选空指针异常
var s = document.getElementsByName("usernameId");
for (var i = 0; i < s.length; i++) {
if(s[i].checked) {
//有一个条目选中了
flag = true;
break;
}
}
//有条目被选中
if(flag) {
//表单提交
document.getElementById("form").submit();
}
}
}
//全选删除
//1.获取第一个checkbox
document.getElementById("first").onclick = function() {
//2.获取下边列表中所有的cb
var s = document.getElementsByName("usernameId");
//3.遍历
for (var i = 0; i < s.length; i++) {
//4.设置这些cbs[i]的checked状态 = first.checked
s[i].checked = this.checked;
}
}
}
</script>
分页查询功能
本质：想查哪一页的数据，就在数据库中查询固定字段的数据。每一页对应固定位置的数据。
分页的好处：减轻服务器内存的开销、提升用户体验
- totalCount：总记录数
- totalPage：总页码
- list：每页的数据
- currentPage：当前页码
- rows：每页显示的条数
选中的页码的样式，样式要不一样。
这些数据要输入到客户端，把这些数据封装成一个对象PageBean，分页对象。
服务器给客户端输出一个分页对象，客户端拿到对象后，在页面上通过EL表达式和JSTL标签输出。
list = select * from user limit ? ?
第一个?：开始查询的索引 = (currengPage - 1) * rows
第二个?：rows 每页显示的条数
客户端需要输入rows和currentPage两个给服务器。服务器再通过这两个参数查询，输出给PageBean，最后在页面上展示PageBean的信息。
错误
java.lang.NumberFormatException: null
java.lang.Integer.parseInt(Integer.java:542)
java.lang.Integer.parseInt(Integer.java:615)
com.yue.service.UserServiceImpl.findUserByPage(UserServiceImpl.java:57)
com.yue.servlet.FindUserByPageServlet.doPost(FindUserByPageServlet.java:22)
com.yue.servlet.FindUserByPageServlet.doGet(FindUserByPageServlet.java:30)
javax.servlet.http.HttpServlet.service(HttpServlet.java:626)
javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
特殊情况，刚开始currentPage和rows没有获取值。
String currentPage = request.getParameter("currentPage"); //当前页码
String rows = request.getParameter("rows"); //每页显示的记录数
if (currentPage == null || "".equals(currentPage)) {
currentPage = "1";
}
if (rows == null || "".equals(rows)) {
rows = "5";
}
@WebServlet("/FindUserByPageServlet")
public class FindUserByPageServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String currentPage = request.getParameter("currentPage"); //当前页码
String rows = request.getParameter("rows"); //每页显示的记录数
if (currentPage == null || "".equals(currentPage) ) {
currentPage = "1";
}
if (rows == null || "".equals(rows)) {
rows = "5";
}
/**
* 获取条件查询的参数
* @param condition：查询条件
*/
Map<String, String[]> condition = request.getParameterMap();
UserService service = new UserServiceImpl();
PageBean<User> p = service.findUserByPage(currentPage, rows, condition);
//将PageBean存入request
request.setAttribute("p", p);
//存储查询条件，方便回显
request.setAttribute("condition", condition);
request.getRequestDispatcher("/list.jsp").forward(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
service里的处理
- 这里需要获取一个PageBean对象
- 一共有五个属性
- 已知的有：当前页码、每页显示的记录数
- 需要从数据库中求出总记录数、分页的数据集合
- 需要计算的是：总页码数
- 把这五个对象封装成一个bean返回给servlet，servlet再输出到页面上
@Override
public PageBean<User> findUserByPage(String CurrentPage, String Rows, Map<String, String[]> condition) {
int currentPage = Integer.parseInt(CurrentPage);
int rows = Integer.parseInt(Rows);
if (currentPage <= 0) {
currentPage = 1;
}
//创建一个空的PageBean对象
PageBean<User> p = new PageBean<User>();
//设置参数
p.setCurrentPage(currentPage);
p.setRows(rows);
//查询总记录数
int totalCount = dao.findTotalCount(condition);
p.setTotalCount(totalCount);
//查询分页的内容
//计算查询开始的索引
int index = (currentPage - 1) * rows;
List<User> list = dao.findByPage(index, rows, condition);
p.setList(list);
//计算总页码数
int totalPage = (totalCount % rows) == 0 ? totalCount / rows : (totalCount / rows) + 1;
p.setTotalPage(totalPage);
return p;
}
Dao层（包含复杂查询）
@Override
public int findTotalCount(Map<String, String[]> condition) {
//定义初始化的sql
String sql = "select count(*) from user where 1 = 1 ";
//遍历map
StringBuilder s = new StringBuilder(sql);
Set<String> strings = condition.keySet();
//定义参数的集合
List<Object> params = new ArrayList<Object>();
for (String key : strings) {
if ("currentPage".equals(key) || "rows".equals(key)) {
continue;
}
String value = condition.get(key)[0];
//判断value是否有值
if (value != null && !"".equals(value)) {
s.append(" and " + key + " like ? ");
//一个?对应一个value的值
//添加参数
params.add("%" + value + "%");
}
}
return template.queryForObject(s.toString(), Integer.class, params.toArray());
}
@Override
public List<User> findByPage(int index, int rows, Map<String, String[]> condition) {
String sql = "select * from user where 1 = 1 ";
//遍历map
StringBuilder s = new StringBuilder(sql);
Set<String> strings = condition.keySet();
//定义参数的集合
List<Object> params = new ArrayList<Object>();
for (String key : strings) {
if ("currentPage".equals(key) || "rows".equals(key)) {
continue;
}
String value = condition.get(key)[0];
//判断value是否有值
if (value != null && !"".equals(value)) {
s.append(" and " + key + " like ? ");
//一个?对应一个value的值
//添加参数
params.add("%" + value + "%");
}
}
//添加分页查询的条件
s.append(" limit ?,? ");
params.add(index);
params.add(rows);
return template.query(s.toString(), new BeanPropertyRowMapper<User>(User.class), params.toArray());
}
复杂条件查询
多条件查询语句为：select * from user where name like ‘%李%’ and address like ‘%北京%’limit 0,5;
从服务器中查询出一个PageBean对象，并输出到浏览器。
- totalCount
- list
重点：拼接SQL语句
添加一个恒等式，然后在后面添加and key like ?
恒等式：select count(*) from user where 1 = 1
小细节：
查询完成后条件的回显。
