« Back

Using a Legacy DB with Service Builder

Company Blogs December 16, 2010 By Sten Martinez Staff

Often we need to display data in a legacy schema. The usual way of doing this in any other application is to set up a new datasource, optionally set up an ORM, and display the data yourself. However, in Liferay there is a way to simplify this step considerably by using Service Builder.

"Service Builder?" you say, "I thought that was only for creating entities in the Liferay DB?"

Well, yes, it is - but by using stock functionality we can use Service Builder's powerful code generation tools to create our own service and persistence layer. Building on the method introduced here, we can modify the service generation and Spring config to get SB to talk to our existing DB and even use Hibernate Mappings! 

Lets use an example.

Say we have a simple schema:


CREATE TABLE book(book_id INT NOT NULL PRIMARY KEY, book_name VARCHAR(50));  CREATE TABLE book_inventory(book_id INT NOT NULL PRIMARY KEY, book_quantity INT);

where the book_id columns are related.

The first step is to create a properly set-up service.xml to map to these tables:


<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.0.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_0_0.dtd">

<service-builder package-path="com.liferay.training">

<author>Sten Martinez</author>


<entity name="Book" table="book" local-service="true" 

data-source="trainingDataSource" session-factory="trainingSessionFactory" tx-manager="trainingTransactionManager">

<column name="book_id" type="long" primary="true"/>

<column name="book_name" type="String"/>


<entity name="BookInventory" table="book_inventory" local-service="true" 

data-source="trainingDataSource" session-factory="trainingSessionFactory" tx-manager="trainingTransactionManager">

<column name="bookId" db-name="book_id" type="long" primary="true"/>

<column name="bookQuantity" db-name="book_quantity" type="int"/>



By using the table and db-name parameters we can force service builder to map to the right names. more importantly, we have specified our own persistence and transaction manager bean names.

After running build-service you may notice that the beans we specified arent created; that's cool, we get to do that.

If you look in the service.properties file, there is a reference to a spring config file called ext-spring,xml, which doesnt exist in our META-INF directory. This is a useful extension point for providing our own overrides and bean defs.


 <!--  Overrides -->

<bean id="transactionAdvice" class="org.springframework.transaction.interceptor.TransactionInterceptor">

<property name="transactionManager" ref="trainingTransactionManager" />

<property name="transactionAttributeSource">

<bean class="org.springframework.transaction.annotation.AnnotationTransactionAttributeSource">


<bean class="com.liferay.portal.spring.annotation.PortalTransactionAnnotationParser" />





<bean id="basePersistence" abstract="true">

<property name="dataSource" ref="trainingDataSource" />

<property name="sessionFactory" ref="trainingSessionFactory" />


<!-- Custom Beans -->

<bean id="trainingHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortalHibernateConfiguration" lazy-init="true">

<property name="dataSource">

<ref bean="trainingDataSource" />


<property name="mappingResources">






<bean id="trainingSessionFactory" class="com.liferay.portal.dao.orm.hibernate.SessionFactoryImpl" lazy-init="true">

<property name="sessionFactoryImplementor">

<ref bean="trainingHibernateSessionFactory" />



<bean id="trainingTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager" lazy-init="true">

<property name="dataSource">

<ref bean="trainingDataSource" />


<property name="sessionFactory">

<ref bean="trainingHibernateSessionFactory" />



<bean id="trainingDataSourceTarget" class="com.liferay.portal.spring.jndi.JndiObjectFactoryBean" lazy-init="true">

<property name="jndiName">




<bean id="trainingDataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy" lazy-init="true">

<property name="targetDataSource">

<ref bean="trainingDataSourceTarget" />



now all we have to do to display this is a simple jsp with a search container:


<liferay-ui:search-container emptyResultsMessage="empty-results-message">


results="<%= BookLocalServiceUtil.getBooks(searchContainer.getStart(), searchContainer.getEnd()) %>"

total="<%= BookLocalServiceUtil.getBooksCount() %>"






<liferay-ui:search-container-column-text property="bookId" />

<liferay-ui:search-container-column-text property="bookName" />


<liferay-ui:search-iterator />


and voila! we can see a list of book names. However, this is still only mapping half of the schema. You might have noticed that the portlet-hbm.xml is actually specified in the ext-spring config. This allows us to change it, and map the other (subordinate) table.

we first need to create a new file, portlet-hbm-ext.xml, and fill it with the contents of the original. then we add a single line:


<hibernate-mapping default-lazy="false" auto-import="false">

<import class="com.liferay.training.model.Book" />

<import class="com.liferay.training.model.BookInventory" />

<class name="com.liferay.training.model.impl.BookImpl" table="book">

<cache usage="read-write" />

<id name="book_id" type="long">

<generator class="assigned" />


<property name="book_name" type="com.liferay.portal.dao.orm.hibernate.StringType" />

<one-to-one name="bookInventory" class="com.liferay.training.model.impl.BookInventoryImpl"/>


<class name="com.liferay.training.model.impl.BookInventoryImpl" table="book_inventory">

<cache usage="read-write" />

<id name="bookId" column="book_id" type="long">

<generator class="assigned" />


<property name="bookQuantity" column="book_quantity" type="com.liferay.portal.dao.orm.hibernate.IntegerType" />



