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)

39 comments :

  1. Best One. Nice Knowledge Shared Amongst. Thanks :)

    ReplyDelete
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. keep up the good work. this is an Ossam post. This is to helpful, i have read here all post. i am impressed. thank you. this is our machine learning courses
    machine learning courses | https://www.excelr.com/machine-learning-course-training-in-mumbai

    ReplyDelete
  8. Attend The Machine Learning Course Bangalore From ExcelR. Practical Machine Learning course Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Machine Learning course Bangalore.
    Machine Learning Course Bangalore

    ReplyDelete
  9. Really impressed! Everything is very open and very clear clarification of issues. It contains truly facts. Your website is very valuable. Thanks for sharing.

    Data Science Course

    ReplyDelete
  10. This post is very simple to read and appreciate without leaving any details out. Great work!

    Data Science Training

    ReplyDelete
  11. Such a very useful article. Very interesting to read this article. I would like to thank you for the efforts you had made for writing this awesome article.
    Data Science Course in Pune
    Data Science Training in Pune

    ReplyDelete
  12. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data Science Institute in Bangalore

    ReplyDelete
  13. This post is good enough to make somebody understand this amazing thing, and I’m sure everyone will appreciate this interesting things.
    Data Science Course in Bangalore

    ReplyDelete
  14. Great tips and very easy to understand. This will definitely be very useful for me when I get a chance to start my blog.
    Data Science Training in Bangalore

    ReplyDelete
  15. I feel really happy to have seen your web page and look forward to so many more entertaining times reading here. Thanks once more for all the details.
    Data Science Training in Hyderabad | Data Science Course in Hyderabad

    ReplyDelete
  16. I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read!! I definitely really liked every part of it and i also have you saved to look at new information in your site.
    Learn best training course:
    Business Analytics Course in Hyderabad
    Business Analytics Training in Hyderabad

    ReplyDelete
  17. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    Data Science Course in Bangalore

    ReplyDelete
  18. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    Data Science Training in Bangalore

    ReplyDelete
  19. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Data Analytics Course in Pune
    Data Analytics Training in Pune

    ReplyDelete
  20. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    Ethical Hacking Course in Bangalore

    ReplyDelete
  21. Wow! Such an amazing and helpful post this is. I really really love it. I hope that you continue to do your work like this in the future also.
    Ethical Hacking Training in Bangalore

    ReplyDelete
  22. I am impressed by the information that you have on this blog. Thanks for Sharing
    Ethical Hacking in Bangalore

    ReplyDelete
  23. I want to thank you for your efforts in writing this article. I look forward to the same best job from you in the future.

    360DigiTMG Data Science Courses

    ReplyDelete
  24. Good blog and absolutely exceptional. You can do a lot better, but I still say it's perfect. Keep doing your best.

    360DigiTMG Data Science Certification

    ReplyDelete
  25. Thanks for provide great informatics and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you
    DevOps Training in Chennai

    DevOps Online Training in Chennai

    DevOps Training in Bangalore

    DevOps Training in Hyderabad

    DevOps Training in Coimbatore

    DevOps Training

    DevOps Online Training

    ReplyDelete
  26. Found your post interesting to read. I cant wait to see your post soon. Good Luck for the upcoming update. This article is really very interesting and effective, data sciecne course in hyderabad

    ReplyDelete
  27. Wonderful blog found to be very impressive to come across such an awesome blog. I should really appreciate the blogger for the efforts they have put in to develop such an amazing content for all the curious readers who are very keen of being updated across every corner. Ultimately, this is an awesome experience for the readers. Anyways, thanks a lot and keep sharing the content in future too.

    360DigiTMG Tableau Course

    ReplyDelete
  28. Thanks for the Information.Interesting stuff to read.Great Article.
    I enjoyed reading your post, very nice share.
    Data Science Course Training in Hyderabad

    ReplyDelete
  29. This is my first time visit here. From the tons of comments on your articles.I guess I am not only one having all the enjoyment right here! ExcelR Business Analytics Course

    ReplyDelete
  30. There is no dearth of Data Science course syllabus or resources. Learn the advanced data science course concepts and get your skills upgraded from the pioneers in Data Science.
    data science course bangalore
    data science course syllabus

    ReplyDelete
  31. Fantastic article with valuable and top quality information thanks for sharing.
    Data Science Course in Hyderabad

    ReplyDelete
  32. I have to search sites with relevant information ,This is a
    wonderful blog,These type of blog keeps the users interest in
    the website, i am impressed. thank you.
    pmp training in bangalore

    ReplyDelete
  33. I was very happy to find this site. I wanted to thank you for this excellent reading !! I really enjoy every part and have bookmarked you to see the new things you post.

    Business Analytics Course in Bangalore

    ReplyDelete
  34. I am delighted to discover this page. I must thank you for the time you devoted to this particularly fantastic reading !! I really liked each part very much and also bookmarked you to see new information on your site.

    Data Analytics Course in Bangalore

    ReplyDelete

Recent Posts

Recent Posts Widget

Popular Posts