Custom queries in Liferay (在liferay中使用传统的Query)

原文：http://www.liferay.com/en/community/wiki/-/wiki/1071674/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 thePost'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 thePost 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 runningant build-service.

After our services was created, let us create a class called PostFinderImpl in the packagebr.com.seatecnologia.portlet.exampleblog.service.persistence of thedocroot/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 classbr.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinderUtil will be created at thedocroot/WEB-INF/service source folder. Once they are created, you should make thePostFinderImpl 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 theFinderImpl implements theFinder 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 atPostFinder andPostFinderUtil.

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

第一种方法：

SQL写在类里面，

1、写一个基本的SQL

2、根据参数在原SQL后面添加条件。

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 aLocalServiceImpl 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: theFinderUtil 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.

第二种方法：

推荐使用此方法，将第一种方法中的SQL 写到 XML中，然后在JAVA代码中用replace进行替换。

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 calleddefault.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 anid 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 asbr.com.seatecnologia.portlet.exampleblog.service.persistence.PostFinder.findByTitleContentAuthor.

Once we have created the docroot/WEB-INF/src/custom-sql/default.xml file, we update theFinderImpl 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 thedefault.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 thedefault.xml file will always have three parameters to be set:

替换后的代码：

 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 isnot what we are looking for.

Fortunately, the CustomSQLUtil class has a trick to 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.

当我们 对要将title作为查询条件，则要设置

CustomSQLUtil.replaceAndOperator(sql, true)

当我们不要title为查询条件，则

CustomSQLUtil.replaceAndOperator(sql, false);//或者不设置，具体看源码

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 byOR 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 theQueryPos 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.

还有一个更好的办法：

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

如果这个title有多个，那么放到数组中：

String[] titles = null;
titles = new String {"%title01%","%title02%"};
sql = CustomSQLUtil.replaceKeywords( sql, "title", StringPool.LIKE, false, titles);

后面还是：

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

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 theAuthor queries and thePost queries. We change thedefault.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.

Links 

Here are some sources used for writing this article:

• How to create a custom query in ext for Liferay models - A series of instructions to do what we did here for Liferay 4.3.
• create finders using service builder
• Join 2 tables using a DynamicQuery {Solved}
• Join with DynamicQuery
• Custom Query in the Ext - What and How
• Custom query in 5.1.2
• Liferay: How to use Custom-sql in Liferay?

Notes 

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

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