This Hibernate mapping will, by default, join the other table to our own. We still have some work to do to get this into the BookImpl model object.

Since BookImpl is the actual persistent class, we need to modify it to put a field in for the display layer to get access to the BookQuantity property, and we also need a property for Hibernate to populate with the related BookInventoryImpl class. 

So lets add some properties to BookImpl:


public int getBookQuantity() {

return _bookInventory.getBookQuantity();


public void setBookQuantity(int bookQuantity) {



public BookInventory getBookInventory() {

return _bookInventory;


public void setBookInventory(BookInventory bookInventory) {

_bookInventory = bookInventory;


private BookInventory _bookInventory;

After re-building the service, our methods should be accessible to the display layer, so if we add

<liferay-ui:search-container-column-text property="bookQuantity"/>

to our search container, it will display the quantity.


Also note that BookQuantity is technically a persistent field via the BookInventory property. We can get and set using the Book model class and never touch the BookInventory service methods.






Threaded Replies Author Date
Hi Sten, really useful post. Just a question... Denis Signoretto December 17, 2010 12:21 AM
Since Service Builder depends heavily on the... Sten Martinez December 17, 2010 10:27 AM
Actually Denis, you could, but keep in mind... Michael C. Han December 20, 2010 1:38 AM
@Michael Hi Michael, very useful answer. ... Denis Signoretto December 20, 2010 5:45 AM
Great post Sten. I really like the way you... Jeffrey Handa December 17, 2010 6:13 AM
Nice post! Thanks, Sten. Jonas Yuan December 17, 2010 6:58 AM
Just what i was looking for. Thanks Sandeep Nair December 17, 2010 8:32 AM
Good post Sten ! Hoping that config of one or... Jack Bakker January 1, 2011 11:52 AM
I have read in a forum entry you can create a... Aniceto P Madrid March 13, 2011 5:24 AM
[...] tldr; clause: My questions are: - What am... Anonymous September 8, 2011 4:16 AM
[...] Sten Martinez wrote a very nice article... Anonymous April 17, 2013 3:26 PM
[...] Sten Martinez wrote a very nice article... Anonymous April 26, 2013 10:36 PM
Great description thanks.... kailash b May 9, 2013 2:35 AM
An update for version 6.1 would be great! Kay Kay June 5, 2013 8:23 AM
Thanks. This was exactly what I was looking... Muradali Hasan July 2, 2013 7:43 PM

Hi Sten,

really useful post. Just a question about Service Builder. Can I use ServiceBuilder in a standalone Java application running outside Liferay Portal? (using jdbc, without web services).

Posted on 12/17/10 12:21 AM.
Great post Sten. I really like the way you designed your sample database schema ;)
Posted on 12/17/10 6:13 AM.
Nice post! Thanks, Sten.
Posted on 12/17/10 6:58 AM.
Just what i was looking for. Thanks
Posted on 12/17/10 8:32 AM.
Since Service Builder depends heavily on the Liferay framework, no.
Posted on 12/17/10 10:27 AM in reply to Denis Signoretto.
Actually Denis, you could, but keep in mind this hasn't been tried with custom plugins. This would also mean importing most of the portal when you're doing so: it's like running the portal's complete services tier without all the servlets, etc. To bootstrap the portal at startup, you'll need to run:


This will perform all the Spring wiring needed for the Liferay out of box services.

As I said, this should work in theory.
Posted on 12/20/10 1:38 AM in reply to Denis Signoretto.

Hi Michael, very useful answer.

Posted on 12/20/10 5:45 AM in reply to Michael C. Han.
Good post Sten !

Hoping that config of one or more other 'legacy' dbs gets into the IDE (I posted a JIRA new feature request a while back).

Also be good to alternatively pick up datasource properties from
portal-ext.properties (I haven't figured this out yet).

I also still need to resolve : where other apps modifying the 'legacy' db - seems clearing liferay db cache (Server Admin) is often needed before changes seen via services...
Posted on 1/1/11 11:52 AM.
I have read in a forum entry you can create a new method in local service, and call there some method to pack all them in a single transaction. Could be used here a JTA tx manager to create XA transactions?
Posted on 3/13/11 5:24 AM.
[...] tldr; clause: My questions are: - What am i still missing in my configuration (or doing wrong) - Can services in the same hook/plugin map to different datasources? /tldr; clause Thread: For a... [...] Read More
Posted on 9/8/11 4:16 AM.
[...] Sten Martinez wrote a very nice article about Using a Legacy DB with Liferay Service Builder: http://www.liferay.com/web/sten.martinez/blog/-/blogs/using-a-legacy-db-with-ser­vice-builder It... [...] Read More
Posted on 4/17/13 3:26 PM.
[...] Sten Martinez wrote a very nice article about Using a Legacy DB with Liferay Service Builder: http://www.liferay.com/web/sten.martinez/blog/-/blogs/using-a-legacy-db-with-ser­vice-builder It... [...] Read More
Posted on 4/26/13 10:36 PM.
Great description thanks....
Posted on 5/9/13 2:35 AM.
An update for version 6.1 would be great!
Posted on 6/5/13 8:23 AM.
Thanks. This was exactly what I was looking for. I had one table that already existed and wanted to use that table. I wanted to avoid creating another table with the namespace as the prefix. This post gave me the answer.
Posted on 7/2/13 7:43 PM.