Get many object from result of Oracle view

Question

At my Oracle database I have two example tables:

• tabel A with columns id, a1, a2, a3
• tabel B with columns id, b1, b2, b3

I have view for get information from this two tables:

CREATE VIEW Foo ("A.id", "A.a1", "A.a2", "A.a3", "B.id", "B.b1", "B.b2", "B.b3") AS
SELECT aaa.*, bbb.*
FROM A aaa, B bbb
WHERE 
   ...some conditions...;

In my Java application I want to get information result by Foo view by Hibernate. So I have to use createSQLQuery() method:

public List<SomeObject> findSomeObjects() {
    Query q = sessionFactory.getCurrentSession()
             .createSQLQuery("select f.* from Foo f")
             .addEntity(A.class).addEntity(B.class);
    List l = q.list();

    //here I want to get object of A class and B class from return l
    //and prepare return list of SomeObject
}

SomeObject is agregate for A and B class.

I have problem with get object of A class and B class from return list and construct SomeObject list. How can I make it properly?

EDIT

• table A have one more column fk_c, which is foreign key to C table
• table B have one more column fk_d, which is foreign key to D tabel

Answer 1

Create a domain Class with all the fields present in the view "Foo" as below.

Class Foo {

    private String a_id;
    private String a1;
    private String a2;
    private String a3;
    private String b_id;
    private String b1;
    private String b2;
    private String b3;

    // Getters
    // Setters
}

Modify your SQL query as below :

public List<SomeObject> findSomeObjects() {
    Query q = sessionFactory.getCurrentSession()
             .createSQLQuery("select f.* from Foo f")
             .setResultTransformer(Transformers.aliasToBean(Foo.class))
    List l = q.list();

    // here you can iterate through the list to fetch the fields and 
    // create a own custom object as per your requirement.

}

Alternative Solution :

You can also create an Entity class for view "Foo" and write criteria query to fetch the results as well.

Please check the below links for the same :

DB View to Hibernate Entity Mapping

How to map a view with Hibernate

EDIT:

If you have a foreign key to other table (C, D) in your View, I would suggest you to map this view as hibernate Entity Class by adding the Relationship to those other dependent entities (C, D).

@Entity
@Immutable
Class Foo {

    private String a_id;
    private String a1;
    private String a2;
    private String a3;
    private String b_id;
    private String b1;
    private String b2;
    private String b3;

    @OneToMany
    @JoinColumn(name = "C_id")
    private List<C> c;

    @OneToMany
    @JoinColumn(name = "d_id")
    private List<D> d;

    // Getters
    // Setters
}