May 7, 2013

Liferay Custom Query

 Step by step process of "How to use custom query"

There are four steps to create custom query and use it.
NOTE :- Here I assume that you know about liferay service builder.

step 1:- custom-sql folder

  • Create custom-sql folder in docroot/WEB-INF/src package
  • Add new default.xml file in above created folder.
  • The content of default.xml is as under
                <?xml version="1.0" encoding="UTF-8"?>
                  <sql file="custom-sql/query.xml" />
                  <sql file="custom-sql/anotherquery.xml" />
  • You can add multiple sql file in above content.
  • Now create query.xml in same folder
  • Write your sql-query in query.xml as under.
               <?xml version="1.0" encoding="UTF-8"?>
                    <sql id="">

select * from xyz x where x.userId=? AND;
           </custom-sql > 
  • The sql id is unique so use the package name of service persistence class and then (xyz) uniqueId.
    • Like ""
    • Here "" is package path of "xyz" service.
    • "Xyz" is the entity name.
    • Here "xyz" is the service created for means table name which we created using service builder.

step 2:- creating service finder impl

  • Create "" in "" package of service.
  • NOTE:- Give the name of finder impl related to the service name
    • Here in our case the service is "XYZ" so we create ""
  • The content of finder impl is as under

          import com.liferay.portal.kernel.dao.orm.QueryPos;
          import com.liferay.portal.kernel.dao.orm.SQLQuery;
          import com.liferay.portal.kernel.dao.orm.Session;
          import com.liferay.portal.kernel.exception.SystemException;
          import com.liferay.portal.kernel.util.StringUtil;
          import com.liferay.portal.kernel.util.Validator;
          import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
          import com.liferay.util.dao.orm.CustomSQLUtil;

          public class XyzFinderImpl extends BasePersistenceImpl<xyz> implements XyzFinder
             public static String CUSTOM_SQL = "";
              public List getXYZ(Long userId, String name) throws SystemException
                  Session session = null;
                  String sql = null;
                      session = openSession();
                      if (Validator.isNotNull(userId))
                          sql = CustomSQLUtil.get(CUSTOM_SQL);
                      SQLQuery query = session.createSQLQuery(sql);
                      QueryPos qPos = QueryPos.getInstance(query);

           //           query.executeUpdate();
                     return (List) query.list();
                  } catch (Exception e)


  • Right now "XyzFinder" interface is not available so no need to worry for that.
  • After doing this much build the service again so it's create appropriate classes and interfaces for you.

step 3:-Define method in service Impl

  • Open XyzLocalServiceImpl generated while first time you build service for that.
  • Define method that call the finder method of "" class as below.
                    public List getUser(Long userId, String name) throws SystemException
                      return  XyzFinderUtil.getXYZ(userId, name);
    • Now build service again and after that you are ready to use the custom query.

    step 4:- use of custom-query

    • Call the method as below in your class.
                 List users = XyzLocalServiceUtil.getUser(12345,"test");

    No comments:

    Post a Comment

    Note: Only a member of this blog may post a comment.