先说一下模糊查询 在方法上写的SQL是跟数据库里边的不一样,这里果断入坑了。
来个MySQL例子:
模糊查询user中的name: select * from user where name like ‘%你想查的数据%’;
在SSM里这样写会出错,如下:
Parameter index out of range (1 > number of parameters, which is 0).
正确写法: @Select(“select * from user where name like CONCAT(‘%’,#{name},’%’)”) 里边的#{name}就是你想查的数据
上代码
mapper.java
//查询所有帖子列表 (统计回复 top/ 时间降序 limit 20)@Select("select count(rp.rcontent) as replyNum,p.*,u.user_name from t_posts p left join t_user u on u.user_id=p.user_id left join t_rpost rp on p.id=rp.post_id group by p.id order by p.top desc,p.starttime desc limit 20")List<Posts> getPostListLimit();//根据帖子标题模糊查询 (降序)@Select("select count(rp.rcontent) as replyNum,p.*,u.user_name from t_posts p left join t_user u on u.user_id=p.user_id left join t_rpost rp on p.id=rp.post_id where p.title like CONCAT('%',#{title},'%') group by p.id order by p.top desc,p.starttime desc")List<Posts> queryByTitle(String title);
service.java
//首页 获取帖子列表public List<Posts> getPostListLimit(){List<Posts> PostsList= mapper.getPostListLimit();return PostsList;}//模糊查询帖子标题public List<Posts> getPostsByTitle(String title){return mapper.queryByTitle(title);}
controller.java
//跳转到帖子主页列表@RequestMapping(value="getPostsIndex")public String getPostsIndex(){return "post/postsIndex";}//首页 获取前二十条帖子列表@RequestMapping(value="getPostListLimit",method=RequestMethod.POST)@ResponseBodypublic List<Posts> getPostListLimit(){return service.getPostListLimit();}//模糊查询帖子标题@RequestMapping(value="getPostsByTitle",method=RequestMethod.POST)@ResponseBodypublic List<Posts> getPostsByTitle(@Param("title")String title){return service.getPostsByTitle(title);}
jsp页面 (只有相关部分):
JS/CSS:
<script type="text/javascript" src="${ctxStatic}/jquery/jquery-2.1.1.min.js"></script><script type="text/javascript" src="${ctxStatic}/layui/res/layui/layui.js"></script><script type="text/javascript" src="${ctxStatic}/laypage/laypage.js"></script><link rel="stylesheet" href="${ctxStatic}/laypage/skin/laypage.css"/><link rel="stylesheet" href="${ctxStatic}/layui/res/layui/css/layui.css"/><link rel="stylesheet" href="${ctxStatic}/layui/res/css/global.css"/>
DIV:
<div class="content"><div class="fly-tab fly-tab-index"><span><a href="${ctx}/getAllPostsIndex">全部</a><a href="jie/index.html">未结帖</a><a href="jie/index.html">已采纳</a><a href="jie/index.html">精帖</a><a href="${ctx}/getPostUserHome">我的帖</a></span><div class="fly-search"><!-- <button class="iconfont icon-sousuo"></button> --><input class="layui-input" placeholder="请输入标题关键字。。。" id="search"></div><a href="${ctx}/addPostInput" class="layui-btn jie-add">发布问题</a></div><ul class="fly-list" id="result"></ul><!-- 置顶/加精 --><ul class="fly-list-top" id="top_list"></ul><!-- 普通帖 --><ul class="fly-list" id="_list"></ul><!-- 分页div --> <div id="fenye" style="text-align: center;margin: 0 auto;"><div class="page_list"></div></div><!-- <ul id="biuuu_city_list" class="fly-list"></ul><div id="biuuu_city" style="text-align: center;"></div> --><div style="text-align: center" id="gengduo"><div class="laypage-main"><a href="${ctx}/getAllPostsIndex" class="laypage-next">更多求解</a></div></div></div>
jQuery+Ajax: /加载主页列表/
<script type="text/javascript">$(document).ready(function(){$('#result').empty().hide();$.ajax({method:'post',url:'${ctx}/getPostListLimit',dataType:'json',success:function(data){if(data.length>0){for(var i=0;i<data.length;i++){if(data[i].top==1&&data[i].nice==1){var html='<li class="fly-list-li">'+'<a href="${ctx}/getPostUserHome?id='+data[i].user_id+'" class="fly-list-avatar">'+'<img src="/1345566427/180/5730976522/0"></img>'+'</a>'+'<h2 class="fly-tip">'+'<a href="${ctx}/getPostById?id='+data[i].id+'">'+data[i].title+'</a>'+'<span class="fly-tip-stick">置顶</span>'+'<span class="fly-tip-jing">精帖</span>'+'</h2>'+'<p>'+'<span><a href="user/home.html">'+data[i].user_name+'</a></span>'+'<span>'+data[i].starttime+'</span>'+'<span>layui框架综合</span>'+'<span class="fly-list-hint">' +'<i class="iconfont" title="回答"></i> '+data[i].replyNum+''+'<i class="iconfont" title="人气"></i> '+data[i].view+''+'</span>'+'</p>'+'</li>';$('#top_list').append(html);}if(data[i].top==1&&data[i].nice==0){/* 置顶帖 */var html1='<li class="fly-list-li">'+'<a href="${ctx}/getPostUserHome?id='+data[i].user_id+'" class="fly-list-avatar">'+'<img src="/1345566427/180/5730976522/0"></img>'+'</a>'+'<h2 class="fly-tip">'+'<a href="${ctx}/getPostById?id='+data[i].id+'">'+data[i].title+'</a>'+'<span class="fly-tip-stick">置顶</span>'//+'<span class="fly-tip-jing">精帖</span>'+'</h2>'+'<p>'+'<span><a href="user/home.html">'+data[i].user_name+'</a></span>'+'<span>'+data[i].starttime+'</span>'+'<span>layui框架综合</span>'+'<span class="fly-list-hint">' +'<i class="iconfont" title="回答"></i> '+data[i].replyNum+''+'<i class="iconfont" title="人气"></i> '+data[i].view+''+'</span>'+'</p>'+'</li>';$('#top_list').append(html1);}if(data[i].top==0&&data[i].nice==1){/* 加精帖 */var html2='<li class="fly-list-li">'+'<a href="${ctx}/getPostUserHome?id='+data[i].user_id+'" class="fly-list-avatar">'+'<img src="/1345566427/180/5730976522/0" alt="">'+'</a>'+'<h2 class="fly-tip">'+'<a href="${ctx}/getPostById?id='+data[i].id+'">'+data[i].title+'</a>'//+'<span class="fly-tip-stick">置顶</span>'+'<span class="fly-tip-jing">精帖</span>'+'</h2>'+'<p>'+'<span><a href="user/home.html">'+data[i].user_name+'</a></span>'+'<span>'+data[i].starttime+'</span>'+'<span>layui框架综合</span>'+'<span class="fly-list-hint">' +'<i class="iconfont" title="回答"></i> '+data[i].replyNum+''+'<i class="iconfont" title="人气"></i>'+data[i].view+''+'</span>'+'</p>'+'</li>';$('#_list').append(html2);}if(data[i].top==0&&data[i].nice==0){/* 普通帖 */var html3='<li class="fly-list-li">'+'<a href="${ctx}/getPostUserHome?id='+data[i].user_id+'" class="fly-list-avatar">'+' <img src="/1345566427/180/5730976522/0" alt="">'+'</a>'+'<h2 class="fly-tip">'+'<a href="${ctx}/getPostById?id='+data[i].id+'">'+data[i].title+'</a>'+'</h2>'+'<p>'+'<span><a href="user/home.html">'+data[i].user_name+'</a></span>'+'<span>'+data[i].starttime+'</span>'+'<span>layui框架综合</span>'+'<span class="fly-list-hint"> '+'<i class="iconfont" title="回答"></i> '+data[i].replyNum+''+'<i class="iconfont" title="人气"></i>'+data[i].view+''+'</span>'+'</p>'+'</li>';$('#_list').append(html3);}}}else{//layer.msg("无数据信息!",{time:1500});var html='<div class="fly-none">并无相关数据</div>';$('.fly-list').append(html);}},error:function(data){layer.msg("数据加载失败,错误信息:"+JSON.stringify(data),{time:10000});}}) })</script>
/加载实时搜索和分页/
<script type="text/javascript">//搜索$('#search').bind('input propertychange',function(){$('#_list').empty().hide();$('#top_list').empty().hide();$('#gengduo').hide();var input="";$('#result').empty().show();input =$('#search').val();if(input.length<0||input==null||input==""){$('#result').empty();} $.ajax({method:'post',url:'${ctx}/getPostsByTitle?title='+input,dataType:'json',success:function(data){if(data.length>0){var nums = 10; //每页出现的数量var pages = Math.ceil((data.length)/nums); //得到总页数var thisDate = function(curr){//此处只是演示,实际场景通常是返回已经当前页已经分组好的数据last = curr*nums - 1;var str=""; var str1="";var str2="";var str3="";last = last >= data.length ? (data.length-1) : last;for(var i = (curr*nums - nums); i <= last; i++){/* for(var i=0;i<data.length;i++){ */if(data[i].top==1&&data[i].nice==1){str+='<li class="fly-list-li">'+'<a href="${ctx}/getPostUserHome?id='+data[i].user_id+'" class="fly-list-avatar">'+'<img src="/1345566427/180/5730976522/0"></img>'+'</a>'+'<h2 class="fly-tip">'+'<a href="${ctx}/getPostById?id='+data[i].id+'">'+data[i].title+'</a>'+'<span class="fly-tip-stick">置顶</span>'+'<span class="fly-tip-jing">精帖</span>'+'</h2>'+'<p>'+'<span><a href="user/home.html">'+data[i].user_name+'</a></span>'+'<span>'+data[i].starttime+'</span>'+'<span>layui框架综合</span>'+'<span class="fly-list-hint">' +'<i class="iconfont" title="回答"></i> '+data[i].replyNum+''+'<i class="iconfont" title="人气"></i> '+data[i].view+''+'</span>'+'</p>'+'</li>';//$('#result').append(html);}if(data[i].top==1&&data[i].nice==0){/* 置顶帖 */str1+='<li class="fly-list-li">'+'<a href="${ctx}/getPostUserHome?id='+data[i].user_id+'" class="fly-list-avatar">'+'<img src="/1345566427/180/5730976522/0"></img>'+'</a>'+'<h2 class="fly-tip">'+'<a href="${ctx}/getPostById?id='+data[i].id+'">'+data[i].title+'</a>'+'<span class="fly-tip-stick">置顶</span>'+'</h2>'+'<p>'+'<span><a href="user/home.html">'+data[i].user_name+'</a></span>'+'<span>'+data[i].starttime+'</span>'+'<span>layui框架综合</span>'+'<span class="fly-list-hint">' +'<i class="iconfont" title="回答"></i> '+data[i].replyNum+''+'<i class="iconfont" title="人气"></i> '+data[i].view+''+'</span>'+'</p>'+'</li>';}if(data[i].top==0&&data[i].nice==1){/* 加精帖 */str2+='<li class="fly-list-li">'+'<a href="${ctx}/getPostUserHome?id='+data[i].user_id+'" class="fly-list-avatar">'+'<img src="/1345566427/180/5730976522/0" alt="">'+'</a>'+'<h2 class="fly-tip">'+'<a href="${ctx}/getPostById?id='+data[i].id+'">'+data[i].title+'</a>'+'<span class="fly-tip-jing">精帖</span>'+'</h2>'+'<p>'+'<span><a href="user/home.html">'+data[i].user_name+'</a></span>'+'<span>'+data[i].starttime+'</span>'+'<span>layui框架综合</span>'+'<span class="fly-list-hint">' +'<i class="iconfont" title="回答"></i> '+data[i].replyNum+''+'<i class="iconfont" title="人气"></i>'+data[i].view+''+'</span>'+'</p>'+'</li>';}if(data[i].top==0&&data[i].nice==0){/* 普通帖 */str3+='<li class="fly-list-li">'+'<a href="${ctx}/getPostUserHome?id='+data[i].user_id+'" class="fly-list-avatar">'+' <img src="/1345566427/180/5730976522/0" alt="">'+'</a>'+'<h2 class="fly-tip">'+'<a href="${ctx}/getPostById?id='+data[i].id+'">'+data[i].title+'</a>'+'</h2>'+'<p>'+'<span><a href="user/home.html">'+data[i].user_name+'</a></span>'+'<span>'+data[i].starttime+'</span>'+'<span>layui框架综合</span>'+'<span class="fly-list-hint"> '+'<i class="iconfont" title="回答"></i> '+data[i].replyNum+''+'<i class="iconfont" title="人气"></i>'+data[i].view+''+'</span>'+'</p>'+'</li>';}}return str+str1+str2+str3;};//调用分页laypage({cont: $('.page_list'),pages: pages, //总页数 groups:3, //连续显示的分页数 // skip:true,//是否显示跳转功能skin:'#009688', //加载内置皮肤,也可以直接赋值16进制颜色值,如:#c00jump: function(obj,first){document.getElementById('result').innerHTML = thisDate(obj.curr); }})}else{var html='<div class="fly-none">并无相关数据</div>';$('#top_list').empty().hide();$('#_list').empty().hide();$('#result').append(html);}},error:function(data){layer.msg('请求异常,请重试'+JSON.stringify(data), {shift: 6,time:3000});}})}); </script>