查询某一时间段结果

来源：互联网发布：乐视tv for mac 编辑：程序博客网时间：2024/05/29 13:10

查询逻辑跟普通的查询差不多，只是其中用到的几个方法需要注意

例如

/**
*
* 此方法描述的是：查询即将到期的抵押合同
*
* @Title: getListNearMortg
* @author: jiafei
* @param response
* @param bi
* @return void 返回类型
* @version: Nov 22, 2016 7:17:13 PM
* @throws Exception
*/
@RequestMapping(params = "action=getListNearMortg")
public void getListNearMortg(HttpServletResponse response) throws Exception {

Calendar c = Calendar.getInstance();
/**
* 一个月后
*/
c.add(Calendar.MONTH, 1);
List<MortgageInfo> list = mortgageInfoService.getListNearMortg(c
.getTime());
super.forwardAjax(response, JSONArray.fromObject(list).toString(),
WebUtils.CONTENTTYPE_TEXTJSON, WebUtils.CONTENT_CHARSET_UTF8);
}

时间可以从前台传或者自己new一个，对应的sql语句如下：

<select id="com.app.mortgage.domain.MortgageInfo.getListNearMortg"
parameterClass="java.util.Date" resultClass="MortgageInfo">
<![CDATA[
SELECT t1.* FROM (SELECT am.*,abi.name_ assetsname FROM TBL_ASSETS_MORTG am
left join tbl_assets_base_info abi on am.assetsinfoid_ = abi.id_
WHERE add_months(am.starttime_,am.timelength_*12) < #value#
and add_months(am.starttime_,am.timelength_*12) > sysdate
AND am.MORTGSTATE_ =0
AND am.ISVALID_ =0
ORDER BY endTime_ DESC )t1
WHERE rownum < 9
]]>
<isNotEmpty>and 1=1</isNotEmpty>
</select>

其中用到了add_months方法，oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间

0 0