[JPQL] UNION

[Fabian Angy]

Hi!

It is possible to do an "UNION" in JPQL or something like this?
Because I have a abstract class ("Table a") which has 3 different subclass ("Table b", "Table c", "Table d") and when I execute my query I don't know in advance what kind of subclass I need.

Thanks in advance!

[James Sutherland]

JPQL does not support UNION. But any query for a superclass will return all subclasses that match the criteria.
What is the query you are trying to do?

[Fabian Angy]

I have 4 entities :

The superclass :

@Entity
@Table(name = "TABLE_A ")
@DiscriminatorColumn(name = "ID")
public abstract class A {
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "…", referencedColumnName = "…", nullable = false)
    private Other other;
}

The subclasses:

@Entity
@Table(name = "TABLE_B")
public class B extends A {
@Column(name = "VAR_B", length = 8)
	private String varB;
	…
}

@Entity
@Table(name = "TABLE_C")
public class C extends A {
@Column(name = "VAR_C", length = 8)
	private String varC;
	…

}

@Entity
@Table(name = "TABLE_D")
public class D extends A {
@Column(name = "VAR_D", length = 8)
	private String varD;
	…

}

 The variables varB, varC, varD represent the same field but with a different name.

Other class:

@Entity
@Table(name = "OTHER")
public class Other {
@Column(name = "OTHER”)
	private String varOther;
	…

}

I would like to retrieve an object A with my query
“SELECT a FROM A a, Other o JOIN FETCH a.other WHERE o.varOther like ‘…’”

In this case, if the user puts something in the search field “var” I need to add either varB, varC, or varD in the WHERE clause but I don’t know in advance if the type of the subclass is varB, varC or varD, I’m stuckeeeed!

I've got a "unknown state or association field [varB] of class […A]" by example

[James Sutherland]

You might want to consider using a dynamic query, so depending on your argument, then decide which subclass to query.

If you need one query, then you may be able to use EclipseLink's "TREAT" function.

See,
http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Casting

Otherwise, you could try multiple queries, one per subclass.

[Fabian Angy]

It doesn't work
To use Treat downcasting, i need a subclass variable no? (example: i don't have a variable "B" in my superclass) so i can't use "TREAT(a.xxx AS B) b"