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

[sharmi prajapati]

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:

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.getRe[color=green] [/color]sultList();

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

            }

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:

SELECT NEW example.CountryAndCapital(c.name, c.capital.name)
FROM Country AS c

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:

package example;

public class CountryAndCapital {
    public String countryName;
    public String capitalName;
 
    public CountryAndCapital(String countryName, String capitalName) {
        this.countryName = countryName;
        this.capitalName = capitalName;
    }
}

The following code demonstrates running this query:

  String queryStr =
      "SELECT NEW example.CountryAndCapital(c.name, c.capital.name) " +
      "FROM Country AS c";
  TypedQuery<CountryAndCapital> query =
      em.createQuery(queryStr, CountryAndCapital.class);
  List<CountryAndCapital> results = query.getResultList();

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.