1. Home
  2. Development
  3. SessionFactory Throwing Exception in Custom SQL

SessionFactory Throwing Exception in Custom SQL

thumbnail
Nirav Prajapati, modified 5 Years ago.
SessionFactory Throwing Exception in Custom SQL
Regular Member Posts: 133 Join Date: 6/25/15 Recent Posts
Hello Friends,

I am using Liferay 7.2 .

I have created Custom SQL and inside FinderImpl, trying to access SessionFactory but it's showing error as, 
                                     com.liferay.portal.kernel.bean.BeanLocatorException with No bean named 'liferaySessionFactory' available.

Following is my code,

        SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("liferaySessionFactory");
        Session session = null;
        
        List<User> lrUsers = new LinkedList<User>();
        List<Long> userOrgIds = getUsersAllOrgIds(userSoldToOrgId);
      
            try {
                session =  sessionFactory.openSession();
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" SELECT DISTINCT u.* FROM User_ u, Users_Orgs uo, Organization_ org ")
                    .append(" WHERE u.userId = uo.userId ")
                    .append(" and org.organizationId = uo.organizationId ")
                    .append(" and org.organizationId in ( " + userOrgIdsCommaSeparated + " ) ")
                    .append(" ORDER BY u.firstName, u.lastName ");                
                SQLQuery sqlQuery = session.createSQLQuery(sqlBuilder.toString());
                sqlQuery.setCacheable(true);
                sqlQuery.addEntity("User_",  PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));
                
                lrUsers =  (List<User>) QueryUtil.list(sqlQuery, getDialect(), start, end);

Please help me to solve it.

Thanks in advance.
thumbnail
Amos Fong, modified 5 Years ago.
RE: SessionFactory Throwing Exception in Custom SQL
Liferay Legend Posts: 2047 Join Date: 10/7/08 Recent Posts
Try openSession() like in this finder:https://github.com/liferay/liferay-portal/blob/master/modules/apps/blogs/blogs-service/src/main/java/com/liferay/blogs/service/persistence/impl/BlogsEntryFinderImpl.java
sqlQuery.addEntity("User_",  PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));

Assuming you're in your own module, I think this won't work. An option you can do is get the userIds in your query and then retrieve the User object from the userId from the user local service.
thumbnail
Nirav Prajapati, modified 5 Years ago.
RE: SessionFactory Throwing Exception in Custom SQL
Regular Member Posts: 133 Join Date: 6/25/15 Recent Posts
Hi Amos Fong,
I have tried based on your suggestion and below is my code,

User userObj = UserLocalServiceUtil.getUser(userId);
sqlQuery.addEntity("User_",  userObj.getClass());
lrUsers =  (List<User>) QueryUtil.list(sqlQuery, getDialect(), start, end);

Afterward, got error message like, "com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.MappingException: Unknown entity: com.liferay.portal.model.impl.UserImpl"
Let me know if you need any more details.
thumbnail
Amos Fong, modified 5 Years ago.
RE: SessionFactory Throwing Exception in Custom SQL
Liferay Legend Posts: 2047 Join Date: 10/7/08 Recent Posts
Oh I think I wasn't clear. Even if you get the User object in addEntity, the hibernate mappings are still in the portal classloader (see the error you get).I meant to change your SQL query to (select userId from User_....), then with that userId get the actual users back.
thumbnail
Mohammed Yasin, modified 5 Years ago.
RE: SessionFactory Throwing Exception in Custom SQL
Liferay Master Posts: 593 Join Date: 8/8/14 Recent Posts
        SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("liferaySessionFactory");
        Session session = null;
        
        List<User> lrUsers = new LinkedList<User>();
        List<Long> userOrgIds = getUsersAllOrgIds(userSoldToOrgId);
      
            try {
                session =  sessionFactory.openSession();
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" SELECT DISTINCT u.* FROM User_ u, Users_Orgs uo, Organization_ org ")
                    .append(" WHERE u.userId = uo.userId ")
                    .append(" and org.organizationId = uo.organizationId ")
                    .append(" and org.organizationId in ( " + userOrgIdsCommaSeparated + " ) ")
                    .append(" ORDER BY u.firstName, u.lastName ");                
                SQLQuery sqlQuery = session.createSQLQuery(sqlBuilder.toString());
                sqlQuery.setCacheable(true);
                sqlQuery.addEntity("User_",  PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));
                
                lrUsers =  (List<User>) QueryUtil.list(sqlQuery, getDialect(), start, end);


Hi ,
Above code is correct and is working fine till 7.1 , its throwing   " com.liferay.portal.kernel.bean.BeanLocatorException with No bean named 'liferaySessionFactory'"    in 7.3 also ,  Below is a workaround which is working in 7.3 also might work in 7.2.

public List<user> searchAllUser() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Session session = _basePersistence.openSession();
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;String sql = "SELECT * FROM USER_ &nbsp;WHERE companyId = " + PortalUtil.getDefaultCompanyId();
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;SQLQuery q = session.createSQLQuery(sql);
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;q.setCacheable(Boolean.TRUE);</user>
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;try {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;q.addEntity("User_",
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;} catch (ClassNotFoundException e) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;_log.error(e);
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;}
        List<user> users = (List<user>) QueryUtil.list(q, getDialect(), -1, -1);
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;session.close();
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return users;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;
&nbsp;&nbsp; &nbsp;
&nbsp;&nbsp; &nbsp;@Reference
&nbsp;&nbsp; &nbsp;private BasePersistence _basePersistence;</user></user>
thumbnail
Nirav Prajapati, modified 5 Years ago.
RE: SessionFactory Throwing Exception in Custom SQL
Regular Member Posts: 133 Join Date: 6/25/15 Recent Posts
Thank You Mohammed Yasin,
​​​​​​​It's working fine for me.
André Bergmann, modified 5 Years ago.
RE: SessionFactory Throwing Exception in Custom SQL
New Member Posts: 2 Join Date: 5/20/20 Recent Posts
This also works:
Session session = UserLocalServiceUtil.getService().getBasePersistence().openSession();