MyBatis按今天、本周、本月过滤查询（涉及日期格式转换、多条件查询、分页）
程序员文章站
2024-01-22 22:57:22
...
要点探讨
String转date
<if test="date eq 'Mon' ">
AND DATE_FORMAT( STR_TO_DATE(time,"%Y年%m月%d日 %H时%i分%s秒"), '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
</if>
源码
<div class="three">
<div class="tag" style="background:#66B3FF;">
<ul>
<li class="active" d1="Day">今天</li>
<li d1="Week">本周</li>
<li d1="Mon">本月</li>
</ul>
</div>
<select class="choose1">
<option>---请选择学校---</option>
</select> <span class="query1">查询</span> <span class="in1"></span>
<div class="tb1">
<table>
<thead>
<tr class="table_head1">
<th>学校名称</th>
<th>学生姓名</th>
<th>接收人电话</th>
<th>打卡时间</th>
<th>打卡状态</th>
</tr>
</thead>
<tbody class="table_body1"></tbody>
</table>
</div>
<div class="bo1">
<span class="up1">上一页</span> <input class="num1" type="text" value="1" />
<span class="down1">下一页</span> <span class="findPage1"></span>
</div>
<div class="search1">
<input class="recvName1" type="text" placeholder="请输入学生姓名"> <input
class="mobile1" type="text" placeholder="请输入接收人电话"> <span
class="btn_search1">搜索</span>
</div>
<a href="#" class="top">TOP↑</a>
</div>//第三个页面
//时间
var e ;
//学校名称
var schName ;
//页数
var page;
var state = 0;
//查询数据
//单击上一页按钮事件
$(".up1").click(function(){
heads = new Array();
page = $(".num1").val();
schName = $(".choose1").val();
var state = 1;
if(parseInt(page)>1){
$(".num1").val(parseInt(page)-1);
page = $(".num1").val();
if(schName != "---请选择学校---"){
for(var i=0;i<3;i++){
if(($(".three ul li").eq(i)).hasClass('active')){
e = ($(".three ul li").eq(i)).attr("d1");
$(".findPage1").css('display','inline-block');
$(".findPage1").html('').html('翻页中 ......');
//查询数据
getTableData1(e,schName,page,state);
}
}
}
}
});
//单击下一页按钮事件
$(".down1").click(function(){
heads = new Array();
page = $(".num1").val();
schName = $(".choose1").val();
var state = 2;
if(schName != "---请选择学校---"){
$(".num1").val(parseInt(page)+1);
}
page = $(".num1").val();
if(schName != "---请选择学校---"){
for(var i=0;i<3;i++){
if(($(".three ul li").eq(i)).hasClass('active')){
e = ($(".three ul li").eq(i)).attr("d1");
$(".findPage1").css('display','inline-block');
$(".findPage1").html('').html('翻页中 ......');
//查询数据
getTableData3(e,schName,page,state);
}
}
}
});
//获得卡片数据并添加模板方法的调用
getSchName();
//获得卡片数据并添加模板
function getSchName(){
//获得卡片数据
CUBE.getDataByAjax(
'../sys/getSchName.do',
'JSON',
console.log(JSON),
function(res){
if(res.length==0 || res==null){
$('.stat_messages').html('').append('<img src="../assets/img/empty.jpg" style="width: 250px;margin-right: 20px;" /><span style="color:#999;font-size: 32px;">啊偶~ 没人用呢!.</span>');
}else{
//获得学校名单
for(var i=0;i<res.length;i++){
$(".choose1").append("<option>"+res[i]+"</option>");
}
}
}
);
}
//平安到校信息统计 单击今天/本周/本月 查询数据
$('.three ul li').click(function(e) {
heads = new Array();
$(".num1").val('1');
schName = $(".choose1").val();
if(schName != "---请选择学校---"){
page = $(".num1").val();
$('.three ul li').removeClass('active');
$(this).addClass('active');
//查询数据
getTableData1($(this).attr("d1"),schName,1,state);
} else{
alert("请先选择学校");
}
});
//点击查询按钮 查询数据
$(".query1").click(function(){
$(".recvName").val('');
$(".recvName").removeClass("red");
$(".recvName").removeClass("green");
$(".mobile").val('');
$(".mobile").removeClass("red");
$(".mobile").removeClass(".green");
heads = new Array();
$(".num1").val('1');
schName = $(".choose1").val();
if(schName != "---请选择学校---"){
for(var i=0;i<3;i++){
if(($(".three ul li").eq(i)).hasClass('active')){
e = ($(".three ul li").eq(i)).attr("d1");
//查询数据
getTableData1(e,schName,1,state);
}
}
}else{
alert("请先选择学校");
}
});
//查询数据的方法 传入时间/学校/页数 默认每页50条数据
function getTableData1(e,schName,page,state){
$(".in1").css('display','inline-block');
$(".in1").html('').html('查询中,请稍后 ......');
recvName1 = $(".recvName1").val().trim();
mobile1 = $(".mobile1").val().trim();
if(recvName1=='' && mobile1==''){
url = '../sys/list.do?schName='+encodeURI(encodeURI(schName))+'&date='+e+'&page='+page+'&pageSize='+20;
}else if(recvName1==''){
url = '../sys/list.do?schName='+encodeURI(encodeURI(schName))+'&date='+e+'&mobile='+mobile1+'&page='+page+'&pageSize='+20;
}else if(mobile1==''){
url = '../sys/list.do?schName='+encodeURI(encodeURI(schName))+'&date='+e+'&recvName='+encodeURI(encodeURI(recvName1))+'&page='+page+'&pageSize='+20;
}else{
url = '../sys/list.do?schName='+encodeURI(encodeURI(schName))+'&date='+e+'&recvName='+encodeURI(encodeURI(recvName1))+'&mobile='+mobile1+'&page='+page+'&pageSize='+20;
}
CUBE.getDataByAjax(
url,
'JSON',
function(res){
search1(e,schName,page);
if(res.length==0 || res==null){
$('.tb1').css('display','block');
$('.tb1').html('').append('<img src="../assets/img/empty.jpg" style="width: 250px;margin-right: 20px;" /><span style="color:#999;font-size: 32px;">啊偶~ 没人用呢!.</span>');
$(".bo1").css('display','none');
}else{
$('.tb1').css('display','block');
$('.tb1').html('').append('<table>'+
'<thead>'+
'<tr class="table_head1">'+
'<th>学校名称</th>'+
'<th>学生姓名</th>'+
'<th>接收人电话</th>'+
'<th>打卡时间</th>'+
'<th>打卡状态</th>'+
'</tr>'+
'</thead>'+
'<tbody class="table_body1"></tbody>'+
'</table>');
//画表体
drawTableBody1(res);
$(".bo1").css('display','block');
search1(e,schName,page);
}
$(".in1").html('').html('查询完毕!');
setTimeout(function(){
$(".in1").css('display','none');
},1000);
if(state == 2){
$(".findPage1").html('').html('没有更多了!');
}else if (state == 1){
$(".findPage1").html('').html('查询完毕!');
}
setTimeout(function(){
$(".findPage1").css('display','none');
},1000);
},
function(){
error();
}
);
}
//画表体的方法
function drawTableBody1(res){
CUBE.renderList(
res,
$(".table_body1"),
$("#table_body1"),
$(".table_body1"),
true,
function(){}
);
}
//点击下一页 查询数据的方法 传入时间/学校/页数 默认每页50条数据
function getTableData3(e,schName,page,state){
$(".in1").css('display','inline-block');
$(".in1").html('').html('查询中,请稍后 ......');
recvName1 = $(".recvName1").val().trim();
mobile1 = $(".mobile1").val().trim();
if(recvName1=='' && mobile1==''){
url = '../sys/list.do?schName='+encodeURI(encodeURI(schName))+'&date='+e+'&page='+page+'&pageSize='+20;
}else if(recvName1==''){
url = '../sys/list.do?schName='+encodeURI(encodeURI(schName))+'&date='+e+'&mobile='+mobile1+'&page='+page+'&pageSize='+20;
}else if(mobile1==''){
url = '../sys/list.do?schName='+encodeURI(encodeURI(schName))+'&date='+e+'&recvName='+encodeURI(encodeURI(recvName1))+'&page='+page+'&pageSize='+20;
}else{
url = '../sys/list.do?schName='+encodeURI(encodeURI(schName))+'&date='+e+'&recvName='+encodeURI(encodeURI(recvName1))+'&mobile='+mobile1+'&page='+page+'&pageSize='+20;
}
CUBE.getDataByAjax(
url,
// '../sys/all/Mon/112899/2/3',
'JSON',
function(res){
search1(e,schName,page);
if(res.length==0 || res==null){
if(page>1){
getTableData1(e,schName,page-1,state);
$(".num1").val(page-1);
}
$('.tb1').css('display','block');
$('.tb1').html('').append('<img src="../assets/img/empty.jpg" style="width: 250px;margin-right: 20px;" /><span style="color:#999;font-size: 32px;">啊偶~ 没人用呢!.</span>');
$(".bo1").css('display','none');
}else{
$('.tb1').css('display','block');
$('.tb1').html('').append('<table>'+
'<thead>'+
'<tr class="table_head1">'+
'<th>学校名称</th>'+
'<th>学生姓名</th>'+
'<th>接收人电话</th>'+
'<th>打卡时间</th>'+
'<th>打卡状态</th>'+
'</tr>'+
'</thead>'+
'<tbody class="table_body1"></tbody>'+
'</table>');
//画表体
drawTableBody1(res);
$(".bo1").css('display','block');
}
$(".in1").html('').html('查询完毕!');
setTimeout(function(){
$(".in1").css('display','none');
},1000);
$(".findPage1").html('').html('查询完毕!');
setTimeout(function(){
$(".findPage1").css('display','none');
},1000);
},
function(){
error();
}
);
}
$(".btn_search1").click(function(){
schName = $(".choose1").val();
if(schName == "---请选择学校---"){
alert("请先选择学校");
}else{
getTableData1($('.three ul li').attr("d1"),schName,1,state);
}
});
//获取ajax请求地址
var url;
var recvName1;
var mobile1;
//搜索按钮点击的方法
function search1(e,schName,page){
$(".btn_search1").unbind("click");
$(".btn_search1").click(function(){
$(".num1").val('1');
recvName1 = $(".recvName1").val().trim();
mobile1 = $(".mobile1").val().trim();
if(recvName1!='' || mobile1!=''){
if($(".recvName1").hasClass('red') || $(".mobile1").hasClass('red')){
alert("格式错误!请重新输入");
}else{
getTableData1(e,schName,1,state);
CUBE.getDataByAjax(
url,
// '../sys/all/Mon/112899/2/3',
'JSON',
function(res){
search1(e,schName,page);
if(res.length==0 || res==null){
if(page>1){
getTableData1(e,schName,page-1,state);
$(".num1").val(page-1);
}
$('.tb1').css('display','block');
$('.tb1').html('').append('<img src="../assets/img/empty.jpg" style="width: 250px;margin-right: 20px;" /><span style="color:#999;font-size: 32px;">啊偶~ 没人用呢!.</span>');
$(".bo1").css('display','none');
}else{
$('.tb1').css('display','block');
$('.tb1').html('').append('<table>'+
'<thead>'+
'<tr class="table_head1">'+
'<th>学校名称</th>'+
'<th>学生姓名</th>'+
'<th>接收人电话</th>'+
'<th>打卡时间</th>'+
'<th>打卡状态</th>'+
'</tr>'+
'</thead>'+
'<tbody class="table_body1"></tbody>'+
'</table>');
//画表体
drawTableBody1(res);
$(".bo1").css('display','block');
}
$(".in1").html('').html('查询完毕!');
setTimeout(function(){
$(".in1").css('display','none');
},1000);
$(".findPage1").html('').html('完毕!');
setTimeout(function(){
$(".findPage1").css('display','none');
},1000);
},
function(){
error();
}
);
}
}
});
}
//筛选接收人姓名/电话
//接收人姓名
var recvName1;
var regName1 = /^[\u4e00-\u9fa5]+$/;
//接收人电话
var mobile1;
var regMob1 = /^\d{11}$/;
//正则判断格式
$(".recvName1").change(function(){
recvName1 = $(".recvName1").val().trim();
if(regName1.test(recvName1)){
$(".recvName1").removeClass('red');
$(".recvName1").removeClass('green');
$(".recvName1").addClass('green');
}else if(recvName1 == undefined || recvName1 == null || recvName1 == ''){
$(".recvName1").removeClass('red');
$(".recvName1").removeClass('green');
}else{
$(".recvName1").removeClass('red');
$(".recvName1").removeClass('green');
$(".recvName1").addClass('red');
}
});
$(".mobile1").change(function(){
mobile1 = $(".mobile1").val().trim();
if(regMob1.test(mobile1)){
$(".mobile1").removeClass('red');
$(".mobile1").removeClass('green');
$(".mobile1").addClass('green');
}else if(mobile1 == undefined || mobile1 == null || mobile1 == ''){
$(".mobile1").removeClass('red');
$(".mobile1").removeClass('green');
}else{
$(".mobile1").removeClass('red');
$(".mobile1").removeClass('green');
$(".mobile1").addClass('red');
}
});
});
package cn.cube.entity;
public class PadxMessage {
private Long id;
private String schName;
private String recv;
private String stuName;
private String time;
private String stuIO;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getSchName() {
return schName;
}
public void setSchName(String schName) {
this.schName = schName;
}
public String getRecv() {
return recv;
}
public void setRecv(String recv) {
this.recv = recv;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
public String getStuIO() {
return stuIO;
}
public void setStuIO(String stuIO) {
this.stuIO = stuIO;
}
@Override
public String toString() {
return "PadxMessage [id=" + id + ", schName=" + schName + ", recv="
+ recv + ", stuName=" + stuName + ", time=" + time + ", stuIO="
+ stuIO + "]";
}
public PadxMessage(Long id, String schName, String recv, String stuName,
String time, String stuIO) {
super();
this.id = id;
this.schName = schName;
this.recv = recv;
this.stuName = stuName;
this.time = time;
this.stuIO = stuIO;
}
}
package cn.cube.service;
import com.alibaba.fastjson.JSONArray;
public interface PadxMessageService {
public JSONArray getSchName();
public JSONArray list(String schName, String date, String recvName, String mobile, Integer start, Integer pageSize);
}
package cn.cube.service.impl;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import com.alibaba.fastjson.JSONArray;
import cn.cube.mapper.PadxMessageDao;
import cn.cube.service.PadxMessageService;
@Service
public class PadxMessageServiceImpl implements PadxMessageService{
PadxMessageDao padxMessageDao;
public PadxMessageDao getPadxMessageDao() {
return padxMessageDao;
}
@Resource
public void setPadxMessageDao(PadxMessageDao padxMessageDao) {
this.padxMessageDao = padxMessageDao;
}
@Override
public JSONArray getSchName() {
return padxMessageDao.getSchName();
}
@Override
public JSONArray list(String schName, String date, String recvName, String mobile, Integer start, Integer pageSize) {
return padxMessageDao.list(schName, date, recvName, mobile, start, pageSize);
}
}
package cn.cube.mapper;
import org.apache.ibatis.annotations.Param;
import com.alibaba.fastjson.JSONArray;
import cn.cube.util.Mybatis;
@Mybatis
public interface PadxMessageDao {
public JSONArray getSchName();
public JSONArray list(@Param("schName")String schName, @Param("date")String date, @Param("stuName")String stuName, @Param("recv")String recv,
@Param("start")Integer start, @Param("pageSize")Integer pageSize);
}
核心
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<!-- namespace指定UserMapper接口 -->
<mapper namespace="cn.cube.mapper.PadxMessageDao">
<select id="getSchName" resultType="String">
select t.schName from padx_message as t group by t.schName
</select>
<select id="list" resultType="cn.cube.entity.PadxMessage">
select * from padx_message where
<if test="schName !=null ">
schName=#{schName}
</if>
<if test="stuName !=null ">
AND stuName=#{stuName}
</if>
<if test="recv !=null ">
AND recv=#{recv}
</if>
<if test="date eq 'Day' ">
AND to_days(STR_TO_DATE(time,"%Y年%m月%d日 %H时%i分%s秒")) = to_days(now())
</if>
<if test="date eq 'Week' ">
AND YEARWEEK(STR_TO_DATE(time,"%Y年%m月%d日 %H时%i分%s秒")) = YEARWEEK(now())
</if>
<if test="date eq 'Mon' ">
AND DATE_FORMAT( STR_TO_DATE(time,"%Y年%m月%d日 %H时%i分%s秒"), '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
</if>
<if test="start!=null and pageSize!=null">
limit #{start},#{pageSize}
</if>
</select>
</mapper>package cn.cube.ctrl;
import java.io.UnsupportedEncodingException;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import cn.cube.service.PadxMessageService;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cicada.cube.ctrl.CubeRestBase;
@Controller
@RequestMapping("/sys")
public class PadxMessageController extends CubeRestBase {
PadxMessageService padxMessageService;
public PadxMessageService getPadxMessageService() {
return padxMessageService;
}
@Resource
public void setPadxMessageService(PadxMessageService padxMessageService) {
this.padxMessageService = padxMessageService;
}
@RequestMapping(value = "/getSchName.do", method = RequestMethod.GET)
public @ResponseBody JSONArray getSchName() {
JSONArray ja = padxMessageService.getSchName();
if (ja != null && !ja.isEmpty()) {
return ja;
} else {
return null;
}
}
@RequestMapping(value = "/list.do", method = RequestMethod.GET)
public @ResponseBody JSONObject list(HttpServletRequest request, HttpServletResponse response ) {
String schName = null;
String recvName = null;
try {
schName = java.net.URLDecoder.decode(request.getParameter("schName"),"UTF-8");
if(request.getParameter("recvName") != null){
recvName = java.net.URLDecoder.decode(request.getParameter("recvName"),"UTF-8");
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
Integer page = Integer.valueOf(request.getParameter("page"));
Integer pageSize = Integer.valueOf(request.getParameter("pageSize"));
Integer start = (page-1)*pageSize;
String date = request.getParameter("date");
String mobile = request.getParameter("mobile");
JSONArray ja = padxMessageService.list(schName,date, recvName, mobile, start, pageSize);
JSONObject jo = new JSONObject();
jo.put("data", ja);
jo.put("success", true);
if (jo != null && !jo.isEmpty()) {
return jo;
} else {
return null;
}
}
}
上一篇： Git【安装及基本操作】
下一篇： MYSQL安装及基本操作