How to use Custom-sql in Liferay?

Step-1:
------

Create the file default-ext.xml under ext-impl/src/custom-sql (cerate custom-sql folder if it is not there)

<?xml version="1.0"?>

<custom-sql>
<sql file="custom-sql/book.xml" />
</custom-sql>

(You can refer default.xml under portal source)

Step-2:
------

Create the file queries.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="ur_id">
<![CDATA[
SELECT
{Book.*}
FROM
Book
WHERE
(Book.title like ?)
]]>
</sql>
</custom-sql>


Step-3:
------

Add your entry in portal-ext.properties

custom.sql.configs=\
custom-sql/default.xml, \
custom-sql/default-ext.xml

Step-4:
------

Create the file "BookFinderImpl.java" under service/persistence and do the required imports

public class BookFinderImpl extends BasePersistenceImpl implements
BookFinder{

}


Step-5:
------

Do ant build-service, so that the necessary interface is generated.

Step-6:
------

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_QUERY = "id_given_in_queries.xml";


// the method which will be called from the ServiceImpl class
public List getBooks(String pattern) throws SystemException {

Session session = null;
try {
// open a new hibernate session in normal case when you are opening session for same entity
session = openSession();
// In case of other entity you set the session first like below then open the session
setSessionFactory((SessionFactory)PortalBeanLocatorUtil.getBeanLocator().locate(TagsAssetModelImpl.SESSION_FACTORY));
session = openSession();

// pull out our query from book.xml, created earlier
String sql = CustomSQLUtil.get(GET_QUERY);

// create a SQLQuery object
SQLQuery q = session.createSQLQuery(sql);

//In normal case : In our case book use this
q.addEntity("Book", BookImpl.class);

//In other case : In our case TagsAsset use like this
q.addEntity(TagsAssetModelImpl.TABLE_NAME, TagsAssetImpl.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)q.list();

/*
// use this block if you want to return the no. of rows (count)

int rows = 0;

Iterator 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:
------

write the method in BookLocalServiceImpl.java

public List searchBook(String title) throws PortalException,
SystemException, RemoteException {

// return bookPersistence.findByTitle(title);
return BookFinderUtil.getBooks("%" + title + "%");
}


Step-8:
------

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:
-------

Now go ahead and call BookLocalServiceImpl method from your jsp or java normally how you call other methods

Thanks