liferay tutorial custom sql - liferay database configuration training

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.