I've got a fairly simple one-to-one join that is generating some incorrect SQL.
The entities:
[code]
@Entity
@Table(name="LOTMSL13")
public class LotMaster implements Serializable
{
@Id
@Column(name="DICODE")
private String divisionCode;
@Id
@Column(name="LOCODE")
private String locationCode;
@Id
@Column(name="LMYEAR")
private Float lotMasterYear;
@Id
@Column(name="LMNUMB")
private Float lotMasterNumber;
@OneToOne(mappedBy="lotMaster", fetch=FetchType.LAZY)
@JoinColumns({
@JoinColumn(name="DICODE", referencedColumnName="DICODE", insertable=false, updatable=false),
@JoinColumn(name="LOCODE", referencedColumnName="LOCODE", insertable=false, updatable=false)
})
private DeliveryList deliveryList;
@OneToMany(mappedBy="lotMaster", fetch=FetchType.LAZY)
private List<LotSummary> lotSummarys;
...............................................
}
@Entity
@Table(name="DELSTDL8")
public class DeliveryList implements Serializable
{
@Id
@Column(name="PRCHDID")
private Float procurementHeaderId;
@Id
@Column(name="PCDSEQ")
private Float procurementDetailSequence;
@Column(name="DICODE", insertable=false, updatable=false)
private String divisionCode;
@Column(name="LOCODE", insertable=false, updatable=false)
private String locationCode;
@Column(name="DLDACDAT")
@Temporal(TemporalType.DATE)
private Date actualDate;
@Column(name="NBRHEAD")
private Float numberOfHead;
@OneToOne(fetch=FetchType.LAZY)
private LotMaster lotMaster;
@OneToMany(mappedBy="deliveryList", fetch=FetchType.LAZY)
private List<ProcurementDetail> procurementDetails;
...............................................
}
@Entity
@Table(name="PROCDTL0")
public class ProcurementDetail implements Serializable
{
@Id
@Column(name="PRCHDID")
private Float procurementHeaderId;
@Id
@Column(name="PCDSEQ")
private Float procurementDetailSequence;
@Column(name="DICODE")
private String divisionCode;
@Column(name="LOCODE")
private String locationCode;
@Column(name="PCDEDAT")
@Temporal(TemporalType.DATE)
private Date procurementDetailDate;
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumns({
@JoinColumn(name="PRCHDID", referencedColumnName="PRCHDID", insertable=false, updatable=false),
@JoinColumn(name="PCDSEQ", referencedColumnName="PCDSEQ", insertable=false, updatable=false)
})
private DeliveryList deliveryList;
...............................................
}
[/code]
The query:
[code]
String ql = "select dl from DeliveryList dl " +
"left join fetch dl.procurementDetails " +
"left join fetch dl.lotMaster " +
"where dl.actualDate between :endRange and CURRENT_DATE " +
"and dl.divisionCode = :division " +
"and dl.locationCode = :location";
[/code]
The SQL output:
[code]
SELECT
t0.PRCHDID,
t0.PCDSEQ,
t0.LOCODE,
t0.DLDACDAT,
t0.NBRHEAD,
t0.DICODE,
t0.LMYEAR,
t0.LMNUMB,
t1.PRCHDID,
t1.PCDSEQ,
t1.DICODE,
t1.LOCODE,
t1.PCDEDAT,
t2.DICODE,
t2.LOCODE,
t2.LMYEAR,
t2.LMNUMB
FROM DELSTDL8 t0
LEFT OUTER JOIN PROCDTL0 t1 ON ((t1.PRCHDID = t0.PRCHDID) AND (t1.PCDSEQ = t0.PCDSEQ))
LEFT OUTER JOIN LOTMSL13 t2 ON ((t2.LMNUMB = t0.LMNUMB) AND ((t2.LOCODE = t0.LOCODE) AND ((t2.LMYEAR = t0.LMYEAR) AND (t2.DICODE = t0.DICODE))))
WHERE (((t0.DLDACDAT BETWEEN '2007-06-13' AND CURRENT_DATE)
AND (t0.DICODE = CAST ('BV' AS VARCHAR(32672) ))) AND (t0.LOCODE = CAST ('BV' AS VARCHAR(32672) )))
[/code]
The exception:
[code]
Caused by: java.sql.SQLException: [SQL0205] Column LMNUMB not in table DELSTDL8.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:520)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1375)
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:252)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1881)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1711)
at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:475)
at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareStatement(ConnectionWrapper40.java:154)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1162)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:612)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:485)
... 65 more
[/code]
...correct, "LMNUMB" and "LMYEAR" do not exist in that table/entity, they do exist in the table it's joining to on the one-to-one, however...so why is the query being generated w/ the fields in the wrong table? Bug?
All the other relationships worked perfectly until I added this one-to-one.
Thanks!
[Message sent by forum member 'zambizzi' (zambizzi)]
http://forums.java.net/jive/thread.jspa?messageID=249951