CODEWITHSUNDEEP
jpa
Marta
Marta

Reputation: 1

Comparing dates in jpa

I am trying to find results in a postgresql database between two dates (field described in ddbb as timestamp).

I have these three records which must meet target: 2016-03-04 00:00:00 2016-03-04 14:00:00 2016-03-04 10:56:00

final Calendar fechaMinima = Calendar.getInstance();

and set the parameters I want.

the jpa column is defined:

@Column(name = "tim_ofrecim")
@NotNull
@Temporal(TemporalType.TIMESTAMP)
@DateTimeFormat(style = "M-")
private Date Tewslofr.timOfrecim;

With JPA I try to find them with the following code:

@NamedQuery(name = "ofr_query2", query = "SELECT COUNT (*) FROM Tewslofr ofr "
                + "WHERE ofr.id.codIdprodto =:codIdprodto "
                + "AND ofr.codUser =:codUser "
                + "AND ofr.timOfrecim BETWEEN :timMinimo AND :timMaximo")

public static Long getMethod(final String codProducto,
            final String codUser, final Calendar fechaMinima,
            final Calendar fechaMaxima) {

    return entityManager().createNamedQuery("ofr_query2", Long.class)
            .setParameter("codIdprodto", codProducto)
            .setParameter("codUser", codUser)
            .setParameter("timMinimo", fechaMinima, TemporalType.TIMESTAMP)
            .setParameter("timMaximo", fechaMaxima, TemporalType.TIMESTAMP)
            .getSingleResult();
}

and obtain this exception:

Caused by: java.lang.IllegalArgumentException: Parameter value [java.util.GregorianCalendar[time=?,areFieldsSet=false,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Europe/Paris",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=184,lastRule=java.util.SimpleTimeZone[id=Europe/Paris,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]],firstDayOfWeek=2,minimalDaysInFirstWeek=4,ERA=1,YEAR=2016,MONTH=2,WEEK_OF_YEAR=10,WEEK_OF_MONTH=2,DAY_OF_MONTH=1,DAY_OF_YEAR=67,DAY_OF_WEEK=2,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=8,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=434,ZONE_OFFSET=3600000,DST_OFFSET=0]] was not matching type [java.util.Date]

The stack trace to this is:

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [java.util.GregorianCalendar[time=?,areFieldsSet=false,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Europe/Paris",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=184,lastRule=java.util.SimpleTimeZone[id=Europe/Paris,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]],firstDayOfWeek=2,minimalDaysInFirstWeek=4,ERA=1,YEAR=2016,MONTH=2,WEEK_OF_YEAR=10,WEEK_OF_MONTH=2,DAY_OF_MONTH=1,DAY_OF_YEAR=67,DAY_OF_WEEK=2,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=9,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=170,ZONE_OFFSET=3600000,DST_OFFSET=0]] was not matching type [java.util.Date]; nested exception is java.lang.IllegalArgumentException: Parameter value [java.util.GregorianCalendar[time=?,areFieldsSet=false,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Europe/Paris",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=184,lastRule=java.util.SimpleTimeZone[id=Europe/Paris,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]],firstDayOfWeek=2,minimalDaysInFirstWeek=4,ERA=1,YEAR=2016,MONTH=2,WEEK_OF_YEAR=10,WEEK_OF_MONTH=2,DAY_OF_MONTH=1,DAY_OF_YEAR=67,DAY_OF_WEEK=2,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=9,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=170,ZONE_OFFSET=3600000,DST_OFFSET=0]] was not matching type [java.util.Date] at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:301) at org.springframework.orm.jpa.aspectj.JpaExceptionTranslatorAspect.ajc$afterThrowing$org_springframework_orm_jpa_aspectj_JpaExceptionTranslatorAspect$1$18a1ac9(JpaExceptionTranslatorAspect.aj:15) at com.bbva.arq.front.spring.ewsl.ofrecimientos.api.v1.domain.Tewslofr.getCuentaOfrecimientosAgente_aroundBody2(Tewslofr.java:49) at com.bbva.arq.front.spring.ewsl.ofrecimientos.api.v1.domain.Tewslofr.getCuentaOfrecimientosAgente(Tewslofr.java:1) at com.bbva.arq.front.spring.ewsl.ofrecimientos.api.v1.daos.ofrecimientos.impl.OfrecimientosDAOImpl.getMethod(OfrecimientosDAOImpl.java:52)... Caused by: java.lang.IllegalArgumentException: Parameter value [java.util.GregorianCalendar[time=?,areFieldsSet=false,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Europe/Paris",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=184,lastRule=java.util.SimpleTimeZone[id=Europe/Paris,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]],firstDayOfWeek=2,minimalDaysInFirstWeek=4,ERA=1,YEAR=2016,MONTH=2,WEEK_OF_YEAR=10,WEEK_OF_MONTH=2,DAY_OF_MONTH=1,DAY_OF_YEAR=67,DAY_OF_WEEK=2,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=9,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=170,ZONE_OFFSET=3600000,DST_OFFSET=0]] was not matching type [java.util.Date] at org.hibernate.ejb.AbstractQueryImpl.registerParameterBinding(AbstractQueryImpl.java:360) at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:414)

If I change to DATE instead of TIMESTAMP:

public static Long getMethod(final String codProducto,
            final String codUser, final Calendar fechaMinima,
            final Calendar fechaMaxima) {

    return entityManager().createNamedQuery("ofr_query2", Long.class)
            .setParameter("codIdprodto", codProducto)
            .setParameter("codUser", codUser)
            .setParameter("timMinimo", fechaMinima.getTime(), TemporalType.DATE)
            .setParameter("timMaximo", fechaMaxima.getTime(), TemporalType.DATE)
            .getSingleResult();
}

the code returns 0.

Any help will be appreciated.

kindest regards

Upvotes: 0

Views: 356

Answers (1)

Marta
Marta

Reputation: 1

I still don't know why the first option does not work, but I have made it work with the option of Date. This option was correct but was returning 0 because of de codIdprodto, not because of the dates.

Upvotes: 0

Related Questions