JPA - Joining of two tables

[Terence Gronowski]

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]

in my client the code is as follows:

query = entityManager.createQuery("SELECT c FROM Customer c, DiscountCode d WHERE c.DISCOUNT_CODE= d.DISCOUNT_CODE", Customer.class);
list = query.getResultList();
 for (Customer cl : list) {
out.println(cl.getName() + "<br />");
}

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]

ups, there is an error in the foregoing post!

It should read:

Manager.createQuery("SELECT c, d FROM Customer c, DiscountCode d WHERE c.discountCode= d.discountCode", Customer.class);

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]

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:

query = entityManager.createQuery("SELECT c.name, d.rate FROM Customer c JOIN c.discountCode d");
            out.println("JOIN MIT DYNAMIC QUERY mit OBJECT-Array<br />");
            List resultat = query.getResultList();

            int count = 0;
            for (Iterator i = resultat.iterator(); i.hasNext();) {
                Object[] values = (Object[]) i.next();
                out.println(++count + ": " + values[0] + ", " + values[1] + "<br />");

            }

[Terence Gronowski]

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