JPA2 CriteriaBuilder: Using LOB property for greaterThan comparison

Question

My application is using SQLServer and JPA2 in the backend. App makes use of a timestamp column (in the SQLServer sense, which is equivalent to row version see here) per entity to keep track of freshly modified entities. NB SQLServer stores this column as binary(8).

Each entity has a respective timestamp property, mapped as @Lob, which is the way to go for binary columns:

@Lob
@Column(columnDefinition="timestamp", insertable=false, updatable=false)
public byte[] getTimestamp() {
...

The server sends incremental updates to mobile clients along with the latest database timestamp. The mobile client will then pass the old timestamp back to the server on the next refresh request so that the server knows to return only fresh data. Here's what a typical query (in JPQL) looks like:

select v from Visit v where v.timestamp > :oldTimestamp

Please note that I'm using a byte array as a query parameter and it works fine when implemented in JPQL this way.

My problems begin when trying to do the same using the Criteria API:

private void getFreshVisits(byte[] oldVersion) {
  EntityManager em = getEntityManager();
  CriteriaQuery<Visit> cq = cb.createQuery(Visit.class);
  Root<Visit> root = cq.from(Visit.class);
  Predicate tsPred = cb.gt(root.get("timestamp").as(byte[].class), oldVersion); // compiler error
  cq.where(tsPred);
  ...
}

The above will result in compiler error as it requires that the gt method used strictly with Number. One could instead use the greaterThan method which simply requires the params to be Comparable and that would result in yet another compiler error.

So to sum it up, my question is: how can I use the criteria api to add a greaterThan predicate for a byte[] property? Any help will be greatly appreciated.

PS. As to why I'm not using a regular DateTime last_modified column: because of concurrency and the way synchronization is implemented, this approach could result in lost updates. Microsoft's Sync Framework documentation recommends the former approach as well.

Answer 1

I know this was asked a couple of years back but just in case anyone else stumbles upon this.. In order to use a SQLServer rowver column within JPA you need to do a couple of things..

Create a type that will wrap the rowver/timestamp:

import com.fasterxml.jackson.annotation.JsonIgnore;

import javax.xml.bind.annotation.XmlTransient;
import java.io.Serializable;
import java.math.BigInteger;
import java.util.Arrays;

/**
 * A RowVersion object
 */
public class RowVersion implements Serializable, Comparable<RowVersion> {

    @XmlTransient
    @JsonIgnore
    private byte[] rowver;

    public RowVersion() {
    }

    public RowVersion(byte[] internal) {
        this.rowver = internal;
    }

    @XmlTransient
    @JsonIgnore
    public byte[] getRowver() {
        return rowver;
    }

    public void setRowver(byte[] rowver) {
        this.rowver = rowver;
    }

    @Override
    public int compareTo(RowVersion o) {
        return new BigInteger(1, rowver).compareTo(new BigInteger(1, o.getRowver()));
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        RowVersion that = (RowVersion) o;
        return Arrays.equals(rowver, that.rowver);
    }

    @Override
    public int hashCode() {
        return Arrays.hashCode(rowver);
    }
}

The key here is that it implement Comparable if you want to use it in calculations (which you definitely do)..

Next create a AttributeConverter that will move from a byte[] to the class you just made:

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

/**
 * JPA converter for the RowVersion type
 */
@Converter
public class RowVersionTypeConverter implements AttributeConverter<RowVersion, byte[]> {

    @Override
    public byte[] convertToDatabaseColumn(RowVersion attribute) {
        return attribute != null ? attribute.getRowver() : null;
    }

    @Override
    public RowVersion convertToEntityAttribute(byte[] dbData) {
        return new RowVersion(dbData);
    }
}

Now let's apply this RowVersion attribute/type to a real world scenario. Let's say you wanted to find all Programs that have changed on or before some point in time.

One straightforward way to solve this would be to use a DateTime field in the object and timestamp column within db. Then you would use 'where lastUpdatedDate <= :date'.

Suppose that you don't have that timestamp column or there's no guarantee that it will be updated properly when changes are made; or let's say your shop loves SQLServer and wants to use rowver instead.

What to do? There are two issues to solve.. one how to generate a rowver and two is how to use the generated rowver to find Programs.

Since the database generates the rowver, you can either ask the db for the 'current max rowver' (a custom sql server thing) or you can simply save an object that has a RowVersion attribute and then use that object's generated RowVersion as the boundary for the query to find the Programs changed after that time. The latter solution is more portable is what the solution is below.

The SyncPoint class snippet below is the object that is used as a 'point in time' kind of deal. So once a SyncPoint is saved, the RowVersion attached to it is the db version at the time it was saved.

Here is the SyncPoint snippet. Notice the annotation to specify the custom converter (don't forget to make the column insertable = false, updateable = false):

/**
 * A sample super class that uses RowVersion
 */
@MappedSuperclass
public abstract class SyncPoint {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // type is rowver for SQLServer, blob(8) for postgresql and h2    
    @Column(name = "current_database_version", insertable = false, updatable = false)
    @Convert(converter = RowVersionTypeConverter.class)
    private RowVersion currentDatabaseVersion;

    @Column(name = "created_date_utc", columnDefinition = "timestamp", nullable = false)
    private DateTime createdDate;
    ...

Also (for this example) here is the Program object we want to find:

@Entity
@Table(name = "program_table")
public class Program {

    @Id
    private Integer id;

    private boolean active;

    // type is rowver for SQLServer, blob(8) for postgresql and h2
    @Column(name = "rowver", insertable = false, updatable = false)
    @Convert(converter = RowVersionTypeConverter.class)
    private RowVersion currentDatabaseVersion;

    @Column(name = "last_chng_dt")
    private DateTime lastUpdatedDate;
    ...

Now you can use these fields within your JPA criteria queries just like anything else.. here is a snippet that we used inside a spring-data Specifications class:

/**
 * Find Programs changed after a synchronization point
 *
 * @param filter that has the changedAfter sync point
 * @return a specification or null
 */
public Specification<Program> changedBeforeOrEqualTo(final ProgramSearchFilter filter) {
    return new Specification<Program>() {
        @Override
        public Predicate toPredicate(Root<Program> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            if (filter != null && filter.changedAfter() != null) {
                // load the SyncPoint from the db to get the rowver column populated
                SyncPoint fromDb = synchronizationPersistence.reload(filter.changedBeforeOrEqualTo());
                if (fromDb != null) {
                    // real sync point made by database
                    if (fromDb.getCurrentDatabaseVersion() != null) {
                        // use binary version
                        return cb.lessThanOrEqualTo(root.get(Program_.currentDatabaseVersion),
                                fromDb.getCurrentDatabaseVersion());
                    } else if (fromDb.getCreatedDate() != null) {
                        // use timestamp instead of binary version cause db doesn't make one
                        return cb.lessThanOrEqualTo(root.get(Program_.lastUpdatedDate),
                                fromDb.getCreatedDate());
                    }
                }
            }
            return null;
        }
    };
}

The specification above works with both the binary current database version or a timestamp.. this way I could test my stuff and all the upstream code on a database other than SQLServer.

That's it really: a) type to wrap the byte[] b) JPA converter c) use attribute in query.