Custom queries in Liferay

(Redirected from Custom queries in Liferay 5.2)

Sometimes it is needed to perform joined queries with the Service Builder. It is not possible to do it with dynamic queries - custom queries are necessary. This article explains how to create custom queries since the documentation was pretty sparse and confusing.

Prerequisites #

You should know how to create services with Service Builder. Also, you should know a little about SQL: the basic syntax and a notion on how it is used inside programming language code.

The steps described here were performed over Liferay 5.2 but it still worked with Liferay 6.

Our example #

Suppose you need to create a simple blog portlet. It will use two service entities, representing posts and authors. We use the following service.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 5.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_5_2_0.dtd">
<service-builder package-path="br.com.seatecnologia.portlet.exampleblog">
    <author>Adam Victor Nazareth Brandizzi</author>
    <namespace>ExampleBlog</namespace>
    <entity name="Author" local-service="true">
        <column name="authorId" type="long" primary="true"/>
        <column name="name" type="String"/>
        <column name="posts" type="Collection" entity="Post" mapping-key="authorId"/>
    </entity>
    <entity name="Post" local-service="true">
        <column name="postId" type="long" primary="true"/>
        <column name="title" type="String"/>
        <column name="content" type="String"/>
        <column name="authorId" type="long"/>
    </entity>
</service-builder>

Also, we define a method in PostImpl which retrieves the author of a post based on the Post's author Id:

