select from two tables using JPQL

Question

Hi

I m using JPQL to retrieve data. I can get data using the statement

List persons = null;
persons = em.createQuery("select p.albumName from PhotoAlbum p , Roleuser r 
where r = p.userId and r.userID = 1");

Now i can get the album names using this...

int i=0;
for (i=0;i<persons.size(); i++)
{   
     System.out.println("Testing n "+ i +" " +  persons.get(0));
}

Now I want to get the album name and the roleuser's row named "firstname"

I m using the query

persons = em.createQuery("select r.firstName , p.albumName from PhotoAlbum p ,   
Roleuser r where r = p.userId and r.userID = 1").getResultList();

Now how do get the rows firstname and albumname as the persons.get(0) is returning a object

by running the code : -

 for (i=0;i<persons.size(); i++)
    {
        //r = (Roleuser) persons.get(i);
        System.out.println("Testing n "+ i +" " + persons.get(i));
    }

i m getting this

Testing n 0 [Ljava.lang.Object;@4edb4077
INFO: Testing n 1 [Ljava.lang.Object;@1c656d13
INFO: Testing n 2 [Ljava.lang.Object;@46dc08f5
INFO: Testing n 3 [Ljava.lang.Object;@654c0a43

how do i map the persons.get(0) and get the firstname and albumname

thanks

Pradyut

India

Answer 1

Now how do get the rows firstname and albumname as the persons.get(0) is returning a object

Queries with multiple select_expressions in the SELECT clause return an Object[] (or a List of Object[]). From the JPA specification:

4.8.1 Result Type of the SELECT Clause

The type of the query result specified by the SELECT clause of a query is an entity abstract schema type, a state-field type, the result of an aggregate function, the result of a construction operation, or some sequence of these.

The result type of the SELECT clause is defined by the the result types of the select_expressions contained in it. When multiple select_expressions are used in the SELECT clause, the result of the query is of type Object[], and the elements in this result correspond in order to the order of their specification in the SELECT clause and in type to the result types of each of the select_expressions.

So in your case, you probably want something like this:

for (i=0;i<persons.size(); i++) {
    //r = (Roleuser) persons.get(i);
    System.out.println("Testing n " + i + " " + persons.get(i)[0] + ", " + 
        persons.get(i)[1]);
}

Note that specifying an inner join by the use of a cartesian product in the FROM clause and a join condition in the WHERE clause is less typical than specifying an explicit join over entity relationships (using the [LEFT [OUTER] | INNER ] JOIN syntax). See the whole section 4.4.5 Joins in the specification.

References

• JPA 1.0 Specification
  • Section 4.8.1 "Result Type of the SELECT Clause"
  • Section 4.8.2 "Constructor Expressions in the SELECT Clause"
  • Section 4.4.5 "Joins"