« Torna a Development

Extend Tables in Another Database

#

This article is based on Liferay 5.0.1 and Tomcat 6 bundle. Ext environment is 5.0.1, except all build.xml files are from trunk (5.0.2), as there were some minor failures with build files in 5.0.1. There are some differences between old and this new version of Liferay and Ext environment so I wanted to have the procedure written. Considering that sometimes community is not very fast and elaborative on answers this article might save you some time.

Preparation #

These installs can be anywere therefore I use aliases to keep it relative.

  • Liferay–Tomcat bundle is installed in folder e.g. liferay-portal-tomcat-6.0-5.0.1. which I will refer to as CATALINA_HOME.
  • Ext environment is installed in folder e.g. liferay-portal-ext-5.0.1 which I will refer to as EXT_ENV.
  • Liferay source 5.0.1 is located in e.g. liferay-portal-src-5.0.1 which I will refer to as LIFERAY_SOURCE.

Few other notes:

  • You need to be sure that app.server.Administrator.properties exists in EXT_ENV and that app.server.parent.dir points to CATALINA_HOME. Note: use slashes instead of backslashes.
  • If EXT_ENV is in directory with spaces and you execute ant from a dos box, use tilda () notation to avoid spaces, otherwise build will fail at certain phases. E.g. c:\program files\Ext should be c:\progra1\ext when working from dos box. Use /x parameter in dir if needed to find out short name. If using eclipse to execute ant, then this might not be relevant.

Example of app.server.Administrator.properties:

    app.server.type=tomcat
    app.server.parent.dir=C:/liferay-portal-tomcat-6.0-5.0.1
    
    app.server.tomcat.version=6.0
    app.server.tomcat.dir=${app.server.parent.dir}
    app.server.tomcat.classes.global.dir=${app.server.tomcat.dir}/lib
    app.server.tomcat.lib.endorsed.dir=${app.server.tomcat.dir}/lib/ext
    app.server.tomcat.lib.global.dir=${app.server.tomcat.dir}/lib/ext
    app.server.tomcat.lib.support.dir=${app.server.tomcat.dir}/lib/ext
    app.server.tomcat.support.dir=${app.server.tomcat.dir}/lib/ext
    app.server.tomcat.zip.name=liferay-portal-tomcat-6.0-${downloads.version.file.name}.zip

ROOT.XML #

Now, we can proceed to the real stuff. So we need to add the another jdbc resource to tomcat. We add that in CATALINA_HOME/conf/Catalina/localhost/ROOT.xml another Resource.

	<!-- MySQL -->

	<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost/lportal?useUnicode=true&amp;characterEncoding=UTF-8"
		username="root"
		password=""
		maxActive="20"
	/>

	<Resource
		name="jdbc/TrainingPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost/training?useUnicode=true&amp;characterEncoding=UTF-8"
		username="root"
		password=""
		maxActive="20"
	/>


	<!-- Oracle -->

We are done with tomcat and datasource here. Now we can move to the Ext environment.

service.xml #

Service xml is located at EXT_ENV/ext-impl and let’s append Training entity at the end before </service-builder>.

<entity name="Training" local-service="true" data-source="trainingDataSource" session-factory="trainingSessionFactory" tx-manager="trainingTransactionManager">
       <!-- PK fields -->
       <column name="userId" type="long" primary="true" />
       <!-- Other fields -->
       <column name="dogName" type="String" />
       <column name="wifeName" type="String" />
</entity>
</service-builder>

As you can see, we have 3 new attributes that we don’t use when we work with lportal database, and they are data-source, session-factory and tx-manager. You can assign these attributes to bean names of your choice, but I would like to follow Liferay naming pattern as above training + TransactionManater, training + SessionFactory, etc.

Now, we need to declare beans that we used in 3 attributes mentioned above . So we meet soon another important config file that we wouldn't have to create if we weren't intend to use a different database.

ext-data-source-spring.xml #

ext-data-source-spring.xml will be created inside EXT_ENV/ext-impl/src/META-INF folder. Actually you can name this file any way you like, but this naming this follows Liferay's pattern.

To keep the life easy, I copied original file from LIFERAY_SOURCE\portal-impl\src\META-INF\data-source-spring.xml and have saved it into EXT_ENV/ext-impl/src/META-INF/ext-data-source-spring.xml

Short explanation of changes - here's original bean:

<bean id="liferayDataSourceTarget" class="com.liferay.util.spring.jndi.PortalDataSourceFactoryBean" lazy-init="true" />

