Establish an external data source using JDBC

Issue

This article outlines how to set up a connection between Liferay and an external database using JDBC properties.

Environment

Liferay DXP 7.3
Liferay DXP 7.4
MySQL database

Resolution

There are two ways to connect Service Builder to an external database in Liferay: DataSourceProvider and Spring. In this case, the Spring dependency injector was used in the service builder module.

The steps that need to be performed are listed following:
1) Add jdbc.default for Liferay dxp DB and jdbc.ext for external DB

jdbc.default.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/dxp_db?characterEncoding=UTF-8&dontTrackOpenResources=true&holdResultsOpenOverStatementClose=true&serverTimezone=GMT&useFastDateParsing=false&useUnicode=true
jdbc.default.username=root
jdbc.default.password=root

jdbc.ext.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.ext.password=root
jdbc.ext.url=jdbc:mysql://localhost/external?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.ext.username=root

2) Create one database and a table

create database external character set utf8;

use external;

create table external_Sample (
name VARCHAR(75) null,
id_ bigint not null primary key
);

3)Restart the server.
4) Deploy the attached jars one by one (A sample plugin with three jars for API, Service, and web portlet is attached)
5) Drag the 'Hello from NewExternalDbSbWeb' portlet from the sample option on a page

6) Fill out the name
7) 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
Place mysql.jar in {Liferay-Home}/tomcat/lib/ext before starting the server.
Note: In DXP 7.3, the following dependency with the Spring extender should be added to build.gradle in the service module
```
compileOnly group: "com.liferay", name: "com.liferay.portal.spring.extender.api", version: "4.0.0"
```
In DXP 7.4, the above dependency of the Spring extender version will be 6.0.4
Via JNDI - Liferay DXP with External Database