Search code examples
javadateormlite

ORMLite how to query using two date columns where one is null or less than another

I am using ORMLite and H2, i have a data model called Student, it has "updatedAt" column/property which is a Date data type(java.util.Date) so it keeps date time information too. Then another column/property that is Date type called "lastSyncedAt", i want to get all students where:

  1. lastSyncedAt is null, i.e has not be synced, OR
  2. lastSyncedAt is less than "updatedAt".

Can someone help me with how to do this on ORMLite. LARAVEL ORM has something like where(columnA, comparison operator, columnB) is there something similar or that can do this in ORMLite.

Solution

  • where(columnA, comparison operator, columnB) is there something similar or that can do this in ORMLite.

    Have you RTFM? There's a lot there about the ORMLite query builder. You could do something like:

    qb = studentDao.queryBuilder();
where = qb.where();
where.or(
    where.isNull("lastSyncedAt"),
    where.lt("lastSyncedAt", new ColumnArg("updatedAt")));
results = queryBuilder.list();

    Couple things to thing about:

    • How are you storing your date in H2? Is that field able to be compared with less-than?
    • How does H2 handle comparing null values in less-than.
    • What if updatedAt is null.

    The first question is critical. H2 has a timestamp type and for the comparison to work, the date needs to be encoded with it in a specific format. Depends on how you created your schema. You could also use ORMlite's DATE_LONG type which stores the date as a long-integer which can be compared of course.

    Hope this helps.