We are not using class PortalDataSourceFactoryBean as it directly points to the liferay datasource. We will use spring’s JndiObjectFactoryBean which takes one argument (property), jndiName, and that’s exactly what we need. Our replacement code is:

<bean id="trainingDataSourceTarget" class="com.liferay.portal.spring.jndi.JndiObjectFactoryBean" lazy-init="true">
	<property name="jndiName">
		<value>jdbc/TrainingPool</value>
	</property>
</bean>

Now, 3 other beans were changed, but these were simple changes, just renamed IDs from liferayDataSource, liferaySessionFactory, liferayTransactionManager to trainingDataSource, trainingSessionFactory, trainingTransactionManager. Note that I changed arguments (properties) to our new bean IDs. So the final code is:

<?xml version="1.0"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<!-- Copy from: liferay-portal-src-5.0.1\portal-impl\src\META-INF\data-source-spring.xml, paste here and then modify.  -->
manager="trainingTransactionManager"> -->
  
<beans>
	<bean id="trainingDataSourceTarget" class="com.liferay.portal.spring.jndi.JndiObjectFactoryBean" lazy-init="true">
		<property name="jndiName">
			<value>jdbc/TrainingPool</value>
		</property>
	</bean>
	<bean id="trainingDataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy" lazy-init="true">
		<property name="targetDataSource">
			<ref bean="trainingDataSourceTarget" />
		</property>
	</bean>
	<bean id="trainingHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortalHibernateConfiguration" lazy-init="true">
		<property name="dataSource">
			<ref bean="trainingDataSource" />
		</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>

</beans>

I hope you remember that jdbc/TrainingPool comes from ROOT.XML. Perhaps you wonder now, how Liferay will know about our newly created ext-data-source-spring.xml file and all beans we did there? Well, it wont - we have to tell Liferay to take our new xml into account.

Hopefully in some future version this ext-data-source-spring.xml might be already included in liferay implementation (vote LEP-6570) and then we might skip the following section, but for now, we have to do changes in portal-ext.properties and add our ext-data-source-spring.xml there, so let's see how.

portal-ext.properties #

This file is located at EXT_ENV/ext-impl/src (and later, after first build-service, at EXT_ENV/ext-impl/classes - always check that they are synchronized, unless you always do annoying ant clean) and we will again do some copy-pasting. So let’s open original file to get some inspiration: LIFERAY_SRC/portal-impl/classes/portal.properties and copy whole spring.configs section:

    spring.configs=\
        META-INF/activemq-spring-jms.xml,\
        META-INF/data-source-spring.xml,\
        META-INF/misc-spring.xml,\
        META-INF/counter-spring.xml,\
        META-INF/documentlibrary-spring.xml,\
        META-INF/lock-spring.xml,\
        META-INF/mail-spring.xml,\
        META-INF/mail-spring-jms.xml,\
        META-INF/portal-spring.xml,\
        META-INF/portal-spring-jcr.xml,\
        META-INF/portal-spring-jms.xml,\
        META-INF/ext-spring.xml

Now at the end add one more line, you guess know which one: META-INF/ext-data-source-spring.xml so our final EXT_ENV/ext-impl/src/portal-ext.properties looks like:

##
## You can override portal.properties by specifying your own settings in this
## file.
##
    spring.configs=\
        META-INF/activemq-spring-jms.xml,\
        META-INF/data-source-spring.xml,\
        META-INF/misc-spring.xml,\
        META-INF/counter-spring.xml,\
        META-INF/documentlibrary-spring.xml,\
        META-INF/lock-spring.xml,\
        META-INF/mail-spring.xml,\
        META-INF/mail-spring-jms.xml,\
        META-INF/portal-spring.xml,\
        META-INF/portal-spring-jcr.xml,\
        META-INF/portal-spring-jms.xml,\
        META-INF/ext-spring.xml,\
        META-INF/ext-data-source-spring.xml

It’s over with configuration. Lets build and see what happens.

Let's build #

Lets do from dos box in EXT_ENV/ext-impl folder: Ant build-service

No errors should appear. After generation is done, let’s do quick and dirty tirck to save some time. By now we should have two classes:

EXT_ENV\ext-impl\src\com\blah\blah\blah\blah\service\impl\TrainingLocalServiceImpl.java
EXT_ENV\ext-service\src\com\blah\blah\blah\blah\service\persistence\TrainingUtil.java

