0

I have 2 entities Addemp and Job.I want to join these 2 tables based on empid .empid is foreign key in job table and primary key in addemp table

here i am doing a search operation based on employee id .i am using criteria builder for search operation

the relationship here is manytoone

 public List<Object[]> findEmployeeList(Integer id)
{
    EntityManager em=null;
    try
    {

        em=getEntityManager();
          CriteriaBuilder cb=em.getCriteriaBuilder();
        CriteriaQuery cq=cb.createQuery(Addemp.class);
        Root<Addemp>rt= cq.from(Addemp.class);
        Join<Addemp,Job> job=rt.join(Addemp_.jobCollection);
        Predicate predicate=cb.equal(rt.get("empId"),id);
        cq.where(predicate);



       /* cq.select(rt.get("firstName"));
        cq.where (cb.equal(rt.<String>get("empId"),id));*/
         Query qry= em.createQuery(cq);
    return qry.getResultList();
user3227175
  • 29
  • 1
  • 10
  • what is your question? The query looks okay. You can use TypedQuery at the end: http://docs.oracle.com/javaee/6/api/javax/persistence/TypedQuery.html – pL4Gu33 Feb 10 '14 at 07:24
  • actually i am displaying the contents using DataModel and my code looks like this – user3227175 Feb 10 '14 at 09:54
  • public DataModel getEmpSearchModel() { if(empSearchModel==null){ empSearchModel=performEmpSearch(); } return empSearchModel; } public String getEmpRecord() { empSearchModel=getEmpSearchModel(); empSearchModel.toString(); return "added"; } – user3227175 Feb 10 '14 at 09:57
  • i am not able to display the contents empid which is in addemp table and emp_status which is in job table kindly help me out guys – user3227175 Feb 10 '14 at 09:59
  • Use TypedQuery "TypedQuery typed = em.createQuery(cq); return typed.getResultList();" than you have your object and you can work with it over point operator. Your method should look like: public List findEmployeeList(Integer id) .Look primcefaces showcase: http://www.primefaces.org/showcase/ui/datatableBasic.jsf how to access a pojo. – pL4Gu33 Feb 10 '14 at 10:09
  • i got my object as u said but what i have done is public DataModel performEmpSearch() { Integer id=emp.getEmpId(); String empname=emp.getFirstName(); // String ln=emp.getLastName(); // String job_title=job.getJobTitle(); // String emp_status=job.getEmpStatus(); return new ListDataModel(serv.searchEmp(id)); } public List searchEmp(Integer id){ initEmpJpa(); return EmpJp.findEmployeeList(id); } – user3227175 Feb 10 '14 at 10:16
  • As far as I can see you want to retrieve `empid` and `emp_status` columns from two seperate tables at once, therefore I would like to suggest you replacing `select()` with `multiselect()` in the query. Of course `CriteriaQuery` and `TypedQuery` will change their type parameters then (either `Tuple` or `Object[]` - depends on your preferences). – wypieprz Feb 10 '14 at 20:13
  • since i am new to jpa joins plz help me out by example of my query em=getEntityManager(); CriteriaBuilder cb=em.getCriteriaBuilder(); CriteriaQuery cq=cb.createQuery(Addemp.class); Rootrt= cq.from(Addemp.class); Join job=rt.join(Addemp_.jobCollection); cq.multiselect(rt.get("empId"),rt.get("empStatus")); Predicate predicate=cb.equal(rt.get("empId"),id); cq.where(predicate); – user3227175 Feb 11 '14 at 05:03
  • @user3227175: Did you try to build your query with JPQL or SQL? Just for quick prototyping and making sure that your persistence model (entities, relationships) is working as expected? – wypieprz Feb 12 '14 at 21:31
  • @wypieprz:i tried using jpql my query is working fine in mysql here is my jpql query which i have written query="Select a.empId,a.firstName,a.lastName,j.jobTitle,j.empStatus,j.subUnit"+ "FROM Job j,Addemp a"+ "WHERE a.empId = j.empId" the main thing is that i am doing a search operation that must be performed on 2 tables help me out – user3227175 Feb 13 '14 at 04:44
  • @user3227175: What you are actually trying to do in JPQL is _cartesian join_ between `Addemp` and `Job` entities, however the code snipped from the question makes _inner join_ between them. Which one is proper then? – wypieprz Feb 13 '14 at 08:52
  • @wypieprz:my main aim is to do search operation from both the Addemp and Job tables by passing parameters .i tried it both usin criteria builder and jpql .firstly i am doing search operation based on empid which is fk in job and pk in addemp table then i want to do search based on other parameters as well.plz help me out – user3227175 Feb 13 '14 at 10:20
  • @user3227175: I am still not sure what result are you expecting from your query, but the answer is: in case of _cartesian join_ you have to use two roots in your Criteria API (`Root` and `Root`), in case of _inner join_ you have to use one root and then join in your Criteria API (`Root` and `Join`) just like you did in the code snippet. Note that these two will give you different results. – wypieprz Feb 13 '14 at 10:38
  • @wypieprz:Is this query correct CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery cq = cb.createQuery(Addemp.class); Root EmpRoot = cq.from(Addemp.class); RootJobRoot=cq.from(Job.class); cq.select(EmpRoot.get("empId")); cq.select(JobRoot.get("empId")); cq.where(cb.equal(EmpRoot.get("empId"), id)); TypedQuery qr1 = em.createQuery(cq); return qr1.getResultList(); – user3227175 Feb 26 '14 at 05:56

1 Answers1

0

Based on the question and JPQL query from the above comments here is the criteria query proposal:

public List<Tuple> findEmployeeList(Integer id)
{
    em = getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<Addemp> empRoot = cq.from(Addemp.class);
    Root<Job> jobRoot = cq.from(Job.class);
    cq.multiselect(empRoot.get("empId").alias("id"), 
                   empRoot.get("firstName").alias("first"),
                   empRoot.get("lastName").alias("last"),
                   jobRoot.get("jobTitle").alias("title"),
                   jobRoot.get("empStatus").alias("status"),
                   jobRoot.get("subUnit").alias("subunit"));
    cq.where(cb.equal(empRoot.get("empId"), id));

    TypedQuery<Tuple> q = em.createQuery(cq);
    return q.getResultList();
}

Next you may want to extract a tuple result:

List<Tuple> tuples = service.findEmployeeList(2);
for (Tuple t : tuples) {
    StringBuilder builder = new StringBuilder(64)
        .append(t.get("id")).append(", ")
        .append(t.get("first")).append(", ")
        .append(t.get("last")).append(", ")
        .append(t.get("title")).append(", ")
        .append(t.get("status")).append(", ")
        .append(t.get("subunit"));
    System.out.println(builder.toString());
}

I hope it helps.

wypieprz
  • 7,981
  • 4
  • 43
  • 46
  • :i have 3 packages service,application,controller within jpa contoller i have written your criteria query which is in service package then within service package i have class called EmpService i am just calling a jpacontroller method EmployeeList like this public List searchEmp(int id){ initEmpJpa(); return EmpJp.findEmployeeList(id); } – user3227175 Mar 03 '14 at 04:58
  • :then again in EmpApp class i need get the values public String getEmpRecords() { List tuples = serv.searchEmp(2); for (Tuple t : tuples) { StringBuilder builder = new StringBuilder(64) .append(t.get("id")).append(", ") .append(t.get("first")).append(", ") .append(t.get("last")).append(", ") .append(t.get("title")).append(", "); .append(t.get("status")).append(", "); .append(t.get("subunit")); System.out.println(builder.toString()); } return null; } but illegal start of expression error – user3227175 Mar 03 '14 at 05:02
  • _Illegal start of expression_ is related to compilation error. I think you need to remove a semicolon in `append(", ");` methods because all of them are part of a single command so only one semicolon is required at the end. – wypieprz Mar 04 '14 at 09:46
  • the query is working fine how to iterate it jsf page and display it in h:datatable i tried it doing i am obtaning the values but they are getting dispalyed 3 times List searchResults; status=(String)t.get("status"); firstname=(String)t.get("first"); lastname=(String)t.get("last"); searchResults=new ArrayList(); searchResults.add(status); searchResults.add(firstname); searchResults.add(lastname); 3 times for 3 values and 2 times for 2 values – user3227175 Mar 05 '14 at 06:23
  • :and one more doubt if the employee id is primary key and i want search independently then empid and empstatus seperately is this possible ? and suggest me some material for jpa and jsf itegeration app – user3227175 Mar 05 '14 at 06:40
  • I would propose you to create a separate question regarding JSF and related issues since I have no experience with it. – wypieprz Mar 05 '14 at 10:11
  • :and one more doubt if the employee id is primary key and i want search independently then empid and empstatus seperately is this possible ? – user3227175 Mar 05 '14 at 10:14