2

Sorry for posting this same question again but i am curious to know.

I want to know how we can compare a current date from java with stored date in mysql.

date stored in MySQL is 2013-01-04 13:15:00

when i compare this date in java by getting current date 

Date date = new Date();

then wrote a query if the date stored in MySQL is less than current display the result. but query is returning 0 result in the List or an exception I have pasted the exception at the end.

select model from abc model where model.abcStartDate <= :? and model.abcEndDate >= :?

In ":?" date is passed In the MySQL The start date and end date are of timestamp data type.

Below is the EJB entity class

@Entity
@Table(name = "abc_table", uniqueConstraints = {})
public class ABCClass implements java.io.Serializable {

private static final long serialVersionUID = -1924961655993587546L;

// Fields
private Date abcStartDate;
private Date abcEndDate;

// Constructors

/** default constructor */
public ABCClass() {
}


@OrderBy(value="3")
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "abc_start_date", unique = false, nullable = false, insertable = true, updatable = true, length = 19)
public Date getabcStartDate() {
    return this.abcStartDate;
}

public void setabcStartDate(Date abcStartDate) {
    this.abcStartDate = abcStartDate;
}

@OrderBy(value="4")
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "abc_end_date", unique = false, nullable = false, insertable = true, updatable = true, length = 19)
public Date getabcEndDate() {
    return this.abcEndDate;
}

public void setabcEndDate(Date abcEndDate) {
    this.abcEndDate = abcEndDate;
}

}

and it throws an exception

Exception thrown from bean; nested exception is: Exception [EclipseLink-3001] (Eclipse Persistence Services - 2.2.1.v20110722-r9776): org.eclipse.persistence.exceptions.ConversionException
Exception Description: The object [65464533565], of class [class java.lang.String], could not be converted to [class java.lang.Integer].
Internal Exception: java.lang.NumberFormatException: For input string: "65464533565"
vels4j
  • 11,208
  • 5
  • 38
  • 63
JN_newbie
  • 5,492
  • 14
  • 59
  • 97

1 Answers1

0

Date comparison in JPQL can be done by following ways.

If you are comparing with current date & time

select model from abc model where model.abcStartDate >= CURRENT_TIMESTAMP 
and model.abcEndDate >= CURRENT_TIMESTAMP

or create a query and pass parameter

query = em.createQuery("select model from abc model where model.abcStartDate >= 
:time and model.abcEndDate >= :time");
query.setParameter("time", new Date(), TemporalType.TIMESTAMP);
vels4j
  • 11,208
  • 5
  • 38
  • 63
  • what would be in CURRENT_TIMESTAMP? I have tried passing timestamp as well but it throws an exception. java.sql.Timestamp t = new Timestamp(date.getTime()); – JN_newbie Jan 08 '13 at 18:36
  • CURRENT_TIMESTAMP assigns current time default. u can used directly in query without any args. see this http://stackoverflow.com/questions/6597588/on-update-current-timestamp-and-jpa – vels4j Jan 08 '13 at 18:40
  • Thanks vels4j. But what if i want to pass with arguments? Then I have to create a TimeStamp Object and then pass to a query? because when I do this it throws an exception. – JN_newbie Jan 08 '13 at 18:55
  • no, its done already `query.setParameter("time", new Date(), TemporalType.TIMESTAMP);` – vels4j Jan 08 '13 at 18:56
  • @vels4j...I have tried with the solution now. it throws an exception. The object [65464533565], of class [class java.lang.String], could not be converted to [class java.lang.Integer]. – JN_newbie Jan 08 '13 at 19:26
  • sorry, I think you are doing some wrong casting and that is not related to this issue. – vels4j Jan 09 '13 at 05:49
  • @vels4j...Thanks. There might be an issue in the project, so that is why might be causing this. – JN_newbie Jan 09 '13 at 05:52
  • Include the stack trace, the error doesn't seem to have anything to do with the date, seems you have some numeric field you are storing as a varchar. – James Jan 09 '13 at 13:38