Multiple Datasources

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

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

Note: 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.

ontimize-examples
- projectwiki-api
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        api
        
        core
        
        service
        
        ICandidateService.java
        
        IUserService.java
  - pom.xml
- projectwiki-boot
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ServerApplication.java
      - resources
        
        application.yml
  - pom.xml
- projectwiki-model
  - src
    - main
      - db
        
        templateDB.properties
        
        templateDB.txt
      - db2
        
        templateDB.properties
        
        templateDB.txt
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        model
        
        core
        
        dao
        
        CandidateDao.java
        
        UserDao.java
        
        UserRoleDao.java
        
        service
        
        CandidateService.java
        
        UserService.java
      - resources
        
        dao
        
        CandidateDao.xml
        
        placeholders.properties
        
        RoleDao.xml
        
        RoleServerPermissionDao.xml
        
        ServerPermissionDao.xml
        
        UserDao.xml
        
        UserRoleDao.xml
  - pom.xml
- projectwiki-ws
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ws
        
        core
        
        rest
        
        CandidateRestController.java
        
        MainRestController.java
        
        TestRestController.java
        
        UserRestController.java
  - pom.xml
- pom.xml
- README.md

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.

ontimize-examples
- projectwiki-api
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        api
        
        core
        
        service
        
        ICandidateService.java
        
        IUserService.java
  - pom.xml
- projectwiki-boot
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ServerApplication.java
      - resources
        
        application.yml
  - pom.xml
- projectwiki-model
  - src
    - main
      - db
        
        templateDB.properties
        
        templateDB.txt
      - db2
        
        templateDB.properties
        
        templateDB.txt
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        model
        
        core
        
        dao
        
        CandidateDao.java
        
        UserDao.java
        
        UserRoleDao.java
        
        service
        
        CandidateService.java
        
        UserService.java
      - resources
        
        dao
        
        CandidateDao.xml
        
        placeholders.properties
        
        RoleDao.xml
        
        RoleServerPermissionDao.xml
        
        ServerPermissionDao.xml
        
        UserDao.xml
        
        UserRoleDao.xml
  - pom.xml
- projectwiki-ws
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ws
        
        core
        
        rest
        
        CandidateRestController.java
        
        MainRestController.java
        
        TestRestController.java
        
        UserRestController.java
  - pom.xml
- pom.xml
- README.md

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
			Property
default	dbSQLStatementHandler	Upper string	false	ontimize.jdbc.sql-condition-processor.upper-string
default	dbSQLStatementHandler	Upper like	true	ontimize.jdbc.sql-condition-processor.upper-like
postgres	postgresSQLStatementHandler	Upper string	false	ontimize.jdbc.postgres-sql-condition-processor.upper-string
postgres	postgresSQLStatementHandler	Upper like	true	ontimize.jdbc.postgres-sql-condition-processor.upper-like
oracle	oracleSQLStatementHandler	Upper string	false	ontimize.jdbc.oracle-sql-condition-processor.upper-string
oracle	oracleSQLStatementHandler	Upper like	true	ontimize.jdbc.oracle-sql-condition-processor.upper-like
oracle12	oracle12SQLStatementHandler	Upper string	false	ontimize.jdbc.oracle12-sql-condition-processor.upper-string
oracle12	oracle12SQLStatementHandler	Upper like	true	ontimize.jdbc.oracle12-sql-condition-processor.upper-like
sqlserver	sqlserverSQLStatementHandler	Upper string	false	ontimize.jdbc.sqlserver-sql-condition-processor.upper-string
sqlserver	sqlserverSQLStatementHandler	Upper like	true	ontimize.jdbc.sqlserver-sql-condition-processor.upper-like
hsqldb	hsqldbSQLStatementHandler	Upper string	false	ontimize.jdbc.hsqldb-sql-condition-processor.upper-string
hsqldb	hsqldbSQLStatementHandler	Upper like	true	ontimize.jdbc.hsqldb-sql-condition-processor.upper-like
mysql	mysqlSQLStatementHandler	Upper string	false	ontimize.jdbc.mysql-sql-condition-processor.upper-string
mysql	mysqlSQLStatementHandler	Upper like	true	ontimize.jdbc.mysql-sql-condition-processor.upper-like

ontimize-examples
- projectwiki-api
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        api
        
        core
        
        service
        
        ICandidateService.java
        
        IUserService.java
  - pom.xml
- projectwiki-boot
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ServerApplication.java
      - resources
        
        application.yml
  - pom.xml
- projectwiki-model
  - src
    - main
      - db
        
        templateDB.properties
        
        templateDB.txt
      - db2
        
        templateDB.properties
        
        templateDB.txt
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        model
        
        core
        
        dao
        
        CandidateDao.java
        
        UserDao.java
        
        UserRoleDao.java
        
        service
        
        CandidateService.java
        
        UserService.java
      - resources
        
        dao
        
        CandidateDao.xml
        
        placeholders.properties
        
        RoleDao.xml
        
        RoleServerPermissionDao.xml
        
        ServerPermissionDao.xml
        
        UserDao.xml
        
        UserRoleDao.xml
  - pom.xml
- projectwiki-ws
  - src
    - main
      - java
        
        com
        
        ontimize
        
        projectwiki
        
        ws
        
        core
        
        rest
        
        CandidateRestController.java
        
        MainRestController.java
        
        TestRestController.java
        
        UserRestController.java
  - pom.xml
- pom.xml
- README.md

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">
  ...