MyBatis联表查询

 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 }

  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 }

 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 }

  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>

1 //bootstrap table分页插件，数据返回
2 BootstrapPage showTask(int offset, int limit,String search);

 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     }

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             })