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.

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.

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

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)