Join tables in Hibernate

Question

I have two tables in my PostgreSQL database:

CREATE TABLE tableOne (id int, name varchar(10), address varchar(20))
CREATE TABLE tableTwo (id int, info text, addresses varchar(20)[])

now I want to create a join as follows:

SELECT * FROM tableOne JOIN tableTwo ON address = ANY(addresses)

I tried to achieve this using Hibernate - class TableOne:

@Entity
@Table(name = "tableOne")
class TableOne {
      private int id;
      private TableTwo tableTwo;
      private String address;

     @Id
     @Column(name = "id")
     public getId() { return id; }

     @ManyToOne
     @JoinFormula(value = "address = any(tableTwo.addresses)", 
                  referencedColumnName = "addresses")
     public TableTwo getTableTwo(){
        return tableTwo;
     }
     // Setters follow here

}

But Hibernate keeps generating queries with non-sense JOIN clauses, like:

... JOIN tableTwo ON _this.address = any(tableTwo.addresses) = tableTwo.addresses

How do I tell Hibernate using annotations to format my join query correctly? Unfortunately, our project must be restricted only to the Criteria API.

EDIT:

After suggestion from ashokhein in the comments below, I annotated the method getTableTwo() with just @ManyToOne - and now I would like to do the join using Criteria API, presumably with createAlias(associationPath,alias,joinType,withClause) method where withClause would be my ON clause in the join.

But Im not sure what to put as associationPath and alias parameters.

Any hints?

Don't do query creation in Entity class.It should not allowed to customize the query. — ashokhein, Sep 04 '14 at 07:05
http://www.dzone.com/tutorials/java/hibernate/hibernate-example/hibernate-mapping-many-to-one-using-annotations-1.html — ashokhein, Sep 04 '14 at 07:15
Why to you use `@ManyToOne`? It looks like a `@OneToOne`. If it is realy a n-1, where is your `Collection` (`Set` or `List`)? — Hannes, Sep 13 '14 at 08:35
Another good example on how Hibernate gets really anoying when you need something that can be done with SQL and goes beyond the ORM... Thank you! :- ) — ericbn, Sep 15 '14 at 00:58

score 0 · Answer 1 · edited May 23 '17 at 12:07

To support PostgreSQL array you need a custom Hibernate Type. Having a dedicated user type will allow you to run native SQL queries to make use of the type:

String[] values = ...
Type arrayType = new CustomType(new ArrayUserType());
query.setParameter("value", values, arrayType);

HQL supports ANY/SOME syntax but only for sub-queries. In your case you'll need a native query to use the PostgreSQL specific ANY clause against array values.

score 0 · Answer 2 · answered Sep 15 '14 at 06:39

You can try Named Query.

  @NamedQuery(name="JOINQUERY", query="SELECT one FROM tableOne one JOIN tableTwo two ON one.address = :address" )
  @Entity
  class TableOne{......

Retrieving part is:

TypedQuery<TableOne> q = em.createNamedQuery("query", TableOne.class);
q.setParameter("address", "Mumbai"); 
for (TableOne t : q.getResultList())
  System.out.println(t.address);

You might need to do some permutations on the query

score 0 · Accepted Answer · answered Sep 15 '14 at 17:28

So after a lot of time searching for the right answer, the only real solution that works for us is creating a view:

CREATE VIEW TableA_view AS SELECT TableOne.*,TableTwo.id FROM TableA JOIN TableTwo ON TableOne.address = ANY(TableTwo.addresses)

and mapping it to an entity TableOne instead of the original table. This was the only solution for us besides, of course, using a named query, which was a no-go as we needed to stick to the Criteria API.

As @ericbn has mentioned in the comments this is really an example where ORM gets really annoying. I would never expect that custom join clause like this is not possible to do in Hibernate.

score -1 · Answer 4 · answered Sep 04 '14 at 02:51

-1

@JoinFormula should contain SQL instead of HQL.
https://docs.jboss.org/hibernate/orm/4.2/javadocs/org/hibernate/annotations/JoinFormula.html

answered Sep 04 '14 at 02:51

Alex

11,451
6
37
52

I think `address = any(tableTwo.addresses)` is "a valid SQL fragment" too. – NumberFour Sep 04 '14 at 05:44

Join tables in Hibernate

4 Answers4