query on calendar class

#1

Hello!

 

I'm looking for help to build query on calendar class. I need to get objects where Calendar.DAY_OF_WEEK is FRIDAY or any other.

My class:

@Entity
public class Cdr {
    private static final long serialVersionUID = 1L;
  
    @Id @GeneratedValue
    private long serialID;
    private String A;
    @Temporal(TemporalType.TIMESTAMP) private Calendar start;

I tested:

Query query=em.createQuery("SELECT c FROM Cdr c where c.start.DAY_OF_WEEK = ?1",Cdr.class);

query.setParameter(1,Calendar.FRIDAY);

It throws:

Exception in thread "AWT-EventQueue-0" [ObjectDB 2.5.6_02] javax.persistence.PersistenceException
Navigation from 'java.util.Calendar' through 'DAY_OF_WEEK' is invalid (error 763)
at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:725)
at cdr_analiza.GUI$3.actionPerformed(GUI.java:150)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.Component.processMouseEvent(Component.java:6505)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3320)
at java.awt.Component.processEvent(Component.java:6270)
at java.awt.Container.processEvent(Container.java:2229)
at java.awt.Component.dispatchEventImpl(Component.java:4861)
at java.awt.Container.dispatchEventImpl(Container.java:2287)
at java.awt.Component.dispatchEvent(Component.java:4687)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
at java.awt.Container.dispatchEventImpl(Container.java:2273)
at java.awt.Window.dispatchEventImpl(Window.java:2719)
at java.awt.Component.dispatchEvent(Component.java:4687)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:735)
at java.awt.EventQueue.access$200(EventQueue.java:103)
at java.awt.EventQueue$3.run(EventQueue.java:694)
at java.awt.EventQueue$3.run(EventQueue.java:692)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue$4.run(EventQueue.java:708)
at java.awt.EventQueue$4.run(EventQueue.java:706)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:705)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)
Caused by: com.objectdb.o.UserException: Navigation from 'java.util.Calendar' through 'DAY_OF_WEEK' is invalid
at com.objectdb.o.MSG.d(MSG.java:61)
at com.objectdb.o.QNF.C(QNF.java:925)
at com.objectdb.o.QNF.z(QNF.java:789)
at com.objectdb.o.QNF.k(QNF.java:258)
at com.objectdb.o.QNF.q(QNF.java:518)
at com.objectdb.o.QNF.k(QNF.java:189)
at com.objectdb.o.QNF.j(QNF.java:135)
at com.objectdb.o.QRC.B(QRC.java:340)
at com.objectdb.o.QRC.x(QRC.java:231)
at com.objectdb.o.QRC.w(QRC.java:185)
at com.objectdb.o.QRM.Vb(QRM.java:272)
at com.objectdb.o.MST.Vb(MST.java:961)
at com.objectdb.o.WRA.Vb(WRA.java:311)
at com.objectdb.o.WSM.Vb(WSM.java:115)
at com.objectdb.o.QRR.g(QRR.java:247)
at com.objectdb.o.QRR.f(QRR.java:153)
at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:716)

Could i get some example code of that?

And second part of my question - how to build query on that same class but results should be objects where c.start > {t '23:00:00'}

I tested:

Query query=em.createQuery("SELECT c FROM Cdr c where c.start > {t '23:00:00'}", Cdr.class);

But it throws:

Exception in thread "AWT-EventQueue-0" [ObjectDB 2.5.6_02] SELECT c FROM Cdr c where c.answered  ==> > <==  {t '23:00:00'}
javax.persistence.PersistenceException
Operand type mismatch for operator '>' (error 757)

I understand it that Calendar.class could not been compare with {t '23:00:00'}? The only solution is keep time and date i other fields and do that same wiht DAY_OF_WEEK?

#2

ObjectDB does not currently support extracting a day of week from a date (methods for extracting other date parts are supported by ObjectDB as an extension to JPA), so you should consider duplicating this information into an additional persistent field.

The comparison failed because of comparing a Calendar to a Date. Using Date or Timestamp as the field type instead of Calendar should work.

ObjectDB Support
#3

Thanks for answer.

I changed type of start field to: java.sql.Date, java.sql.Timestamp, java.util.Date with @Temporal(TemporalType.DATE), @Temporal(TemporalType.TIME) and @Temporal(TemporalType.TIMESTAMP). I test the queries:

Query query=em.createQuery("SELECT c FROM Cdr c where c.start > {t '23:00:00'}", Cdr.class);

Query query=em.createQuery("SELECT c FROM Cdr c where c.start > {ts '23:00:00'}", Cdr.class);

Query query=em.createQuery("SELECT c FROM Cdr c where HOUR({ts c.start}) >= 23", Cdr.class);

Query query=em.createQuery("SELECT c FROM Cdr c where HOUR({t c.start}) >= 23", Cdr.class);

Maybe i tried few more, but all of them doesnt work. I couldnt extract part of date/timestamp from c.start field. System still throws

Caused by: com.objectdb.o.UserException: Invalid date literal {t c.start}

or erros caused by bad operand

---After Edit:

I was too accurate when tried rewrite manual functions. I should use HOUR(c.start) instead of HOUR({t c.start}).Correct query look like (it resolve my trouble):

Query query=em.createQuery("select  c from Cdr c where HOUR(c.start)=23", Cdr.class);

 

Options with using {t '23:00:00'} doesnt work for me. It works only with "ts" type

Query query=em.createQuery("select c from Cdr c where c.start between {ts '2044-12-01 00:00:00'} and {ts '2044-12-01 01:00:00'}", Cdr.class);

but it not very usefull for my project

#4

There are several possible causes of failure mentioned in your post:

  1. The {} syntax is for literals, so it cannot contain variables or parameters.
  2. In comparison the operand types must match.
  3. Not all the date/time types support the same functionality.

If you still have issues with working with dates, please post a simple single top class running example (as explained in the posting instructions) that demonstrates all the issues.

ObjectDB Support

Reply