JDBC Setup and Pooling Strategy

Use Modern JDBC Drivers

You should always prefer vendor-provided JDBC drivers over legacy bridges for better performance and security:

• Do not use: MS Access, ODBC-JDBC bridges, or DB2 legacy stacks
• Do use: Latest vendor JDBC drivers directly
• Download drivers from official vendor sites
• Place JAR files in {cf_root}/lib
• Restart ColdFusion after adding drivers

Popular Database Drivers

MySQL / MariaDB

Driver Class: com.mysql.cj.jdbc.Driver
JDBC URL: jdbc:mysql://hostname:3306/database?useSSL=true
Validation Query: SELECT 1

PostgreSQL

Driver Class: org.postgresql.Driver
JDBC URL: jdbc:postgresql://hostname:5432/database
Validation Query: SELECT 1

Microsoft SQL Server

Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL: jdbc:sqlserver://hostname:1433;databaseName=mydb;encrypt=true
Validation Query: SELECT 1

Oracle

Driver Class: oracle.jdbc.OracleDriver
JDBC URL: jdbc:oracle:thin:@hostname:1521:SID
Validation Query: SELECT 1 FROM DUAL

Connection Pool Configuration

Configure connection pooling for optimal performance and resource usage:

• Max Connections: Set the pool size to database CPU cores × 2 as a starting point
• Timeout: Configure the connection timeout to 5-10 seconds
• Validation: Enable connection validation with a validation query
• Test on Borrow: Configure the pool to validate connections before using them
• Leak Detection: Enable leak detection to identify unclosed connections

Example Configuration

Max Connections: 50
Connection Timeout: 10 seconds
Validation Query: SELECT 1
Validate Connection: true (on borrow)
Max Idle Time: 15 minutes
Remove Abandoned: true
Remove Abandoned Timeout: 300 seconds
Log Abandoned: true

Security Best Practices

• Store credentials in external secrets manager (not in Administrator)
• Use encrypted connections (SSL/TLS) to database
• Create dedicated database user per application
• Grant minimum required permissions
• Use read-only datasources for reporting queries
• Never use sa or root accounts

Read/Write Splitting

For databases with replication enabled, you should configure separate datasources for read and write operations:

• Write Datasource: Configure this datasource to point to the primary/master database
• Read Datasource: Configure this datasource to point to the replica/slave database
• Use the write datasource for all INSERT, UPDATE, and DELETE operations
• Use the read datasource for SELECT queries to improve performance
• This strategy reduces load on the primary database and improves overall scalability

Connection Leak Detection

You should enable leak detection to identify and track down unclosed database connections that can exhaust your connection pool:

# In ColdFusion Administrator > Data Sources
Enable: "Remove Abandoned Connections"
Timeout: 300 seconds
Log Abandoned: true

# Review logs for leaked connections
tail -f {cf_root}/logs/coldfusion-out.log | grep abandoned

Performance Optimization

• Use cfqueryparam for all SQL parameters (security + performance)
• Enable query caching for expensive, frequently-run queries
• Use lazy loading in ORM to avoid N+1 queries
• Index database columns used in WHERE clauses
• Monitor slow queries with PMT
• Consider database connection pooling at load balancer level