data table question

[mkabir]

I have a master and detail table.

I have a entityquery to query the master.

In the data table , I display the master data. I would also like to display the detail data.

What is the best way to display the detail.

1. Join master and detail. This makes the number of master records inaccurate.

2. Use the data table variable to get the detail. master.getDetails returns a set. A list is needed

Any other ideas?

Thanks,

Kabir

[skunk]

Hello all,

I have the same question as Kabir above. The entities in the database I am designing have many time dependent properties to maintain an audit history. This means to show information as basic as an Organisations name involves using at least two database tables - organisations and organisation_names.

Is there a recommended way of displaying a list of Organisations and their details from multiple database tables in nice HTML tables? These are two methods I have explored so far:

1) Simple query of the main object.

SELECT * FROM organisations

Then when iterating through the resultList access its related properties using expression language:

<h:column>
    #{organisation.currentName}
</h:column>

However, if I want to retrieve five properties per main object and there are 50 rows shown in the table, hibernate will have to do 501 queries to render the page! Also, as in my case these properties are time dependent, I could not leave hibernate to retrieve the correct versions itself. I think I would need to specify my own queries in the getters for the Organisations properties. Is there a simple solution to this?

2) Complicated query to get all information in one go (or use a database view).

My orm.xml:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings     xmlns="http://java.sun.com/xml/ns/persistence/orm"
                         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                         xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
                         version="1.0">
     <named-native-query name="Organisation.All" result-set-mapping="OrganisationListWithNames">
          <query>SELECT o.organisation_id, o.organisation_details_id, n.organisation_name_id, n.organisation_id, n.ccr_id, n.name, n.nickname
               FROM organisations o LEFT JOIN organisation_names n ON n.organisation_id = o.organisation_id</query>
          <hint name="org.hibernate.readOnly" value="true" />
     </named-native-query>
     <sql-result-set-mapping name="OrganisationListWithNames">
          <entity-result entity-class="$my-domain-removed.entity.Organisation">
               <field-result name="id" column="organisation_id" />
               <field-result name="details" column="organisation_details_id" />
          </entity-result>
          <entity-result entity-class="$my-domain-removed.entity.OrganisationName">
               <field-result name="id" column="organisation_name_id" />
               <field-result name="organisation" column="organisation_id" />
               <field-result name="ccr" column="ccr_id" />
               <field-result name="name" column="name" />
               <field-result name="nickName" column="nickname" />
          </entity-result>
     </sql-result-set-mapping>
</entity-mappings>

Here I have created a NativeNamedQuery which gets all the columns necessary from two tables for two entities. The sql-result-set-mapping node and its children then defines how these columns are mapped into the classes. I am not entirely sure why this is necessary, as it's duplication of information I have attached in @Column(name=xyz) annotations, but it does work. The <query> is a placeholder for one about 100 lines longer.

