How to Inner Join Two Independent Entities in Hibernate

Question

I have 2 entities: DocumentEntity (docNumber (primary key), dateOfFill, ...) and FileEntity (id, title, size, ...). I have an HQL query with inner join of 2, which should run on Oracle DB:

String queryStr = "SELECT docNumber " +
             + "FROM DocumentEntity d " +
             + "INNER JOIN FileEntity f " +
             + "ON d.docNumber = f.title " +
             + "WHERE d.date > to_date('01.01.2011','dd.mm.yyyy')"

Query query = em.createQuery(query_string);
return query.getResultList();

When I run the code snippet I'm getting an exception org.hibernate.hql.ast.QuerySyntaxException: Path expected for join!

I looked through

but none resolved my problem. The suggested paths cannot be used in this example (at least it gives wrong path error). The answer of the last link says that:

Joins can only be used when there is an association between entities.

The issue is that I cannot associate these 2 entities.

The question is: How can I join these 2 entities?

UPDATE: My entities are:

@Entity
@Table(name = "DOCUMENT")
public class DocumentEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "DOC_NUMBER", nullable = false)
    private String docNumber;

    @Basic(optional = false)
    @Column(name = "DATE_OF_FILL")
    @Temporal(TemporalType.DATE)
    private Date dateOfFill;

    ...
}

and

@Entity
@Table(name = "FS_FILE")
public class FileEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "FS_FILE_SEQ", allocationSize = 1, sequenceName = "FS_FILE_SEQ")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FS_FILE_SEQ")
    @Column(name = "ID", nullable = false)
    protected Long id;

    @Column(name = "TITLE", nullable = false)
    protected String title;

    @Column(name = "MIMETYPE", nullable = false)
    protected String mimetype;

    @Column(name = "FILESIZE", nullable = false)
    protected Long filesize;

    @Column(name = "FILEPATH", nullable = false)
    protected String filepath;

    ...
}

@davidhxxx: what exactly do you mean? What exactly you would like to see? — Armine, Aug 08 '16 at 14:10
To do a join, you should have at least a fk fiel in the slave entity. I don't see it.. — davidxxx, Aug 08 '16 at 14:34
@davidhxxx: Yes, I don't have a FK, since FS_FILE has data from tables other than DOCUMENT. — Armine, Aug 08 '16 at 14:50
Ok, I answered you with a code proposition. Test it and if not ok, tell me. — davidxxx, Aug 08 '16 at 14:59

davidxxx · Accepted Answer · 2016-08-08T15:13:30.160

1

In this case, you don't need to do a join since you limit the result with the condition d.docNumber = f.title. Just add the condition in the where clause and use a SQL query instead of a JPQL query since it seems more matching to your need.

String sqlString= "SELECT d.docNumber " +
             + "FROM DOCUMENT d, FS_FILE f " +
             + "WHERE d.docNumber = f.title " +
             + "AND d.date > to_date('01.01.2011','dd.mm.yyyy')"


Query query = em.createNativeQuery(sqlString);
return query.getResultList();

edited Aug 08 '16 at 15:13

answered Aug 08 '16 at 14:58

davidxxx

125,838
23
214
215

and put FileEntity right next to DocumentEntity in the FROM caluse? – Armine Aug 08 '16 at 15:08
No, you cannot. I updated my code. In your case, a native sql query seems more matching to your need. – davidxxx Aug 08 '16 at 15:14
I tried yesterday using createNativeQuery(), but I mixed JPQL and SQL, so with no result... Once I used pure SQL and createNativeQuery(), that worked. Thanks a lot. But, it means, that JPQL has kind of limitation and kind of cons against SQL :) – Armine Aug 09 '16 at 12:44
1

Great. You are welcome. JPQL is a layer above of SQL, it brings ORM features but it has some limitations. Particularly for a query not friendly for the tool. In your case, your entities are independent for the tool as not related in the JPA mapping. For addressing your need, you could do a JPQL query but it would much more complicated as the SQL query. You should query the two objects in the select and you would retrieve an Object array with the two entities... The hell to retrieve a number :) – davidxxx Aug 09 '16 at 12:46

How to Inner Join Two Independent Entities in Hibernate

1 Answers1