社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
【多表结合】
准备,下载sixth.zip并导入到eclipse,下载地址http://pan.baidu.com/s/1o6spOJS
1,参考InitDB.sql,初始化数据库
2,修改UsersExtMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="org.com.sample.mapper.ext.UsersExtMapper">
<resultMap id="BaseResultMap" type="org.com.sample.dataset.ext.UsersExt">
<id column="USERNAME" property="username" jdbcType="VARCHAR" />
<result column="PASSWORD" property="password" jdbcType="VARCHAR" />
<result column="ROLENAME" property="rolename" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_Ext_List">
USERNAME, PASSWORD, ROLENAME
</sql>
<sql id="Base_Column_WithTbl_Ext_List">
u.USERNAME, u.PASSWORD, r.ROLENAME
</sql>
<select id="countForPaging" parameterType="map" resultType="java.lang.Integer">
select count(*) from USERS
</select>
<select id="selectMcUserExtForPaging" resultMap="BaseResultMap"
parameterType="map">
select
<include refid="Base_Column_Ext_List" />
from (
select
<include refid="Base_Column_WithTbl_Ext_List" />
, ROWNUM line
from USERS u
left join ROLES r
on u.ROLE = r.ROLE
<where>
<if test="end_line != null">
and ROWNUM <= #{end_line,jdbcType=DECIMAL}
</if>
</where>
<if test="orderByClause != null">
order by ${orderByClause}
</if>
)
<where>
<if test="start_line != null">
and line >= #{start_line,jdbcType=DECIMAL}
</if>
</where>
</select>
</mapper>
3,新规UsersExt.java
package org.com.sample.dataset.ext;
import org.com.sample.dataset.Users;
import lombok.Data;
@Data
public class UsersExt extends Users {
private String rolename;
}
4,修改users.html
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8" />
<title>Users</title>
<meta
content='width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no'
name='viewport' />
<meta th:replace="head-cssjs" />
</head>
<body class="skin-blue">
<div class="wrapper">
<!-- Content Wrapper. Contains page content -->
<div class="content-wrapper">
<!-- Content Header (Page header) -->
<section class="content-header">
<h1>Users</h1>
</section>
<!-- Main content -->
<section class="content">
<div class="row">
<div class="col-md-12">
<form id="usersInputForm" method="post" class="form-horizontal">
<div id="maincontent" class="box box-primary"
style="overflow-y: auto; overflow-x: hidden;">
<div class="box-body">
<div class="form-group">
<div class="col-md-12">
<div th:if="${page} != null and ${ph} != null " class="row">
<div class="col-md-12">
<p class="pageList">
检索结果 <span th:text="${page.totalElements}"></span>件 <span
class="prev"
th:attr="style=${ph.hiddenFirst} ? 'visibility:hidden'"><a
class="js-pagenation"
th:href="@{/sample/users/show(page=0,size=${page.size})}"
id="searchIdFirst"><<</a></span> <span class="prev"
th:attr="style=${ph.hiddenPrev} ? 'visibility:hidden'"><a
class="js-pagenation"
th:href="@{/sample/users/show(page=${page.number - 1},size=${page.size})}"><</a></span>
<span th:text="${page.number + 1}"></span> / <span
th:text="${page.totalPages}"></span> 页 <span class="next"
th:attr="style=${ph.hiddenNext} ? 'visibility:hidden'"><a
class="js-pagenation"
th:href="@{/sample/users/show(page=${page.number + 1},size=${page.size})}">></a></span>
<span class="next"
th:attr="style=${ph.hiddenLast} ? 'visibility:hidden'"><a
class="js-pagenation"
th:href="@{/sample/users/show(page=${page.totalPages - 1},size=${page.size})}">>></a></span>
</p>
</div>
</div>
<div class="box box-success"
th:attrappend="style=${page == null ? 'display: none;' : ''}">
<div class="col-md-12" style="width: 99%;">
<table id="searchResult"
class="table table-condensed table-striped table-hover">
<thead>
<tr>
<th class="col-md-1">No.</th>
<th class="col-md-1">username</th>
<th class="col-md-1">password</th>
<th class="col-md-1">rolename</th>
</tr>
</thead>
<tbody>
<tr th:each="content, iterStat : ${page}">
<td class="col-md-1" th:id="${iterStat.index}"
th:name="${iterStat.index}"
th:value="${iterStat.index}"
th:text="${iterStat.index} + 1"></td>
<td class="col-md-1"
th:id="'page['+${iterStat.index}+'].username'"
th:name="'page['+${iterStat.index}+'].username'"
th:value="${content.username}"
th:text="${content.username}"></td>
<td class="col-md-1"
th:id="'page['+${iterStat.index}+'].password'"
th:name="'page['+${iterStat.index}+'].password'"
th:value="${content.password}"
th:text="${content.password}"></td>
<td class="col-md-1"
th:id="'page['+${iterStat.index}+'].rolename'"
th:name="'page['+${iterStat.index}+'].rolename'"
th:value="${content.rolename}"
th:text="${content.rolename}"></td>
</tr>
</tbody>
</table>
</div>
</div>
<div th:if="${page} != null and ${ph} != null " class="row">
<div class="col-md-12">
<p class="pageList">
检索结果 <span th:text="${page.totalElements}"></span>件 <span
class="prev"
th:attr="style=${ph.hiddenFirst} ? 'visibility:hidden'"><a
class="js-pagenation"
th:href="@{/sample/users/show(page=0,size=${page.size})}"
id="searchIdFirst"><<</a></span> <span class="prev"
th:attr="style=${ph.hiddenPrev} ? 'visibility:hidden'"><a
class="js-pagenation"
th:href="@{/sample/users/show(page=${page.number - 1},size=${page.size})}"><</a></span>
<span th:text="${page.number + 1}"></span> / <span
th:text="${page.totalPages}"></span> 页 <span class="next"
th:attr="style=${ph.hiddenNext} ? 'visibility:hidden'"><a
class="js-pagenation"
th:href="@{/sample/users/show(page=${page.number + 1},size=${page.size})}">></a></span>
<span class="next"
th:attr="style=${ph.hiddenLast} ? 'visibility:hidden'"><a
class="js-pagenation"
th:href="@{/sample/users/show(page=${page.totalPages - 1},size=${page.size})}">>></a></span>
</p>
</div>
</div>
</div>
</div>
</div>
</div>
</form>
</div>
</div>
</section>
<!-- /.content -->
</div>
<!-- /.content-wrapper -->
</div>
<!-- ./wrapper -->
<div th:replace="bottom-js"></div>
<script th:inline="javascript">
/*<![CDATA[*/
$(document).ready(function() {
var table = $('#searchResult').DataTable({
scrollY : "390px",
scrollX : true,
scrollCollapse : true,
paging : false,
searching : false,
});
new $.fn.dataTable.FixedColumns(table, {
leftColumns : 1
});
$('.js-pagenation').each(function() {
$(this).click(function() {
$('#usersInputForm').attr('action', $(this).attr('href'));
$(this).attr('href', '#');
$('#usersInputForm').submit();
});
});
});
/*]]>*/
</script>
</body>
</html>
5, 启动项目,输入URL,http://localhost:8888/sample/users/show
代码下载地址
http://pan.baidu.com/s/1o6spOJS,seventh.zip
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!