Connecting External Database in Liferay

In certain use cases, Liferay portlet applications may need to connect to external databases that exist outside the default Liferay database. This blog post provides a step-by-step guide on how to configure and connect to an external database using JNDI DataSource in a Liferay environment.

Overview

When building enterprise applications on Liferay, you may encounter scenarios where your portlet needs to access data from an external system or legacy database. Instead of embedding connection details directly in your code, the recommended approach is to use JNDI (Java Naming and Directory Interface) to manage database connections through the application server.

Benefits of Using JNDI DataSource

Benefit	Description
Centralized Configuration	Database credentials are managed at the server level, not in application code
Connection Pooling	Efficient resource management with configurable pool settings
Easy Maintenance	Change database settings without recompiling or redeploying your application
Security	Credentials are not exposed in application source code

Configuration Steps

Step 1: Configure the JNDI Resource in Tomcat

Add the following resource configuration to your Tomcat server.xml file located at Liferay_home/tomcat/conf/server.xml. This defines the connection pool for your external database.

<Resource     name="jdbc/ExternalDBPool"     auth="Container"     type="javax.sql.DataSource"     factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"     driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"     url="jdbc:sqlserver://<DB_URL>:1433;databaseName=<DB_NAME>;integratedSecurity=true"     username="<USERNAME>"     password="<PASSWORD>"     maxActive="20"     maxIdle="5"     maxWait="10000" />

Configuration Parameters Explained

Parameter	Description
name	JNDI name used to look up the DataSource
maxActive	Maximum number of active connections in the pool
maxIdle	Maximum number of idle connections to keep in the pool
maxWait	Maximum time (ms) to wait for a connection before throwing an exception

Step 2: Configure portal-ext.properties

Add the JNDI name reference in your Liferay_home/portal-ext.properties file:

jdbc.ext.jndi.name=jdbc/ExternalDBPool

Step 3: Create the Database Utility Class

Create a utility class that handles the database connection and query execution. This class uses Liferay's DataSourceFactoryUtil to initialize the DataSource from the properties configured in portal-ext.properties.

public class ExternalDatabaseUtil {       private static final Log _log =         LogFactoryUtil.getLog(ExternalDatabaseUtil.class);     private static final String DATASOURCE_PREFIX = "jdbc.ext.";     private static final String TABLE_NAME = "dbo.TABLE_NAME";       private static final String BASE_SELECT = "SELECT " +         ALL_COLUMNS + " FROM " + TABLE_NAME + " WITH(NOLOCK) WHERE 1=1";       private static DataSource getDataSource() throws Exception {         return DataSourceFactoryUtil.initDataSource(             PropsUtil.getProperties(DATASOURCE_PREFIX, true));     }       private static Connection getConnection() throws Exception {         return getDataSource().getConnection();     }       public static String findByFileUploadId(String fileUploadId) {         String result = null;         String sql = BASE_SELECT + " AND ID = ?";           _log.info(String.format(             "Executing SQL: {%s} with fileUploadId={%s}",             sql, fileUploadId));           try (Connection conn = getConnection();              PreparedStatement ps = conn.prepareStatement(sql)) {               ps.setString(1, fileUploadId);               try (ResultSet rs = ps.executeQuery()) {                 while (rs.next()) {                     result = processResultSet(rs);                 }             }         } catch (Exception e) {             _log.error("SQL Exception in findByFileUploadId", e);         }         return result;     } }

Key Implementation Points

• DataSource Initialization: The getDataSource() method uses Liferay's DataSourceFactoryUtil to create a DataSource from the properties prefixed with jdbc.ext. in portal-ext.properties.
• Connection Management: The code uses try-with-resources to ensure proper cleanup of database connections, preventing connection leaks.
• SQL Server Hints: The WITH(NOLOCK) hint is used to prevent blocking on read operations, which is useful for reporting queries.
• Prepared Statements: Using PreparedStatement prevents SQL injection and improves query performance through statement caching.
• Logging: Liferay's LogFactoryUtil is used for consistent logging across the application.

Best Practices

1. Always close connections: Use try-with-resources or ensure connections are closed in a finally block.
2. Use connection pooling: Configure appropriate pool sizes based on your application's load requirements.
3. Handle exceptions gracefully: Log errors with sufficient context for troubleshooting.
4. Externalize configuration: Keep database credentials in server configuration, not in source code.
5. Test thoroughly: Verify connectivity and query performance in all environments before deployment.

Conclusion

Connecting to external databases in Liferay is straightforward when using JNDI DataSource configuration. This approach provides a clean separation between configuration and code, enables connection pooling for better performance, and simplifies maintenance. By following the steps outlined in this blog, you can seamlessly integrate external data sources into your Liferay portlet applications.

─────────────────────────────────────────

Happy Coding!