• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA - Joining of two tables

 
Terence Gronowski
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to join the tables Customer and DiscountCode with JPA (Netbeans 6.8, EJB 3.1, Glassfish 3).

I use the built in sample database.

Netbeans creates the following lines in the Entity-Bean (or better the Jpa-Bean, the bean corresponding to the table)

@JoinColumn(name="DISCOUNT_CODE", referencedColumnName= "DISCOUNT_CODE"
@ManyToOne(optional = false)
private DiscountCode discountCode

My question:

How can I make use of these statements. The result should be the Customer.name together with the Discount_Code.rate, i.e. the joining of two tables.

Is there some Named Query to make use of the above statements in the DiscountCode Class?
 
Terence Gronowski
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
in my client the code is as follows:





Error:

Exception Description: Error compiling the query [SELECT c FROM Customer c, DiscountCode d WHERE c.DISCOUNT_CODE= d.DISCOUNT_CODE], line 1, column 49: unknown state or association field [DISCOUNT_CODE] of class [ch.geo.jpa.Customer].
at org.eclipse.persistence.exceptions.JPQLException.unknownAttribute(JPQLException.java:457)
 
Terence Gronowski
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ups, there is an error in the foregoing post!

It should read:



The error is:

Column 'T1.DISCOUNT_CODE.T1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.DISCOUNT_CODE.T1' is not a column in the target table.
 
Terence Gronowski
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If nobody answers, I find the solution myself...

If you join two tables and want to display field name form table Customer and field discountCode from table DiscountCode you cannot either use Customer or Discount Code.

One solution is to read results in a List (of type Object). In this List there are Object[] arrays which can be read out:



 
Terence Gronowski
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some remarks to JPQL

The syntax of the joins in JPQL look unfamiliar.

SELECT c.name, d.rate FROM Customer c JOIN c.discountCode d WHERE d.rate>15

You seemingly must use an alias and c.discountCode after JOIN means the foreign key in the Table Customer and d means the primary key in Table discountCode.

Another example:

SELECT pub FROM Publisher pub JOIN pub.magazines mag WHERE pub.revenue (JPQL Language Reference)

The meaning

pub => select all fields from Table Publisher
JOIN pub.magazines mag => pub.magazines => Foreign key in Table Publisher
map => Primary Key in Table Magazines
 
sharmi prajapati
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Terence Gronowski wrote:If nobody answers, I find the solution myself...

If you join two tables and want to display field name form table Customer and field discountCode from table DiscountCode you cannot either use Customer or Discount Code.

One solution is to read results in a List (of type Object). In this List there are Object[] arrays which can be read out:





There is one more solution

Result Classes (Constructor Expressions)

JPA supports wrapping JPQL query results with instances of custom result classes. This is mainly useful for queries with multiple SELECT expressions, where custom result objects can provide an object oriented alternative to representing results as Object[] elements.

The fully qualified name of the result class is specified in a NEW expression, as follows:

This query is identical to the previous query above except that now the result list contains CountryAndCapital instances rather than Object[] elements.

The result class must have a compatible constructor that matches the SELECT result expressions, as follows:

The following code demonstrates running this query:

Any class with a compatible constructor can be used as a result class. It could be a JPA managed class (e.g. an entity class) but it could also be a lightweight 'transfer' class that is only used for collecting and processing query results.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic