Displaying related data from JPA query in JSF table component

[Futaleufu_John]

I have an existing web app built using J2EE 1.4, JSF 1.1, MySQL and Tomcat 6. The DB has 40 tables with lots of links between tables. The DB will soon grow to 60+ tables with even more links.

The current web app works fine - it's been up for almost a year - but managing inserts, updates and deletes is getting more and more complicated.

I'd like to migrate from data providers to JPA. So far I like what I've seen of JPA but I'm still evaluating it.

One problem is that the web app uses lots of table components that display information from related DB tables. That's easy to do with data providers. Not so easy to do with JPA, as far as I can tell.

Simple example:

I have a contact table whose fields are FKs to company (company name, website), address (address1, address2, city, state, zip code), and person (first name, last name, email) tables. I can execute a JPA query to get the contacts I want. I can bind the results to a table component. But I can't display any properties of the company, address or person objects. Winston suggested adding getters to the contact POJO, but that seems to be duplicating what's already there.

Does anyone have another way to solve this problem?

[Manuel Mall-2]

One possibility is to define a View at database level containing all the
fields you want and map that to a JPA entity. But I agree it could be argued
that this is also a form of duplication.

> -----Original Message-----
> From: Futaleufu_John [mailto:jhalupka@...]
> Sent: Friday, 3 October 2008 9:34 PM
> To: nbusers@...
> Subject: [nbusers] Displaying related data from JPA query in JSF table
> component
>
>
> I have an existing web app built using J2EE 1.4, JSF 1.1, MySQL and Tomcat
> 6.
> The DB has 40 tables with lots of links between tables. The DB will soon
> grow to 60+ tables with even more links.
>
> The current web app works fine - it's been up for almost a year - but
> managing inserts, updates and deletes is getting more and more
> complicated.
>
> I'd like to migrate from data providers to JPA. So far I like what I've
> seen
> of JPA but I'm still evaluating it.
>
> One problem is that the web app uses lots of table components that display
> information from related DB tables. That's easy to do with data providers.
> Not so easy to do with JPA, as far as I can tell.
>
> Simple example:
>
> I have a contact table whose fields are FKs to company (company name,
> website), address (address1, address2, city, state, zip code), and person
> (first name, last name, email) tables. I can execute a JPA query to get
> the
> contacts I want. I can bind the results to a table component. But I can't
> display any properties of the company, address or person objects. Winston
> suggested adding getters to the contact POJO, but that seems to be
> duplicating what's already there.
>
> Does anyone have another way to solve this problem?
> --
> View this message in context: http://www.nabble.com/Displaying-related-
> data-from-JPA-query-in-JSF-table-component-tp19797848p19797848.html
> Sent from the Netbeans - Users mailing list archive at Nabble.com.

[Futaleufu_John]

I hadn't thought of using views, but I may try that if I can't find another solution.

What bothers me is that the query results are an array of Contact POJOs. Each Contact POJO has a reference to an organization POJO, an address POJO, and a person POJO.

The table component's source data is:

    sourceData="#{SessionBean1.contacts} <=== array of Contact POJOs

If I select one of those objects, I can bind text fields as follows:

   text="#{SessionBean1.selectedContact.organization.organizationName}" <=== property of property

In the table component, the current row is also a Contact object. What I'd like to be able to do is:

   text="#{currentRow.value['organization.organizationName']}"

Instead, I have to define getOrganizationName in the Contact POJO which returns organization.getOrganizationName(). The binding in the table becomes:

    text="#{currentRow.value['organizationName']}"

Manuel Mall-2 wrote:

One possibility is to define a View at database level containing all the
fields you want and map that to a JPA entity. But I agree it could be argued
that this is also a form of duplication.

[nbnbnewb]

John,

I have written a VW JSF /JPA App recently, having done Winston's 2 tutorials
beginning with:-
http://www.netbeans.org/kb/60/web/web-jpa.html

My tables had related tables and I managed to use them without any problems.

I don't know if you are using the VW JSF framework, if so you may find them
useful.

Regards
Nick

-----Original Message-----
From: Futaleufu_John [mailto:jhalupka@...]
Sent: 03 October 2008 14:34
To: nbusers@...
Subject: [nbusers] Displaying related data from JPA query in JSF table
component


