Multiple Datasources

This module works only for Ontimize Boot version 3.9.0 or above. Actual release version: Ontimize Boot

Introduction

Ontimize Boot allows a single application to query multiple data sources, thus allowing you to separate the user and permission tables in a database and use one or more other data sources to query the rest of the information you want by configuring the data source you want to query in each DAO.

Prerequisites

You can follow this tutorial using your own application, although for this example we will use an application created using the archetype that can be found on this page, with a REST service and two HSQLDB databases.

There are 2 options to follow this tutorial, clone the repository with the initial state and follow the tutorial step by step, or download the final example and see which files are new and which have been updated.

Initial project

/$ git clone https://github.com/ontimize/ontimize-examples
/ontimize-examples$ cd ontimize-examples
/ontimize-examples$ git checkout boot-multidatasource-initial

Final example

/$ git clone https://github.com/ontimize/ontimize-examples
/ontimize-examples$ cd ontimize-examples
/ontimize-examples$ git checkout boot-multidatasource

Steps

Modify application.yml file

Although you can keep the data source already indicated in the file, you can delete it without any problem. New data sources will be added in the *.yml under the ontimize.datasources properties, followed by a name for that data source, and then the same settings as required for the data source you normally use.

application.yml

...

ontimize:
   datasources:
      dbone:
         driver-class-name: org.hsqldb.jdbcDriver
         jdbc-url: jdbc:hsqldb:hsql://localhost:9013/templateDB
         username: SA
         password:
      dbtwo:
         driver-class-name: org.hsqldb.jdbcDriver
         jdbc-url: jdbc:hsqldb:hsql://localhost:9013/templateDB
         username: SA
         password:

...

Modify DAO’s datasource

In the DAOs the data source must be modified to indicate the correct data source, and the database table that belongs to that data source. In the case of this example, as indicated in the *.yml file, that would be dbone and dbtwo.

RoleDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
    xmlns="http://www.ontimize.com/schema/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
    catalog="" schema="${mainschema}" table="TROLE"
    datasource="dbone" sqlhandler="dbSQLStatementHandler">
    ...

RoleServerPermissionDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
    xmlns="http://www.ontimize.com/schema/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
    catalog="" schema="${mainschema}" table="TROLE_SERVER_PERMISSION"
    datasource="dbone" sqlhandler="dbSQLStatementHandler">
    ...

ServerPermissionDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
    xmlns="http://www.ontimize.com/schema/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
    catalog="" schema="${mainschema}" table="TSERVER_PERMISSION"
    datasource="dbone" sqlhandler="dbSQLStatementHandler">
    ...

UserDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
    xmlns="http://www.ontimize.com/schema/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
    catalog="" schema="${mainschema}" table="TUSER"
    datasource="dbone" sqlhandler="dbSQLStatementHandler">
    ...

UserRoleDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
	xmlns="http://www.ontimize.com/schema/jdbc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
	catalog="" schema="${mainschema}" table="TUSER_ROLE"
	datasource="dbone" sqlhandler="dbSQLStatementHandler">
  ...

CandidateDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
	xmlns="http://www.ontimize.com/schema/jdbc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
	catalog="" schema="${mainschema}" table="CANDIDATE"
	datasource="dbtwo" sqlhandler="dbSQLStatementHandler">
  ...

Modify SQL handler

At this point more data sources can be queried, as shown in the example, but they all have the same SQL handler. It is quite possible that, when multiple data sources are queried, unlike the example, they are databases of different technologies. To do this, in the same way as it is allowed to do with the data sources, the SQL handlers of each of the DAOs are changed. There is a SQL handler that is configured by the ontimize.jdbc.sqlhandler and ontimize.jdbc.sql-condition-processor.* properties, called by default dbSQLStatementHandler. From this version, it is no longer necessary to indicate which SQL handler we will use in the ontimize.jdbc.sqlhandler property, but we will indicate its name, listed below. By default, they already have some pre-established configurations, although they can be modified without any problem in the application.yml file.

Handler Bean name Default configuration Property
default dbSQLStatementHandler Upper string false ontimize.jdbc.sql-condition-processor.upper-string
Upper like true ontimize.jdbc.sql-condition-processor.upper-like
postgres postgresSQLStatementHandler Upper string false ontimize.jdbc.postgres-sql-condition-processor.upper-string
Upper like true ontimize.jdbc.postgres-sql-condition-processor.upper-like
oracle oracleSQLStatementHandler Upper string false ontimize.jdbc.oracle-sql-condition-processor.upper-string
Upper like true ontimize.jdbc.oracle-sql-condition-processor.upper-like
oracle12 oracle12SQLStatementHandler Upper string false ontimize.jdbc.oracle12-sql-condition-processor.upper-string
Upper like true ontimize.jdbc.oracle12-sql-condition-processor.upper-like
sqlserver sqlserverSQLStatementHandler Upper string false ontimize.jdbc.sqlserver-sql-condition-processor.upper-string
Upper like true ontimize.jdbc.sqlserver-sql-condition-processor.upper-like
hsqldb hsqldbSQLStatementHandler Upper string false ontimize.jdbc.hsqldb-sql-condition-processor.upper-string
Upper like true ontimize.jdbc.hsqldb-sql-condition-processor.upper-like
mysql mysqlSQLStatementHandler Upper string false ontimize.jdbc.mysql-sql-condition-processor.upper-string
Upper like true ontimize.jdbc.mysql-sql-condition-processor.upper-like

Modified application.yml (will use default values) and some SQL handlers

aplication.yml

...
ontimize:
   datasources:
      dbone:
         driver-class-name: org.hsqldb.jdbcDriver
         jdbc-url: jdbc:hsqldb:hsql://localhost:9013/templateDB
         username: SA
         password:
         initial-size: 10
         test-on-borrow: true
      dbtwo:
         driver-class-name: org.hsqldb.jdbcDriver
         jdbc-url: jdbc:hsqldb:hsql://localhost:9014/templateDB
         username: SA
         password:
         initial-size: 10
         test-on-borrow: true
   jdbc:
      name-convention: upper
...

RoleDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
    xmlns="http://www.ontimize.com/schema/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
    catalog="" schema="${mainschema}" table="TROLE"
    datasource="dbone" sqlhandler="hsqldbSQLStatementHandler">
    ...

ServerPermissionDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
    xmlns="http://www.ontimize.com/schema/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
    catalog="" schema="${mainschema}" table="TSERVER_PERMISSION"
    datasource="dbone" sqlhandler="hsqldbSQLStatementHandler">
    ...

UserRoleDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
	xmlns="http://www.ontimize.com/schema/jdbc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
	catalog="" schema="${mainschema}" table="TUSER_ROLE"
	datasource="dbone" sqlhandler="hsqldbSQLStatementHandler">
  ...

CandidateDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
	xmlns="http://www.ontimize.com/schema/jdbc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
	catalog="" schema="${mainschema}" table="CANDIDATE"
	datasource="dbtwo" sqlhandler="hsqldbSQLStatementHandler">
  ...