1 of 8
Home
Liferay
Hook
Alloy
Code Snippet
How To
Contact Us
About Us
Privacy Policy
Home Liferay Liferay Custom SQL Example
Liferay Custom SQL Example
Tags
Posted on June 14, 2015 by Hamidul Islam in Liferay.
Alloy
alloy ui
DXP
hook
liferay
custom attribute
javascript
json
liferay service builder
liferay theme development
search container
We know Liferay service builder tool which generate all our necessary stuffs to
ipc
liferay 7
liferay theme
velocity
angular js
service builder
OSGI
spring mvc
velocity template
interact with database. By Liferay Custom SQL we can query database by writing
raw SQL query. You might be thinking of Liferay Finder to fetch data. But it has some
limitations. If we need to aggregate data from from different tables then Liferay
Custom
By continuing to use the site, you agree to the use of cookies. more information
Query would be very handy. Liferay Custom Query is nothing but native query. For example
Accept
Top
01/12/2016 20:31
2 of 8
we are writing custom query for oracle but it may not work for MySQL. Liferay Custom
Query is very easy to use by means of using XML file where we write our SQL Query. This
article explains in details about how to create Liferay Custom Query in our custom portlet.
Before starting the topic I am assuming that you have basic knowledge of Liferay Service
builder tool. If you are new to Liferay Service builder then it would be better to
understand the concept of Liferay Service Builder first and then learn how to build Lifery
Custom SQL. Follow the below links to know Liferay Service Builder
http://proliferay.com/an-introduction-to-liferay-service-builder/
Subscribe to this Site via
http://proliferay.com/crud-operations-in-liferay-portlet/
Email
I am considering the below service.xml file for explaining Liferay Custom SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Enter your email address to subscribe to this
<?xml version="1.0" encoding="UTF-8"?>
site and receive notications of new posts
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.2.0//EN" "http://www.liferay.com/
<service-builder package-path="com.proliferay.sbuilder.example.customquery">
by email.
<author>Hamidul Islam</author>
<namespace>Customquery</namespace>
<entity name="Book" table="CUSTOMQUERY_BOOK_PORTLET" local-service="true" remote-service="true">
Join 127 other subscribers
<column name="bookId" type="long" primary="true" id-type="increment"/>
<column name="bookName" type="String" />
<column name="description" type="String" />
<column name="authorName" type="String" />
<column name="isbn" type="int" />
<column name="price" type="int" />
</entity>
</service-builder>
RSS Feed
Note:
RSS - Posts
RSS - Comments
The main purpose of Liferay Custom SQL is to execute our own hand written query to fetch data from one
table or many tables.
Now follow some few steps to implement
Continue reading
Consume OSGI Service in Liferay Portlet
Liferay Custom Query...
Writing your rst OSGI Service in Liferay 7
By continuing to use the site, you agree to the use of cookies. more information
Open Gogo Shell Using Liferay IDE
Accept
Top
01/12/2016 20:31
3 of 8
1.
Liferay 7-OSGI Module MVC Portlet by
Maven
First inform Liferay from where to pick Custom SQL
Liferay should know the location of our custom query. In src folder of our portlet we need to create a new
Invoke Liferay JSON Web Service from Web
Content Display Portlet
directory called custom-sql. Under custom-sql folder we should create one new file called default.xml
file. In default.xml file we can write our all the custom query. But its better to write custom queries in
Important Code Snippets for Liferay Asset
separate files if number of custom queries are more. The content of default.xml is
Publisher ADT
1
2
3
4
Liferay 7 Portlet Creation using Liferay IDE
<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
<sql file="custom-sql/book.xml" />
</custom-sql>
Explore Gradle as Build Tool
Which means that book.xml is another file where our custom queries are available. Download the source
Apache Felix Web Console in Liferay 7
code at the end of this article.
How To Access Gogo Shell in Liferay 7
2.
Learn The Basics Of OSGI Framework
Write the actual finder class
Liferay Custom Authentication by Hook
Have a look into the service.xml shown in the above. We write finder class based on service.xml. Initially
BookFinderImpl class is created with below content.
1
2
3
4
5
6
7
8
9
Add Custom Field Capability To Custom
Portlet
package com.proliferay.sbuilder.example.customquery.service.persistence;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import com.proliferay.sbuilder.example.customquery.model.Book;
Control Panel Portlet:Move a Portlet to
Control Panel
public class BookFinderImpl extends BasePersistenceImpl<Book> implements
BookFinder {
Explore Liferay Asset Tag
Spring MVC Form Validation
Note: BookFinderImpl class implements BookFinder. BookFinder interface will be generated only after
building the service.
Form Submission Example in Spring MVC
XML Conguration Files For Spring MVC
Basic Spring MVC Project By Maven
Write Your First AngularJS Controller
Liferay Custom Portlet To Change Password
By continuing to use the site, you agree to the use of cookies. more information
Angular JS Expression
Accept
Top
01/12/2016 20:31
4 of 8
Angular JS Directive
AuthType:3 Ways to Login Liferay
Liferay Portlet Show Caps Lock On for
Password Field
Getting Started with Angular JS
. Write
Liferay Auto Fields Example
some SQL in book.xml file
Create JSON by Jackson API
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
Liferay Live Users Session
<sql id="com.proliferay.sbuilder.example.customquery.service.persistence.BookFinderImpl.findBooks
Liferay Portlet Conguration Page
<![CDATA[
SELECT
*
FROM
CUSTOMQUERY_BOOK_PORTLET
WHERE
price = ?
]]>
Action URL in Liferay
Portlet Preferences In Liferay Portal
</sql>
</custom-sql>
Liferay Terms Of Use Customization
Note: The ? mark in the above SQL is dynamic. We can execute this query from BookFinderImpl class
How To Hide Default Error Message in
passing dynamic value for the ? mark.
Liferay
4.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CSS Media Queries
Write a new method in BookFinderImpl to execute the query
Liferay Custom SQL Example
package com.proliferay.sbuilder.example.customquery.service.persistence;
import java.util.List;
import
import
import
import
import
import
import
Singleton Class In Java
Design Patterns In Java
com.liferay.portal.kernel.dao.orm.QueryPos;
com.liferay.portal.kernel.dao.orm.SQLQuery;
com.liferay.portal.kernel.dao.orm.Session;
com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
com.liferay.util.dao.orm.CustomSQLUtil;
com.proliferay.sbuilder.example.customquery.model.Book;
com.proliferay.sbuilder.example.customquery.model.impl.BookImpl;
public class BookFinderImpl extends BasePersistenceImpl<Book> implements
BookFinder {
Concept of ClassPK in liferay
Inject Spring Bean into Liferay Impl Class
Liferay Service Builder Finder Query
CRUD
Operations
in more
Liferay
Portlet
By continuing to use the site, you agree
to the
use of cookies.
information
private String FIND_BOOKS = BookFinderImpl.class.getName()
+ ".findBooksByPrice";
Accept
Top
01/12/2016 20:31
5 of 8
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
5.
public List<Book> findBooksByPrice(int price) {
// 1. Open an ORM session
Session session = openSession();
/**
* 2. Get SQL statement from XML file with its name
*
* FIND_BOOKS is actually equal to
* com.proliferay.sbuilder.example.customquery
* .service.persistence.BookFinderImpl.findBooksByPrice
*/
String sql = CustomSQLUtil.get(FIND_BOOKS);
// 3. Transform the normal query to HQL query
SQLQuery query = session.createSQLQuery(sql);
// 4. Add the actual entity to be searched
query.addEntity("Book", BookImpl.class);
An introduction to Liferay Service Builder
Access Liferay Services in Velocity Template
Liferay Theme Settings
jQuery in Liferay Portal
Sass and Compass in Liferay Theme
Liferay Theme and Velocity Template
Basic Liferay Theme Development
Form Submit in Spring MVC Portlet
// 5. Replace positional parameters in the query
QueryPos qPos = QueryPos.getInstance(query);
qPos.add(price);
// 6. Execute query and return results.
return (List<Book>) query.list();
Write a new method in BookLocalServiceImpl and call the above method. Hit ant
build-service to generate the service again
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package com.proliferay.sbuilder.example.customquery.service.impl;
import java.util.List;
import com.proliferay.sbuilder.example.customquery.model.Book;
import com.proliferay.sbuilder.example.customquery.service.base.BookLocalServiceBaseImpl;
import com.proliferay.sbuilder.example.customquery.service.persistence.BookFinderUtil;
/**
* The implementation of the book local service.
*
* <p>
* All custom service methods should be put in this class. Whenever methods are
* added, rerun ServiceBuilder to copy their definitions into the
* {@link com.proliferay.sbuilder.example.customquery.service.BookLocalService}
* interface.
*
* <p>
* This is a local service. Methods of this service will not have security
By continuing to use the site, you agree to the use of cookies. more information
* checks based on the propagated JAAS credentials because this service can only
* be accessed from within the same VM.
* </p>
Accept
Top
01/12/2016 20:31
6 of 8
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
*
* @author Hamidul Islam
* @see com.proliferay.sbuilder.example.customquery.service.base.BookLocalServiceBaseImpl
* @see com.proliferay.sbuilder.example.customquery.service.BookLocalServiceUtil
*/
public class BookLocalServiceImpl extends BookLocalServiceBaseImpl {
/*
* NOTE FOR DEVELOPERS:
*
* Never reference this interface directly. Always use {@link
* com.proliferay.sbuilder.example.customquery.service.BookLocalServiceUtil}
* to access the book local service.
*/
6.
1
public List<Book> getBooksByPrice(int price) {
return BookFinderUtil.findBooksByPrice(price);
}
Now we are ready to call getBooksByPrice method like
List<Book> books = BookLocalServiceUtil.getBooksByPrice(123);
Check the method using it in your any JSP of the portlet.
Summery:
In this article we have discussed only the basic concept of Liferay Custom SQL. We will discuss some
advance topic in another article.
Download Source Code
Share this:
Share
G WhatsApp
< Print
9 Email
Like this:
Be the first to like this.
Related
By continuing to use the site, you agree to the use of cookies. more information
Accept
Top
01/12/2016 20:31
7 of 8
Post Tagged with liferay, Liferay Custom Query, Liferay Custom SQL, Lifery Custom Query Example
Previous Post
Next Post
If you enjoyed this article please consider sharing it!
One Response so far.
Alexandre Mlard says:
July 7, 2015 at 8:53 am
Hi, thanks for that nice tutorial, I would like to call the
**BookLocalServiceUtil.getBooksByPrice(123);** service from another portlet, I tried to add the
following to the portlet using the service:
required-deployment-contexts=mf3-plugin-service-portlet
The jar le of the portlet is added to the WEB_INF/lib directory, but when calling the methode, I
get a NoSuchBean Exception.
Woould be glad if you could assit me on this
Reply
Leave a Reply
Comment
By continuing to use the site, you agree to the use of cookies. more information
Accept
Top
01/12/2016 20:31
8 of 8
Name
Email
Are you human?
6 + 6 =
Notify me of follow-up comments by email.
Notify me of new posts by email.
Pro Liferay - A guide for Liferay Developer
By continuing to use the site, you agree to the use of cookies. more information
Accept
Top
01/12/2016 20:31
