javaoracle12cjndiliferay-service-builderliferay-7.2

How to connect Liferay 7.2 CE with Oracle database using JNDI

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?

Solution

  • 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:

    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!