Getting Data from Multiple tables in Liferay

Getting Data from Multiple tables in Liferay

Objective: The main objective of this document is to get data from multiple tables using liferay custom query mechanism.

Following are the steps how to write custom query in plug-in environment.

Step: 1

Create service.xml file for your entities which are required for your portlet. Then run service builder. You will get all configuration files and java classes for your service layer.

Step: 2

We need create one finder class. Please make sure finder class name should be XXXFinderIml.java under package youpackageName.service.persistence means under persistence folder of your plug-in portlet.

Here XXX is Entity name.

We need to implements one interface XXXFinder and we need to extend the BasePersistenceImpl class.

The following is the Example for Snippet

public class UserAddressFinderImpl extends BasePersistenceImpl implements UserAddressFinder { }

Note: Entity Name in Service.xml is: UserAddress

Step: 3

Write your sql query in one xml file and that xml file should be configure in defauld.xml file. Both files should be available in custom-sql directory this should be available in src directory of your portlet.

Src/custom-sql/default.xml

<?xml version="1.0" encoding="UTF-8"?> <custom-sql > <sql file="custom-sql/multipledata.xml"/> </custom-sql >

Src/custom-sql/multipledata.xml

<?xml version="1.0" encoding="UTF-8"?> <custom-sql> <sql id="multipleTableQueryId" > <![CDATA[ SELECT user_.*, multipletables_UserAddress.* FROM user_ AS user_ INNER JOIN multipletables_UserAddress AS multipletables_UserAddress ON multipletables_UserAddress.userId=user_.userId; ]]> </sql> </custom-sql>

Step: 4

Create method in XXXFinderImpl.java  and do following steps;

·         Open Session

·         Create query object by passing sql query as a String

·         Add entities for query object

·         Create QueryPosition instance to pass positional parameter for the query.

·         Call list () method over query object.

The following is Code for Custom SQL

public List getUserData() throws SystemException { public static String queryId = "multipleTableQueryId";                         Session session = null                         try {                         session = openSession();                         String sql = CustomSQLUtil.get(queryId);                         SQLQuery query = session.createSQLQuery(sql);                         query.addEntity("UserAddress",UserAddressImpl.class"));                         QueryPos qPos = QueryPos.getInstance(query);                         objectListUser=(List)query.list();                         objectList.add(objectListUser);                         session=openSession();                         query = session.createSQLQuery(sql);                         query.addEntity("UserAddress",UserAddressImpl.class);                         qPos = QueryPos.getInstance(query);                         return =query.list();                         }catch (Exception e) {                                     e.printStackTrace();                                     return null;                         }                         }

Step: 5

Use service method in EntityLocalServiceUtil

First we need to implement methods in EntityLocalServceImpl then we will run the service builder after we will get method in EntityLocalServiceUtil java class

Following is code

public class UserAddressLocalServiceImpl extends UserAddressLocalServiceBaseImpl { public List getUserData() throws SystemException {                         return UserAddressFinderUtil.getUserData();             } }

Step: 6

Now we can call custom sql implemented method in anywhere,

The following is code:

java.util.List userAddressList=UserAddressLocalServiceUtil.getUserData();

All the above procedure for normal Custom Sql Implamentation in plugin portlet.

Generally we have requirement to get the data from multiple tables which are in different places like it may be portal level or different plug-in portlets. The following are the scenarios we will get.

Scenarios:

1)      Get The data from multiple tables which are in same plugin portlet.

2)      Get the data from multiple tables which are available in portal level.

3)      Get the data from multiple tables which are available in portal level and Plugin portlet.

4)      Get the data from multiple tables which are in two different plugin portlets.

5)      Get the data from multiple tables which are in two different plugin portlets and portal.

Note: Above all scenarios consider for plug-in environment.

Get the data from multiple tables which are in same plugin portlet.

This is straight forward way we can achieve this. Because all entities are available in with plugin so that we can achieve this without any obstacles.

Get the data from multiple tables which are available in portal level.

In this scenario all the tables are available in Potlal level like User, Role and Group.

If we want get data among tables which are in portal level. Which is not much straight forward way, because we are writing custom query in plugin environment.

Approach:

If we want get data from multiple tables which are in Portle level we need open the portal session factory so that all the entities are available so that we can get the data.

Generally in plugin portlet when we open session we are using openSession() method. But if we use this method we will get sessionFactory object to respective plugin. If we use this session for portal level entities we get Exception saying UNKNOWN Entity.

Example:

