Native Inner Query join in EJB

EJB stands for Enterprise JavaBeans. There are two types of query one is Native Query and other is Named Query.

Named query means Java Persistence query language(JPQA). In Native query SQL is used to express database queries. An SQL INNER JOIN return all rows from multiple tables based on a common field between them. As a IDE Jdeveloper has been used for creating this application.(Download Application)

Steps::

1. Create a Custom Application and Generate Entities from tables for Person, Country, State and City tables (From HR schema)


2. Generate a new Session Bean with default options.


3. Create a java class which will be used for mapping native query result. Generate accessors for it (such as i have created PersonDetail class). This class should implements Serializable interface.


4. Create a method inside session bean.



   @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)  
   public List<PersonDetail> getInnerJoin() {  
        Query query = em.createNativeQuery("SELECT Person.id,Person.name,Country.name,State.name,City.name from Person INNER JOIN Country ON Person.COUNTRY_ID = Country.ID INNER JOIN State ON Person.STATE_ID = State.ID INNER JOIN City ON Person.CITY_ID = City.ID order by Person.id");  
        List<Person> list = query.getResultList();  
        Iterator person = list.iterator();  
        List<PersonDetail> personIterator = new ArrayList();  
        while (person.hasNext()) {  
              Object col[] = (Object[]) person.next();  
              PersonDetail test1 = new PersonDetail();  
               test1.setPersonId(new Integer(col[0].toString()));  
               test1.setPersonName(col[1].toString());  
               test1.setCountryName(col[2].toString());  
               test1.setStateName(col[3].toString());  
               test1.setCityName(col[4].toString());  
            personIterator.add(test1);  
          }  
     return (personIterator);  
   }  



5. Add this method into Session EJB, Local and Remote java file.
   List<PersonDetail> getInnerJoin();


6. Right click on SessionEJBBean, 


•  Select "Session Bean Client.."
•  Choose client type as "Java Client", finish it.


7. Add this code inside Session EJB Client



  for (PersonDetail person : (List<PersonDetail>) sessionEJB.getInnerJoin())  
            {  
                System.out.println(".................................................");  
                System.out.println("Person Id = "+person.getPersonId());  
                System.out.println("Person Name = "+person.getPersonName());  
                System.out.println("Country Name = "+person.getCountryName());  
                System.out.println("State Name = "+person.getStateName());  
                System.out.println("City Name = "+person.getCityName());  
                System.out.println(".................................................");  
            }  



8. First run session EJB bean and after that run Session EJB client.


9. Screen shot of output console