接上一篇：日历插件
1. 要实现的页面
- 点击市场活动导航，显示tbl_activity表的所有数据
2. 输入查询条件，点击查询按钮，在表中显示查询结果
2. 分析
3. Mapper层
3.1 ActivityMapper.java接口类
-
定义一个selectActivityByConditionForPage()方法，参数为Map<String,Object> map，返回值为int类型，条件查询市场活动并分页查询
-
定义selectCountOfActivityByCondition()方法，条件查询市场活动的数量，参数为Map<String,Object>，返回值为long类型
3.2 ActivityMapper.xml【重点】
3.2.1 selectActivityByConditionForPage
<select id="selectActivityByConditionForPage" parameterType="map" resultMap="BaseResultMap">
select a.activity_id, u1.u_login_act as activity_owner, activity_name,
activity_start_date, activity_end_date, activity_cost,
activity_description,activity_create_time , u2.u_login_act as activity_create_by,
activity_edit_time, u3.u_login_act as activity_edit_by
from tbl_activity a
join tbl_user u1 on a.activity_owner = u1.user_id
join tbl_user u2 on a.activity_create_by = u2.user_id
left join tbl_user u3 on a.activity_edit_by = u3.user_id
<where>
<if test="activity_name!=null and activity_name!=''">
and a.activity_name like '%' #{activity_name} '%'
</if>
<if test="activity_owner!=null and activity_owner!=''">
and u1.u_login_act like '%' #{activity_owner} '%'
</if>
<if test="activity_start_date!=null and activity_start_date!=''">
and a.activity_start_date >= #{activity_start_date}
</if>
<if test="activity_end_date!=null and activity_end_date!=''">
and a.activity_end_date <= #{activity_end_date}
</if>
</where>
order by a.activity_create_time desc
limit #{pageNo},#{pageSize}
</select>
- 添加一个select标签，id为selectActivityByConditionForPage，与刚刚定义的方法名一致，参数类型parameterType为map，结果集为表中所有列，所以复制自动生成的resultMap标签的id值
- 向市场活动表tbl_activity中插入数据时，对于市场活动的所有者，创建者，编辑者，为了防止tbl_user表中的用户改名，所以我们保存的是user表中u_name对应的id值，然而我们现在查找数据，想要显示name值，由此需要关联查询：表名 表名缩写 join 表名 表名缩写 on 条件，关联的都是tbl_user表
select a.activity_id, u1.u_login_act as activity_owner, activity_name,
activity_start_date, activity_end_date, activity_cost,
activity_description,activity_create_time , u2.u_login_act as activity_create_by,
activity_edit_time, u3.u_login_act as activity_edit_by
from tbl_activity a
join tbl_user u1 on a.activity_owner = u1.user_id
join tbl_user u2 on a.activity_create_by = u2.user_id
left join tbl_user u3 on a.activity_edit_by = u3.user_id
-
用于市场活动的编辑者还没有赋值（为空），页面未写，所以查出来的是null值，所以需要左外连接查询：左外连接（LEFT JOIN ON)
-
where标签
用于要实现的功能是用户可以点击查询按钮，对名称和所有者模糊查询，开始日期和结束日期查比开始日期小比结束日期大的，且部分值可以为空，所以用where标签和if标签：Mybatis的XML映射文件中常用标签 -
排序查询：order by 条件 desc
排序查询 -
分页查询
limit 0，5//从第一行开始查，依次查5个数据
3.2.2 selectCountOfActivityByCondition
- select count(*)
- 注意sql语句count(*)，括号里面不要有空格
4. Service层
4.1 ActivityService.java类
List<Activity> queryActivityByConditionForPage(Map<String, Object> map);
int queryCountOfActivityByCondition(Map<String, Object> map);
4.2 ActivityServiceImpl.xml
5. Controller层
5.1 search()方法
- 方法的返回值类型为Object，返回值类型前添加@ResponseBody注解，把返回值解析为json字符串返回
- 根据功能，方法的参数为所有者，名称，开始/结束日期，要查询第几行的数据，依次查询几行
- 调用service层方法给的参数map，key要于sql语句中#{}里面的名称一一对应
- 查询的结果保存在Map<String,Object> resultMap = new HashMap<>();中并返回
- 如何在jsp中显示数据？
6. script标签封装函数
在script标签中定义函数
- success:function(ret){}如下
success: function (ret) {
//显示总条数
$("#totalRowsId").text(ret.totalRows);
//显示市场活动的列表
//遍历activityList,拼接所有行数据
var htmlStr = "";
$.each(ret.activities, function (index, object) {
htmlStr += "<tr>";
htmlStr += "<td><input type=\"checkbox\" value=\"" + object.activityId + "\"></td>";
htmlStr += "<td><a style=\"text-decoration: none;cursor:pointer;\" οnclick=\"window.location.href='#'\">" + object.activityName + "</a></td>";
htmlStr += "<td>" + object.activityOwner + "</td>";
htmlStr += "<td>" + object.activityStartDate + "</td>";
htmlStr += "<td>" + object.activityEndDate + "</td>";
htmlStr += "</tr>";
});
//$("#tbodyId").html(htmlStr);覆盖追加
//$("#tbodyId").append(jsp页面片段的字符串);追加显示
$("#tbodyId").html(htmlStr);
}
- ret.totalRows获取search()方法返回值，也就是json字符串，resultMap转为json字符串为
[{"name": "xx", "age": "16"},{"name": "yy", "age": "18"}]
- 取其中key值为totalRows的数据
- $(“#totalRowsId”).text(ret.totalRows);
<button class="btn btn-default col-md-1">共<b id="totalRowsId">50</b>条数据</button>
-
定义一个字符串变量 var htmlStr=“”
-
$.each()遍历数据 并把遍历是数据追加到htmlStr变量里面
-
each()方法里面给两个参数，一个是要遍历的数据，也就是controller层方法的返回值中，key值为activities的数据
一个是函数，函数的2个参数：index相当于遍历数据的第几个，object为当前遍历的数据
- 最后把htmlStr显示在table标签的tbody标签里面
$(“#tbodyId”).html(htmlStr)；
7. script标签入口函数里面调用函数
在入口函数里面调用刚刚封装的函数。一个是直接调用，实现，点击导航栏的市场活动li标签table表格显示tbl_activity表中第一页的数据，显示10条
一个是点击查询按钮，调用，为查询按钮绑定点击事件，选择器.val()获取select标签的value值
然后调用queryActivityByCondition()函数
8. index.jsp页面完整代码
<%
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + request.getContextPath() + "/";
%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<base href="<%=basePath%>">
<title>市场活动主页面</title>
<script type="text/javascript"
src="jquery/bootstrap-datetimepicker-master/sample in bootstrap v3/jquery/jquery-1.8.3.min.js"></script>
<link rel="stylesheet" type="text/css"
href="jquery/bootstrap-datetimepicker-master/sample in bootstrap v3/bootstrap/css/bootstrap.css">
<script type="text/javascript"
src="jquery/bootstrap-datetimepicker-master/sample in bootstrap v3/bootstrap/js/bootstrap.min.js"></script>
<link rel="stylesheet" type="text/css"
href="jquery/bootstrap-datetimepicker-master/css/bootstrap-datetimepicker.min.css">
<script type="text/javascript"
src="jquery/bootstrap-datetimepicker-master/js/bootstrap-datetimepicker.min.js"></script>
<script type="text/javascript"
src="jquery/bootstrap-datetimepicker-master/js/locales/bootstrap-datetimepicker.zh-CN.js"></script>
<script type="text/javascript">
$(function () {
//给“创建”按钮添加单击事件
$("#createActivityBtn").click(function () {
//初始化,每次点击创建按钮，清空上次填写的数据
$("#create-ActivityForm").get(0).reset();
//弹出创建市场活动的模态窗口
$("#create-ActivityModel").modal("show");
});
//创建市场活动的模态窗口"保存"按钮的单击事件
$("#saveActivityBtn").click(function () {
var owner = $("#create-activityOwner").val();
var name = $.trim($("#create-activityName").val());
var startDate = $("#create-activityStartDate").val();
var endDate = $("#create-activityEndDate").val();
var cost = $.trim($("#create-activityCost").val());
var description = $.trim($("#create-activityDescription").val());
if (owner == "") {
alert("所有者不能为空");
return;
}
if (name == "") {
alert("名称不能为空");
return;
}
if (startDate != "" && endDate != "") {
if (endDate < startDate) {
alert("结束日期不能比开始日期小");
return;
}
}
var regExp = /^(([1-9]\d*)|0)$/;
if (!regExp.test(cost)) {
alert("成本只能为非负整数");
return;
}
//发送请求
$.ajax({
url: "/workbench/activity/save.do",
data: {
activityOwner: owner,
activityName: name,
activityStartDate: startDate,
activityEndDate: endDate,
activityCost: cost,
activityDescription: description
},
type: "post",
success: function (ret) {
if (ret.code == "1") {
alert("保存成功");
$("#create-ActivityModel").modal("hide");
} else {
alert(ret.message);
$("#create-ActivityModel").modal("show");
}
}
});
});
//日历插件
$("#create-activityStartDate").datetimepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
minView: 'month',
initialDate: new Date(),
autoclose: true,
todayBtn: true,
clearBtn: true
});
$("#create-activityEndDate").datetimepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
minView: 'month',
initialDate: new Date(),
autoclose: true,
todayBtn: true,
clearBtn: true
});
$("#query_startDate").datetimepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
minView: 'month',
initialDate: new Date(),
autoclose: true,
todayBtn: true,
clearBtn: true
});
$("#query_endDate").datetimepicker({
language: 'zh-CN',
format: 'yyyy-mm-dd',
minView: 'month',
initialDate: new Date(),
autoclose: true,
todayBtn: true,
clearBtn: true
});
//当市场活动主页面加载完成，查询所有数据的第一页集街所有数据的总条数
//收集参数
queryActivityByConditionForPage(1,10);
//给查询按钮添加单击事件
$("#queryActivityBtn").click(function () {
var pageSize = $("#pageSize").val();
//查询所有符合条件的数据
queryActivityByConditionForPage(1,pageSize);
})
});
//封装函数
function queryActivityByConditionForPage(pageNo,pageSize) {
var name = $("#query_name").val();
var owner = $("#query_owner").val();
var startDate = $("#query_startDate").val();
var endDate = $("#query_endDate").val();
$.ajax({
url: "/workbench/activity/search.do",
data: {
name: name,
owner: owner,
startDate: startDate,
endDate: endDate,
pageNo: pageNo,
pageSize: pageSize
},
type: 'post',
dataType: 'json',
success: function (ret) {
//显示总条数
$("#totalRowsId").text(ret.totalRows);
//显示市场活动的列表
//遍历activityList,拼接所有行数据
var htmlStr = "";
$.each(ret.activities, function (index, object) {
htmlStr += "<tr>";
htmlStr += "<td><input type=\"checkbox\" value=\"" + object.activityId + "\"></td>";
htmlStr += "<td><a style=\"text-decoration: none;cursor:pointer;\" οnclick=\"window.location.href='#'\">" + object.activityName + "</a></td>";
htmlStr += "<td>" + object.activityOwner + "</td>";
htmlStr += "<td>" + object.activityStartDate + "</td>";
htmlStr += "<td>" + object.activityEndDate + "</td>";
htmlStr += "</tr>";
});
//$("#tbodyId").html(htmlStr);覆盖追加
//$("#tbodyId").append(jsp页面片段的字符串);追加显示
$("#tbodyId").html(htmlStr);
}
});
}
</script>
</head>
<body>
<!--创建市场活动的模态框-->
<div class="modal fade" id="create-ActivityModel" tabindex="-1">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span
aria-hidden="true">×</span></button>
<h4 class="modal-title" id="myModalLabel">创建市场活动</h4>
</div>
<form class="form-inline" id="create-ActivityForm">
<div class="modal-body col-md-12">
<div class="form-group">
<label class="col-md-2 control-label">所有者<span style="color: red;">*</span></label>
<div class="col-md-10">
<select class="form-control" id="create-activityOwner">
<c:forEach items="${requestScope.users}" var="u">
<option value="${u.userId}">${u.uLoginAct}</option>
</c:forEach>
</select>
</div>
<label class="col-md-2 control-label">名称<span style="color:red">*</span></label>
<div class="col-md-10"><input type="text" id="create-activityName" class="form-control"/></div>
</div>
<div class="form-group col-md-12">
<label>开始日期</label>
<input type="text" class="form-control" id="create-activityStartDate" readonly/>
<label>结束日期</label>
<input type="text" class="form-control" id="create-activityEndDate" readonly/>
</div>
<div class="form-group col-md-12">
<label>成本</label>
<input type="text" class="form-control" id="create-activityCost"/>
<label>描述</label>
<textarea class="form-control" rows="3" id="create-activityDescription"></textarea>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
<button type="button" class="btn btn-primary" id="saveActivityBtn">保存</button>
</div>
</form>
</div>
</div>
</div>
<!--修改市场活动的模态框-->
<div class="modal fade" id="edit-ActivityModel" tabindex="-1">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span
aria-hidden="true">×</span></button>
<h4 class="modal-title">修改市场活动</h4>
</div>
<form class="form-inline">
<div class="modal-body col-md-12">
<div class="form-group">
<label class="col-md-2 control-label">所有者<span style="color: red;">*</span></label>
<div class="col-md-10">
<select class="form-control">
<c:forEach items="${requestScope.users}" var="u">
<option value="${u.userId}">${u.uLoginAct}</option>
</c:forEach>
</select>
</div>
<label class="col-md-2 control-label">名称<span style="color:red">*</span></label>
<div class="col-md-10"><input type="text" class="form-control"/></div>
</div>
<div class="form-group col-md-12">
<label>开始日期</label>
<input type="text" class="form-control"/>
<label>结束日期</label>
<input type="text" class="form-control"/>
</div>
<div class="form-group col-md-12">
<label>成本</label>
<input type="text" class="form-control"/>
</div>
<div class="form-group col-md-12">
<label>描述</label>
<textarea class="form-control" rows="3"></textarea>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
<button type="button" class="btn btn-primary">保存</button>
</div>
</form>
</div>
</div>
</div>
<!--主页面-->
<div>
<!--搜索-->
<div>
<h2>市场活动列表</h2>
<hr/>
<div>
<div class="row">
<div class="col-xs-3">
<div class="input-group">
<span class="input-group-btn">
<button class="btn btn-default" type="button">名称</button>
</span>
<input type="text" class="form-control" id="query_name">
</div><!-- 名称 -->
</div>
<div class="col-xs-3">
<div class="input-group">
<span class="input-group-btn">
<button class="btn btn-default" type="button">所有者</button>
</span>
<input type="text" class="form-control" id="query_owner">
</div><!-- 所有者 -->
</div>
<div class="col-xs-3">
<div class="input-group">
<span class="input-group-btn">
<button class="btn btn-default" type="button">开始日期</button>
</span>
<input type="text" class="form-control" id="query_startDate">
</div><!-- 开始日期-->
</div>
<div class="col-xs-3">
<div class="input-group">
<span class="input-group-btn">
<button class="btn btn-default" type="button">结束日期</button>
</span>
<input type="text" class="form-control" id="query_endDate">
</div><!-- 结束日期-->
</div>
<div class="col-xs-3">
<button id="queryActivityBtn" type="button" class="btn btn-success">查询</button>
</div>
</div>
</div>
</div>
<br/><br/>
<!--操作按钮-->
<div class="btn-group" role="group">
<button type="button" style="pointer-events:auto" class="btn btn-primary" id="createActivityBtn">
<span class="glyphicon glyphicon-plus" aria-hidden="true"></span>
创建
</button>
<button type="button" class="btn btn-default" data-toggle="modal" data-target="#edit-ActivityModel">
<span class="glyphicon glyphicon-pencil" aria-hidden="true"></span>
修改
</button>
<button type="button" class="btn btn-warning">
<span class="glyphicon glyphicon-minus" aria-hidden="true"></span>删除
</button>
<div class="btn-group" role="group">
<button type="button" class="btn btn-default"><span class="glyphicon glyphicon-import"
aria-hidden="true"></span>上次到列表数据（导入）
</button>
<button type="button" class="btn btn-default"><span class="glyphicon glyphicon-export"
aria-hidden="true"></span>下载戴列表数据（批量导入）
</button>
<button type="button" class="btn btn-default"><span class="glyphicon glyphicon-export"
aria-hidden="true"></span>下载列表数据（选择导出）
</button>
</div>
</div>
<!--表格-->
<table class="table table-striped text-left">
<thead>
<tr>
<th><input type="checkbox"></th>
<th>名称</th>
<th>所有者</th>
<th>开始日期</th>
<th>结束日期</th>
</tr>
</thead>
<tbody id="tbodyId">
</tbody>
</table>
<!--分页查询-->
<div class="col-md-12">
<button class="btn btn-default col-md-1">共<b id="totalRowsId">50</b>条数据</button>
<div class="btn-group col-md-4" role="group" aria-label="...">
<button class="btn btn-default">显示</button>
<div class="btn-group" role="group">
<select class="form-control" id="pageSize">
<option value="10">10</option>
<option value="20">20</option>
<option value="30">30</option>
</select>
</div>
<button class="btn btn-default">条/页</button>
</div>
<div class="col-md-4">
<nav aria-label="Page navigation">
<ul class="pagination">
<li>
<a href="#" aria-label="Previous">
<span aria-hidden="true">«</span>
</a>
</li>
<li><a href="#">1</a></li>
<li><a href="#">2</a></li>
<li><a href="#">3</a></li>
<li><a href="#">4</a></li>
<li><a href="#">5</a></li>
<li>
<a href="#" aria-label="Next">
<span aria-hidden="true">»</span>
</a>
</li>
</ul>
</nav>
</div>
</div>
</div>
</body>
</html>
17万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
