Multiple Databases

Broadleaf datasources

Broadleaf uses multiple datasource beans each with a specific purpose.

• webDS - the general datasource that is used to manage the majority of the entities within broadleaf.
• webStorageDS - the datasource that is used to store static CMS assets
• webEventDS - the datasource that is used to manage broadleaf scheduled jobs and events
• webSecureDS - the datasource that is used to store credit card and payment information (this datasource is not used if you are not storing explicit payment information, which is the normal case)

In ReferenceSite these beans are referenced in a map bean called blMergedDataSources which is created in the CorePersistenceConfig configuration class

@Autowired
@Qualifier("webDS")
DataSource webDS;

@Autowired
@Qualifier("webSecureDS")
DataSource webSecureDS;

@Autowired
@Qualifier("webStorageDS")
DataSource webStorageDS;

@Autowired
@Qualifier("webEventDS")
DataSource webEventDS;

@Bean
public MapFactoryBean blMergedDataSources() throws Exception {
    MapFactoryBean mapFactoryBean = new MapFactoryBean();
    Map<String, DataSource> sourceMap = new HashMap<>();
    sourceMap.put("jdbc/web", webDS);
    sourceMap.put("jdbc/webSecure", webSecureDS);
    sourceMap.put("jdbc/cmsStorage", webStorageDS);
    sourceMap.put("jdbc/event", webEventDS);
    mapFactoryBean.setSourceMap(sourceMap);

    return mapFactoryBean;
}

These beans are then referenced by their map keys in the entries in the persistence-core.xml file, which configure which datasources each persistence unit should use.

<persistence-unit name="blPU" transaction-type="RESOURCE_LOCAL">
    <non-jta-data-source>jdbc/web</non-jta-data-source>
    <exclude-unlisted-classes/>
</persistence-unit>

<persistence-unit name="blSecurePU" transaction-type="RESOURCE_LOCAL">
    <non-jta-data-source>jdbc/webSecure</non-jta-data-source>
    <exclude-unlisted-classes/>
</persistence-unit>

<persistence-unit name="blCMSStorage" transaction-type="RESOURCE_LOCAL">
    <non-jta-data-source>jdbc/cmsStorage</non-jta-data-source>
    <exclude-unlisted-classes/>
</persistence-unit>

<persistence-unit name="blEventPU" transaction-type="RESOURCE_LOCAL">
    <non-jta-data-source>jdbc/event</non-jta-data-source>
    <exclude-unlisted-classes/>
</persistence-unit>

Using Multiple Databases

Sometimes it is beneficial to use different databases to manage the different partitions of data within broadleaf. For instance it might be beneficial to store CMS assets in a separate database that is better optimized for that type of data. Since broadleaf already uses separate datasources to manage different data, it is easy to setup multiple separate database connections.

The different datasource beans listed above are usually defined automatically for you within the broadleaf-boot-starter-database dependency. However, the definitions are annotated with @ConditionalOnMissingBean, which means that if you define one of the beans manually within your project, it will take precedence. Assuming you have already setup your project to use a database (MS SQL Server, MySQL, Oracle DB, PostgreSQL), all that you have to do to use a different database for a specific datasource is just redefine the datasource bean within you project. Here is an example of using 2 different mysql databases for the webEventDS and the webStorageDS:

@Bean
public DataSource webEventDS() {
    return DataSourceBuilder
        .create()
        .username("webUser")
        .password("password")
        .url("jdbc:mysql://localhost:3306/event")
        .driverClassName("com.mysql.jdbc.Driver")
        .type(JDBCDataSource.class)
        .build();
}

@Bean
public DataSource webStorageDS() {
    return DataSourceBuilder
        .create()
        .username("contentUser")
        .password("password")
        .url("jdbc:mysql://localhost:3306/cms")
        .driverClassName("com.mysql.jdbc.Driver")
        .type(JDBCDataSource.class)
        .build();
}

Using different types of databases

You can also have different data sources pointing to completely different database platforms. One use case for this is to use a shared database in local development between developers. If you are using the Broadleaf jobs and events and pointing the webEventDS to the shared database then you can run into issues with admin deployments and running scheduled jobs since local developer 'nodes' will continually drop in and out. Instead, you can keep all of the jobs and events data within a local HSQLDB. Here is an example, which also assumes that you are using the broadleaf-boot-starter-hsql-database:

@Bean
public DataSource webEventDS() {
    DataSource dataSource = DataSourceBuilder
            .create()
            .username("SA")
            .password("")
            .url("jdbc:hsqldb:hsql://localhost/broadleaf")
            .driverClassName("org.hsqldb.jdbcDriver")
            .type(org.apache.tomcat.jdbc.pool.DataSource.class)
            .build();
    ((org.apache.tomcat.jdbc.pool.DataSource) dataSource).setInitSQL("SET DATABASE TRANSACTION CONTROL MVCC");
}

You will need to take special care to ensure that the dialect for just the blEventPU is still set to the HSQLDialect, and make this datasource always create the schema on startup:

blEventPU.hibernate.dialect=org.broadleafcommerce.common.dialect.DemoHSQLDialect
blEventPU.hibernate.hbm2ddl.auto=create