1

I want to prepare a simple SQL query with join using criteriaAPI. Let's assume that I have table PERSON with column ID and PET_ID which refers table PET. In my application I have both Person and PET entity, however PET isn't mapped to an entity - person keeps only pet id as number. 

@Entity
public class Person {

    @Id
    private int id;

    @Column("PET_ID")
    private int petId;
}

@Entity
public class Pet {

    @Id
    private int id;

}

Those are only examples but it's enough to show what I want to achieve. In Oracle I would write simple query: 

SELECT _p.id, _pet.id from PERSON _p join PET _pet on _p.pet_id=_pet.id;

But I am not able to achieve this using criteriaAPI - I know that if I would have Pet entity mapped in Person class it would be easy, but with below example I already spent much time without finding any solution

JSON C11
  • 11,272
  • 7
  • 78
  • 65
truck.daw
  • 9
  • 4
  • How come there is no link between `Person` and `Pet` entity? If link exists between these two entities then you could use `Join` and achieve the desired results. – Jacob Feb 10 '16 at 06:24
  • Yes I know, In my real problem two entities are from different buisness domain, our architect doesn't want to mix them. – truck.daw Feb 10 '16 at 15:32
  • Have a look at [this](http://stackoverflow.com/a/17106521/599528), perhaps of some help. – Jacob Feb 10 '16 at 18:04
  • Another option would be to create a database view where you join all tables and use the view as your entity. – Jacob Feb 10 '16 at 18:55
  • First possibility doesn't help since I finally have to make outer join - it would help if i would need inner. Second one would help that's true but I was wondering if I am able to create query which I wanted to. Anyway thanks for help :) – truck.daw Feb 11 '16 at 11:51

1 Answers1

1

As far as I know, you cannot do this from criteria unless there a reference to Pet in Person.

However, you can get the desired result using org.hibernate.Query. reply in comment and i'll post the required code for that.

However, if it's necessary to get the desired result using Criteria, then I've posted a temporary Hack which will work in your case.

List<Integer> allPetIds = session.createCriteria(Pet.class)
            .setProjection(Projections.property("id"))
            .list();

List<Person> persons = session.createCriteria(Person.class)
            .add(Restrictions.in("petId", allPetIds))
            .list();
Raman Sahasi
  • 30,180
  • 9
  • 58
  • 71
  • Thanks - it's kind of trick however it doesn't solve my problem. I was not precise in my example since I wanted to clearly show idea of thing which I am searching for. In my real problem I want to make left outer join PET and check some condition on joined table, not only Ids therefore I need full entity in my query to cooperate. It seems that only way to achieve that is to link the second entity – truck.daw Feb 10 '16 at 15:31