Establish an external data source using JNDI

Issue

• Sometimes there is a requirement to use data sources other than Liferay DXPs. To do this, the data source can be configured via JNDI data source or JDBC properties.
• Any database other than Liferay DXP's database is referred to as an external database. The procedures outlined in this document are for setting up an external database using JNDI.

Environment

• Liferay DXP 7.3
• MySQL database

Resolution

• The following steps are:
  

  (1) MySQL commands to create the database:

  create database external character set utf8;
  use external;
  create table external_Sample (
  name VARCHAR(75) null,
  id_ bigint not null primary key
  );

  (2) Configure JNDI Resource entry in server.xml like below in tomcat/conf

  <GlobalNamingResources>
  <Resource
  name="jdbc/externalDataSource"
  auth="Container"
  type="javax.sql.DataSource"
  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
  driverClassName="com.mysql.cj.jdbc.Driver"
  url="jdbc:mysql://localhost/external?characterEncoding=UTF-8"
  username="root"
  password="root"
  maxActive="20"
  maxIdle="5"
  maxWait="10000"
  />
  </GlobalNamingResources>

  (3) Create Resource link in context.xml file in tomcat/conf

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

  (4) Create a com.liferay.blade.samples.jndiservicebuilder.service-log4j-ext.xml in your Liferay DXP instance’s [LIFERAY_HOME]/osgi/log4 folder. Create this folder if it doesn’t yet exist. Add this content to the XML file that you created.

  <?xml version="1.0"?>
  <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
  <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
  <category name="com.liferay.blade.samples.jndiservicebuilder.service.impl">
  <priority value="INFO" />
  </category>
  </log4j:configuration>

  (5) Restart the server.
  (6) Deploy the attached jars one by one (A sample plugin with three jars for API, Service, and web portlet is attached)
  (7) Drag the 'Hello from NewExternalDbSbWeb' portlet from the sample option on a page
  (8) Fill out the name
  (9) Check your external database for external_sample table entry.
  Expected Behavior: The name is saved in the target external database table.

Additional Information

• LDS version:3.9.5.202112170330-ga6

• Create a service builder module project and in the Service Builder module’s service.xml file, set the entity’s data source to the liferayDataSource. If the above sample plugin was not used for testing, a parent context extension (e.g.,ext-spring.xml) should be created in the service module's src/main/resources/META-INF/spring folder or the traditional portlet's WEB-INF/src/META-INF folder. This file is used to overwrite a liferayDataSource that already exists.

  <?xml version="1.0"?>
  <beans default-destroy-method="destroy"
  default-init-method="afterPropertiesSet"
  xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
  
  <bean
  class="com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean"
  id="liferayDataSourceFactory"
  >
  <property name="propertyPrefix" value="custom." />
  <property name="properties">
  <props>
  <prop key="custom.jndi.name">jdbc/externalDataSource</prop>
  </props>
  </property>
  </bean>
  <!-- The data source bean refers to the factory to access the data source. -->
  <bean
  class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy" 
  id="liferayDataSource">
  <property name="targetDataSource"
  ref="liferayDataSourceFactory" />
  </bean>
  
  <!-- In service.xml, we associated our entity with the extDataSource. To 
  associate the extDataSource with our overridden liferayDataSource, we define 
  this alias. -->
  <alias alias="extDataSource" name="liferayDataSource" />
  </beans>

• Place mysql.jar in {Liferay-Home}/tomcat/lib/ext before starting the server.
• Liferay's Service Builder is unable to generate a table for the external data source.
  The tables in the external database have to be manually created. As per the documentation, the Service Builder will only manage Liferay internal tables. ServiceBuilder, on the other hand, will not manage the tables, indexes, etc. for any external databases. However, the POC provided here works on 7.3 and can insert a value from the user interface into an external MySQL database.
• Establish an external data source using JDBC