Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JPA multiple select query

Tags:

jpa

jpa-2.0

I have a query like this, how can I get my entity type from the list? It is returning like object,but when I cast to my Entity it does not cast.

my tables: B(id_A, id_C) , A(id_A,name,location) C (id_C,......)

        String queryCompany = "select s.name,s.location from B b," +
                " A s where b.bPK.id_A=s.id_A " +
                "and b.PK.id_C= :idEvent";

        Query queryGetCompany = this.entityManager.createQuery(queryCompany);
        queryGetCompany.setParameter("idEvent",c.getID());

        List companyList = queryGetCompany.getResultList(); 
        //how can I get A.name A.location from this list?

Also, is this a good way to do my query ?

like image 929
Shahriar Avatar asked Jul 27 '11 09:07

Shahriar


1 Answers

First: What kind of List is your result "companyList"? From your query you get a List of lists and would have to say

    companyList.get(i).get(0) // for name
    companyList.get(i).get(1) // for location

For easier use you might also put some more effort into it at the beginning. Then you can have a practical, object-orientated structure.

For this, use a kind of DTO (data transfer object) whose class might look like:

    class CompanyDTO{

      public CompanyDTO(String name, String location){
        this.name = name;
        this.location = location;
      }

      private String name;
      private String location;

      public String getName(){
        return this.name;
      }
      public String getLocation(){
        return this.location;
      }
    }

Then you can say in your query:

    select new CompanyDTO (s.name, s.location) 
    from B b, A s 
    where b.bPK.id_A = s.id_A and b.PK.id_C= :idEvent

Then you can say:

    List<CompanyDTO> companyList = queryGetCompany.getResultList();

and later get all names and locations by

    for (CompanyDTO company : companyList){
      System.out.print(company.getName() + ", ");
      System.out.println(company.getLocation());
    }

or other List-operations. You will get each DTO from the List and can call the get-Methods on these objects.

like image 65
Jana Avatar answered Sep 28 '22 09:09

Jana