How to get liferay object through custom search in plugins custom portlet?

Question

How can we get liferay objects through custom search using custom SQL?

• Below is my sql query written in default.xml ( _{). I trimmed the request to the minimum minimum so that the logic remains simple. Since it includes several functions and associations, we could not use the DynamicQuery API} ):

   SELECT grp.* FROM Group_ WHERE site = 1 AND active_ = 1 AND type_ <> 3 

• Relevant code in MyCustomGroupFinderImpl.java :

   Session session = null; try { session = openSession(); // fetches the query string from the default.xml String sql = CustomSQLUtil.get(FIND_ONLY_ACTIVE_SITES); SQLQuery sqlQuery = session.createSQLQuery(sql); sqlQuery.addEntity("Group_", GroupImpl.class); // sqlQuery.addEntity("Group_", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.GroupImpl")); return (List<Group>) QueryUtil.list(sqlQuery, getDialect(), 0, QueryUtil.ALL_POS); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } 

This code above will not work, since the GroupImpl class GroupImpl present in portal-impl.jar , and this jar cannot be used in the user portlet.

I also tried using sqlQuery.addEntity("Group_", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.GroupImpl"))
But this code above raises an exception:

 com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.MappingException: Unknown entity: com.liferay.portal.model.impl.GroupImpl 

But the same code works for our custom object if we write sqlQuery.addEntity("MyCustomGroup", MyCustomGroupImpl.class); .

thanks

Answer 1

I learned from the forum thread liferay , which instead of session = openSession(); we will need to get the session from liferaySessionFactory as follows so that it works:

 // fetch liferay session factory SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("liferaySessionFactory"); Session session = null; try { // open session using liferay session factory session = sessionFactory.openSession(); // fetches the query string from the default.xml String sql = CustomSQLUtil.get(FIND_ONLY_ACTIVE_SITES); SQLQuery sqlQuery = session.createSQLQuery(sql); // use portal class loader, since this is portal entity sqlQuery.addEntity("Group_", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.GroupImpl")); return (List<Group>) QueryUtil.list(sqlQuery, getDialect(), 0, QueryUtil.ALL_POS); } catch (Exception e) { throw new SystemException(e); } finally { sessionFactory.closeSession(session); // edited as per the comment on this answer // closeSession(session); } 

Hope this helps someone on stackoverflow, also I found a nice tutorial regarding custom-sql that also uses the same approach.