该系列为imooc Java数据库开发与实战应用中MyBatis课程笔记,跟随课程加入自己见解,同时也为项目中碰到一些问题做了解答
本章节是mybatis中使用update配置及set动态语句操作,实现对用户的资料进行修改
映射配置:update片段
动态SQL配置:set配置
先梳理下之前开发的项目,在用户详情页可以查看用户的详细信息,所以对用户的资料进行修改的功能就放到用户详情页中
一、在用户详情页实现用户数据修改的前端功能
在detail.jsp中添加提交按钮,并设置表单的提交地址
使用隐藏域向处理用户数据修改的Servlet提供用户id
<%--
Created by IntelliJ IDEA.
User: 35208
Date: 2021/4/7
Time: 0:58
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<head>
<title>慕课网用户管理中心</title>
<link rel="stylesheet" href="lib/bootstrap-3.3.7-dist/css/bootstrap.min.css">
<script src="lib/2.2.4/jquery-1.12.4.min.js"></script>
<script src="lib/bootstrap-3.3.7-dist/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="page-header">
<h1>慕课网管理后台<small>用户数据管理中心</small></h1>
</div>
</div>
<div class="row">
<div class="jumbotron">
<h1>MyBstis基础入门课程</h1>
<p>通过一个项目来完成基础部分学习</p>
<p><a class="btn btn-primary btn-lg" href="#" role="button">查看更多,请上慕课网</a></p>
</div>
</div>
<c:set var="user" value="${user}"></c:set>
<div class="row">
<div class="col-md-8 col-md-offset-2">
<form class="form-horizontal" action="${pageContext.request.contextPath}/updateusers">
<input type="hidden" name="id" value="${user.id}">
<%--使用隐藏域向UsersUpdateServlet提供用户id--%>
<div class="form-group">
<label class="col-sm-2 control-label">用户账号</label>
<div class="col-sm-10">
<%-- <p class="form-control-static">${user.username}</p>--%>
<p class="form-control-static">${user.name}</p>
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">登录密码</label>
<div class="col-sm-10">
<p class="form-control-static">******</p>
</div>
</div>
<div class="form-group">
<label for="nickname" class="col-sm-2 control-label">昵称</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="nickname" value="${user.nickname}" name="nickname" placeholder="请输入昵称">
</div>
</div>
<div class="form-group">
<label for="age" class="col-sm-2 control-label">年龄</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="age" value="${user.age}" name="age" placeholder="请输入年龄">
</div>
</div>
<div class="form-group">
<label for="gender" class="col-sm-2 control-label">性别</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="gender" value="${user.gender}" name="gender" placeholder="请输入性别">
</div>
</div>
<div class="form-group">
<label for="phone" class="col-sm-2 control-label">联系方式</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="phone" value="${user.phone}" name="phone" placeholder="请输入联系方式">
</div>
</div>
<div class="form-group">
<label for="email" class="col-sm-2 control-label">邮箱</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="email" value="${user.email}" name="email" placeholder="请输入邮箱">
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">账号创建时间</label>
<div class="col-sm-10">
<p class="form-control-static">
<fmt:formatDate value="${user.createTime}" pattern="yyyy-MM-dd"></fmt:formatDate>
</p>
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">最后修改时间</label>
<div class="col-sm-10">
<p class="form-control-static">
<fmt:formatDate value="${user.updateTime}" pattern="yyyy-MM-dd"></fmt:formatDate>
</p>
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">最后登录时间</label>
<div class="col-sm-10">
<p class="form-control-static">
<fmt:formatDate value="${user.lastLogTime}" pattern="yyyy-MM-dd"></fmt:formatDate>
</p>
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">用户状态</label>
<div class="col-sm-10">
<c:if test="${user.userStatus == 0}">
<td>正常</td>
</c:if>
<c:if test="${user.userStatus == 1}">
<td>锁定</td>
</c:if>
<c:if test="${user.userStatus == 2}">
<td>删除</td>
</c:if>
</div>
</div>
<div class="form-group">
<label for="remark" class="col-sm-2 control-label">备注</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="remark" value="${user.remark}" name="remark" placeholder="请输入备注">
</div>
</div>
<div class="form-group">
<input type="submit" value="提交数据更新" class="btn btn-primary">
</div>
</form>
</div>
</div>
</div>
</body>
</html>
二、在mapper映射文件中增加update标签
- 查看官方文档update标签使用动态sql语句的案例
<update id="updateAuthorIfNecessary">
update Author
<!--Author为表名-->
<set>
<if test="name != null">username=#{name},</if>
<!--test判断中的name为实体类中的属性名称,username为数据库字段名称-->
<!--当test中的name为空,就不把username=#{name}包含到update语句中-->
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
- 完善mapper
<update id="updateUser">
update users
<!--users为表名-->
<set>
<if test="name != null">username=#{name},</if>
<!--test判断中的name为实体类中的属性名称,username为数据库字段名称-->
<!--当test中的name为空,就不把username=#{name}包含到update语句中-->
<if test="userpass != null">userpass=#{userpass},</if>
<if test="nickname != null">nickname=#{nickname},</if>
<if test="age != null">age=#{age},</if>
<if test="gender != null">gender=#{gender},</if>
<if test="phone != null">phone=#{phone},</if>
<if test="email != null">email=#{email},</if>
<if test="createTime != null">createTime=#{createTime},</if>
<if test="updateTime != null">updateTime=#{updateTime},</if>
<if test="lastLogTime != null">lastLogTime=#{lastLogTime},</if>
<if test="userStatus != null">userStatus=#{userStatus},</if>
<if test="remark != null">remark=#{remark},</if>
</set>
where id=#{id}
</update>
三、在UsersDAO中增加调用mapper映射文件的配置
/**
* 用于修改用户资料的方法
* @return
*/
public Users updateUsers(Users user) {
try {
//返回值:是insert执行过程中影响的行数
getSqlSession().update("updateUser",user);
//将一个用户数据更新后添加到数据库,更新后,一定要提交数据
sqlSession.commit();//进行数据提交,提交后我们才能在数据库查看到对应的数据
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
return user;
}
四、新建用于修改用户数据的Servlet
com.damu.servlet>新建UsersUpdateServlet
package com.damu.servlet;
import com.damu.dao.UsersDAO;
import com.damu.entity.Users;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Date;
@WebServlet("/updateusers")
public class UsersUpdateServlet extends HttpServlet {
private UsersDAO usersDAO=new UsersDAO();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//获取用户要更新的数据(用户可更新数据为昵称、年龄、性别、联系方式、邮箱、备注)
String id= req.getParameter("id");
String nickname= req.getParameter("nickname");
String age= req.getParameter("age");
String gender= req.getParameter("gender");
String email= req.getParameter("email");
String phone= req.getParameter("phone");
String remark= req.getParameter("remark");
//创建用户对象,需要完善Users的构造方法
Users user=new Users(Integer.parseInt(id),nickname,Integer.parseInt(age),gender,email,phone,new Date(),remark);
//提交更新
usersDAO.updateUsers(user);
//查看更新后的用户数据
resp.sendRedirect("/detail?id="+user.getId());
}
}
可以看到添加数据操作成功