Liferay Development

Liferay Consulting

Liferay Training

Your Trusted Liferay Solutions Partner

Wednesday, February 13, 2013

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)

8 comments :

  1. Jay TrivediMarch 15, 2013 at 3:45 PM

    Best One. Nice Knowledge Shared Amongst. Thanks :)

    ReplyDelete
  2. hyd princeMarch 15, 2013 at 4:29 PM

    Thank you

    ReplyDelete
  3. Prakash KhanchandaniApril 9, 2013 at 12:41 PM

    Thanks for such a nice detailed tutorial, I have provided a link to your tutorial in one of my answers:

    How to fetch liferay entity through custom-finder in custom plugin portlet?

    Keep up the good work.

    ReplyDelete
  4. seeyaMay 22, 2013 at 10:46 AM

    Thanks for such a nice tutorial. I have a query though.
    I am displaying the data retrieved using join in custom query.
    How should I display it in the search container since
    1. I have a join between tables; and
    2. liferay search container has className where we need to supply the model class

    ReplyDelete
  5. hyd princeMay 22, 2013 at 1:21 PM

    HI when get data put data in Map object as key value. when we use in search container mention model class as Map and get columns by using Key.

    ReplyDelete
  6. seeyaMay 22, 2013 at 4:45 PM

    hyd prince:
    the problem that I am getting with map is that all the data of one entire field of database is displayed in each row of the specified column.
    for ex if i have a name to be displayed, all the names are displayed in each row..

    ReplyDelete
  7. narnar kaMay 23, 2013 at 2:46 PM

    Hi hyd prince,
    whene i get this "UNKNOWN ENTITY" what should i do because i don't understand what u mean by "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" because i have only one file "portlet-hbm.xml" could u please help me.

    ReplyDelete

Subscribe to: Post Comments ( Atom )

Recent Posts

Recent Posts Widget

Popular Posts