Configuring multiple database in Liferay 6

[Suman Kumar, módosítva 12 év-val korábban]

Hi all,

In our project we have the following requirement:

1. We want to use the Liferay User, Groups etc feature provided by liferay which uses the default 'lporrtal' database.
2. We want to get the application related data from other database (legacy) .

Now I have following query:
1. Is it possible to configure multiple database in Liferay?
2. If yes, what (or which) configuration changes we have to make so that we continue using lportal for User and Administration related work and Legacy for business data.

Unfortunatly I didn't found any doument which address this issue?

Thanks and Regards
Suman Kumar

[Hitoshi Ozawa, módosítva 12 év-val korábban]

Just use lportal for liferay and develop portlets to interact with your legacy system. How your portlet interacts with your application is based on how you design your portlet - e.g. you can desigin to make a portlet do a jdbc access directly to your application database.

[Suman Kumar, módosítva 12 év-val korábban]

Thanks.

But I want to use the Liferay Service builder to access the lagecy databse.

So first let me know it is possible to configure two database details? If yes the steps I need to follow.

Thanks.
Suman Kumar

[Hitoshi Ozawa, módosítva 12 év-val korábban]

Look up Database sharding. Google "liferay sharding".

BTW, I think you'll probably save yourself and your company a lot of time and money by
hiring someone who actually have experience doing this. It's really a very basic question,
and I'm sure there are many qualified people in your area who have already done it. It's really
a waste of our time explaining the same question over and over again.

[Sagar A Vyas, módosítva 12 év-val korábban]

Suman Kumar:

Hi all,

In our project we have the following requirement:

1. We want to use the Liferay User, Groups etc feature provided by liferay which uses the default 'lporrtal' database.
2. We want to get the application related data from other database (legacy) .

Now I have following query:
1. Is it possible to configure multiple database in Liferay?
2. If yes, what (or which) configuration changes we have to make so that we continue using lportal for User and Administration related work and Legacy for business data.

Unfortunatly I didn't found any doument which address this issue?

Thanks and Regards
Suman Kumar

Hi Suman,

Yes it is possible to have two database in liferay .

In other word you may have two DB entry in portal-ext.properties.

More info. Please have look into following link

http://www.liferay.com/web/sten.martinez/blog/-/blogs/6831821

Thanks,
Sagar Vyas

[Suman Kumar, módosítva 12 év-val korábban]

Hi Sagar,

Thanks. But the link you have suggested doesn't open (i.e abort's with closing warning's) so I am unable to go though the contents it has.

Please let me know any other link or let me know the steps or confg changes I need to do to make the other datasource work with service builder.

Also I have another query. In service.xml very limited data types are avaible for column data type. If I have to use other data types like BLOB etc what I need to do?

With Regards
Suman Kumar

[Sagar A Vyas, módosítva 12 év-val korábban]

Hi Suman,

Try this : http://www.liferay.com/web/sten.martinez/blog

or

Just Try search in Google with : Using a Legacy DB with Service Builder : blog + Liferay

You will get first link.

Hope this help

Thanks,
Sagar Vyas

[Suman Kumar, módosítva 12 év-val korábban]

Hi Sagar,

I tried.... ... but the link opens and then it gives windows abort operation warning prompt ... and closes the link.

Are you not facing this issue with this link?

Thanks and Reards
Suman Kumar

[Suman Kumar, módosítva 12 év-val korábban]

Hi Sagar,

I am able to access now .... It was not allowing to open it in IE... in Mozilla its fine.

Thanks.
Suman

[Ravi Kumar Gupta, módosítva 12 év-val korábban]

Thanks Sagar, that was quite useful information.

Suman: Copying the whole content from blog here.

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



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

Source: http://www.liferay.com/web/sten.martinez/blog/-/blogs/6831821
HTH

[Suman Kumar, módosítva 12 év-val korábban]

Hi Ravi,

How to configure the ext-psring.xml for multiple datasource?

i.e. multiple datasource in single service.xml.

Regards
Suman

[Sagar A Vyas, módosítva 12 év-val korábban]

Suman Kumar:

Hi Ravi,

How to configure the ext-psring.xml for multiple datasource?

i.e. multiple datasource in single service.xml.

Regards
Suman

Hi Suman,

You can find your answer over here http://www.liferay.com/community/forums/-/message_boards/message/11565910

Thanks,
Sagar Vyas

[shima shima, módosítva 9 év-val korábban]

would u please help me I have a problem inverse this . I have 3 portal with 1 database .all things are good but I have cash problem I mean if I update information in one portal it will be cashed in others.would you please help me?
I think you can solve your problem with shard. in this case you will have 1 database in some seprate machin