Hi All.

I have an object called Checkpoint which is mapped to a DB table using annotations which seems to work ok.

In another table (Patrols) there is some data that I want to get into my Checkpoint objects. I need to get a simple count of how many times a particular checkpoint's ID appears in that table when another column in the Patrols table has a specific value.

The SQL would be something like this:
select count(OID) from PATROLS where checkpoint = 904 and result = 1;

The patrol table is a record of when certain checkpoints have been visited.

The field in Checkpoint would be transient, I have no need to write the data back to the patrol table when storing the checkpoint object.

So I need help working out what the annotation should be, I guess it sounds a bit like a onetomany checkpoint->patrol relationship, but the patrol table doesn't really represent an object as such as far as I can figure (despite the naming). I don't really want to create a patrol object to model that table, but would rather see if it is possible (and then if it is a good idea) to have an annotation something like this in my CHeckpoint class:

@Query(select count(OID) from PATROLS where this.OID = checkpoint and result = 1)
private Integer checkpointHitCount;

I'm hoping that this makes sense to someone.

This isn't a DB I have designed, so I'm still working through how the relationships are supposed to work in this one!

I think the @formula annotation may hold the key... possibly something like this but not nailed it yet

@Transient
@Formula("(select count(OID) from PATROLS where checkpoint = Checkpoint.OID and result = 1")
private Integer hitCount = 0;
public Integer getHitCount() {return hitCount; }
public void setHitCount(Integer in) {this.hitCount = hitCount; }
: