学习MyBtatis 的第二个星期, 大概已经明白差不多了. 已经可以在 控制台完成 增删改查
了,哈哈哈哈(大佬路过别笑😀)…
感觉还不错呢. 现在也开始学习在 CSDN上写笔记博客, 哈哈以前都是看其它大佬的. 自己平时也记笔记但都是在 OneNote上:
自己偷偷的写,偷偷的看…哈哈现在在转变ing 加油,奥里给!
以后有时间也许会想办法吧 笔记都搬到 CSDN上来嘿嘿~ 各位大佬,多多指教;
MyBatis+JSP+Servlet…MySql实现分页/增删改查:
**回归主题还是继续今天的代码笔记:**对MyBatis的总结吧:实现基本的增删改查分页…
如果有问题的朋友可以借鉴之前的笔记哦~
初始MyBatis.
MyBatis sql文件映射.
MyBatis 动态SQL.
MyBatis 实例代码. 这一篇是访问最高的!! 早上起床看到访问量一下子 700多,真的是激动了好一会儿~
回归主题:当当当~ 惊天的代码~
其中一部分的文件是和 MyBatis 实例代码. 一样的, 就不在这里写了~
database.properties
log4j.properties
MyBatisUtil
mybatis-config.xml MyBatis的核心配置文件应该不会有变化,顶多是引用 映射文件地址不一样~
这里使用的数据库:也在 MyBatis 实例代码.
两个实体类:
Role
User 因为这里 java 时间类型date 在页面上展示格式有点混乱是英文格式的 并不是 yyyy-MM-dd的所有我改成String 了; 如果有大佬有解决方法. 麻烦点拨一下~;
正品开始
首先写的分页,分页当然需要的是一个分页类; page.java
// 考虑到分页不一定 ,非得是针对 User数据, page类为一个 泛型类;
package com.wsm.util;
import java.util.List;
//泛型类;
public class Page<T> {
//当前页数默认是 1
private int dye =1;
//每页行数 5
private int hang=5;
//总行数:数据库查寻
private int zhang;
//总页数:根据总行数和每页行数计算而出;
private int zye;
//每页的呈现数据集合;
private List<T> data;
//get/set封装;
public int getDye() {
return dye;
}
public void setDye(int dye) {
this.dye = dye;
}
public int getHang() {
return hang;
}
public void setHang(int hang) {
this.hang = hang;
}
public int getZhang() {
return zhang;
}
public void setZhang(int zhang) {
this.zhang = zhang;
this.zye = zhang%hang==0?zhang/hang:zhang/hang+1; //根据总行计算出总页数;
}
public int getZye() {
return zye;
}
public void setZye(int zye) {
this.zye = zye;
}
public List<T> getData() {
return data;
}
public void setData(List<T> data) {
this.data = data;
}
}
UserMapper.java
接口
package com.wsm.dao;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import com.wsm.entity.User;
public interface UserMapper {
//分页
/**
* Map需要存储的参数; 多条件分页查看,可能会比较复杂,慢慢理解.
* name 姓名
* roleid 部门Id
* opdate 生日 开始时间
* cldate 生日结束时间
* dye 当前页
* hang 每页行;
* @param uMap
* @return
*/
public List<User> chapage(Map<String, Object> uMap);
//查总行,用于获取总页数;
public int zhang(Map<String, Object> uMap);
//新增
public int add(User u);
//修改
public int upd(User u);
//修改前,根据id 查询一下要修改的对象及数据;
public User updc(int id);
//删除
public int del(int id);
}
UserMapper.xml
实现接口:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.wsm.dao.UserMapper" >
<!-- resultMap结果类型:两表连接操作.. -->
<resultMap type="User" id="userMap">
<result property="id" column="id" />
<!-- ...还好我这儿与数据库都一样就不需要一一映射了 -->
<association property="role" javaType="Role" >
<!-- ...还好我这儿与数据库都一样 且MyBatis: setting 设置 autoMappingBehavior 属性级别 FULL 就不需要一一映射了 -->
</association>
</resultMap>
<!-- 分页 -->
<!-- Map参数
* name 姓名
* roleid 部门Id
* opdate 生日 开始时间
* cldate 生日结束时间
* dye 当前页
* hang 每页行; -->
<!-- 因为:这个where会在 查集合/查总行;都用到通过 sql标签提取出来,可以多次使用; 通过 include引用 -->
<sql id="userpagewhere">
<!-- 动态Sql 如果name 是空则不根据name查,同理往下~但它不是空就加它这个条件.. -->
<where>
<if test="name!=null and name!=''" >
and `userName` LIKE CONCAT('%',#{name},'%')
</if>
<if test="roleid!=null and roleid!=''" >
and `userRole` = #{roleid}
</if>
<if test="opdate!=null and cldate!=''" >
and `birthday` BETWEEN #{opdate} AND #{cldate}
</if>
</where>
</sql>
<!-- 分页查询 -->
<select id="chapage" resultMap="userMap" parameterType="map" >
SELECT su.*,sr.`roleName` FROM `smbms_user` su INNER JOIN `smbms_role` sr ON su.`userRole`=sr.`id`
<include refid="userpagewhere"/> <!-- 引用SQL标签文件; -->
<if test="dye!=null and hang!=null" >
limit #{dye},#{hang}
</if>
</select>
<!-- 查总记录数 -->
<select id="zhang" resultType="int" parameterType="map" >
SELECT COUNT(0) FROM `smbms_user` su INNER JOIN `smbms_role` sr ON su.`userRole`=sr.`id`
<!-- 总记录数不是 所有数据,它是根据不同的条件查询对应不同的总行数,在通过总行数获取出总页数
不可能:所有员工有八页 而查姓王的也是八页数据吧.. 哈哈所有这里也是需要的哦;
-->
<include refid="userpagewhere"/> <!-- 引用SQL标签文件; -->
</select>
<!-- 新增 -->
<insert id="add" parameterType="User" >
<!-- 数据库列太长了,个人太懒了一些数据就写死了 -->
INSERT into `smbms_user` values
(null,'userCode',#{userName},111,1,#{birthday},null,#{address},#{userRole},1,'2020-08-18 09:56:31',NULL,NULL);
</insert>
<!-- 修改 -->
<update id="upd" parameterType="User" >
<!-- 数据库列太长了,个人太懒了一些列就不写了 -->
UPDATE `smbms_user`
<set>
<if test="userName!=null and userName!='' " >
userName = #{userName},
</if>
<if test="address!=null and address!='' " >
address = #{address},
</if>
<if test="userRole!=null and userRole!='' " >
userRole = #{userRole},
</if>
<if test="birthday!=null and birthday!='' " >
birthday = #{birthday},
</if>
</set>
where id = #{id}
</update>
<!-- 修改之前,根据要修改的用户id 来查看当前用户进行修改; -->
<select id="updc" resultType="User" parameterType="int" >
select * from `smbms_user` where id=#{id}
</select>
<!-- 删除 -->
<delete id="del" parameterType="int">
DELETE FROM `smbms_user` WHERE `id` =#{id}
</delete>
</mapper>
UserMapper.java
接口
package com.wsm.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.wsm.entity.Role;
public interface RoleMapper {
//查看所有部门信息; 下拉框查询;
public List<Role> allRole();
}
UserMapper.xml
实现接口:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.wsm.dao.RoleMapper" >
<select id="allRole" resultType="Role" >
select * from smbms_role
</select>
</mapper>
Service : 业务逻辑层,用于接受 Servlet
的数据进行对应的操作: 返回至页面显示;
RoleService.Java
package com.wsm.service;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.wsm.dao.RoleMapper;
import com.wsm.entity.Role;
import com.wsm.util.MyBatisUtil;
//Service 业务逻辑层,处理代码逻辑;
public class RoleService {
public List<Role> allRole(){
//创建SqlSession对象;
SqlSession session = MyBatisUtil.createSqlSession();
//获取接口对象;
RoleMapper rm = session.getMapper(RoleMapper.class);
//面向接口编程...返回部门集合;
List<Role> roles = rm.allRole();
//关闭sqlSession
MyBatisUtil.closeSqlSession(session);
return roles; //返回;
};
}
UserService。Java
package com.wsm.service;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import com.wsm.dao.UserMapper;
import com.wsm.entity.User;
import com.wsm.util.MyBatisUtil;
import com.wsm.util.Page;
//Service 业务逻辑层,处理代码逻辑;
public class UserService {
private MyBatisUtil myba = new MyBatisUtil();
/**
* name 姓名
* roleid 部门Id
* opdate 生日 开始时间
* cldate 生日结束时间
* dye 当前页
* hang 每页行;
*/
//业务逻辑层实现,分页操作;
public Page<User> chaPage(String name,Integer roleid,String opdate,String cldate,Integer dye,Integer hang){
Page<User> page = new Page<User>();
//包装到map的参数对象
Map<String,Object> umap = new HashMap<String, Object>();
umap.put("name",name);
umap.put("roleid",roleid);
umap.put("opdate",opdate);
umap.put("cldate",cldate);
umap.put("dye",(dye-1)*hang);
umap.put("hang",hang);
//创建连接对象;
SqlSession session = MyBatisUtil.createSqlSession();
UserMapper uMapper = session.getMapper(UserMapper.class);
List<User> users = uMapper.chapage(umap); //获取当前集合
int zhang = uMapper.zhang(umap); //获取总行;
MyBatisUtil.closeSqlSession(session);
//封装返回数据
page.setDye(dye);
page.setData(users);
page.setZhang(zhang); //set方法赋值,同时求出总页数;
return page; //返回;
};
//新增用户
public int add(User u){
SqlSession session = myba.createSqlSession();
UserMapper um = session.getMapper(UserMapper.class);
int han = um.add(u);
session.commit(); //提交事务;
myba.closeSqlSession(session);
return han;
}
//删除
public int shan(int id){
SqlSession session = myba.createSqlSession();
UserMapper um = session.getMapper(UserMapper.class);
int han = um.del(id);
session.commit(); //提交事务;
myba.closeSqlSession(session);
return han;
}
//修改
public int upd(User u){
SqlSession session = myba.createSqlSession();
UserMapper um = session.getMapper(UserMapper.class);
int han = um.upd(u);
session.commit(); //提交事务;
myba.closeSqlSession(session);
return han;
}
//获取修改对象;
public User updc(int id){
SqlSession session = myba.createSqlSession();
UserMapper um = session.getMapper(UserMapper.class);
User u = um.updc(id);
myba.closeSqlSession(session);
return u;
}
}
Servlet 虽然听说现在已经很少用了… …但我现在只会这个哈哈哈… Web项目还需要注意 Web.XML配置信息; 这个是Myelicpse自动生成的…比较重要而且错误致命!cope的话需要注意别漏了;
UserServlet;
package com.wsm.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wsm.entity.Role;
import com.wsm.entity.User;
import com.wsm.service.RoleService;
import com.wsm.service.UserService;
import com.wsm.util.Page;
public class UserServlet extends HttpServlet {
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response); //简便代码就不分开写了。 get/post
}
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//设置编码格式
response.setContentType("text/html;charset=UTF-8");
request.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
//根据参数,获取当前操作;
String op = request.getParameter("op");
//获取业务逻辑层对象;
RoleService rs = new RoleService();
UserService us = new UserService();
if("page".equals(op)){
//页面参数的传递
Integer dye = 1;
if(request.getParameter("dye")!=null && !"".equals(request.getParameter("dye")) ){
dye = Integer.parseInt(request.getParameter("dye"));
}
Integer hang = 5;
String name = request.getParameter("name");
String opdate = request.getParameter("opdate");
String cldate = request.getParameter("cldate");
Integer roleid = null;
if(request.getParameter("role")!=null && !"-1".equals(request.getParameter("role")) ){
roleid = Integer.parseInt(request.getParameter("role"));
}
//调用对应业务逻辑层获取返回结果;
Page<User> page = us.chaPage(name, roleid, opdate, cldate, dye, hang);
List<Role> roles = rs.allRole();
//需要的数据存在 request中转发——JSP显示
request.setAttribute("roles", roles);
request.setAttribute("page",page);
//转发
request.getRequestDispatcher("index.jsp").forward(request, response);
}else if("roles".equals(op)){
List<Role> roles = rs.allRole();
request.setAttribute("roles", roles);
request.getRequestDispatcher("add.jsp").forward(request, response);
}else if("add".equals(op)){
User u = new User();
u.setAddress(request.getParameter("address"));
u.setUserName(request.getParameter("name"));
u.setUserRole(Integer.parseInt(request.getParameter("role")));
u.setBirthday(request.getParameter("birthday"));
out.print("<script>");
if(us.add(u)==1){
out.print("alert('新增成功');");
out.print("location.href='index.jsp';");
}else{
out.print("alert('新增失败');");
out.print("location.href='add.jsp';");
}
out.print("</script>");
}else if("san".equals(op)){
int id = Integer.parseInt(request.getParameter("id"));
out.print("<script>");
if(us.shan(id)==1){
out.print("alert('删除成功');");
out.print("location.href='index.jsp';");
}else{
out.print("alert('删除失败');");
out.print("location.href='add.jsp';");
}
out.print("</script>");
}else if("updc".equals(op)){
int id = Integer.parseInt(request.getParameter("id"));
User u = us.updc(id);
request.setAttribute("u",u);
List<Role> roles = rs.allRole();
request.setAttribute("roles", roles);
request.getRequestDispatcher("upd.jsp").forward(request,response);
}else if("upd".equals(op)){
User u = new User();
u.setId(Integer.parseInt(request.getParameter("id")));
u.setAddress(request.getParameter("address"));
u.setUserName(request.getParameter("name"));
u.setUserRole(Integer.parseInt(request.getParameter("role")));
u.setBirthday(request.getParameter("birthday"));
out.print("<script>");
if(us.upd(u)==1){
out.print("alert('修改成功');");
out.print("location.href='index.jsp';");
}else{
out.print("alert('修改失败');");
out.print("location.href='add.jsp';");
}
out.print("</script>");
}
out.flush();
out.close();
}
}
JSP
index.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type="text/javascript">
//分页事件;
function topage(index){
//给隐藏表单赋值
document.getElementById("dye").value=index;
document.forms[0].submit(); //手动提交 第一个表单form !
}
</script>
</head>
<body>
<c:if test="${page==null }">
<!-- <jsp:forward page="UserServlet?op=page"></jsp:forward> -->
<script type="text/javascript">
location.href="UserServlet?op=page";
//alert("sas");
</script>
</c:if>
<a href="add.jsp" >新增员工</a>
<form action="UserServlet?op=page" method="post">
<!-- 隐藏表单域:用于存储当前页,当点击下一页时触发JS事件;改变值并提交请求; -->
<input type="hidden" name="dye" id="dye" value="1"/>
<!-- EL表达式:${param.属性 }
${id} 意思是取出某一范围中名称为id的变量,它的取值范围Page,Request,Session,Application
而${param.id}就不是从这四个范围取值的方式了,而是相当于 request.getParameter("id"),就好像从一个jsp页面传值到另一个jsp页面或从表单传递过来的值。
只要页面不 重定向/重新访问.. 可以做到多条件查询,会显数据的操作! 我只能说代码牛!想到以前做项目存 requset 就想哭;
-->
<p>
名称<input type="text" name="name" value="${param.name }">
</p>
<p>
部门
<select name="role">
<option value="-1">请选择</option>
<c:forEach var="r" items="${roles }">
<option value="${r.id }" <c:if test="${param.role==r.id }">selected="selected"</c:if> >${r.roleName }</option>
</c:forEach>
</select>
</p>
<p>
生日<input type="text" name="opdate" value="${param.opdate }">
-<input type="text" name="cldate" value="${param.cldate }">
</p>
<input type="submit" value="提交">
</form>
<div>
<table border="1" width="100%" >
<tr>
<td>编号</td>
<td>名字</td>
<td>生日</td>
<td>地址</td>
<td>部门</td>
<td> 操作 </td>
</tr>
<c:forEach var="u" items="${page.data }">
<tr>
<td>${u.id }</td>
<td>${u.userName }</td>
<td>${u.birthday }</td>
<td>${u.address }</td>
<td>${u.role.roleName }</td>
<td>
<a href="UserServlet?op=updc&id=${u.id }" >修改</a>
<a href="UserServlet?op=san&id=${u.id }" >删除</a>
</td>
</tr>
</c:forEach>
</table>
</div>
<div>
<c:if test="${page.dye>1 }">
<a href="javascript:topage(1)">首页</a>
<a href="javascript:topage(${page.dye-1 })">上一页</a>
</c:if>
<c:if test="${page.dye<page.zye }">
<a href="javascript:topage(${page.dye+1 })">下一页</a>
<a href="javascript:topage(${page.zye })">末页</a>
</c:if>
</div>
</body>
</html>
add.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'add.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<c:if test="${roles==null }">
<script type="text/javascript">
location.href="UserServlet?op=roles";
</script>
</c:if>
<form action="UserServlet?op=add" method="post" >
<table>
<tr>
<th colspan="2" > 新增员工 </th>
</tr>
<tr>
<td>姓名</td>
<td> <input name="name" > </td>
</tr>
<tr>
<td>部门</td>
<td>
<select name="role">
<option value="-1">请选择</option>
<c:forEach var="r" items="${roles }">
<option value="${r.id }" <c:if test="${param.role==r.id }">selected="selected"</c:if> >${r.roleName }</option>
</c:forEach>
</select>
</td>
</tr>
<tr>
<td>生日</td>
<td> <input type='date' name="birthday" > </td>
</tr>
<tr>
<td>地址</td>
<td> <input name="address" > </td>
</tr>
<tr>
<td colspan="2" >
<input type="submit" value="提交" >
</td>
</tr>
</table>
</form>
</body>
</html>
upd.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'upd.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<form action="UserServlet?op=upd&id=${u.id }" method="post" >
<table>
<tr>
<th colspan="2" > 新增员工 </th>
</tr>
<tr>
<td>姓名</td>
<td> <input name="name" value="${u.userName }" > </td>
</tr>
<tr>
<td>部门</td>
<td>
<select name="role">
<option value="-1">请选择</option>
<c:forEach var="r" items="${roles }">
<option value="${r.id }" <c:if test="${u.userRole==r.id }">selected="selected"</c:if> >${r.roleName }</option>
</c:forEach>
</select>
</td>
</tr>
<tr>
<td>生日</td>
<td> <input type='date' name="birthday" value="${u.birthday }" > </td>
</tr>
<tr>
<td>地址</td>
<td> <input name="address" value="${u.userName }" > </td>
</tr>
<tr>
<td colspan="2" >
<input type="submit" value="提交" >
</td>
</tr>
</table>
</form>
</body>
</html>
终于搞完了, 感谢阅读。有没有路过盆友运行,成功的.有bug 的要加油找哦~ 感谢给各位给出意见~;
说到错误:也遇到了很多补 :↓↓↓
常见错误
java.lang.NullPointerException :空指针,显而易见,对象没new 就调方法对就是这么简单; 我找了一小时…没找到!!;主要是它包的是MyBatisUtil
的因为是复制的很自信没错误; 后面无从下手了
最后的最后才发现是 MyBatis-config.xml 或 sql映射文件 引用失败! 又或者…反之就是这么坑; 导致, MyBatisUtil 初始化失败… 还有很多写代码还是要耐心…
有时候真的很坑!
还有要注意:resultMap 需要映射主键,以确保唯一。不然后面查的数据会把前面的覆盖.导致可能每次都是查到一个记录…
最后最后,如果我的代码复制使用后还是存在异常请告诉我。我在进行改正;因为是一直cope的,害怕漏了什么影响效果...