public Author getAuthor() {
    try {
        return AuthorLocalServiceUtil.getAuthor(getAuthorId());
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
}
 
 // end

The problem #

Besides providing forms for inserting and listing all posts and authors, our portlet should provide a form for searching posts based on title, content and author name. The challenge is that, since the author name is not in the Post table, we cannot search on it using finders or dynamic queries. We need to do a joined search, so we need a custom query.

Creating a finder #

To use custom queries, we need a finder class. This class should be created after running ant build-service.

After our services was created, let us create a class called PostFinderImpl in the package br.com.seatecnologia.portlet.exampleblog.service.persistence of the docroot/WEB-INF/src source folder.[1] This class should extend com.liferay.portal.service.persistence.impl.BasePersistenceImp<Post>:

package br.com.seatecnologia.portlet.exampleblog.service.persistence;

import br.com.seatecnologia.portlet.exampleblog.model.Post;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;

public class PostFinderImpl extends BasePersistenceImpl<Post> { }

Once we have written the PostFinderImpl class we execute ant build-service again. The interface br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder and the util class br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinderUtil will be created at the docroot/WEB-INF/service source folder. Once they are created, you should make the PostFinderImpl class implement the PostFinder interface:

public class PostFinderImpl extends BasePersistenceImpl<Post> implements PostFinder { }

It is very important to implement the interface. A recurrent error is to forget to declare that the FinderImpl implements the Finder interface. Usually it results in a error message like this:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name
'br.com.seatecnologia.portlet.exampleblog.service.AuthorLocalService' defined in ServletContext resource
[/WEB-INF/classes/META-INF/portlet-spring.xml]: Initialization of bean failed; nested exception is
org.springframework.beans.factory.BeanCreationException: Error creating bean with name
'br.com.seatecnologia.portlet.exampleblog.service.AuthorService' defined in ServletContext resource
[/WEB-INF/classes/META-INF/portlet-spring.xml]: Initialization of bean failed; nested exception is
org.springframework.beans.factory.BeanCreationException: Error creating bean with name
'br.com.seatecnologia.portlet.exampleblog.service.PostLocalService' defined in ServletContext resource
[/WEB-INF/classes/META-INF/portlet-spring.xml]: Initialization of bean failed; nested exception is
org.springframework.beans.factory.BeanCreationException: Error creating bean with name
'br.com.seatecnologia.portlet.exampleblog.service.PostService': Could not inject BeanReference fields;
nested exception is java.lang.IllegalArgumentException: Can not set
br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder field
br.com.seatecnologia.portlet.exampleblog.service.base.PostServiceBaseImpl.postFinder to $Proxy262

If you find something like this in the log of a faulty portlet it is very likely that you forgot declare the Impl class an implement the interface.

Writing the query method #

Now we can create the query method in the PostFinderImpl class:

public List<Post> findByTitleContentAuthor(String title, String content, String author, 
            int begin, int end) {
    return null;
}

 // end

We run ant build-service again and voilà new methods are created at PostFinder and PostFinderUtil.

Every time we add a new method at some FinderImpl we should execute ant build-service for propagating the method to the interface and util class. However, if we just change a method in FinderImpl but does not change the method signature neither add a new method to the finder, we do not need to execute the ant task.

Implementing the query #

The process of creating and executing a query is pretty similar to the SQL execution in various programming languages and frameworks. We will not explain it in detail. Below, we present the implementation:

package br.com.seatecnologia.portlet.exampleblog.service.persistence;

import java.util.ArrayList;
import java.util.List;

import br.com.seatecnologia.portlet.exampleblog.model.Post;
import br.com.seatecnologia.portlet.exampleblog.model.impl.PostImpl;

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

public class PostFinderImpl extends BasePersistenceImpl<Post> {

    public List<Post> findByTitleContentAuthor(String title, String content, String author, int begin, int end) throws SystemException {
        /// This stuff is basic set up
        Session session = null;
        try {
            session = openSession();
            // Here ends the basic set up; 

            //now we build the query. Note that we use the name of the tables from the database!
            String base = "SELECT {ExampleBlog_Post.*} FROM ExampleBlog_Post " +
                    "JOIN ExampleBlog_Author " +
                    "ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId ";
            StringBuffer sqlBuilder = new StringBuffer(base);
            List<String> criteria = new ArrayList<String>();
            if (Validator.isNotNull(title)) {
                criteria.add("ExampleBlog_Post.title like ?");
            }
            if (Validator.isNotNull(content)) {
                criteria.add("ExampleBlog_Post.content like ?");
            }
            if (Validator.isNotNull(author)) {
                criteria.add("ExampleBlog_Author.name like ?");
            }
            if (!criteria.isEmpty()) {
                sqlBuilder.append("WHERE ");
                sqlBuilder.append(criteria.remove(0) + " ");
                for (String criterion : criteria) {
                    sqlBuilder.append(" AND "+criterion);
                }
            }
            /* If all the parameters was given, the "sql" variable content should be something like
                 SELECT {ExampleBlog_Post.*} FROM ExampleBlog_Post
                  JOIN ExampleBlog_Author
                   ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId
                 WHERE ExampleBlog_Post.title like ?
                      AND ExampleBlog_Post.content like ?
                      AND ExampleBlog_Author.name like ?//
             */
             String sql = sqlBuilder.toString();
            // Now that we have built the query, we can do all the usual stuff.
            SQLQuery q = session.createSQLQuery(sql);
            q.setCacheable(false);
            q.addEntity("ExampleBlog_Post", PostImpl.class);

            QueryPos qPos = QueryPos.getInstance(q);
            // Setting the positions
            if (Validator.isNotNull(title)) {
                qPos.add("%"+title+"%");
            }
            if (Validator.isNotNull(content)) {
                qPos.add("%"+content+"%");
            }
            if (Validator.isNotNull(author)) {
                qPos.add("%"+author+"%");
            }

            //execute the query and return a list from the db
            return (List<Post>) QueryUtil.list(q, getDialect(), begin, end);
        } catch (Exception e) {
            throw new SystemException(e);
        } finally {
            //must have this to close the hibernate session..
            // if you fail to do this.. you will have a lot of open sessions… 
            closeSession(session);
        }
    }
}

 // end

Pay attention to the imported classes, since probably there are a lot of Session, SystemExceptions etc. classes in your path.

Adding the method to the local service #

Although we have created a FinderImpl and generated a FinderUtil our portlet class cannot use the FinderUtil directly, only a LocalServiceImpl class can do so. It is so for encouraging proper layer separation in our portlets: the finders (and persistences) classes care only about, well, persistence, dealing with database and so on. Just the service and local service classes provide business logic for portlets, so the finders should not be directly accessed by portlets. It would not be possible, anyway: the FinderUtil classes have dependencies which are injected by the LocalServiceImpl classes.

The practical consequence is: we should create a method in the PostLocalServiceImpl class calling the finder util:

public List<Post> findByTitleContentAuthor(String title, String content, String author, 
        int begin, int end) throws SystemException {
    return PostFinderUtil.findByTitleContentAuthor(title, content, author, begin, end);
}

 // end

Once we have written the method, we execute ant build-service and the PostLocalServiceUtil will have the method.

Using the query in the portlet #

Once we have followed all the steps above, we can just use the new method of the Local Service in the portlet, as below:

public class BlogPortlet extends GenericPortlet {
    // . . .
    @Override
    public void doView(RenderRequest renderRequest,
            RenderResponse renderResponse) throws IOException, PortletException {
        // ...
        List<Post> posts = PostLocalServiceUtil.findByTitleContentAuthor(title, content, author, 0, 10); 
        // . . .
    }
}

 // end

At the attachments, you can find the resulting portlet.

Bonus Point: CustomSQLUtil #

In our example we wrote the SQL query in a string inside the code. However, it is not really elegant, so Liferay provides a way to separate the SQL queries from the Java code.

Creating the default.xml file #

First, we should create the folder custom-sql at the docroot/WEB-INF/src source folder. There, we create a file called default.xml where we put the queries. This is a XML file like the one below:

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
    <sql id="br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder.findByTitleContentAuthor">
        SELECT {ExampleBlog_Post.*}
        FROM ExampleBlog_Post
        INNER JOIN 
            ExampleBlog_Author ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId
        WHERE
            (ExampleBlog_Post.title LIKE ?) AND
            (ExampleBlog_Post.content LIKE ?) AND
            (ExampleBlog_Author.name LIKE ?)
    </sql>
</custom-sql>

Each SQL query comes inside a <sql> element. Each one of these <sql> elements should have an id attribute. The attribute can be anything unique in the portlet (that is, you cannot repeat ids) but it is usual to use the name of the finder interface followed by a dot and the name of the finder method. In fact, it is what we did when defined the id as br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder.findByTitleContentAuthor.

Once we have created the docroot/WEB-INF/src/custom-sql/default.xml file, we update the FinderImpl class. We replace the code which builds the SQL query manually...

 //now we build the query. Note that we use the name of the tables from the database!
 String base = "SELECT {ExampleBlog_Post.*} FROM ExampleBlog_Post " +
 "JOIN ExampleBlog_Author " +
 "ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId ";
 StringBuffer sqlBuilder = new StringBuffer(base);
 List<String> criteria = new ArrayList<String>();
 if (Validator.isNotNull(title)) {
     criteria.add("ExampleBlog_Post.title like ?");
 }
 if (Validator.isNotNull(content)) {
     criteria.add("ExampleBlog_Post.content like ?");
 }
 if (Validator.isNotNull(author)) {
     criteria.add("ExampleBlog_Author.name like ?");
 }
 if (!criteria.isEmpty()) {
     sqlBuilder.append("WHERE ");
     sqlBuilder.append(criteria.remove(0) + " ");
     for (String criterion : criteria) {
         sqlBuilder.append(" AND "+criterion);
     }
 }
 /* If all the parameters was given, the "sql" variable content should be something like
     SELECT {ExampleBlog_Post.*} FROM ExampleBlog_Post
      JOIN ExampleBlog_Author
       ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId
     WHERE ExampleBlog_Post.title like ?
          AND ExampleBlog_Post.content like ?
          AND ExampleBlog_Author.name like ?
 */
 String sql = sqlBuilder.toString();

 // Now that we have built the query, we can do all the usual stuff.
 SQLQuery q = session.createSQLQuery(sql);

...with the use of the CustomSQLUtil class:

 //now we build the query. Note that we use the name of the tables from the database!
 String sql = CustomSQLUtil.get("br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder.findByTitleContentAuthor");
 // Now that we have built the query, we can do all the usual stuff.
 SQLQuery q = session.createSQLQuery(sql);

Note how we retrieve the SQL query using the id of the <sql> element from the default.xml file.

Also, we replace the current query parameter setting code...

 QueryPos qPos = QueryPos.getInstance(q);
 // Setting the positions 
 if (Validator.isNotNull(title)) {
     qPos.add("%"+title+"%");
 }
 if (Validator.isNotNull(content)) {
     qPos.add("%"+content+"%");
 }
 if (Validator.isNotNull(author)) {
     qPos.add("%"+author+"%");
 }
 //execute the query and return a list from the db

...with one which always set all parameters (since the query retrieved from the default.xml file will always have three parameters to be set[2]):

 QueryPos qPos = QueryPos.getInstance(q);  
 qPos.add("%"+title+"%");
 qPos.add("%"+content+"%");
 qPos.add("%"+author+"%");

[$AND_OR_NULL_CHECK$] and [$AND_OR_CONNECTOR$] #

It will not work, however. The code will compile and will run without any exception, but the logic of the query is wrong: if some of the parameters is null, it will be added as "%null%", so only the posts which contains such string in one of its fields will be found. Certainly, it is not what we are looking for.

Fortunately, the CustomSQLUtil class has a trick tho solve this problem. First, we should update the query. instead of

SELECT {ExampleBlog_Post.*}
FROM ExampleBlog_Post
INNER JOIN 
    ExampleBlog_Author ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId
WHERE
    (ExampleBlog_Post.title LIKE ?) AND
    (ExampleBlog_Post.content LIKE ?) AND
    (ExampleBlog_Author.name LIKE ?)

we will write

SELECT {ExampleBlog_Post.*}
FROM ExampleBlog_Post
INNER JOIN 
    ExampleBlog_Author ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId
WHERE
    (ExampleBlog_Post.title LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$] 
    (ExampleBlog_Post.content LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
    (ExampleBlog_Author.name LIKE ? [$AND_OR_NULL_CHECK$])

Once we made it, we invoke the CustomSQLUtil.replaceAndOperator method on the retrieved query string:

 String sql = sqlBuilder.toString();
 String sql = CustomSQLUtil.get("br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder.findByTitleContentAuthor");
 sql = CustomSQLUtil.replaceAndOperator(sql, true);
 // Now that we have built the query, we can do all the usual stuff.

Note that the CustomSQLUtil.replaceAndOperator method receives two arguments. The first one is the query to be altered; the second one is a flag saying if the query is a disjunction (that is, only the elements which satisfy all criteria are returned) or a conjunction (that is, all elements which satisfy at least one criteria are returned). When the value is true, it is a disjunction: the [$AND_OR_NULL_CHECK$] string is replaced by "OR ? IS NULL" and the [$AND_OR_CONNECTOR$] is replaced with "AND". So, in the code above, the resulting query would be:

SELECT {ExampleBlog_Post.*}
FROM ExampleBlog_Post
INNER JOIN 
    ExampleBlog_Author ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId
WHERE
    (ExampleBlog_Post.title LIKE ? OR ? IS NULL) AND
    (ExampleBlog_Post.content LIKE ? OR ? IS NULL) AND
    (ExampleBlog_Author.name LIKE ? OR ? IS NULL)

(If the second argument of CustomSQLUtil.replaceAndOperator were false, [$AND_OR_CONNECTOR$] would be replaced by OR and [$AND_OR_NULL_CHECK$] would be replaced by "AND ? IS NOT NULL". The resulting query would be:

SELECT {ExampleBlog_Post.*}
FROM ExampleBlog_Post
INNER JOIN 
    ExampleBlog_Author ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId
WHERE
    (ExampleBlog_Post.title LIKE ? AND ? IS NOT NULL) OR
    (ExampleBlog_Post.content LIKE ? AND ? IS NOT NULL) OR
    (ExampleBlog_Author.name LIKE ? AND ? IS NOT NULL)

Note how the [$AND_OR_NULL_CHECK$] replacements add a new parameter to the query each one[2], so we should add more parameters to the QueryPos object:

QueryPos qPos = QueryPos.getInstance(q);
qPos.add("%"+title+"%");
qPos.add(title);
qPos.add("%"+content+"%");
qPos.add(content);
qPos.add("%"+author+"%");
qPos.add(author);

Now, the query should work correctly.

Good practices #

Looking inside the Liferay source code, we can find various custom queries, and some common practices. Some of these practices bring some safety and elegance to the code, so we describe them below.

Use <![CDATA[ on default.xml #

Instead of writing a query "freely" inside the <sql> element, put the query inside a <![CDATA[ session:

<sql id="br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder.findByTitleContentAuthor">
<![CDATA[
    SELECT {ExampleBlog_Post.*}
    FROM ExampleBlog_Post
    INNER JOIN 
        ExampleBlog_Author ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId
    WHERE
        (ExampleBlog_Post.title LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$] 
        (ExampleBlog_Post.content LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
        (ExampleBlog_Author.name LIKE ? [$AND_OR_NULL_CHECK$])
    ]]>
</sql>

It avoids problems with XML parsing.

Put the id of the query in a constant #

Instead of writing

CustomSQLUtil.get("br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder.findByTitleContentAuthor");

create a constant containing the id

public static final String FIND_BY_TITLE_CONTENT_AUTHOR = "br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder.findByTitleContentAuthor";

and use it to find the query:

String sql = CustomSQLUtil.get(FIND_BY_TITLE_CONTENT_AUTHOR);

If we follow the practice of using the finder name in the id, we can use the name of the class to create the constant:

public static final String FIND_BY_TITLE_CONTENT_AUTHOR = PostFinder.class.getName()+".findByTitleContentAuthor";

Indeed, it is a fairly common idiom.

Separate queries in more than one XML file #

In our example, we created only one finder, for one entity only: Post. Eventually, we can create more finders for more entities. In these cases, the default.xml could become very big and cumbersome.

Fortunately, we can put queries in various files and import them into the default.xml. Let us suppose we will create a finder for the Author entity, too. This finder will contain various queries, so it would be better to separate the Author queries and the Post queries. We change the default.xml for just including two other files:

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
    <sql file="custom-sql/post.xml" />
    <sql file="custom-sql/author.xml" />
</custom-sql>

The content of the post.xml file would be:

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
    <sql id="br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder.findByTitleContentAuthor"><![CDATA[
        SELECT {ExampleBlog_Post.*}
        FROM ExampleBlog_Post
        INNER JOIN 
            ExampleBlog_Author ON ExampleBlog_Post.authorId = ExampleBlog_Author.authorId
        WHERE
            (ExampleBlog_Post.title LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$] 
            (ExampleBlog_Post.content LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
            (ExampleBlog_Author.name LIKE ? [$AND_OR_NULL_CHECK$])
    ]]></sql>
</custom-sql>

and the content of author.xml file would be similar.

Here are some sources used for writing this article:

Notes #

[1] Note that it is not created at the docroot/WEB-INF/service folder. The Service Builder will generate two new files in the docroot/WEB-INF/service source folder: the interface br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder and the util class br.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinderUtil. We implement the query method in PostFinderImpl but will use PostFinderUtil in our portlet.

[2] Each ? in the query is a parameter.

1 Attachment
84008 Views
Average (8 Votes)
The average rating is 4.125 stars out of 5.
Comments
Threaded Replies Author Date
Im getting a problem using the code above: The... Ian Harrigan January 9, 2012 4:08 PM
Hi, Ian. When do you get the class cast... Adam Victor Nazareth Brandizzi February 27, 2012 7:04 PM
You can have more details about custom query on... Jignesh Vachhani March 19, 2012 4:27 AM
Note that the custom finder for a service... Ivo Ivanov March 11, 2013 9:22 AM
Its fantastic guide . I solved my problem by... Vijayakumar G May 21, 2013 12:42 AM
I tried it..using Liferay portal 6.1.1... sheela mk July 25, 2013 1:10 AM
I've found that sometimes the ant build-service... Avinash R December 13, 2013 2:50 AM

Im getting a problem using the code above: The list returned simply contains a list of Objects (ie, List<Object> rather than List<Post>). This means that in my view.jsp i can do things like "results.get(0).getTitle()". If i try an cast the entry i get a class cast exception.

Has anyone managed to get this working.

Thanks in advance,
Ian
Posted on 1/9/12 4:08 PM.
Hi, Ian.

When do you get the class cast exception? When you excute "return QueryUtil.list(q, getDialect(), begin, end);"? Or when you invoke the persistence method, such as "PostFinderUtil.findByTitleContentAuthor(title, content, author, begin, end)"?
Posted on 2/27/12 7:04 PM in reply to Ian Harrigan.
You can have more details about custom query on http://www.liferaysolution.com/2012/03/custome-query.html as well
Posted on 3/19/12 4:27 AM.
Note that the custom finder for a service entity must match the pattern <EntityName>FinderImpl. Otherwise the service builder won't find it and won't do anything with the class. This feature leaves us with one custom finder per service entity.
Posted on 3/11/13 9:22 AM.
Its fantastic guide . I solved my problem by reading this.
Posted on 5/21/13 12:42 AM.
I tried it..using Liferay portal 6.1.1 GA2..running fine..Thanks for the post!!
Posted on 7/25/13 1:10 AM in reply to Vijayakumar G.
I've found that sometimes the ant build-service doesn't do any good, and the Finder interface remains ungenerated. This can be fixed by removing docroot/WEB-INF/service/ folder completey and running ant clean build-service instead.
Posted on 12/13/13 2:50 AM.