public List getUserData() throws SystemException { Session session = null; try { session = openSession(); String sql = CustomSQLUtil.get(queryId); SQLQuery query = session.createSQLQuery(sql); query.addEntity("User_",PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl")); QueryPos qPos = QueryPos.getInstance(query); objectListUser=(List)query.list(); objectList.add(objectListUser); session=openSession(); query = session.createSQLQuery(sql); query.addEntity("UserAddress",UserAddressImpl.class); qPos = QueryPos.getInstance(query); return query.list(); }catch (Exception e) { e.printStackTrace(); return null; } }

Problem:

If see the above code we have used the openSession() method. So that it will open the current portlet session. But in above scenarios’ we are adding entity which is available in portal level that is UserImpl class. Because of this we will get Unknown Enity exception.

Whenever we use the portal level entities we need to specify the Portal class loader. The following is the code for load the class from portlal.

query.addEntity("User_", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));

PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl") is code for load portal level classes.

Solution:

To resolve above Problem We need get sessionFactory Object of portal. The following is the code for getting portalSession factory object.

private static SessionFactory  sessionFactory = (SessionFactory)PortalBeanLocatorUtil.locate("liferaySessionFactory"); session = sessionFactory.openSession();

The following is code for to get the data from multiple tables which are available in portal.

public List getUserData() throws SystemException { private static SessionFactory  sessionFactory =     (SessionFactory)PortalBeanLocatorUtil.locate("liferaySessionFactory");                         Session session = null;                         try {                         session = sessionFactory.openSession();                         String sql = CustomSQLUtil.get(queryId); SQLQuery query = session.createSQLQuery(sql); query.addEntity("User_",PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));                         QueryPos qPos = QueryPos.getInstance(query);                         objectListUser=(List)query.list();                         objectList.add(objectListUser);                         session=openSession();                         query = session.createSQLQuery(sql);                         query.addEntity("UserAddress",UserAddressImpl.class);                         qPos = QueryPos.getInstance(query);                         return query.list();                         }catch (Exception e) {                                     e.printStackTrace();                                     return null;                         }             }

Get the data from multiple tables which are available in portal level and Plugin portlet.

In this scenario we need get the data from multiple tables and which are available in portal level and plugin portlet.

Problems:

If we use the portlet sessionFactory object then we will get UnknownEntity  exception for Portal level Entities.

Example:

Session=openSession(); SQLQuery query = session.createSQLQuery(sql); query.addEntity(“UserAddress”,UserAddress.class); query.addEntity("User_",PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));

In above scenario if we add UserImpl class we will get UserImpl  is UnknownEntity because we are opened the session related to portlet sessionFactory.

private static SessionFactory  sessionFactory = (SessionFactory)PortalBeanLocatorUtil.locate("liferaySessionFactory"); session = sessionFactory.openSession(); query.addEntity(“UserAddress”,UserAddressImpl.class); query.addEntity("User_",PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));

In above scenario if we add UserAddressImpl class we will get UserAddressImpl  is UnknownEntity because we are opened the session related to portal  sessionFactory.

Similarly the following canaries also will get same problems.

·         Get the data from multiple tables which are in two different plugin portlets.

·         Get the data from multiple tables which are in two different plugin portlets and portal.

Note: I could not find the solution for the above problem.

But I did work around for the all above scenarios.

Work Around: 1

Use Two Session factory objects in Single custom Sql Method.

1)      First get the Portlet session  and add Portlet Level class

2)      The get the list .

3)      Next get Portal session Factory Object.

4)      Add Portal Level Entity.

The following code will give Better Understating.

public List getUserData() throws SystemException { public static String queryId = "multipleTableQueryId";             private static SessionFactory sessionFactory = (SessionFactory)PortalBeanLocatorUtil.locate("liferaySessionFactory");                         Session session = null;                         List objectListUserAddress=new ArrayList();                         List objectListUser=new ArrayList();                         List objectList=new ArrayList();                         try {                         session = sessionFactory.openSession();                         System.out.println("======================="+session);                         String sql = CustomSQLUtil.get(queryId);                                                 SQLQuery query = session.createSQLQuery(sql);                         query.addEntity("User_", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));                         QueryPos qPos = QueryPos.getInstance(query);                         objectListUser=(List)query.list();                         objectList.add(objectListUser);                         session=openSession();                         query = session.createSQLQuery(sql);                         query.addEntity("UserAddress",UserAddressImpl.class);                         qPos = QueryPos.getInstance(query);                         objectListUserAddress=(List)query.list();                         objectList.add(objectListUserAddress);                                                                         return objectList;                         }catch (Exception e) {                                     e.printStackTrace();                                     return null;                         }                                                 }

Retrieving of objects in JSP page

java.util.List userAddressList=UserAddressLocalServiceUtil.getUserData(); try{ List userObjectList=(List)userAddressList.get(0);             List userAddressObjectList=(List)userAddressList.get(1);             User userObject=(User)userObjectList.get(0);             out.println("Use Email Id"+userObject.getEmailAddress());             UserAddress userAddressObject=(UserAddress)userAddressObjectList.get(0);             out.println("Use Address"+userAddressObject.getUserAddress());            }catch(Exception e){             e.printStackTrace(); }

Work Around: 2

It is Combination of Serialization and JSON concepts.

Step: 1

We need write query in xml file

Example:

SELECT user_.emailAddress, multipletables_UserAddress.userAddress FROM user_ AS user_ INNER JOIN multipletables_UserAddress AS multipletables_UserAddress ON multipletables_UserAddress.userId=user_.userId;

We need write query for required columns.

Step: 2