Open both if your favorite editor (be carefull don’t change TrainingUtil.java by accident!):

  • In your TrainingLocalServiceImpl class add import to TrainingUtil, e.g. import com.blah.blah.blah.service.persistence.TrainingUtil;
  • copy public functions from TraningUtil, starting from the first one (including that one) to the countAll() (including that one as well).
  • paste them inside the TrainingLocalServiceImpl class
  • Delete all static keywords (Find/replace static to [space])
  • Find/Replace or rename all getPersistence() to TrainingUtil

That’s all. However this is really a bad way to get TrainingLocalServiceImpl class implemented ! You need to add business logic to TrainingLocalServiceImpl. For the sake of simplicity of this article I have above trick to make it shorter.

Now rerun ant to promote our new public interface to other classes:

Ant build-service Ant compile Ant jar Ant deploy

This is end of java programing, if we might call it that way...

Note that after build-service that you did here again, few new classes were generated. One of them will be important for use later, and that's not located in persistance package (like TrainingUtil was that we mentioned above)!

EXT_ENV\ext-service\src\com\blah\blah\blah\blah\service\TrainingServiceUtil.java. 

Create database and table #

Last task is to create table in the training database, and create database as well. Use your favorite tool to create database. DDL to create table is in EXT_ENV/sql/portal-tables.sql, open it and search for Training. If it's not there, just type "ant" from dos box in EXT_ENV/sql and it will get generated.

create table Training (
	userId LONG not null primary key,
	dogName VARCHAR(75) null,
	wifeName VARCHAR(75) null
);

If using MySql you might want to modify it like this:

