MyBatis逆向工程主要用于单表操作，那么需要进行联表操作时，往往需要我们自己去写sql语句。
写sql语句之前，我们先修改一下实体类
Course.java：
1 public class Course { 2 private Integer id; 3 4 private String cNum; 5 6 private String cName; 7 8 private String remark; 9 10 private Integer status; 11 12 public Integer getId() { 13 return id; 14 } 15 16 public void setId(Integer id) { 17 this.id = id; 18 } 19 20 public String getcNum() { 21 return cNum; 22 } 23 24 public void setcNum(String cNum) { 25 this.cNum = cNum == null ? null : cNum.trim(); 26 } 27 28 public String getcName() { 29 return cName; 30 } 31 32 public void setcName(String cName) { 33 this.cName = cName == null ? null : cName.trim(); 34 } 35 36 public String getRemark() { 37 return remark; 38 } 39 40 public void setRemark(String remark) { 41 this.remark = remark == null ? null : remark.trim(); 42 } 43 44 public Integer getStatus() { 45 return status; 46 } 47 48 public void setStatus(Integer status) { 49 this.status = status; 50 } 51 52 @Override 53 public String toString() { 54 return "Course{" + 55 "id=" + id + 56 ", cNum='" + cNum + '\'' + 57 ", cName='" + cName + '\'' + 58 ", remark='" + remark + '\'' + 59 ", status=" + status + 60 '}'; 61 } 62 }
Task.java：
1 import java.util.Date; 2 3 public class Task { 4 private Integer id; 5 6 private String cid; 7 8 private Integer uid; 9 10 private String filename; 11 12 private String fileUrl; 13 14 private Date created; 15 16 private Date updated; 17 18 private String remark; 19 20 private Integer status; 21 22 //自定义 23 private Course course;//联表查询使用 24 25 public Integer getId() { 26 return id; 27 } 28 29 public void setId(Integer id) { 30 this.id = id; 31 } 32 33 public String getCid() { 34 return cid; 35 } 36 37 public void setCid(String cid) { 38 this.cid = cid == null ? null : cid.trim(); 39 } 40 41 public Integer getUid() { 42 return uid; 43 } 44 45 public void setUid(Integer uid) { 46 this.uid = uid; 47 } 48 49 public String getFilename() { 50 return filename; 51 } 52 53 public void setFilename(String filename) { 54 this.filename = filename == null ? null : filename.trim(); 55 } 56 57 public String getFileUrl() { 58 return fileUrl; 59 } 60 61 public void setFileUrl(String fileUrl) { 62 this.fileUrl = fileUrl == null ? null : fileUrl.trim(); 63 } 64 65 public Date getCreated() { 66 return created; 67 } 68 69 public void setCreated(Date created) { 70 this.created = created; 71 } 72 73 public Date getUpdated() { 74 return updated; 75 } 76 77 public void setUpdated(Date updated) { 78 this.updated = updated; 79 } 80 81 public String getRemark() { 82 return remark; 83 } 84 85 public void setRemark(String remark) { 86 this.remark = remark == null ? null : remark.trim(); 87 } 88 89 public Integer getStatus() { 90 return status; 91 } 92 93 public void setStatus(Integer status) { 94 this.status = status; 95 } 96 97 //自定义 98 public Course getCourse() { 99 return course; 100 } 101 102 public void setCourse(Course course) { 103 this.course = course; 104 } 105 106 @Override 107 public String toString() { 108 return "Task{" + 109 "id=" + id + 110 ", cid='" + cid + '\'' + 111 ", uid=" + uid + 112 ", filename='" + filename + '\'' + 113 ", fileUrl='" + fileUrl + '\'' + 114 ", created=" + created + 115 ", updated=" + updated + 116 ", remark='" + remark + '\'' + 117 ", status=" + status + 118 ", course=" + course + 119 '}'; 120 } 121 }
TaskMapper.java：
1 import com.sun123.springboot.entity.Task; 2 import com.sun123.springboot.entity.TaskExample; 3 import org.apache.ibatis.annotations.Param; 4 5 import java.util.List; 6 7 public interface TaskMapper { 8 int countByExample(TaskExample example); 9 10 int deleteByExample(TaskExample example); 11 12 int deleteByPrimaryKey(Integer id); 13 14 int insert(Task record); 15 16 int insertSelective(Task record); 17 18 List<Task> selectByExample(TaskExample example); 19 20 Task selectByPrimaryKey(Integer id); 21 22 int updateByExampleSelective(@Param("record") Task record, @Param("example") TaskExample example); 23 24 int updateByExample(@Param("record") Task record, @Param("example") TaskExample example); 25 26 int updateByPrimaryKeySelective(Task record); 27 28 int updateByPrimaryKey(Task record); 29 30 List<Task> taskList();//联表查询 31 }
TaskMapper.xml：（MyBatis逆向工程的基础上进行修改）
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <mapper namespace="com.sun123.springboot.mapper.TaskMapper" > 4 <resultMap id="BaseResultMap" type="com.sun123.springboot.entity.Task" > 5 <id column="id" property="id" jdbcType="INTEGER" /> 6 <result column="cid" property="cid" jdbcType="VARCHAR" /> 7 <result column="uid" property="uid" jdbcType="INTEGER" /> 8 <result column="filename" property="filename" jdbcType="VARCHAR" /> 9 <result column="file_url" property="fileUrl" jdbcType="VARCHAR" /> 10 <result column="created" property="created" jdbcType="TIMESTAMP" /> 11 <result column="updated" property="updated" jdbcType="TIMESTAMP" /> 12 <result column="remark" property="remark" jdbcType="VARCHAR" /> 13 <result column="status" property="status" jdbcType="INTEGER" /> 14 </resultMap> 15 <sql id="Example_Where_Clause" > 16 <where > 17 <foreach collection="oredCriteria" item="criteria" separator="or" > 18 <if test="criteria.valid" > 19 <trim prefix="(" suffix=")" prefixOverrides="and" > 20 <foreach collection="criteria.criteria" item="criterion" > 21 <choose > 22 <when test="criterion.noValue" > 23 and ${criterion.condition} 24 </when> 25 <when test="criterion.singleValue" > 26 and ${criterion.condition} #{criterion.value} 27 </when> 28 <when test="criterion.betweenValue" > 29 and ${criterion.condition} #{criterion.value} and #{criterion.secondValue} 30 </when> 31 <when test="criterion.listValue" > 32 and ${criterion.condition} 33 <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," > 34 #{listItem} 35 </foreach> 36 </when> 37 </choose> 38 </foreach> 39 </trim> 40 </if> 41 </foreach> 42 </where> 43 </sql> 44 <sql id="Update_By_Example_Where_Clause" > 45 <where > 46 <foreach collection="example.oredCriteria" item="criteria" separator="or" > 47 <if test="criteria.valid" > 48 <trim prefix="(" suffix=")" prefixOverrides="and" > 49 <foreach collection="criteria.criteria" item="criterion" > 50 <choose > 51 <when test="criterion.noValue" > 52 and ${criterion.condition} 53 </when> 54 <when test="criterion.singleValue" > 55 and ${criterion.condition} #{criterion.value} 56 </when> 57 <when test="criterion.betweenValue" > 58 and ${criterion.condition} #{criterion.value} and #{criterion.secondValue} 59 </when> 60 <when test="criterion.listValue" > 61 and ${criterion.condition} 62 <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," > 63 #{listItem} 64 </foreach> 65 </when> 66 </choose> 67 </foreach> 68 </trim> 69 </if> 70 </foreach> 71 </where> 72 </sql> 73 <sql id="Base_Column_List" > 74 id, cid, uid, filename, file_url, created, updated, remark, status 75 </sql> 76 <select id="selectByExample" resultMap="BaseResultMap" parameterType="com.sun123.springboot.entity.TaskExample" > 77 select 78 <if test="distinct" > 79 distinct 80 </if> 81 <include refid="Base_Column_List" /> 82 from task 83 <if test="_parameter != null" > 84 <include refid="Example_Where_Clause" /> 85 </if> 86 <if test="orderByClause != null" > 87 order by ${orderByClause} 88 </if> 89 </select> 90 <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > 91 select 92 <include refid="Base_Column_List" /> 93 from task 94 where id = #{id,jdbcType=INTEGER} 95 </select> 96 <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > 97 delete from task 98 where id = #{id,jdbcType=INTEGER} 99 </delete> 100 <delete id="deleteByExample" parameterType="com.sun123.springboot.entity.TaskExample" > 101 delete from task 102 <if test="_parameter != null" > 103 <include refid="Example_Where_Clause" /> 104 </if> 105 </delete> 106 <insert id="insert" parameterType="com.sun123.springboot.entity.Task" > 107 insert into task (id, cid, uid, 108 filename, file_url, created, 109 updated, remark, status 110 ) 111 values (#{id,jdbcType=INTEGER}, #{cid,jdbcType=VARCHAR}, #{uid,jdbcType=INTEGER}, 112 #{filename,jdbcType=VARCHAR}, #{fileUrl,jdbcType=VARCHAR}, #{created,jdbcType=TIMESTAMP}, 113 #{updated,jdbcType=TIMESTAMP}, #{remark,jdbcType=VARCHAR}, #{status,jdbcType=INTEGER} 114 ) 115 </insert> 116 <insert id="insertSelective" parameterType="com.sun123.springboot.entity.Task" > 117 insert into task 118 <trim prefix="(" suffix=")" suffixOverrides="," > 119 <if test="id != null" > 120 id, 121 </if> 122 <if test="cid != null" > 123 cid, 124 </if> 125 <if test="uid != null" > 126 uid, 127 </if> 128 <if test="filename != null" > 129 filename, 130 </if> 131 <if test="fileUrl != null" > 132 file_url, 133 </if> 134 <if test="created != null" > 135 created, 136 </if> 137 <if test="updated != null" > 138 updated, 139 </if> 140 <if test="remark != null" > 141 remark, 142 </if> 143 <if test="status != null" > 144 status, 145 </if> 146 </trim> 147 <trim prefix="values (" suffix=")" suffixOverrides="," > 148 <if test="id != null" > 149 #{id,jdbcType=INTEGER}, 150 </if> 151 <if test="cid != null" > 152 #{cid,jdbcType=VARCHAR}, 153 </if> 154 <if test="uid != null" > 155 #{uid,jdbcType=INTEGER}, 156 </if> 157 <if test="filename != null" > 158 #{filename,jdbcType=VARCHAR}, 159 </if> 160 <if test="fileUrl != null" > 161 #{fileUrl,jdbcType=VARCHAR}, 162 </if> 163 <if test="created != null" > 164 #{created,jdbcType=TIMESTAMP}, 165 </if> 166 <if test="updated != null" > 167 #{updated,jdbcType=TIMESTAMP}, 168 </if> 169 <if test="remark != null" > 170 #{remark,jdbcType=VARCHAR}, 171 </if> 172 <if test="status != null" > 173 #{status,jdbcType=INTEGER}, 174 </if> 175 </trim> 176 </insert> 177 <select id="countByExample" parameterType="com.sun123.springboot.entity.TaskExample" resultType="java.lang.Integer" > 178 select count(*) from task 179 <if test="_parameter != null" > 180 <include refid="Example_Where_Clause" /> 181 </if> 182 </select> 183 <update id="updateByExampleSelective" parameterType="map" > 184 update task 185 <set > 186 <if test="record.id != null" > 187 id = #{record.id,jdbcType=INTEGER}, 188 </if> 189 <if test="record.cid != null" > 190 cid = #{record.cid,jdbcType=VARCHAR}, 191 </if> 192 <if test="record.uid != null" > 193 uid = #{record.uid,jdbcType=INTEGER}, 194 </if> 195 <if test="record.filename != null" > 196 filename = #{record.filename,jdbcType=VARCHAR}, 197 </if> 198 <if test="record.fileUrl != null" > 199 file_url = #{record.fileUrl,jdbcType=VARCHAR}, 200 </if> 201 <if test="record.created != null" > 202 created = #{record.created,jdbcType=TIMESTAMP}, 203 </if> 204 <if test="record.updated != null" > 205 updated = #{record.updated,jdbcType=TIMESTAMP}, 206 </if> 207 <if test="record.remark != null" > 208 remark = #{record.remark,jdbcType=VARCHAR}, 209 </if> 210 <if test="record.status != null" > 211 status = #{record.status,jdbcType=INTEGER}, 212 </if> 213 </set> 214 <if test="_parameter != null" > 215 <include refid="Update_By_Example_Where_Clause" /> 216 </if> 217 </update> 218 <update id="updateByExample" parameterType="map" > 219 update task 220 set id = #{record.id,jdbcType=INTEGER}, 221 cid = #{record.cid,jdbcType=VARCHAR}, 222 uid = #{record.uid,jdbcType=INTEGER}, 223 filename = #{record.filename,jdbcType=VARCHAR}, 224 file_url = #{record.fileUrl,jdbcType=VARCHAR}, 225 created = #{record.created,jdbcType=TIMESTAMP}, 226 updated = #{record.updated,jdbcType=TIMESTAMP}, 227 remark = #{record.remark,jdbcType=VARCHAR}, 228 status = #{record.status,jdbcType=INTEGER} 229 <if test="_parameter != null" > 230 <include refid="Update_By_Example_Where_Clause" /> 231 </if> 232 </update> 233 <update id="updateByPrimaryKeySelective" parameterType="com.sun123.springboot.entity.Task" > 234 update task 235 <set > 236 <if test="cid != null" > 237 cid = #{cid,jdbcType=VARCHAR}, 238 </if> 239 <if test="uid != null" > 240 uid = #{uid,jdbcType=INTEGER}, 241 </if> 242 <if test="filename != null" > 243 filename = #{filename,jdbcType=VARCHAR}, 244 </if> 245 <if test="fileUrl != null" > 246 file_url = #{fileUrl,jdbcType=VARCHAR}, 247 </if> 248 <if test="created != null" > 249 created = #{created,jdbcType=TIMESTAMP}, 250 </if> 251 <if test="updated != null" > 252 updated = #{updated,jdbcType=TIMESTAMP}, 253 </if> 254 <if test="remark != null" > 255 remark = #{remark,jdbcType=VARCHAR}, 256 </if> 257 <if test="status != null" > 258 status = #{status,jdbcType=INTEGER}, 259 </if> 260 </set> 261 where id = #{id,jdbcType=INTEGER} 262 </update> 263 <update id="updateByPrimaryKey" parameterType="com.sun123.springboot.entity.Task" > 264 update task 265 set cid = #{cid,jdbcType=VARCHAR}, 266 uid = #{uid,jdbcType=INTEGER}, 267 filename = #{filename,jdbcType=VARCHAR}, 268 file_url = #{fileUrl,jdbcType=VARCHAR}, 269 created = #{created,jdbcType=TIMESTAMP}, 270 updated = #{updated,jdbcType=TIMESTAMP}, 271 remark = #{remark,jdbcType=VARCHAR}, 272 status = #{status,jdbcType=INTEGER} 273 where id = #{id,jdbcType=INTEGER} 274 </update> 275 276 277 <resultMap id="TaskResultMap" type="com.sun123.springboot.entity.Task" extends="BaseResultMap"> 278 <association property="course" resultMap="com.sun123.springboot.mapper.CourseMapper.BaseResultMap"></association> 279 </resultMap> 280 <select id="taskList" resultMap="TaskResultMap"> 281 SELECT t.*,c.* FROM task t LEFT JOIN course c ON t.cid=c.c_num 282 </select> 283 284 </mapper>
<association property="course" resultMap="com.sun123.springboot.mapper.CourseMapper.BaseResultMap"></association>这种写法直接引入了CourseMApper.xml中的字段信息，不需要再次定义，比较简洁。
接下来以Bootstrap table分页插件为例，实现完整的调用
StuService.java：
1 //bootstrap table分页插件，数据返回 2 BootstrapPage showTask(int offset, int limit,String search);
StuServiceImpl.java：
1 /** 2 * @Description //bootstrap table分页插件，数据返回 3 * @Date 2019-04-04 19:54 4 * @Param [limit, offset] 5 * @return com.sun123.springboot.entity.bootstrap.PageHelper 6 **/ 7 @Override 8 public BootstrapPage showTask(int offset, int limit,String search) { 9 BootstrapPage bootstrapPage = new BootstrapPage(); 10 //pageNumber pageSize 11 Page pages = PageHelper.startPage(offset, limit); 12 13 List<Task> taskList = taskMapper.taskList(); 14 bootstrapPage.setRows(taskList); 15 bootstrapPage.setTotal((int)pages.getTotal()); 16 return bootstrapPage; 17 }
StuController.java：
1 @GetMapping("pageInfo") 2 @ResponseBody 3 public BootstrapPage pageInfo(int offset, int limit, String search){ 4 System.out.println("======"+offset+"==="+limit+"====="+search+"====="); 5 BootstrapPage page = stuService.showTask(offset, limit,search); 6 return page; 7 }
后台查询结果：
1 Task{id=25, cid='04021611', uid=3, filename='呵呵呵', fileUrl='http://192.168.83.133/images/2019/03/24/1 - 副本1553391128920311.jpg', created=Sun Mar 24 09:32:11 CST 2019, updated=Sun Mar 24 09:32:11 CST 2019, remark='5263', status=0, 2 course=Course{id=25, cNum='04021611', cName='Hadoop数据分析平台Ⅰ', remark='5263', status=0}}
表格展示时，操作如下：
1 $(function() { 2 $('#table').bootstrapTable({ 3 url: 'pageInfo', 4 pagination: true, //是否显示分页（*） 5 sortable: false, //是否启用排序 6 sortOrder: "asc", //排序方式 7 //toolbar: '#toolbar', //工具按钮用哪个容器 8 //method:'post', 9 //sortable: true,//排序 10 showColumns: true,//是否显示 内容列下拉框 11 //clickToSelect: true,//点击选中checkbox 12 sidePagination: "server", //分页方式：client客户端分页，server服务端分页（*） 13 pageNumber: 1, //初始化加载第一页，默认第一页 14 pageSize: 5, //每页的记录行数（*） 15 pageList: [5, 10, 50, 100], //可供选择的每页的行数（*） 16 showRefresh: true,//是否显示刷新按钮 17 showToggle: true,//是否显示详细视图和列表视图的切换按钮 18 //search: true, //是否显示表格搜索，此搜索是客户端搜索，不会进服务端，所以，个人感觉意义不大 19 //queryParamsType: "", //默认值为 'limit' ,在默认情况下 传给服务端的参数为：offset,limit,sort 20 // 设置为 '' 在这种情况下传给服务器的参数为：pageSize,pageNumber 21 showExport: true,//是否显示导出 22 columns: [{ 23 field: 'course.cName', 24 title: '课程名称' 25 }, { 26 field: 'filename', 27 title: '文件名称' 28 }, { 29 field: 'remark', 30 title: '说明' 31 },{ 32 field: 'created', 33 title: '上传时间' 34 },{ 35 field: 'fileUrl', 36 title: '下载地址' 37 }, ] 38 }); 39 })
说明：course对象属性的使用需要多写一层，例如：course.cName
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
