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"?>
               <custom-sql>
                  <sql file="custom-sql/query.xml" />
                  <sql file="custom-sql/anotherquery.xml" />
              </custom-sql>

• 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"?>

               <custom-sql>

                    <sql id="com.test.services.xyz.service.persistence.uniqueId">

  <![CDATA[
select * from xyz x where x.userId=? AND x.name=?;
  ]]>
  </sql>

           </custom-sql > 

• The sql id is unique so use the package name of service persistence class and then (xyz) uniqueId.
• Like "com.test.services.xyz.service.persistence.uniqueId"
• Here "com.test.services.xyz.service.persistence" 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 "XyzFinderImpl.java" in "com.test.services.xyz.service.persistence" 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 "XyzFinderImpl.java"
• The content of finder impl is as under

          package com.test.services.xyz.service.persistence;

          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 = "com.test.services.xyz.service.persistence.uniqueId";

   

              public List getXYZ(Long userId, String name) throws SystemException

              {

                  Session session = null;

                  String sql = null;

                  try

                  {

                      session = openSession();

                      if (Validator.isNotNull(userId))

                      {

                          sql = CustomSQLUtil.get(CUSTOM_SQL);

                      } 

                      SQLQuery query = session.createSQLQuery(sql);

                      QueryPos qPos = QueryPos.getInstance(query);

                      qPos.add(userId);

                      qPos.add(name);

           //           query.executeUpdate();

                     return (List) query.list();

                  } catch (Exception e)

                  {

                      e.printStackTrace();

                  }

              }

          }

• 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 "XyzFinderImple.java" 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");