Liferay 6: How to connect to an external database using the Service Builder

It’s possible to connect the Liferay Service Builder to a database different from the Liferay DB. It could be very useful and it’s pretty simple to set up.

Here are the steps:

  1. Add the JDBC DB information in the portal-ext.properties file
  2. Instantiate a new datasource using Spring configuration
  3. Use the new datasource in service.xml entities

1. Add the JDBC DB information in the portal-ext.properties file

In the portal-ext.properties, add the following properties to declare a new JDBC connection (called “myExternalDB” in this example) :

jdbc.myExternalDB.driverClassName=com.mysql.jdbc.Driver
jdbc.myExternalDB.url=jdbc:mysql://localhost:3306/db
jdbc.myExternalDB.username=root
jdbc.myExternalDB.password=root

2. Instantiate a new datasource using Spring configuration

Declare a new datasource in the file src/main/resources/META-INF/ext-spring.xml that points to the database you set in portal-ext.properties.

You also need to declare a custom hibernate session factory and a custom hibernate transaction manager otherwise the default datasource (Liferay) will be used for the hibernate layer.

<bean id="myExternalDS" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
    <property name="targetDataSource">
        <bean class="com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean">
            
        <!--<span class="hiddenSpellError" pre="" data-mce-bogus="1"-->bean>
    



    



    



    
    
    

3. Use the new datasource in service.xml entities

Let’s assume you have a very simple table called “DB_CAR” with columns:

  • CAR_ID (Primary Key)
  • CAR_NAME
  • CAR_YEAR

In the service.xml you should do the matching between the service layer you are going to generate and the existing table by names.

&lt;entity
    name=&quot;Car&quot;
    table=&quot;DB_CAR&quot;
    local-service=&quot;true&quot;
    remote-service=&quot;false&quot;
    data-source=&quot;myExternalDS&quot;
    session-factory=&quot;myExternalSessionFactory&quot;
    tx-manager=&quot;myExternalTransactionManager&quot;
&gt;
    &lt;column name=&quot;id&quot; db-name=&quot;CAR_ID&quot; type=&quot;long&quot; primary=&quot;true&quot; /&gt;
    &lt;column name=&quot;name&quot; db-name=&quot;CAR_NAME&quot; type=&quot;String&quot; /&gt;
    &lt;column name=&quot;year&quot; db-name=&quot;CAR_YEAR&quot; type=&quot;int&quot; /&gt;
&lt;/entity&gt;

Then you just have to generate the service layer and you will get access to the external database!
If you get errors, have a look to the datasource declaration, the user provided should have at least read rights on table you want to map.

That’s it! Feel free to contact me if you have any issues 🙂
(I used Liferay 6.1 EE in that example)

One thought on “Liferay 6: How to connect to an external database using the Service Builder

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s