Hello,
I have two entities so defined:
@Entity
@Inheritance(strategy=InheritanceType.JOINED)
@Table(name="an_nodi")
@DiscriminatorColumn(name="tipo", discriminatorType=DiscriminatorType.STRING, columnDefinition="VARCHAR(31) NOT NULL")
public abstract class Nodo implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable=false)
private String nome;
....
@Entity
@Table(name="ordini")
public class Ordine implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(cascade=CascadeType.PERSIST)
private Nodo nodo;
.....
In entity Ordine the object nodo can be null.
I'm trying to write a query that returns all objects Ordine with nodo set to NULL OR nodo specified with some values.
For example in the database (a DB2 database) we can have
table "an_nodi"
ID NOME
1 abc
2 def
table "ordini"
ID NODO_ID
5 null
6 1
QUESTION 1)
I have the correct results if I call these queries:
a) select o from Ordine o where o.nodo.nome = 'abc' -> Ordine[id=6]
b) select o from Ordine o where o.nodo is null -> Ordine[id=5]
If I try to combine the queries with an 'OR' I have a wrong result:
c) select o from Ordine o where o.nodo is null OR o.nodo.nome = 'abc' -> Ordine[id=6] (ONLY !!!)
This is the generated SQL:
d) SELECT t0.ID, t0.NODO_ID, .... FROM ordini t0, an_nodi t1 WHERE (((t1.NOME = 'abc') OR (t0.NODO_ID IS NULL)) AND (t1.ID = t0.NODO_ID))
I understood the relationship @ManyToOne specified in Ordine is an INNER JOIN.... Is it always an INNER JOIN or I can set it as an OUTER JOIN?
QUESTION 2)
This is the new query, with the LEFT JOIN:
e) select o from Ordine o
left join o.nodo n
where n.nome = 'abc' OR n is null
I have the exception listed below. If I replace '[b]=*[/b]' with '[b]IS[/b]' the generated SQL is OK!!!
Where did I go wrong?
Local Exception Stack:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2006.8 (Build 060830)): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: org.apache.derby.client.am.SqlException: Syntax error: Encountered "*" at line 1, column 199.Error Code: -1
Call:SELECT t0.ID, t0.NODO_ID, t0.ATTORE_ID, t0.STATOORDINE_ID, t0.CLASSIFICAZIONE_ID FROM ordini t0 LEFT OUTER JOIN an_nodi t1 ON (t1.ID = t0.NODO_ID) WHERE ((t1.NOME = 'abc') OR t0.NODO_ID =* NULL)
Query:ReportQuery(it.test.app.entity.Ordine)
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:303)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:551)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
at oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:465)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:270)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:600)
at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2115)
at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2081)
at oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:774)
at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)
at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:677)
at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:731)
at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2218)
at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)
at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:909)
at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:346)
at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:447)
Thanks,
rooz
[Message sent by forum member 'rooz' (rooz)]
http://forums.java.net/jive/thread.jspa?messageID=232717