Liferay tutorial
Liferay custom sql
Liferay portal tables
Liferay Mysql
Liferay database
In this liferay tutorial, we will learn about Custom-sql.
- custom sql query in liferay portal
- custom SQL configurations
- default database
- how to build custom sql
- portal database
- custom SQL in liferay
- How to configure Liferay Database
- MySQL, Oracle, MS SQL Server
- custom SQL statements
- database SQL script
- Liferay Database Document
- How to Make Database connection in Liferay
- SQL Database tables
How to work with Custom-sql in liferay portal?
Step-1:
liferay portal custom sql Create ext.xml file
Create the file default-ext.xml under ext-impl/src/custom-sql (You need to first create this folder)
<?xml version="1.0"?>
<custom-sql>
<sql file="custom-sql/book.xml" />
</custom-sql>
(This file will list all the custom sql files developed for a specific application.
Also refer default.xml under portal source)
Step-2:
liferay portal custom sql Create book.xml file
------
Create the file book.xml, under the same folder, which will contain all the application specific queries as name / value pairs.
<?xml version="1.0"?>
<custom-sql>
<sql id="com.ext.portlet.library.service.persistence.BookFinderImpl.getBooks">
<![CDATA[
SELECT
{Book.*}
FROM
Book
WHERE
(Book.title like ?)
]]>
</sql>
</custom-sql>
(The beauty is the queries are separated from the code, so that we can change them any time without touching the code)
Step-3:
liferay portal custom sql Over ride portal-ext.properties
------
Over-ride the property in portal-ext.properties
custom.sql.configs=\
custom-sql/default.xml, \
custom-sql/default-ext.xml
So far we have seen the configuration part.
Now we'll move on to the implementation part.
Step-4:
liferay portal custom sql create bookfinderimpl.java
------
Create the file "BookFinderImpl.java" under service/persistence
package com.ext.portlet.library.service.persistence;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
public class BookFinderImpl extends BasePersistenceImpl implements
BookFinder{
}
Step-5:
liferay portal custom sql ant build service
------
Do ant build-service, so that the necessary interface is generated.
Refresh the workspace in eclipse to see everything compiled properly.
Step-6:
liferay portal custom sql update bookfinderimpl
------
Now write the actual logic to access the custom SQL. You need to update the BookFinderImpl
we developed in the previous step.
// the name of the query
public static String GET_BOOKS = BookFinderImpl.class.getName()
+ ".getBooks";
// the method which will be called from the ServiceImpl class
public List<Book> getBooks(String pattern) throws SystemException {
Session session = null;
try {
// open a new hibernate session
session = openSession();
// pull out our query from book.xml, created earlier
String sql = CustomSQLUtil.get(GET_BOOKS);
// create a SQLQuery object
SQLQuery q = session.createSQLQuery(sql);
// replace the "Book" in the query string with the fully qualified java class
// this has to be the hibernate table name
q.addEntity("Book", BookImpl.class);
// Get query position instance
QueryPos qPos = QueryPos.getInstance(q);
// fill in the "?" value of the custom query
// this is same like forming a prepared statement
qPos.add(pattern);
// execute the query and return a list from the db
return (List<Book>)q.list();
/*
// use this block if you want to return the no. of rows (count)
int rows = 0;
Iterator<Long> itr = q.list().iterator();
if (itr.hasNext()) { Long count = itr.next();
if (count != null) { rows = count.intValue(); } }
return rows;
*/
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
Make the necessary additional imports.
import java.util.List;
import com.ext.portlet.library.model.Book;
import com.ext.portlet.library.model.impl.BookImpl;
import com.liferay.portal.SystemException;
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.util.dao.orm.CustomSQLUtil;
Note:
To get the result between a start and end index, you have to use,
QueryUtil.list(q, getDialect(), begin, end);
in the place of
q.list();
where, you will pass the parameters (begin and end) from your ServiceImpl class.
Step-7:
liferay portal custom sql write method booklacalserviceimpl.java
------
write the method in BookLocalServiceImpl.java
public List<Book> searchBook(String title) throws PortalException,
SystemException, RemoteException {
// return bookPersistence.findByTitle(title);
return BookFinderUtil.getBooks("%" + title + "%");
}
Step-8:
liferay portal custom sql and build service
------
run "ant build-service" again passing the service.xml file as parameter.
This will update the corresponding interface with the new method defined.
Step 9:
liferay portal custom sql add search2 button
-------
in view.jsp
Add for 'search2' button
<input type="button" value="Search2" onClick="<portlet:namespace />findBook('search2');" />
Note: the findBook JS function is now accepting a parameter, do changes to this function and also to the "Find Book" button.
Step 10:
liferay portal custom sql write code addbookaction.java
-------
Write code in AddBookAction.java to invoke the new finder API thru the BookFinderUtil.
if (Validator.isNull(cmd)) {
BookLocalServiceUtil.create(bookTitle);
} else {
List<Book> results = null;
if (cmd.equals("find")) {
results = BookLocalServiceUtil.findBooks(bookTitle);
} else if (cmd.equals("search")) {
DetachedCriteria dCriteria = DetachedCriteria.forClass(Book.class);
dCriteria.add(Restrictions.like("title", "%" + bookTitle + "%"));
DynamicQuery dynamicQuery = new DynamicQueryImpl(dCriteria);
results = (List)BookLocalServiceUtil.dynamicQuery(dynamicQuery);
} else {
results = (List)BookLocalServiceUtil.searchBook(bookTitle);
}
req.setAttribute("results", results);
req.setAttribute("cmd", cmd);
}
Step 11 :
liferay portal custom sql deploy
========
deploy :
verify we've done all the steps properly
ant deploy from ext-impl,
ant deploy-fast from ext-web,
restart tomcat
Congratulations !!!
Glossary:
--------
1. Look at the custom-sql xml files written for some liferay portlets.
2. How to replace strings in the sql statements using StringUtil.replace.