CREATE TABLE `training` (
  `userId` bigint(20) NOT NULL DEFAULT '0',
  `dogName` varchar(75) DEFAULT NULL,
  `wifeName` varchar(75) DEFAULT NULL,
  PRIMARY KEY  (`userId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

You can add two lines to table manually:

insert into training.training values (1,"tom","anna");
insert into training.training values (2,"boxy","silvy");

Testing from existing portlet #

Finally add this two lines of code in any of your backing bean init function (if using JSF), to test if it’s working:

Note you need:

  • appropriate "import ... service.TrainingLocalServiceUtil;" in your existing backing bean. (from ext-service)
  • ext-impl.jar in your WEB-INF/lib folder of your portlet.

Warning: be sure not to "import ...service.persistence.TrainingUtil;" (also from ext-service), as calling findAll() from this class/package will cause binding exceptions.

List a=TrainingLocalServiceUtil.findAll();
System.out.println("--> Size of Training table in training database: "+a.size());

And console output should be:

--> Size of Training table in training database: 2

Now. It’s really over.

Troubleshooting #

However, few hints. As the final result of above procedure you will have 2 jar files generated:

EXT_ENV/ext-impl/ext-impl.jar
EXT_ENV/ext-service/ext-service.jar

During build:

  • ext-impl.jar should coitain ext-data-source-spring.xml in META-INF directory, as well as other 3 ext- xmls (hbm, model-hints and spring). If not check your build.xml files and take new ones from the trunk (5.0.2 currently).

During deploy:

  • ext-service.jar will be copied to the CATALINA_HOME/lib/ext. Check that it’s there.
  • ext-impl.jar will be copied to the CATALINA_HOME/ webapps\ROOT\WEB-INF\lib. Check that it’s there
  • CATALINA_HOME/webapps/ROOT/WEB-INF/classes should contain 2 property files: portal-ext.properties and system-ext-roperties, where portal-ext.properties should have content as we wrote above. If not copy it manually and check your build files and take new ones from the trunk (5.0.2 currently).

When using this service in your portlet:

  • ext-impl.jar should also be included in your portlet lib directory.

See Also #

You could achieve similar goals by developing with Expando

0 Allegati
44468 Visualizzazioni
Media (0 Voti)
La media del punteggio è 0.0 stelle su 5.
Commenti
Commenti Autore Data
When you get an Error like this: Name jdbc is... Michel H. 2 dicembre 2008 2.34
In version 5.1.2 you may get exception like: ... petar banicevic 24 maggio 2009 17.02
I've been trying to get this working in liferay... N. Belo 21 agosto 2009 10.08
I work on liferay source version 5.2.2 and I've... Mahdy Khayyamian 4 settembre 2009 5.01
I tried using propertyPrefix like you mention... alex ace 8 dicembre 2009 19.00
Can we achieve this using plugins sdk or using... Faris Abdulla 24 febbraio 2010 4.11

When you get an Error like this: Name jdbc is not bound in this Context...

Set up your new Database resource in "context.xml" and not in "ROOT.xml" and its work.
Inviato il 02/12/08 2.34.
In version 5.1.2 you may get exception like:
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(String.java:1768)
at com.liferay.portal.tools.servicebuilder.ServiceBuilder._createRemotingXml(Servic­eBuilder.java:2165)
at com.liferay.portal.tools.servicebuilder.ServiceBuilder.<init>(ServiceBuilder.jav­a:971)
at com.liferay.portal.tools.servicebuilder.ServiceBuilder.<init>(ServiceBuilder.jav­a:390)
at com.liferay.portal.tools.servicebuilder.ServiceBuilder.main(ServiceBuilder.java:­157)

Quick and dirty solution is to open ext-web/docroot/WEB-INF/remoting-servlet-ext.xml

and replace </beans> with <beans></beans>

Better solution would be to fetch newer build file.
Inviato il 24/05/09 17.02.
I've been trying to get this working in liferay 5.2.3, but I am finding some problems using another database source.
Also, my development environment is a bit different. I am using:
- Liferay 5.2.3
- SQL server 2005
- Netbeans 6.7 + Portal Pack 3.0.

I work just fine, in this environment, using liferay and the SQL database, but...

I use Portalpack to generate de services, and all my code(service, model, etc) is in the portlet. It seems to me more simple to build portlets this way because I don't have to change or add anything in liferay's ext environment.

My portlet META-INF folder as the following xml files(I've added context.xml to reference another database connection) and I've included this reference in the service.properties
"WEB-INF/classes/META-INF/context.xml"

- base-spring.xml
- dynamic-data-source-spring.xml
- hibernate-spring.xml
- infrastructure-spring.xml
- portlet-hbm.xml
- portlet-model-hints.xml
- portlet-spring.xml
- context.xml

Do you have any sample using this environment or Liferay 5.2.x?
Thanks
Inviato il 21/08/09 10.08 in risposta a petar banicevic.
I work on liferay source version 5.2.2 and I've been successful configuring another database connection following the instructions of this page, though I had to do it slightly different.
More precisely, I have added connection info in portal.properties file below the current default connection like this:

#
# MySQL
#
jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncodin­g=UTF-8&useFastDateParsing=false
jdbc.default.username=root
jdbc.default.password=8145

jdbc.idl.driverClassName=com.mysql.jdbc.Driver
jdbc.idl.url=jdbc:mysql://localhost/iin_mis_dbo?useUnicode=true&characterEncodin­g=UTF-8&useFastDateParsing=false
jdbc.idl.username=root
jdbc.idl.password=8145

Note that the first five lines was present and I added the next five lines for my custom connection.

My added spring configuration file is somewhat different.

<?xml version="1.0"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "[http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
<bean id="IDLDataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
<pr­operty name="targetDataSource">
<bean class="com.liferay.portal.dao.jdbc.util.DataSourceFactoryBean">
<proper­ty name="propertyPrefix" value="jdbc.idl." />
</bean>
</property>
</bean>

<bean id="IDLHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortalHibernateConfiguration" lazy-init="true">
<property name="dataSource">
<ref bean="IDLDataSource" />
</property>
</bean>
<bean id="IDLSessionFactory" class="com.liferay.portal.dao.orm.hibernate.SessionFactoryImpl" lazy-init="true">
<property name="sessionFactoryImplementor">
<ref bean="IDLHibernateSessionFactory" />
</property>
</bean>
<bean id="IDLTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager" lazy-init="true">
<property name="dataSource">
<ref bean="IDLDataSource" />
</property>
<property name="sessionFactory">
<ref bean="IDLHibernateSessionFactory" />
</property>
</bean>
</beans>


The difference is that I use the same class for IDLTargetDataSource as liferay uses and that is com.liferay.portal.dao.jdbc.util.DataSourceFactoryBean. Note that this class takes
a property named "propertyPrefix" that should be correctly assigned in order to find the connection properties in portal.properties file. here it is assigned "jdbc.idl."
I have used the same procedure for the rest of configuration.

Good luck!
Inviato il 04/09/09 5.01 in risposta a N. Belo.
I tried using propertyPrefix like you mention above. However, I can't seem to pull any data from my table..I'm not getting any errors so am assuming that the datasource connected etc...
Inviato il 08/12/09 19.00 in risposta a Mahdy Khayyamian.
Can we achieve this using plugins sdk or using netbeans portal pack portlet.

Currently this sample shows for EXT environment..
Inviato il 24/02/10 4.11 in risposta a alex ace.