I'm facing a problem to join two tables request, request_approve_panel based on two conditions. The traditional sql query should look like:
select * from request r, request_approve_panel rap where r. request_id = rap.request_id and r.status = r.status
I only need join the "status" column in method which I posted below. I'm very confused among using @filter, @filterjointable or through Criteria. Could any one tell me with way is the best and how to implement the extra join of "status" column. Thanks ahead.
My current query code:
Code:
public RequestBean retrieveRequest(RequestBean request) {
final long requestId = request.getRequestId();
RequestBean rq = (RequestBean)getJpaTemplate().execute( new JpaCallback(){
public Object doInJpa(EntityManager em)
throws PersistenceException {
Session session = (Session)em.getDelegate();
Criteria criteria = session.createCriteria(RequestBean.class)
.setFetchMode("panel", FetchMode.JOIN).setFetchMode("history", FetchMode.JOIN)
.add(Expression.eq("requestId", requestId));
RequestBean reqBean = (RequestBean) criteria.uniqueResult();
return reqBean;
}
});
return rq;
}
My persistence beans:
Code:
@Entity (name = "request")
@Table (name = "request")
public class RequestBean implements Serializable{
private static final long serialVersionUID = 9102649993414256863L;
@Id
@Column (name = "request_id")
@GeneratedValue (strategy = GenerationType.IDENTITY)
private long requestId;
@Column
private long status;
@OneToMany (mappedBy = "request", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
@OrderBy ("changeDate desc, id desc")
private List<RequestHistory> history = new ArrayList<RequestHistory>();
@OneToMany (mappedBy = "request", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
private Set<RequestApprovePanel> panel = new HashSet<RequestApprovePanel>();
}
@Table (name = "request_approve_panel")
@Entity (name = "requestApporovePanel")
public class RequestApprovePanel implements Serializable {
private static final long serialVersionUID = -6729957172518975850L;
@Id
@Column
@GeneratedValue (strategy = GenerationType.IDENTITY)
private long id;
@ManyToOne (cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
@JoinColumn (name = "request_id")
private RequestBean request;
@ManyToOne
@PrimaryKeyJoinColumn
@JoinColumn (name = "approve_user_id")
private User user;
@Column
private long status;
}
@Table (name = "request_history")
@Entity (name = "requestHistory")
public class RequestHistory implements Serializable {
private static final long serialVersionUID = -8609395245171134561L;
@Id
@Column
@GeneratedValue (strategy = GenerationType.IDENTITY)
private long id;
@Column
private String action;
@ManyToOne (cascade = {CascadeType.PERSIST, CascadeType.MERGE})
@JoinColumn (name = "request_id")
private RequestBean request;
@Column
private long status;
}
[/code]
Login
FAQ
Search