Custom Join Clause For Hibernate Many To One

Is it possible to tailor the query that hibernate executes when retrieving associations for a given entity? More specifically I would like to add an extra condition to a join clause to cater for a design flaw in a legacy database I am working with.

The problem I am facing is below. The primary key for table b consists of 2 fields, a category id and a value_id. However in table A I only have the value_id stored and it is assumed that the category id is hard coded:

@Entity
@Table(name = "table_a")
public class A {
   @ManyToOne
   @JoinColumn(name = "b_id")
   private B b;
}

@Entity
@Table(name = "table_b")
public class B {
   @Id
   private int id;
}

When querying for instances of A, hibernate generates the following join clause :

SELECT *
FROM table_a a LEFT OUTER JOIN table_b b
  ON a_.b_id = b_.value_id

Whereas they query I require it to execute is :

SELECT *
FROM table_a a LEFT OUTER JOIN table_b b
  ON a_.b_id = b_.value_id
 AND b.category_id = 2

I know that I could use a view to work aroun开发者_JS百科d this problem, however it would require me to create around 70 views which I would like to avoid if possible.

---

I solved the problem. It wasn't the prettiest solution, but the only one that worked.

Basically I had to do:

1. Simulate a Embbeded ID to class A
2. Make a Join between A -> B using the real key column and categoryId comparing to a formula

Like this:

Obs: I didn't test this code, just adapted from mine.

@Entity
@Table(name = "table_a")
public class A {
   @ManyToOne(fetch=FetchType.LAZY)
   @JoinColumnsOrFormulas(value={
        @JoinColumnOrFormula(column=
                @JoinColumn(name="B_ID", referencedColumnName="B_ID")),
        @JoinColumnOrFormula(formula=
                @JoinFormula(referencedColumnName="CATEGORY_ID", 
                        value = "(SELECT 2 from DUAL)"))}) //I am using Oracle
   @JoinColumn(name = "b_id")
   private B b;
}

@Entity
@Table(name = "table_b")
class B {
   @Id
   private BId id;
}

@Embeddable
class BId{
    private int id;
    private Integer categoryId;
}

---

You can use this:

@OneToMany(mappedBy = "b")
@Cascade(CascadeType.ALL)
private List<A> aList = new ArrayList<A>(0);

on class B to get the join and a named query to get category_id (also in class B)

@NamedQueries({
@NamedQuery(name = "B.findByCategoryId", query = "SELECT b FROM B b WHERE b.Id = :categoryId")
})