CODEWITHSUNDEEP
mysqlsqlsql-serverhibernatehql
Marcelo Glasberg
Marcelo Glasberg

Reputation: 30909

How can I update an interval of dates in the database (Hibernate HQL)

Consider, for some specific database entry of id id, an interval of dates in the database, from BEGIN_DATE to END_DATE, denoted by (BEGIN_DATE,END_DATE).

Given two new dates, that form the interval (newBeginDate, newEndDate), I must update (BEGIN_DATE,END_DATE) so that the resulting interval contains all 4 dates. In other words, I want to add the two intervals (And since I am adding them, the resulting interval may remain the same or grow, but never shrink).

A few examples:

If (BEGIN_DATE,END_DATE) = (January 10,January 20), then:

The two following queries together achieve this goal. The first one updates BEGIN_DATE, and the second updates END_DATE:

session.createQuery(
   "update APPOINTMENTS " +
     "set BEGIN_DATE = :newBeginDate " +
     "where " +
     "(BEGIN_DATE is null or BEGIN_DATE > :newBeginDate) " +
     "and ID = :id ")
           .setParameter("newBeginDate", newBeginDate)
           .setParameter("id", id)
           .executeUpdate();

session.createQuery(
   "update APPOINTMENTS " +
     "set END_DATE = :newEndDate " +
     "where " +
     "(END_DATE is null or END_DATE < :newEndDate) " +
     "and ID = :id ")
        .setParameter("newEndDate", newEndDate)
        .setParameter("id", id)
        .executeUpdate();

My question is: How can I do it in a single query?

Upvotes: 1

Views: 302

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31812

Use LEAST() and GREATEST() functions. For NULLs use COALESCE().

update APPOINTMENTS
set BEGIN_DATE = coalesce(least(BEGIN_DATE, :newBeginDate), :newBeginDate),
    END_DATE   = coalesce(greatest(END_DATE, :newEndDate), :newEndDate)
where ID = :id

I think LEAST and GREATEST don't need an explanation. COALESCE will pick the new date, if the old one is NULL. So no condition except of ID = :id is needed in the WHERE clause.

Upvotes: 1

Dragan Bozanovic
Dragan Bozanovic

Reputation: 23562

SQL is limited when it comes to more complex programming constructs, so since you use Hibernate I would just read the appointment entity instance and update its fields. That would require two queries, one to read and one to update, but still it would be only one update statement and is the most readable solution.

If you still want only one update statement and nothing else, your two statements could be merged into one by using case: 

update APPOINTMENTS 
set BEGIN_DATE = case when (BEGIN_DATE is null or BEGIN_DATE > :newBeginDate) then :newBeginDate else BEGIN_DATE end,
    END_DATE = case when (END_DATE is null or END_DATE < :newEndDate) then :newEndDate else END_DATE end
where (BEGIN_DATE is null or BEGIN_DATE > :newBeginDate or END_DATE is null or END_DATE < :newEndDate)
    and ID = :id

Upvotes: 1

Related Questions