JPQL supports following datetime expressions:
CURRENT_DATE Returns current date as java.sql.Date .
CURRENT_TIME Returns current time as java.sql.Time .
CURRENT_TIMESTAMP Returns current datetime as java.sql.Timestamp .
Example
Entity
@Entity
public class ScheduledEvent {
@Id
@GeneratedValue
private long id;
private String eventName;
private Date date;
private Time start;
private Time end;
private Timestamp eventCreated;
.............
}
Using datetime functions
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEvent();
findCurrentEvent();
findTodayEvent();
findEventCreatedNow();
} finally {
entityManagerFactory.close();
}
}
private static void persistEvent() {
ScheduledEvent e1 = ScheduledEvent.create("Test event",
LocalDate.of(2018, 6, 1),
LocalTime.of(9, 30, 45),
LocalTime.of(11, 45, 30),
LocalDateTime.now().minusDays(10));
ScheduledEvent e2 = ScheduledEvent.create("Test event2",
LocalDate.now(),
LocalTime.of(9, 30, 45),
LocalTime.of(20, 45, 30),
LocalDateTime.now().minusMonths(1));
ScheduledEvent e3 = ScheduledEvent.create("Test event3",
LocalDate.now(),
LocalTime.of(18, 0, 0),
LocalTime.of(20, 0, 0),
LocalDateTime.now());
EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
em.persist(e1);
em.persist(e2);
em.persist(e3);
em.getTransaction().commit();
System.out.println("-- all persisted entities --");
em.createQuery("SELECT e FROM ScheduledEvent e")
.getResultList()
.forEach(System.out::println);
em.close();
}
private static void findCurrentEvent() {
System.out.println("-- current ongoing event --");
EntityManager em = entityManagerFactory.createEntityManager();
List list = em
.createQuery("SELECT e FROM ScheduledEvent e WHERE CURRENT_DATE = e.date AND "
+ "CURRENT_TIME BETWEEN e.start AND e.end")
.getResultList();
list.forEach(System.out::println);
em.close();
}
private static void findTodayEvent() {
System.out.println("-- today event --");
EntityManager em = entityManagerFactory.createEntityManager();
List list = em
.createQuery("SELECT e FROM ScheduledEvent e WHERE CURRENT_DATE = e.date")
.getResultList();
list.forEach(System.out::println);
em.close();
}
private static void findEventCreatedNow() {
System.out.println("-- event created within 10 sec --");
EntityManager em = entityManagerFactory.createEntityManager();
List list = em
.createQuery("SELECT e FROM ScheduledEvent e WHERE e.eventCreated BETWEEN :timestampStart "
+ "AND CURRENT_TIMESTAMP")
.setParameter("timestampStart", Timestamp.valueOf(LocalDateTime.now().minusSeconds(10)))
.getResultList();
list.forEach(System.out::println);
em.close();
}
} -- all persisted entities -- ScheduledEvent{id=1, eventName='Test event', date=2018-06-01, start=09:30:45, end=11:45:30, eventCreated=2018-06-05 16:12:02.369} ScheduledEvent{id=2, eventName='Test event2', date=2018-06-15, start=09:30:45, end=20:45:30, eventCreated=2018-05-15 16:12:02.371} ScheduledEvent{id=3, eventName='Test event3', date=2018-06-15, start=18:00:00, end=20:00:00, eventCreated=2018-06-15 16:12:02.371} -- current ongoing event -- ScheduledEvent{id=2, eventName='Test event2', date=2018-06-15, start=09:30:45, end=20:45:30, eventCreated=2018-05-15 16:12:02.371} -- today event -- ScheduledEvent{id=2, eventName='Test event2', date=2018-06-15, start=09:30:45, end=20:45:30, eventCreated=2018-05-15 16:12:02.371} ScheduledEvent{id=3, eventName='Test event3', date=2018-06-15, start=18:00:00, end=20:00:00, eventCreated=2018-06-15 16:12:02.371} -- event created within 10 sec -- ScheduledEvent{id=3, eventName='Test event3', date=2018-06-15, start=18:00:00, end=20:00:00, eventCreated=2018-06-15 16:12:02.371}
Example ProjectDependencies and Technologies Used: - h2 1.4.197: H2 Database Engine.
- hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
Implements javax.persistence:javax.persistence-api version 2.1 - JDK 1.8
- Maven 3.3.9
|
|