Hi all,
I am currently stuck on this JPA issue.
I have an @Entity Trace.
I need to be able to execute a query like this
select * from trace t join
(select trackerid, max(dt) as maxdt
from trace
group by trackerid) innertbl
on t.trackerid = innertbl.trackerid
and t.dt = innertbl.maxdt;
(I am doing a self join to get some max values from the table...)
How can I achieve that using EntityManager? If i do with EntityManager.createNativeQuery then I am not able to get the objects back from the DB.
I tried annonating the class with a "self-join" but was not succefull.
Thanks for any help\suggestion
The class look like this:
@Entity
@Table(name = "trace")
@NamedQueries({
@NamedQuery(name = "Trace.findAll", query = "SELECT t FROM Trace t"),
@NamedQuery(name = "Trace.findByIdtrace", query = "SELECT t FROM Trace t WHERE t.idtrace = :idtrace"),
@NamedQuery(name = "Trace.findByLat", query = "SELECT t FROM Trace t WHERE t.lat = :lat"),
@NamedQuery(name = "Trace.findByLon", query = "SELECT t FROM Trace t WHERE t.lon = :lon"),
@NamedQuery(name = "Trace.findByMessageLength", query = "SELECT t FROM Trace t WHERE t.messageLength = :messageLength"),
@NamedQuery(name = "Trace.findByAlarmType", query = "SELECT t FROM Trace t WHERE t.alarmType = :alarmType"),
@NamedQuery(name = "Trace.findBySpeed", query = "SELECT t FROM Trace t WHERE t.speed = :speed"),
@NamedQuery(name = "Trace.findByDirection", query = "SELECT t FROM Trace t WHERE t.direction = :direction"),
@NamedQuery(name = "Trace.findByFixSatellite", query = "SELECT t FROM Trace t WHERE t.fixSatellite = :fixSatellite"),
@NamedQuery(name = "Trace.findByDt", query = "SELECT t FROM Trace t WHERE t.dt = :dt"),
@NamedQuery(name = "Trace.findByChargeState", query = "SELECT t FROM Trace t WHERE t.chargeState = :chargeState"),
@NamedQuery(name = "Trace.findByBatteryVoltage", query = "SELECT t FROM Trace t WHERE t.batteryVoltage = :batteryVoltage"),
@NamedQuery(name = "Trace.findByGsmCell", query = "SELECT t FROM Trace t WHERE t.gsmCell = :gsmCell"),
@NamedQuery(name = "Trace.findByMessageSN", query = "SELECT t FROM Trace t WHERE t.messageSN = :messageSN")})
public class Trace implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "idtrace")
private Long idtrace;
@Basic(optional = false)
@Column(name = "lat")
private BigDecimal lat;
@Basic(optional = false)
@Column(name = "lon")
private BigDecimal lon;
@Column(name = "message_length")
private Short messageLength;
@Column(name = "alarm_type")
private String alarmType;
@Column(name = "speed")
private BigDecimal speed;
@Column(name = "direction")
private BigDecimal direction;
@Column(name = "fix_satellite")
private String fixSatellite;
@Column(name = "dt")
@Temporal(TemporalType.TIMESTAMP)
private Date dt;
@Column(name = "charge_state")
private Boolean chargeState;
@Column(name = "battery_voltage")
private BigDecimal batteryVoltage;
@Column(name = "gsm_cell")
private String gsmCell;
@Column(name = "message_SN")
private String messageSN;
@JoinColumn(name = "trackerID", referencedColumnName = "trackerID")
@ManyToOne
private Tracker trackerID;
public Trace() {
}
public Trace(Long idtrace) {
this.idtrace = idtrace;
}
public Trace(Long idtrace, BigDecimal lat, BigDecimal lon) {
this.idtrace = idtrace;
this.lat = lat;
this.lon = lon;
}
public Long getIdtrace() {
return idtrace;
}
public void setIdtrace(Long idtrace) {
this.idtrace = idtrace;
}
public BigDecimal getLat() {
return lat;
}
public void setLat(BigDecimal lat) {
this.lat = lat;
}
public BigDecimal getLon() {
return lon;
}
public void setLon(BigDecimal lon) {
this.lon = lon;
}
public Short getMessageLength() {
return messageLength;
}
public void setMessageLength(Short messageLength) {
this.messageLength = messageLength;
}
public String getAlarmType() {
return alarmType;
}
public void setAlarmType(String alarmType) {
this.alarmType = alarmType;
}
public BigDecimal getSpeed() {
return speed;
}
public void setSpeed(BigDecimal speed) {
this.speed = speed;
}
public BigDecimal getDirection() {
return direction;
}
public void setDirection(BigDecimal direction) {
this.direction = direction;
}
public String getFixSatellite() {
return fixSatellite;
}
public void setFixSatellite(String fixSatellite) {
this.fixSatellite = fixSatellite;
}
public Date getDt() {
return dt;
}
public void setDt(Date dt) {
this.dt = dt;
}
public Boolean getChargeState() {
return chargeState;
}
public void setChargeState(Boolean chargeState) {
this.chargeState = chargeState;
}
public BigDecimal getBatteryVoltage() {
return batteryVoltage;
}
public void setBatteryVoltage(BigDecimal batteryVoltage) {
this.batteryVoltage = batteryVoltage;
}
public String getGsmCell() {
return gsmCell;
}
public void setGsmCell(String gsmCell) {
this.gsmCell = gsmCell;
}
public String getMessageSN() {
return messageSN;
}
public void setMessageSN(String messageSN) {
this.messageSN = messageSN;
}
public Tracker getTrackerID() {
return trackerID;
}
public void setTrackerID(Tracker trackerID) {
this.trackerID = trackerID;
}
@Override
public int hashCode() {
int hash = 0;
hash += (idtrace != null ? idtrace.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Trace)) {
return false;
}
Trace other = (Trace) object;
if ((this.idtrace == null && other.idtrace != null) || (this.idtrace != null && !this.idtrace.equals(other.idtrace))) {
return false;
}
return true;
}
@Override
public String toString() {
return "com.wondersys.wf.wfRESTServer.model.Trace[idtrace=" + idtrace + "]";
}
}
[Message sent by forum member 'aonorati']
http://forums.java.net/jive/thread.jspa?messageID=395919