I have an existing web app built using J2EE 1.4, JSF 1.1, MySQL and Tomcat
6.
The DB has 40 tables with lots of links between tables. The DB will soon
grow to 60+ tables with even more links.

The current web app works fine - it's been up for almost a year - but
managing inserts, updates and deletes is getting more and more complicated.

I'd like to migrate from data providers to JPA. So far I like what I've seen
of JPA but I'm still evaluating it.

One problem is that the web app uses lots of table components that display
information from related DB tables. That's easy to do with data providers.
Not so easy to do with JPA, as far as I can tell.

Simple example:

I have a contact table whose fields are FKs to company (company name,
website), address (address1, address2, city, state, zip code), and person
(first name, last name, email) tables. I can execute a JPA query to get the
contacts I want. I can bind the results to a table component. But I can't
display any properties of the company, address or person objects. Winston
suggested adding getters to the contact POJO, but that seems to be
duplicating what's already there.

Does anyone have another way to solve this problem?
--
View this message in context:
http://www.nabble.com/Displaying-related-data-from-JPA-query-in-JSF-table-co
mponent-tp19797848p19797848.html
Sent from the Netbeans - Users mailing list archive at Nabble.com.

[Rick Fincher]

Since JPA uses pojos, can't you just create a new persistence entity
class that takes your two (or more) classes generated from your database
tables in the constructor and builds and populates the new class from
the sub-classes?

Rick

Nick Beare wrote:

> John,
>
> I have written a VW JSF /JPA App recently, having done Winston's 2 tutorials
> beginning with:-
> http://www.netbeans.org/kb/60/web/web-jpa.html
>
> My tables had related tables and I managed to use them without any problems.
>
> I don't know if you are using the VW JSF framework, if so you may find them
> useful.
>
> Regards
> Nick
>
> -----Original Message-----
> From: Futaleufu_John [mailto:jhalupka@...]
> Sent: 03 October 2008 14:34
> To: nbusers@...
> Subject: [nbusers] Displaying related data from JPA query in JSF table
> component
>
>
> I have an existing web app built using J2EE 1.4, JSF 1.1, MySQL and Tomcat
> 6.
> The DB has 40 tables with lots of links between tables. The DB will soon
> grow to 60+ tables with even more links.
>
> The current web app works fine - it's been up for almost a year - but
> managing inserts, updates and deletes is getting more and more complicated.
>
> I'd like to migrate from data providers to JPA. So far I like what I've seen
> of JPA but I'm still evaluating it.
>
> One problem is that the web app uses lots of table components that display
> information from related DB tables. That's easy to do with data providers.
> Not so easy to do with JPA, as far as I can tell.
>
> Simple example:
>
> I have a contact table whose fields are FKs to company (company name,
> website), address (address1, address2, city, state, zip code), and person
> (first name, last name, email) tables. I can execute a JPA query to get the
> contacts I want. I can bind the results to a table component. But I can't
> display any properties of the company, address or person objects. Winston
> suggested adding getters to the contact POJO, but that seems to be
> duplicating what's already there.
>
> Does anyone have another way to solve this problem?
>  

[Futaleufu_John]

I created a view containing the fields I wanted to access, but the Entity Classes from Database feature doesn't include the view in the list of available tables.

I did some research and it looks like NB 6.5 may support views. (I'm using NB 6.1.)

I have a couple of options.

(1) Use data providers for queries involving more than one DB table. Display the results in a table component. Select the row I want to edit with and use simple JPA queries to get the specific row from the related tables. Display that information in editable fields and use JPA to update those tables.

(2) Create an entity class by hand and see if I can get that to work with views.

Option 1 seems easier to me.

I'll keep you posted.

Manuel Mall-2 wrote:

One possibility is to define a View at database level containing all the
fields you want and map that to a JPA entity. But I agree it could be argued
that this is also a form of duplication.

[xcallejas]

Futaleufu_John wrote:

> (1) Use data providers for queries involving more than one DB table.
> Display the results in a table component. Select the row I want to edit
> with and use simple JPA queries to get the specific row from the related
> tables. Display that information in editable fields and use JPA to update
> those tables.

Hi,

I use this option in my VW project.

Queries for visual tables use data providers for visual web components.

Write (insert/update) operations are done by JPA.

rgds.

--
Xavier Callejas