用hql语句获取当前系统的前7天河后7天的记录

[白衣ol]

如题，急，麻烦写个例子，谢谢！

[BUG胡汉三]

如果你的表里面有创建时间这个字段，就可以使用between and来对创建时间做条件查询，在程序里面得到当前系统的前7天跟后7天。

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class Test {
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	public static void main(String[] args) throws ParseException {
		System.out.println("当前时间的前7天："+getStatetime(-7));
		System.out.println("当前时间的后7天："+getStatetime(7));
	}
	/**
	 * 获取系统时间的前n(正数往前，负数往后)天
	 * @param date
	 * @return
	 * @throws ParseException
	 */
	public static String getStatetime(int date) throws ParseException{
		Calendar c = Calendar.getInstance();  
		c.add(Calendar.DATE, date);  
		Date monday = c.getTime();
		String preMonday = sdf.format(monday);
		return preMonday;
	}
}

如你的时间字段属性为：dateTime

String hql = "from Tables t where t.dateTime between '"+getStatetime(-7)+"'  and '"+getStatetime(7)+"' ";

[白衣ol]

引用 1 楼 hzw2312 的回复:

如果你的表里面有创建时间这个字段，就可以使用between and来对创建时间做条件查询，在程序里面得到当前系统的前7天跟后7天。

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class Test {
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	public static void main(String[] args) throws ParseException {
		System.out.println("当前时间的前7天："+getStatetime(-7));
		System.out.println("当前时间的后7天："+getStatetime(7));
	}
	/**
	 * 获取系统时间的前n(正数往前，负数往后)天
	 * @param date
	 * @return
	 * @throws ParseException
	 */
	public static String getStatetime(int date) throws ParseException{
		Calendar c = Calendar.getInstance();  
		c.add(Calendar.DATE, date);  
		Date monday = c.getTime();
		String preMonday = sdf.format(monday);
		return preMonday;
	}
}

如你的时间字段属性为：dateTime

String hql = "from Tables t where t.dateTime between '"+getStatetime(-7)+"'  and '"+getStatetime(7)+"' ";

类型是timestamp,又是怎么样的呢？

[BUG胡汉三]

引用 2 楼 qq_33441103 的回复:

类型是timestamp,又是怎么样的呢？

应该都能自动转换的~你先试试看！

[白衣ol]

引用 3 楼 hzw2312 的回复:

[quote=引用 2 楼 qq_33441103 的回复:] 类型是timestamp,又是怎么样的呢？

应该都能自动转换的~你先试试看！[/quote]

public List<FlightSchedule> getFlightScheduleByTerminal(String code,String startDate,String endDate) {
		return (List<FlightSchedule>) this.queryHQL(
				"from FlightSchedule where destTerminal.code=:code or depTerminal.code=:code and flightDate between "+startDate+" and "+endDate+"", new String[] { "code" },
				code);
	}

public String getFlightScheduleByTerminal() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Calendar cl1 = Calendar.getInstance();
		cl1.add(Calendar.DAY_OF_MONTH, 7);
		Date endDate = cl1.getTime();
		Calendar cl2 = Calendar.getInstance();
		cl2.add(Calendar.DAY_OF_MONTH, -7);
		Date startDate = cl2.getTime();
		String newStartDate = sdf.format(startDate);
		String newEndDate = sdf.format(endDate);
		reminder.setSignDateBegin(newStartDate);
		reminder.setSignDateEnd(newEndDate);
		List<FlightSchedule> flightSchedules = reminderFacadeManager.getFlightScheduleByTerminal(reminder);
		getAjaxResult().setSuccess(true);
		getAjaxResult().setResult(flightSchedules);
		return "status";
	}

public List<FlightSchedule> getFlightScheduleByTerminal(Reminder reminder) {
		return reminderDao.getFlightScheduleByTerminal(reminder.getTerminal().getCode(),reminder.getSignDateBegin(),reminder.getSignDateEnd());
	}

我上面这些代码有问题吗？为什么报错

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 8 near line 1, column 139 [from com.itran.terminal.business.pojo.FlightSchedule where destTerminal.code=:code or depTerminal.code=:code and flightDate between 2016-08-24 02:32:28 and 2016-09-07 02:32:28]
	at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
	at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
	at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
	at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:124)
	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1770)
	at sun.reflect.GeneratedMethodAccessor103.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.orm.hibernate3.HibernateTemplate$CloseSuppressingInvocationHandler.invoke(HibernateTemplate.java:1282)
	at com.sun.proxy.$Proxy50.createQuery(Unknown Source)
	at com.itran.terminal.dao.BaseHibernateDao$8.doInHibernate(BaseHibernateDao.java:397)
	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
	... 121 more

[BUG胡汉三]

"from FlightSchedule where destTerminal.code=:code or depTerminal.code=:code and flightDate between ‘"+startDate+"’ and ‘"+endDate+"’"

sql拼接加上单引号' 或者你直接用占位符的方式，像你的code那样

[白衣ol]

引用 5 楼 hzw2312 的回复:

"from FlightSchedule where destTerminal.code=:code or depTerminal.code=:code and flightDate between ‘"+startDate+"’ and ‘"+endDate+"’"

sql拼接加上单引号' 或者你直接用占位符的方式，像你的code那样

我用这样的语句，没有报错，但是好像时间的条件没有用

public List<FlightSchedule> getFlightScheduleByTerminal(String code,Date startDate,Date endDate) {
		return (List<FlightSchedule>) this.queryHQL(
				"from FlightSchedule where destTerminal.code=:code or depTerminal.code=:code and flightDate >=:startDate and flightDate <=:endDate", new String[] { "code","startDate","endDate" },
				code,startDate,endDate);
	}

我数据库的时间类型是timestrap