java语言利用MySQL数据库自带的DATE_SUB()函数查询一周、一个月、半年、一年之内的数据记录
在JSP页面中根据论坛帖子的发帖时间查询帖子记录,前台JSP页面代码如下:
<s:form id="postTime" action="findPostByTime" method="post" theme="simple" target="mFrame"> <table class="tab_data" width="70%" > <tr> <td class="left" width="30%"> 请选择时间: </td> <td width="70"> <s:select name="postTime" list="#{'最近一周':'最近一周','最近半个月':'最近半个月','最近一个月':'最近一个月','最近半年':'最近半年','最近一年':'最近一年'}" listKey="value" listValue="key" theme="simple" headerKey="-1" headerValue="—发帖时间—"> </s:select> </td> </tr> <tr> <td class="right" colspan="2" > <span style="white-space:pre"> </span><div align="center"> <s:submit value="查询" theme="simple" cssClass="btn_normal" /> </div> </td> </tr> </table> </s:form>
后台的java代码如下:
private List<McpForumPost> forumPostList; private String postTime; //类的私有成语变量及其get()和set()方法 public String getPostTime() { return postTime; } public void setPostTime(String postTime) { this.postTime = postTime; } public List<McpForumPost> getForumPostList() { return forumPostList; } public void setForumPostList(List<McpForumPost> forumPostList) { this.forumPostList = forumPostList; } /** * * 前台jsp页面传入一个String类型的postTime,根据发帖时间查询帖子 * **/ public String findPostByTime() { System.out.println(postTime); private String columnSql = "select a.post_id,a.user_id,a.board_id,a.post_title,a.post_time,a.check_state,a.is_top,a.is_good,a.post_state,b.account from mcp_forum_post as a,scpn_user as b "; String sql_fy; try { //其中post_time在MySQL数据库中以datetime类型保存 if(postTime.equals("最近一周")){ sql_fy=columnSql+"where a.user_id=b.user_id and a.post_state=1 and a.post_time>DATE_SUB(CURDATE(), INTERVAL 7 DAY)" ; }else if(postTime.equals("最近半个月")){ sql_fy=columnSql+"where a.user_id=b.user_id and a.post_state=1 and a.post_time>DATE_SUB(CURDATE(), INTERVAL 15 DAY)"; }else if(postTime.equals("最近一个月")){ sql_fy=columnSql+"where a.user_id=b.user_id and a.post_state=1 and a.post_time>DATE_SUB(CURDATE(), INTERVAL 1 MONTH)"; }else if(postTime.equals("最近半年")){ sql_fy=columnSql+"where a.user_id=b.user_id and a.post_state=1 and a.post_time>DATE_SUB(CURDATE(), INTERVAL 6 MONTH)"; }else if(postTime.equals("最近一年")){ sql_fy=columnSql+"where a.user_id=b.user_id and a.post_state=1 and a.post_time>DATE_SUB(CURDATE(), INTERVAL 1 YEAR)"; }else { sql_fy=columnSql+"where a.user_id=b.user_id and a.post_state=1 "; } System.out.println(sql_fy); forumPostList = jdbcTemplate.queryForList(sql_fy); //将日志集合保存到List中 if(forumPostList.size()==0||flag==false) { return ERROR; } return SUCCESS; } catch (Exception e) { e.printStackTrace(); return ERROR; } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。