[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
RE: [eclipselink-users] NamedNativeQuery, SqlResultSetMapping and left outer join
|
Hi. The original query was big, referencing 6 tables. I created a test query using 2 of the tables and the same error is generated. I'll try the ObjectBuildingQuery in a bit. Here are details, thanks.
================== PaperRequest entity =====================
@NamedNativeQueries({
@NamedNativeQuery(
name="PaperRequest.test",
query="SELECT d.people_id AS d_people_id, d.sectional_cd AS d_sectional_cd, d.period_cd AS d_period_cd, " +
" d.cit_cd AS d_cit_cd, d.delig_i AS d_delig_i, " +
" d.felig_d AS d_felig_d, d.req_i AS d_req_i, " +
" d.c_o_r AS d_c_o_r, " +
" dw.people_id AS dw_people_id, dw.sectional_cd AS dw_sectional_cd, dw.period_cd AS dw_period_cd, " +
" dw.cit_cd AS dw_cit_cd, dw.delig_i AS dw_delig_i, " +
" dw.felig_d AS dw_felig_d, dw.req_i AS dw_req_i, " +
" dw.c_o_r AS dw_c_o_r " +
"FROM isi.pap_req d " +
"LEFT JOIN isi.pap_req_wrk dw " +
" ON d.people_id = dw.people_id AND " +
" d.period_cd = dw.period_cd AND " +
" d.sectional_cd = dw.sectional_cd " +
"WHERE d.period_cd = ? ",
resultSetMapping="PaperRequest.AlertTest"
)
})
@SqlResultSetMappings({
@SqlResultSetMapping(
name="PaperRequest.AlertTest",
entities={
@EntityResult(
entityClass=PaperRequest.class,
fields={
@FieldResult(name="people_id", column="d_people_id"),
@FieldResult(name="sectional_cd", column="d_sectional_cd"),
@FieldResult(name="period_cd", column="d_period_cd"),
@FieldResult(name="cit_cd", column="d_cit_cd"),
@FieldResult(name="delig_i", column="d_delig_i"),
@FieldResult(name="felig_d", column="d_felig_d"),
@FieldResult(name="req_i", column="d_req_i"),
@FieldResult(name="c_o_r", column="d_c_o_r")
}
),
@EntityResult(
entityClass=PaperRequestWork.class,
fields={
@FieldResult(name="people_id", column="dw_people_id"),
@FieldResult(name="sectional_cd", column="dw_sectional_cd"),
@FieldResult(name="period_cd", column="dw_period_cd"),
@FieldResult(name="cit_cd", column="dw_cit_cd"),
@FieldResult(name="delig_i", column="dw_delig_i"),
@FieldResult(name="felig_d", column="dw_felig_d"),
@FieldResult(name="req_i", column="dw_req_i"),
@FieldResult(name="c_o_r", column="dw_c_o_r")
}
)
}
)
})
@Table(name = "PAP_REQ")
@IdClass(ca.somewhere.isi.pxr.entity.conv.PaperRequestPK.class)
public class PaperRequest extends BaseEntity implements Serializable {
private BigInteger peopleId;
@Id
@Column(name = "PEOPLE_ID", nullable = false, length = 10)
public BigInteger getPeopleId() {
return peopleId;
}
public void setPeopleId(BigInteger peopleId) {
this.peopleId = peopleId;
}
private String sectionalCode;
@Id
@Column(name = "SECTIONAL_CD", nullable = false, length = 10)
public String getSectionalCode() {
return sectionalCode;
}
private String periodCode;
@Id
@Column(name = "PERIOD_CD", nullable = false, length = 6)
public String getPeriodCode() {
return periodCode;
}
public void setPeriodCode(String periodCode) {
this.periodCode = periodCode;
}
// other setters and getters omitted
================== End PaperRequest Entity ==================
================== PaperRequestWork Entity =================
@Table(name = "PAP_REQ_WRK")
@IdClass(ca.somewhere.isi.pxr.entity.conv.PaperRequestPK.class)
public class PaperRequestWork extends BaseEntity implements Serializable {
private BigInteger peopleId;
@Id
@Column(name = "PEOPLE_ID", nullable = false, length = 10)
public BigInteger getPeopleId() {
return peopleId;
}
public void setPeopleId(BigInteger peopleId) {
this.peopleId = peopleId;
}
private String sectionalCode;
@Id
@Column(name = "SECTIONAL_CD", nullable = false, length = 10)
public String getSectionalCode() {
return sectionalCode;
}
public void setSectionalCode(String sectionalCode) {
this.sectionalCode = sectionalCode;
}
private String periodCode;
@Id
@Column(name = "PERIOD_CD", nullable = false, length = 6)
public String getPeriodCode() {
return periodCode;
}
public void setPeriodCode(String periodCode) {
this.periodCode = periodCode;
}
// other setter and getters omitted
================== End PaperRequestWork Entity ==============
================== Java code to call JPA NativeNamedQuery =======
public List<Transport> getItNow (Transport transport) {
Query query = em.createNamedQuery ("PaperRequest.test");
query.setParameter (1, transport.getPeriodCode ());
List<Vector<Object>> objArray = query.getResultList ();
// For testing I just returned an empty list. Normally code here to loop through
// objArray to populate transport and return a list of those.
List<Transport> alerts = new ArrayList<Transport> ();
return alerts;
}
================== End Java code to call JPA NativeNamedQuery ====
Manually executing the query shown in the exception below returns records as expected. As in, there are values for
PERIOD_CD, PEOPLE_ID and SECTIONAL_CD in all rows (LEFT JOIN properties) ... so not sure why they are showing as null below in the exception message. Maybe some other issue as opposed to what I had originally thought (LEFT JOIN effect).
================== The Exception ==========================
Caused by: Exception [EclipseLink-6044] (Eclipse Perisi.ence Services - 1.1.0.r3639-SNAPSHOT): org.eclipse.perisi.ence.exceptions.QueryException
Exception Description: The primary key read from the row [DatabaseRecord(
ISI.PAP_REQ.PERIOD_CD => null
ISI.PAP_REQ.PEOPLE_ID => null
ISI.PAP_REQ.SECTIONAL_CD => null
ISI.PAP_REQ.RETR_DT => null
ISI.PAP_REQ.STS_DT => null
ISI.PAP_REQ.CIT_CD => null
ISI.PAP_REQ.THING => null
ISI.PAP_REQ.FELIG_D => null
ISI.PAP_REQ.TMST => null
ISI.PAP_REQ.SEC_CD => null
ISI.PAP_REQ.REQ_I => null
ISI.PAP_REQ.A_I => null
ISI.PAP_REQ.D_DT => null
ISI.PAP_REQ.DELIG_I => null
ISI.PAP_REQ.CAND_S_CD => null
ISI.PAP_REQ.UP_C_S => null
ISI.PAP_REQ.S_R_N => null
ISI.PAP_REQ.R_F_P_I => null
ISI.PAP_REQ.F_E_S_D => null
ISI.PAP_REQ.C_O_R => null)] during the execution of the query was detected to be null. Primary keys must not contain null.
Query: ResultSetMappingQuery(name="PaperRequest.test" referenceClass=PaperRequest sql="SELECT d.people_id AS d_people_id, d.sectional_cd AS d_sectional_cd, d.period_cd AS d_period_cd, d.cit_cd AS d_cit_cd, d.delig_i AS d_delig_i, d.felig_d AS d_felig_d, d.req_i AS d_req_i, d.c_o_r AS d_c_o_r, dw.people_id AS dw_people_id, dw.sectional_cd AS dw_sectional_cd, dw.period_cd AS dw_period_cd, dw.cit_cd AS dw_cit_cd, dw.delig_i AS dw_delig_i, dw.felig_d AS dw_felig_d, dw.req_i AS dw_req_i, dw.c_o_r AS dw_c_o_r FROM isi.pap_req d LEFT JOIN isi.pap_req_wrk dw ON d.people_id = dw.people_id AND d.period_cd = dw.period_cd AND d.sectional_cd = dw.sectional_cd WHERE d.period_cd = ? ")
at org.eclipse.perisi.ence.exceptions.QueryException.nullPrimaryKeyInBuildingObject(QueryException.java:842)
at org.eclipse.perisi.ence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:464)
================== End Exception ==========================
In this case PaperRequest and PaperRequestWork both extend BaseEntity. PaperRequestPK exists because the primary key is a composite key of 3 fields.
Thanks,
Andrew
____________________________________
From: eclipselink-users-bounces@xxxxxxxxxxx [eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of James Sutherland [jamesssss@xxxxxxxxx]
Sent: Wednesday, March 25, 2009 9:12 AM
To: eclipselink-users@xxxxxxxxxxx
Subject: Re: [eclipselink-users] NamedNativeQuery, SqlResultSetMapping and left outer join
Could you include the code and SQL for your query, and your object model.
I assume you are trying to select objects from the outer joined data, you
could try just selecting the data, then would not have id issues.
Their is an API of EclipseLink ObjectBuildingQuery
setShouldBuildNullForNullPk() that you could call, that would bypass this
error. You could try setting this on your query. Perhaps it should default
to true for result-set queries, you may want to log a bug for this.
((ObjectBuildingQuery)((JpaQuery)query).getDatabaseQuery()).setShouldBuildNullForNullPk(true);
Andrew Wang-4 wrote:
>
> Hi,
> I am using a NamedNativeQuery that uses an OUTER LEFT JOIN. I am using
> SqlResultSetMapping to get the columns that I want (I don't need all the
> columns from the 6 tables). The return list is an object array of the
> entities with what I was hoping to be populated with the properties that I
> need.
>
> The exception I am getting is:
>
> Exception Description: The primary key read from the row [DatabaseRecord(
> ... )] during the execution of the query
> was detected to be null. Primary keys must not contain null.
>
> Which I am assuming that since the entities I am using have Id annotated
> properties, those properties can't be null (which makes sense). But the
> nature of the OUTER LEFT JOIN is to retrieve those null values in table B
> for each entry in table A.
>
> Should I be doing this another way ?
>
> Thanks.
>
>
-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
--
View this message in context: http://www.nabble.com/NamedNativeQuery%2C-SqlResultSetMapping-and-left-outer-join-tp22681230p22701521.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
