Total Pageviews

Saturday, April 5, 2014

Liferay: Connecting Portlet to an External Database Table

Sometimes we need to fetch data from two different databases in one project. There are several approaches, such as creating a new hibernate/spring session or fetching through web services. Both have their advantage and disadvantages. This article explains how we can create another session for fetching data from a database which is not configured as the portal's default database, but as some other (custom).
The problem with this approach is with the table creation, which is Liferay's obvious behavior. Why? I'll explain this in the end of this article.

1. First of all, create a new portlet with a service.xml file. Now build the service. For example, we create the following service. Here we need to know that the data-source value specifies the target data source which is the persistence class. The session-factory value specifies the session factory which is also set to the persistence class.The tx-manager value specifies the transaction manager which is used by Spring services. Rest of the things are pretty standard.

2. As we have built the service, Now its time to create table in the external database we're going to connect our portlet.

3. Now we have to supply the database connection details to our code. We can hardcode the database connection details directly in XML file directly, but its handy if  use the approach Liferay uses, i.e. through property file. So, lets just create some custom entries in portal-ext.properties file, not to mention you have to change the connection details as per your's:
4. As connection details now available, we have to use them to create a new session. So, for this we have to do spot the spring-ext.xml file which gets generated as soon as we build the service. The path of this file is: \docroot\WEB-INF\src\META-INF. We have to add the following entries in the end of this file:
Now we are done. We can now simple write LocalServiceInple code to access data from external database's table.

For the security reason, Liferay does not allow to create new table ontside the default DB. If you want to analyze liferay SRC, you can see the ServiceBuilder class's _createSQLTables method, where there is a condition check for default database.

You can download sample portlet from here. Hope this article will help you.

7 comments:

  1. Hi Apporva,

    It was a nice post, thanks for sharing this! You have shown how to connect to an external database using liferay service builder. But, how do I connect to an external database's table which is not created by service.xml?

    Please reply!

    ReplyDelete
    Replies
    1. Hi Anindya,

      Get the DTDand create your service.xml amd model-hints as per the same. It will work.

      Delete
  2. Hi Apporva,
    I have the same question i.e is it's possible to connect to an INFORMIX server table which is not created by service.xml. Please help me, it's urgent

    ReplyDelete
  3. Hi All,
    For retrieving data from Informix 11.5 in Liferay using service layer refer below link
    http://www.mpowerglobal.com/retrieving-data-from-ibm-informix-11-5-dynamic-server-in-liferay-6-2-ce/

    ReplyDelete
  4. Hibernate Online Training Hibernate Online Training Hibernate Training in Chennai Hibernate Training in Chennai Java Online Training Java Online Training Hibernate Training Institutes in ChennaiHibernate Training Institutes in Chennai

    ReplyDelete
  5. Hello All,
    Is it possible to connect to more than one external database.

    ReplyDelete