Use "connect by prior" in QueryDSL

[zahra barati]

Hi everybody !
I use Oracle10g and there is a table like it :
LOCATION(ID , NAME , PARENT_ID)
 
and for retrieve a hierarchy of locations I need to use a "connected by prior" query like it:
" select *
from LOCATION
connect by prior
ID = PARENT_ID
start with ID =  x "

that x can be replaced with different IDs.
Please help me implement it with queryDSL.
thank you

[Timo Westkämper]

Hi.

You can use the .mysema.query.sql.oracle.OracleQuery class for that. It has connectByPrior and startWith methods.

Br
Timo

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[zahra barati]

Tanks a lot for your help.
I try it in this way:
EBoolean priorPredicate = QLocation.location.id.eq(QLocation.location.parent.id);
OracleQuery    oQuery = new OracleQuery(connection, new OracleTemplates()).connectByPrior(priorPredicate);

This is a part of  Location entity class:
@Entity
@Table(name="LOCATION")
public class Location implements Serializable {
    @Id
 
    private long id;
    private String name;

    //bi-directional many-to-one association to Location
    @ManyToOne
    @JoinColumn(name="PARENT_ID")
    private Location parent;

    //bi-directional many-to-one association to Location
    @OneToMany(mappedBy="parent")
    private List<Location> children;


and this is a part of QLocation class :
public class QLocation extends PEntity<Location> {

    public static final QLocation location = new QLocation("location");

    public final PSet<Location> children = createSet("children", Location.class);

    public final PNumber<Long> id = createNumber("id", Long.class);

    public final PString name = createString("name");

    public final QLocation parent;
...

But there is some problem yet, with my priorPredicate.
when I run the code , this exception throws :
com.mysema.query.QueryException: java.sql.SQLSyntaxErrorException: ORA-00904: "LOCATION"."PARENT"."ID": invalid identifier

So what's the correct format for the predicate?

[Timo Westkämper]

Hi.

It looks like you are mixing JPA Entity Q-types with Querydsl SQL. That's not supported. You need to generated the SQL Q-types like documented in the reference docs.

Br,
Timo