Topic
  • 3 replies
  • Latest Post - ‏2010-03-07T15:27:42Z by Paul Ianas
ishakteyran
ishakteyran
1 Post

Pinned topic retrieving from multiple entities in JPA

‏2010-01-27T22:09:16Z |
hello to all,

I have a question about if there is a capability of JPA, either the one that is shipped with RAD/RSA 7.5.4 or JPA 2.0 , that enables us to simulate resultset data structure. In other words, i want to manage the result of the query q="SELECT D.ID, F.NAME, S.GRADE FROM DEPT D, FACULTY F, STUDENT S WHERE F.ID=D.DEP_ID AND S.GRADE>70 AND .....(whatever) " , after executing this query the resulting table has columns ID, NAME, GRADE but this table is not a DEPT, FACULTY, or STUDENT.. Since , as i know, JPA Entities must correspond to tables in database (correct me if i am wrong please), how can i handle such a query when i am using JPA , do i need to create these tables for every possible result of SQL queries ??
is there a way to get results for such queries in JPA (if not is this feature available in Hibernate or any other alternative of JPA) ? if yes, what is the way to achieve this goal ? if no, using JSF and persistence (not only JPA but could be Hibernate or any other alternative) how can i achieve this goal ?

thank you all
Updated on 2010-03-07T15:27:42Z at 2010-03-07T15:27:42Z by Paul Ianas
  • Paul Ianas
    Paul Ianas
    3 Posts

    Re: retrieving from multiple entities in JPA

    ‏2010-03-07T11:08:16Z  
    Hi,

    There are two main approaches:
    1. if you want - for whatever reasons - to go from data model to database, then use JPA, Hibernate, etc. There are means to do what you want:
    @Entity
    public class Department{
    ...
    @ManyToOne (cascade=CascadeType.ALL)
    private Faculty faculty;
    ...
    }

    @Entity
    public class Faculty {
    ....
    @OneToMany(mappedBy="department")
    private Collection<Department> departments;
    ....
    }

    and so on...

    2. if you want to go from datatable to data model, you can use iBatis; it lets you map any class member to any table column.
    • you make an SqlMapConfig.xml where you specify the connection attributes, etc plus the map files:
    <sqlMapConfig>
    <settings ...../>
    <transactionManager type="JDBC" commitRequired="false">
    <dataSource type="SIMPLE">
    <property name="JDBC.Driver" value="org.gjt.mm.mysql.Driver"/>
    <property name="JDBC.ConnectionURL" value="<path to db>"/>
    <property name="JDBC.Username" value="..."/>
    <property name="JDBC.Password" value="..."/>
    ...
    </dataSource>
    </transactionManager>
    <sqlMap resource="your/package/FlatEntry.xml"/>
    </sqlMapConfig>

    • in FlatEntry.xml you may have:
    <sqlMap namespace="GetTheFlatEntry">
    <select id="getAuthor" resultClass="your.package.FlatEntry">
    SELECT D.ID as deptId,
    F.NAME as facultyName,
    S.GRADE as studentGrade,
    FROM DEPT D, FACULTY F, STUDENT S
    WHERE F.ID=D.DEP_ID AND S.GRADE>70
    AND F.ID = #value#
    </select>
    </sqlMap>

    deptId, facultyName and studentGrade are members of the FlatEntry class. Flat entry is just a POJO with getters and setters (no annotation needed)

    Now what do you see:
    • you have whatever class structure you want
    • you can give your own SQL query (you can actually edit it, like you'd do in JPA when using EntityManager.createNativeQuery(...))
    • you can map any field from any table with any member in your (flat) class; you can use non-flat classes as well (class-to-table mapping)
    • from somewhere in your code:
    ...
    static SqlMapClient sqlMapper;
    try{
    Reader reader = Resources.getResourceAsReader("ro/level7/webpublisher/service/content/db/SqlMapConfig.xml");
    static SqlMapClient sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
    reader.close();
    }catch(...){...}
    try{
    FlatEntry entry = (FlatEntry) sqlMapper.queryForObject("GetTheFlatEntry", <someValueForID if any>);
    }catch(...){}
    ...
    I hope it helps.
  • Paul Ianas
    Paul Ianas
    3 Posts

    Re: retrieving from multiple entities in JPA

    ‏2010-03-07T11:10:41Z  
    Hi,

    There are two main approaches:
    1. if you want - for whatever reasons - to go from data model to database, then use JPA, Hibernate, etc. There are means to do what you want:
    @Entity
    public class Department{
    ...
    @ManyToOne (cascade=CascadeType.ALL)
    private Faculty faculty;
    ...
    }

    @Entity
    public class Faculty {
    ....
    @OneToMany(mappedBy="department")
    private Collection<Department> departments;
    ....
    }

    and so on...

    2. if you want to go from datatable to data model, you can use iBatis; it lets you map any class member to any table column.
    • you make an SqlMapConfig.xml where you specify the connection attributes, etc plus the map files:
    <sqlMapConfig>
    <settings ...../>
    <transactionManager type="JDBC" commitRequired="false">
    <dataSource type="SIMPLE">
    <property name="JDBC.Driver" value="org.gjt.mm.mysql.Driver"/>
    <property name="JDBC.ConnectionURL" value="<path to db>"/>
    <property name="JDBC.Username" value="..."/>
    <property name="JDBC.Password" value="..."/>
    ...
    </dataSource>
    </transactionManager>
    <sqlMap resource="your/package/FlatEntry.xml"/>
    </sqlMapConfig>

    • in FlatEntry.xml you may have:
    <sqlMap namespace="GetTheFlatEntry">
    <select id="getAuthor" resultClass="your.package.FlatEntry">
    SELECT D.ID as deptId,
    F.NAME as facultyName,
    S.GRADE as studentGrade,
    FROM DEPT D, FACULTY F, STUDENT S
    WHERE F.ID=D.DEP_ID AND S.GRADE>70
    AND F.ID = #value#
    </select>
    </sqlMap>

    deptId, facultyName and studentGrade are members of the FlatEntry class. Flat entry is just a POJO with getters and setters (no annotation needed)

    Now what do you see:
    • you have whatever class structure you want
    • you can give your own SQL query (you can actually edit it, like you'd do in JPA when using EntityManager.createNativeQuery(...))
    • you can map any field from any table with any member in your (flat) class; you can use non-flat classes as well (class-to-table mapping)
    • from somewhere in your code:
    ...
    static SqlMapClient sqlMapper;
    try{
    Reader reader = Resources.getResourceAsReader("ro/level7/webpublisher/service/content/db/SqlMapConfig.xml");
    static SqlMapClient sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
    reader.close();
    }catch(...){...}
    try{
    FlatEntry entry = (FlatEntry) sqlMapper.queryForObject("GetTheFlatEntry", <someValueForID if any>);
    }catch(...){}
    ...
    I hope it helps.
    errata:

    Reader reader = Resources.getResourceAsReader("your/pakage/SqlMapConfig.xml");
  • Paul Ianas
    Paul Ianas
    3 Posts

    Re: retrieving from multiple entities in JPA

    ‏2010-03-07T15:27:42Z  
    errata:

    Reader reader = Resources.getResourceAsReader("your/pakage/SqlMapConfig.xml");
    another error:
    <select id="getFlatEntry">