java語言利用MySQL資料庫內建的DATE_SUB()函數查詢一周、一個月、半年、一年之內的資料記錄

標籤：mysql   date_sub   sql語句   查詢一周資料記錄   jdbctemplate   

在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><tdclass="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;}}

java語言利用MySQL資料庫內建的DATE_SUB()函數查詢一周、一個月、半年、一年之內的資料記錄