I then added another method to a seam-generated organisationList.java (just for the convenience of using its .getEntityManager():

public Collection<?> getAllOrganisations() {
     return this.getEntityManager().createNamedQuery("Organisation.All").getResultList();     
}

Finally, you have to do something with whatever is returned in your view:

<h:dataTable id="organisationList" var="objArray"
          value="#{organisationList.allOrganisations}" 
          rendered="#{not empty organisationList.allOrganisations}">
     <h:column>
          <f:facet name="header">Id</f:facet>
          #{objArray[0].id}
     </h:column>
     <h:column>
          <f:facet name="header">Name</f:facet>
          #{objArray[1].name}
     </h:column>
</h:dataTable>

As the native sql query returned lots of columns per row, and I used an sql-result-set-mapping to link many entities, hibernate has given me back an array of objects per row. The first object of each array (objArray[0]) is my Organisation and the second (objArray[1]) is the related OrganisationName.

Questions!

Is either of these approaches recommended or is there a better way altogether which I have missed. If method #2 is the best way, because it is hopefully more database efficient, can writing the lengthy sql-result-set-mapping be avoided, as-well-as the ugly object array syntax?

Thanks!

Dave

[damianharvey.damianharvey.gmail.com]

For option (1) have you tried the fetch join from Hibernate?

From Hibernate docs

a fetch join allows associations or collections of values to be initialized along with their parent objects, using a single select.

Cheers,

Damian.

[skunk]

Thanks for your post Damian.

That looks like a very cool feature and I will remember it for use elsewhere, but I don't think it helps me out here.

I could use that to fetch an Organisation and all the OrganisationNames it has had during its history in our database with one ejb-ql query. However, it doesn't say which of those names is the current one - notionally a private OrganisationName currentName property in the Organisation class.

The only way round this I can think of is having code in a getCurrentName method to sift through the collection of names hibernate will fetch to find the current one (based on which has the most recent 'change control request', another database table). This would be slow as you would have to iterate through all the objects rather than using database indexes so this puts me back on doing individual queries for each organisation to find its latest name and retrieving it into an @Transient property. That would then require having a persistence context in each of the entity classes I need to get time dependent properties for.

I could use database views so my entities would believe there was a real field in the Organisations for the current name, but that wouldn't help me for the other properties where there can be a Collection of currently valid objects.

I am stuck.

[damianharvey.damianharvey.gmail.com]

In your query why can't you join Organisation, OrganisationName and your ChangeControlRequest tables?

Cheers,

Damian.

[skunk]

Would that not then have all the names, not just the correct one? Or, if I were to select multiple objects like

select o, n, c
from Organisation as o
left join o.names as n
left join n.ccr as c

then I might be able to work out how to restrict that to only current names, but I would get back a list of object arrays which I am trying to avoid. Ideally I want to have hibernate somehow fill a Organisation.currentName property from the currently valid member of Organisation.names without me having to write a load of code to do it.

Otherwise I am thinking it would be best to have my own OrganisationHome class that could fill these temporal properties with extra queries, but still hide the complexity from the facelet (which might be written by someone else).

I'm sorry if I'm being particularly slow, I'm new to seam and java and programming in general.

Thanks,
Dave

Here is a mysql query which gets an Organisation and its current name for reference:

SELECT o.*, n.*
FROM organisations o
LEFT JOIN organisation_names n ON o.organisation_id = n.organisation_id
LEFT JOIN ccrs c ON c.ccr_id = n.ccr_id
WHERE n.organisation_id = 2
AND c.completed = TRUE
AND effective < NOW()
AND effective = (
     SELECT MAX(effective) FROM ccrs c2
     LEFT JOIN organisation_names n2 ON c2.ccr_id = n2.ccr_id
     WHERE n2.organisation_id = n.organisation_id
)

[damianharvey.damianharvey.gmail.com]

Can you show me your Entity Beans? It's more than likely that your OrganisationName Bean has a field for Organisation. If this is the case you only need to select the OrganisationName eg

select n from Names n
left join n.ccr as c
where n.organisation.id = 2
and c.completed = true
etc....

and then refer to the Organisation like name.organisation (or similar).

Alternately read up on how Hibernate can instantiate a new class from a query. You would create a Bean to represent the Organisation, Name and CCR (eg. called OrganisationNameBean that takes Organisation, Name and CCR in a constructor) and populate it like:

select new OrganisationNameBean(o, n, c) 
from Organisation as o
left join o.names as n
left join n.ccr as c
etc....

Cheers,

Damian.

[skunk]

Hi Damian,

You were right, my OrganisationName entity does have a field for the Organisation it applies to, so your first solution works great, thank you very much!

The query has got a bit longer, because Organisations and not just their names need their creation and closure audited with the possibility of time based views but I think this will save me a lot of time writing application code.

Thanks again,

Dave

select name from OrganisationName name
left join name.ccr as name_ccr
left join fetch name.organisation org
left join org.organisationCcrs org_ccr
left join org_ccr.ccr org_ccr_ccr
left join org_ccr.ccrOperation as org_ccr_op
where name_ccr.effective = (
     select max(name_ccr_l.effective) from OrganisationName name_l
     left join name_l.ccr as name_ccr_l
     where name_l.organisation.id = name.organisation.id
     and name_ccr_l.completed = true
     and name_ccr_l.effective < :currentDate
)
and org_ccr_ccr.effective = (
     select max(org_ccr_ccr_l.effective) from OrganisationCcr org_ccr_l
     left join org_ccr_l.ccr as org_ccr_ccr_l
     where org_ccr_l.organisation.id = name.organisation.id
     and org_ccr_ccr_l.completed = true
     and org_ccr_ccr_l.effective < :currentDate
)
and org_ccr_op.id in (1)

[skunk]

Hi Damian,

I tried using Hibernate to instantiate a new class for some different objects where it seemed more appropriate, but am having trouble with performance.

The view is of a list of Ccr objects each of which has a collection of CcrAudit objects. In each of these collections, one CcrAudit is the first and another the last (by time). The collection must contain at least one of these, so sometimes the first and last will be the same.

I am trying to use the query below to create a list of helper entities which I can iterate through in a JSF table:

select new domain.removed.mud.helperentity.CcrListItem(ccrAuditFirst, ccrAuditLast, ccr)
from Ccr ccr
left join ccr.ccrAudits ccrAuditFirst
left join ccr.ccrAudits ccrAuditLast
where ccrAuditFirst.timeStamp = (
     select min(ccrAudit_l.timeStamp) from CcrAudit ccrAudit_l
     left join ccrAudit_l.ccr ccr_l
     where ccr_l = ccr
)
and ccrAuditLast.timeStamp = (
     select max(ccrAudit_l.timeStamp) from CcrAudit ccrAudit_l
     left join ccrAudit_l.ccr ccr_l
     where ccr_l = ccr
)

This works, but unfortunately the first query hibernate performs selects only the id field for each of the three objects. Hundreds of queries are then used to get all the properties meaning the page takes about 3 seconds instead of ~50ms to load (there are only currently 25 rows in this table).

Am I doing something obviously wrong in my query?

[damianharvey.damianharvey.gmail.com]

I don't think that you're doing anything wrong but you may want to read up on, and experiment with, the fetch join. have a read of chapter 7.3 of the Hibernate EntityManager docs

You may be able to use this in your query eg:

select new domain.removed.mud.helperentity.CcrListItem(ccrAuditFirst, ccrAuditLast, ccr)
from Ccr ccr
left join fetch ccr.ccrAudits ccrAuditFirst
left join fetch ccr.ccrAudits ccrAuditLast
etc

Cheers,

Damian.

[skunk]

Hello, thanks for your reply again.

I had tried using fetch joins but it seems that they don't work in conjunction with select new Object() (see link below). I have also tried rewriting the query as a cartesian product (i.e. select new Object(x, y, z) from x, y, z where x.y = y and x.z = z) but without any change in the queries hibernate executes.

Since this is a hibernate question I have added it to a topic in the Hibernate Forum

Thanks,

Dave

[cash1981]

David Beaumont wrote on May 13, 2008 12:35:

<h:dataTable id="organisationList" var="objArray"
          value="#{organisationList.allOrganisations}" 
          rendered="#{not empty organisationList.allOrganisations}">
     <h:column>
          <f:facet name="header">Id</f:facet>
          #{objArray[0].id}
     </h:column>
     <h:column>
          <f:facet name="header">Name</f:facet>
          #{objArray[1].name}
     </h:column>
</h:dataTable>

This is exactly what I am wondering too.
If anybody knows please let me know.

[skunk]

Shervin Asgari wrote on Jun 03, 2008 15:46:


This is exactly what I am wondering too.
If anybody knows please let me know.

Hi Shervin, I think that the forum must have mangled your question or else I have misunderstood, The example code I gave with named-native-queries and list of object arrays in the views does work, despite not being very tidy.

Dave