We need to write custom sql method that should give the Object list  but any specific type list.

The following is the code.

public List getAllUserData() throws SystemException {                         Session session = null;                         try {                                         session=openSession();                         SQLQuery query = session.createSQLQuery(sql);                         QueryPos qPos = QueryPos.getInstance(query);                         return (List)query.list();                         }catch (Exception e) {                                     e.printStackTrace();                                            return null;                         }                                                 }

Note: we should not add any EnityImple classes for query.

Step: 3

In the step 2 we will get List having objects. Each object has the data related to multiple tables.

Now we have to serialize the each object and we convert as JOSON Array.

The following is code for serialize and covert as JSON Array.

java.util.List allUserDetailsList=UserAddressLocalServiceUtil.getAllUserData(); JSONArray jsonArraytObject=JSONFactoryUtil.createJSONArray(JSONFactoryUtil.serialize(allUserDetailsList.get(0))); out.println("Email "+jsonArraytObject.getString(0)); out.println("Address "+jsonArraytObject.getString(1));

Work Around: 3

            This is another  work around for getting data from multiple tables. We already know we have two session factory object based on session factory it can load EntityImpl clasess.

If we use portlet session factory we can load only portlet level entity impl classes in custom sql. If we use portal session factory we can load only portal entity impl classes. This is because of we are using multiple session factories.

We have two liferayHibernateSessionFactory configurations for liferay portal and plugin portlet.

The following are the Configuration we can found in hibernate-spring.xml. Life ray portal having hibernate-spring.xml file and each plugin portlet have it own hibernate-spring.xml file.

For Portal the following is configuration:

This is in portal/portal-impl/src/META-INF/hibernate-spring.xml

<bean id="liferayHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortalHibernateConfiguration">                         <property name="dataSource" ref="liferayDataSource" />             </bean>

For Plugin portlet the following is configuration:

This is in docroot/WEB_INF/src/META-INF/hibernate-spring.xml

<bean id="liferayHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration">                         <property name="dataSource" ref="liferayDataSource" />             </bean>

If observe the bean classes for portlal and portlet are following.

1)      com.liferay.portal.spring.hibernate.PortalHibernateConfiguration

2)      com.liferay.portal.spring.hibernate.PortletHibernateConfiguration

What will happen in Portal?

In portal com.liferay.portal.spring.hibernate.PortalHibernateConfiguration class get the mapping configuration from portal-hbm.xml, mail-hbm.xml and ext-hbm.xml  this configuration are available in portal.properties file as  following

hibernate.configs=\

        META-INF/mail-hbm.xml,\

        META-INF/portal-hbm.xml,\

        META-INF/ext-hbm.xml

Load configuration code snippet:

protected String[] getConfigurationResources() {                         return PropsUtil.getArray(PropsKeys.HIBERNATE_CONFIGS);             }

So this portal class get the hibernate config files from above mentioned property in portal.properties. Because of this when we open  session related to Portal session factory it will load all entities which are available in above mentioned file if any entity which is not configured in above mention file it will throw exception like UNKNOWN ENTITY.

What will happen in plugin portlet?

In portal com.liferay.portal.spring.hibernate.PortletlHibernateConfiguration class get the mapping configuration from portlet-hbm.xml only. Which related to only that plugin portlet. This is hard coded in PortletHibernateConfiguration class as follows.

protected String[] getConfigurationResources() {                         return new String[] {"META-INF/portlet-hbm.xml"};             }

Because of this if entity which not configured in above file then it will through the UNKNOWNENTITY exception for portlet session factory.

How we can get data  from Multiple tables which are in Portal and Portlet?

Solution:

This is also work around I successfully done this.

·         Assume If we want get data from User Table And our local tables means portlet level table.

·         Fist run the service builder and Create custome sql for your requirement.

·         Add the portal entity and portlet entities for your qury in custome sql method.

·         Deploy  the application

·         Now you will get Unknown Entity for UserImpl.

·         When you get this exception you just copy User table hbm configuration from portal-hbm.xml file and add this configuration to your portlet-hbm.xml.

·         Now you can get the data from User table and your local table.

Note: Once you add this configuration you should not run service builder. If you run service builder again you need add.

Important Points.

1)      We have Different Session Factory Objects. For Portal we have portal session factory object and for each port let have its own session factory.

2)      To open Session in plug-in portlet related to portal we have to use the following code.

private static SessionFactory  sessionFactory = (SessionFactory)PortalBeanLocatorUtil.locate("liferaySessionFactory");

session = sessionFactory.openSession();

3)      To open session related to respective portlet in plug-in portlet. Directly use the opneSession() method.

4)      To load Portal level class in plugin portlet we have use following method.

PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl")

5)      To load class which is available in other portlet we have to use the following code.

ClassLoader classLoader = (ClassLoader)PortletBeanLocatorUtil.locate(ClpSerializer.SERVLET_CONTEXT_NAME,"portletClassLoader");

classLoader. loadClass("your portlet class name with fully qualified name");

6)      Sterilize object use the following code.

JSONFactoryUtil.serialize(Object)