3.3.1. Connecting to Databases

CUBA application obtains connections to a database through JDBC DataSource. A data source can be configured in the application or obtained from JNDI. The method of obtaining the data source is specified by the cuba.dataSourceProvider application property: it can be either application or jndi.

You can easily configure connections for the main and additional data stores using CUBA Studio, see its documentation. The information below can be helpful for troubleshooting and for defining parameters not available in Studio, e.g. connection pool settings.

Configuring a Data Source in the Application
Since7.2

When the data source is configured in the application, the framework creates a connection pool using HikariCP. Both the connection parameters and the pool settings are configured using application properties located in the app.properties file of the core module. This is the recommended way if you don’t need a specific connection pool provided by an application server.

The following application properties define the database type and connection parameters:

  • cuba.dbmsType - defines the DBMS type.

  • cuba.dataSourceProvider - application value indicates that the data source must be configured using application properties.

  • cuba.dataSource.username - the database user name.

  • cuba.dataSource.password - the database user password.

  • cuba.dataSource.dbName - the database name.

  • cuba.dataSource.host - the database host.

  • cuba.dataSource.port - optional parameter, sets the database port if it is non-standard for the selected DBMS type.

  • cuba.dataSource.jdbcUrl - optional parameter, sets the full JDBC URL if some additional connection parameters need to be passed. Note that all separate properties described above are still required for database migration tasks.

In order to configure connection pool settings, specify the HikariCP properties prefixed with cuba.dataSource., for example cuba.dataSource.maximumPoolSize or cuba.dataSource.connectionTimeout. See the full list of supported parameters and their default values in the HikariCP documentation.

If your application uses additional data stores, you should define the same set of parameters for each data store. The data store name is added to the second part of each property name:

For example:

# main data store connection parameters
cuba.dbmsType = hsql
cuba.dataSourceProvider = application
cuba.dataSource.username = sa
cuba.dataSource.password =
cuba.dataSource.dbName = demo
cuba.dataSource.host = localhost
cuba.dataSource.port = 9111
cuba.dataSource.maximumPoolSize = 20

# names of additional data stores
cuba.additionalStores = clients,orders

# 'clients' data store connection parameters
cuba.dbmsType_clients = postgres
cuba.dataSourceProvider_clients = application
cuba.dataSource_clients.username = postgres
cuba.dataSource_clients.password = postgres
cuba.dataSource_clients.dbName = clients_db
cuba.dataSource_clients.host = localhost

# 'orders' data store connection parameters
cuba.dbmsType_orders = mssql
cuba.dataSourceProvider_orders = application
cuba.dataSource_orders.jdbcUrl = jdbc:sqlserver://localhost;databaseName=orders_db;currentSchema=my_schema
cuba.dataSource_orders.username = sa
cuba.dataSource_orders.password = myPass123
cuba.dataSource_orders.dbName = orders_db
cuba.dataSource_orders.host = localhost

Besides, for each additional data store, the spring.xml file of the core module must contain a definition of the CubaDataSourceFactoryBean bean with the appropriate storeName parameter. For example:

<bean id="cubaDataSource_clients" class="com.haulmont.cuba.core.sys.CubaDataSourceFactoryBean">
    <property name="storeName" value="clients"/>
</bean>

<bean id="cubaDataSource_orders" class="com.haulmont.cuba.core.sys.CubaDataSourceFactoryBean">
    <property name="storeName" value="orders"/>
</bean>

If you configure the data source in the application, the database migration Gradle tasks may have no parameters, as they will be obtained from the same set of application properties. This is an additional benefit of configuring data sources in the application. For example:

task createDb(dependsOn: assembleDbScripts, description: 'Creates local database', type: CubaDbCreation) {
}

task updateDb(dependsOn: assembleDbScripts, description: 'Updates local database', type: CubaDbUpdate) {
}
Obtaining a Data Source from JNDI

If you want to use a data source provided by an application server via JNDI, define the following application properties in the app.properties file of the core module:

  • cuba.dbmsType - defines the DBMS type.

  • cuba.dataSourceProvider - jndi value indicates that the data source must be obtained from JNDI.

The JNDI name of the data source is specified in the cuba.dataSourceJndiName application property, which is java:comp/env/jdbc/CubaDS by default. For additional data stores, specify the same properties adding the data store name.

For example:

# main data store connection parameters
cuba.dbmsType = hsql
cuba.dataSourceProvider = jndi

# names of additional data stores
cuba.additionalStores = clients,orders

# 'clients' data store connection parameters
cuba.dbmsType_clients = postgres
cuba.dataSourceProvider_clients = jndi
cuba.dataSourceJndiName_clients = jdbc/ClientsDS

# 'orders' data store connection parameters
cuba.dbmsType_orders = mssql
cuba.dataSourceProvider_orders = jndi
cuba.dataSourceJndiName_orders = jdbc/OrdersDS

Besides, for each additional data store, the spring.xml file of the core module must contain a definition of the CubaDataSourceFactoryBean bean with the appropriate storeName and jndiNameAppProperty parameters. For example:

<bean id="cubaDataSource_clients" class="com.haulmont.cuba.core.sys.CubaDataSourceFactoryBean">
    <property name="storeName" value="clients"/>
    <property name="jndiNameAppProperty" value="cuba.dataSourceJndiName_clients"/>
</bean>

<bean id="cubaDataSource_orders" class="com.haulmont.cuba.core.sys.CubaDataSourceFactoryBean">
    <property name="storeName" value="orders"/>
    <property name="jndiNameAppProperty" value="cuba.dataSourceJndiName_orders"/>
</bean>

Data sources provided via JNDI are configured in a way specific to the application server. In Tomcat, it is done in the context.xml file. CUBA Studio writes connection parameters into modules/core/web/META-INF/context.xml and use this file in the standard deployment process when developing the application.

If the data source is configured in context.xml, the database migration Gradle tasks must have own parameters defining the database connection, for example:

task createDb(dependsOn: assembleDbScripts, description: 'Creates local database', type: CubaDbCreation) {
    dbms = 'hsql'
    host = 'localhost:9111'
    dbName = 'demo'
    dbUser = 'sa'
    dbPassword = ''
}

task updateDb(dependsOn: assembleDbScripts, description: 'Updates local database', type: CubaDbUpdate) {
    dbms = 'hsql'
    host = 'localhost:9111'
    dbName = 'demo'
    dbUser = 'sa'
    dbPassword = ''
}