JPA query joining tables

Question

I have two tables: Segment and Observation. A segment can have multiple observations, a 1 to N relationship (segment_id is a foreign key at the Observation table). I have to verify if for a given timestamp and a given segment there are observations or not.

The Observation class is as follows:

@Entity
@Table(name="observation")
@NamedQuery(name="Observation.findAll", query="SELECT o FROM Observation o")
public class Observation implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(unique=true, nullable=false)
private int id;

@Column(nullable=false)
private byte observation;

@Column(name="observed_at", nullable=false)
private Timestamp observedAt;

//bi-directional many-to-one association to Segment
@ManyToOne
@JoinColumn(name="segment_id", nullable=false)
private Segment segment;

public Observation() {
}

public int getId() {
    return this.id;
}

public void setId(int id) {
    this.id = id;
}

public byte getObservation() {
    return this.observation;
}

public void setObservation(byte observation) {
    this.observation = observation;
}

public Timestamp getObservedAt() {
    return this.observedAt;
}

public void setObservedAt(Timestamp observedAt) {
    this.observedAt = observedAt;
}

public Segment getSegment() {
    return this.segment;
}

public void setSegment(Segment segment) {
    this.segment = segment;
}
}

And the Segmenttable is as follows:

@Entity
@Table(name="segment")
@NamedQuery(name="Segment.findAll", query="SELECT s FROM Segment s")
public class Segment implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.TABLE)
@Column(name="segment_id", unique=true, nullable=false)
private int segmentId;

private int dummy;

//bi-directional many-to-one association to Observation
@OneToMany(mappedBy="segment")
private List<Observation> observations;

public Segment() {
}

public int getSegmentId() {
    return this.segmentId;
}

public void setSegmentId(int segmentId) {
    this.segmentId = segmentId;
}

public int getDummy() {
    return this.dummy;
}

public void setDummy(int dummy) {
    this.dummy = dummy;
}

public List<Observation> getObservations() {
    return this.observations;
}

public void setObservations(List<Observation> observations) {
    this.observations = observations;
}

public Observation addObservation(Observation observation) {
    getObservations().add(observation);
    observation.setSegment(this);

    return observation;
}

public Observation removeObservation(Observation observation) {
    getObservations().remove(observation);
    observation.setSegment(null);

    return observation;
}
}

Normally, in standard SQL I would write:

 SELECT COUNT(*)
 FROM Observation o, Segment s
 WHERE o.segment_id = s.segment_id
 AND  s.segment_id = segmentIdParam
 AND o.observed_at = observedAtParam
 AND o.observation = observationParam

I want to write this query using a JPA join. I wrote the following:

Query query = initEntityManager().createQuery(
                  "SELECT s "
                + "FROM Observation o JOIN o.Segment s "
                + "WHERE s.segmentId   = :segmentIdParam"
                +  " AND o.observedAt  = :observedAtParam"
                +  " AND o.observation = :observationParam", Observation.class);

    query.setParameter("segmentIdParam",   segmentId);
    query.setParameter("observedAtParam",  observedAt);
    query.setParameter("observationParam", observation);

Unfortunately I am getting an exception as follows:

java.lang.IllegalArgumentException: An exception occurred while creating a query in     EntityManager: 
 Exception Description: Problem compiling [SELECT s FROM Observation o JOIN o.Segment s WHERE s.segmentId = :segmentIdParam AND o.observedAt = :observedAtParam AND o.observation = :observationParam ]. 
[33, 42] The collection-valued path 'o.Segment' cannot be resolved to a valid association field.
[51, 62] The state field path 's.segmentId' cannot be resolved to a valid type.

How should be written this JPA query?

Answer 1

In some (maybe all) JPA implementations fields are case sensitive.

So your FROM clause should be

FROM Observation o JOIN o.segment s

i.e. o.segment instead of o.Segment

Answer 2

Try

SELECT o FROM Observarion o 
    WHERE o.segment =: segmentParam
      AND o.observedAt =: observedAt
      AND o.observation =: observationParam

In this case you should send an object of type Segment as parameter, not its ID.