CODEWITHSUNDEEP
javaoracle-databasehibernatejoinentity
Armine
Armine

Reputation: 1695

How to Inner Join Two Independent Entities in Hibernate

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;

    ...
}

Upvotes: 1

Views: 3488

Answers (1)

davidxxx
davidxxx

Reputation: 131526

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();

Upvotes: 1

Related Questions