How to connect Liferay 7.2 CE with Oracle database using JNDI

Question

If I have Liferay 7.2 Community Edition and want to use Oracle 12c as an external database for my business data then what would be the simplest and best way to connect using JNDI? Can I create DataSource in Tomcat and use JNDI lookup to connect with Liferay Service Builder?

Answer 1

Here is a simple way I have identified and using:

Step (1): Define/Map Entities into service.xml based on existing external databases. If tables are not present create all tables and fields manually because Liferay Service builder will not generate SQL code to automatically create tables in external database. If you want to use namespace, map manually below entities and define the same in database as well.

Service.xml

<entity local-service="true" name="Employee" table="employee" data-source="extDataSource" remote-service="false" uuid="false">
        <column name="employeeId" db-name="employeeid" primary="true" type="long" />
        <column name="groupId" db-name="groupid" type="long" />
        <column name="userName" db-name="username" type="String" />
    </entity>
</service-builder>

Step (2): Put following in LIFERAY-HOME\tomcat-9.0.17\conf\server.xml under

<Resource
    name="jdbc/myDataSource"
    auth="Container"
    type="javax.sql.DataSource"
    factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
    driverClassName="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:@localhost:1521:orcl"
    username="USERNAME"
    password="PASSWORD"
    maxActive="20"
    maxIdle="5"
    maxWait="10000"
/>

Step (3): Put following in LIFERAY-HOME\tomcat-9.0.17\conf\context.xml under Context

<ResourceLink name="jdbc/myDataSource" global="jdbc/myDataSource" type="javax.sql.DataSource"/>

Step (4): To connect with Oracle or any proprietary database following 2 JARs are needed:

• Oracle Driver: get the driver and put under liferay-ce-portal-7.2.1-ga2\tomcat-9.0.17\lib\ext
• External DB Support Library: Download the latest

Liferay Portal Database All In One Support JAR

from Maven repository

https://mvnrepository.com/artifact/it.dontesta.labs.liferay.portal.db/liferay-portal-database-all-in-one-support/1.2.1

Download the JAR liferay-portal-database-all-in-one-support-1.2.1.jar and put under LIFERAY-HOME\tomcat-9.0.17\webapps\ROOT\WEB-INF\lib

Step (5): Define followings into portal-ext.properties

jdbc.mydb.jndi.name=jdbc/myDataSource

Step (6): Create a DataSourceProviderImpl class under *-service:

package com.demo4.external.data.source.spi;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.liferay.portal.kernel.dao.jdbc.DataSourceFactoryUtil;
import com.liferay.portal.kernel.dao.jdbc.DataSourceProvider;
import com.liferay.portal.kernel.util.PropsUtil;

public class DataSourceProviderImpl implements DataSourceProvider {

@Override
public DataSource getDataSource() {

DataSource dataSource = null;

try {
            
dataSource = DataSourceFactoryUtil.initDataSource(PropsUtil.getProperties("jdbc.mydb.", true));

            
//  **Note:** Sometimes above line dosn't work in some environments, then follow below approach. In this case above Step(5) is not required because it's directly making lookup into server's context.


//  InitialContext initialContext = new InitialContext();
//  dataSource = (DataSource) initialContext.lookup("java:comp/env/jdbc/myDataSource");


        } catch (Exception e) {
            e.printStackTrace();
        }

        return dataSource;
    }
}

Step (7): Register above implementation with JDK SPI (Service Provider Interface). To do this, create following folder/file under *-service

META-INF/services/com.liferay.portal.kernel.dao.jdbc.DataSourceProvider

Put following line under this file:

com.demo4.external.data.source.spi.DataSourceProviderImpl

All done. Just build the service, do Gradle refresh and start the server. This will work perfectly.

Happy Learning!