Using a Legacy DB with Service Builder

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>

<namespace>BookInventory</namespace>




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

<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"/>

</entity>

</service-builder>

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.

ext-spring:

 <!--  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">

<constructor-arg>

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

</constructor-arg>

</bean>

</property>

</bean>

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

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

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

</bean>

<!-- Custom Beans -->

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

<property name="dataSource">

<ref bean="trainingDataSource" />

</property>

<property name="mappingResources">

            <list>

                <value>META-INF/portlet-hbm.xml</value>

            </list>

</property>

</bean>

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

<property name="sessionFactoryImplementor">

<ref bean="trainingHibernateSessionFactory" />

</property>

</bean>

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

<property name="dataSource">

<ref bean="trainingDataSource" />

</property>

<property name="sessionFactory">

<ref bean="trainingHibernateSessionFactory" />

</property>

</bean>

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

<property name="jndiName">

<value>jdbc/legacyDB</value>

</property>

</bean>

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

<property name="targetDataSource">

<ref bean="trainingDataSourceTarget" />

</property>

</bean>

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

<liferay-ui:search-container-results

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

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

/>




<liferay-ui:search-container-row

className="com.liferay.test.model.Book"

modelVar="aBook"

>

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




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


</liferay-ui:search-container-row>




<liferay-ui:search-iterator />

</liferay-ui:search-container>

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" />

</id>

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

<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" />

</id>

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

</class>

</hibernate-mapping>

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) {

_bookInventory.setBookQuantity(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.

 

 

 

 

 

[Denis Signoretto]

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).

Thanks,
Denis.

[Sten Martinez]

Since Service Builder depends heavily on the Liferay framework, no.

[Michael C. Han]

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:

InitUtil.initWithSpring();

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

As I said, this should work in theory.

[Denis Signoretto]

@Michael

Hi Michael, very useful answer.

Thanks,
D.

[Jeffrey Handa]

Great post Sten. I really like the way you designed your sample database schema ;)

[Jonas X. Yuan]

Nice post! Thanks, Sten.

[Sandeep Nair]

Just what i was looking for. Thanks

[Jack Bakker]

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

[Aniceto P Madrid]

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?

[(You)]

[...] 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-service-builder It... [...] Read More

[(You)]

[...] 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-service-builder It... [...] Read More

[kailash b]

Great description thanks....

[Kay Kay]

An update for version 6.1 would be great!

[Muradali Hasan]

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.

[Paul Allain]

An update for 7.0 would be even greater.