Spring Boot JDBCTemplate with c3p0 DataBase Pooling and failover

Question

I need to establish DB connectionfailover in SpringBoot application(embedded tomcat) for ibatis with jdbctemplate which uses c3p0 for connection pooling.Below is the applicaiton.properties of my applicaiton.I have primary and secondary databases.If primary fails then secondary should be used for the DB connection failover in runtime. Please help me to achieve this.I tried to include multiple DB urls as part of configuration but which is not working.

c3p0 Java Database Pooling, failover configuration https://docs.genesys.com/Documentation/Composer/8.1.4/Help/ConnectionPooling

application.properties:( error is coming)

#      connection properties for data source
##########################################################################################################
spring.datasource.c3p0.driverClass=oracle.jdbc.driver.OracleDriver
spring.datasource.c3p0.maxConnectionAge=3600
spring.datasource.c3p0.maxIdleTime=600
spring.datasource.c3p0.initialPoolSize=5
spring.datasource.c3p0.maxPoolSize=10
spring.datasource.c3p0.minPoolSize=5
spring.datasource.c3p0.acquireIncrement=1

##########################################################################################################


spring.datasource.url=jdbc:oracle:thin:@primary.com:1521:db1,jdbc:oracle:thin:@secondary.com:1521:db2
spring.datasource.username=user
spring.datasource.password=password

Quinquagesima

What is the error? You have already looked at #39208528 and medium.com/@joeclever/… – Actin

it says format of the url is wrong as I have splitted the url using comma. – Quinquagesima

You need to define 2 set of keys - see my answer below and refer to links and code i provided. – Actin

Answer 1

As far as I know the failover configuration is dependent on the JDBC driver. In case of Oracle you configure it with connect descriptors. So in your case you would put this in tnsnames.ora:

CONNECTION_WITH_FAILOVER = 
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = primary.com)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = secondary.com)(PORT = 1521))
            (LOAD_BALANCE = no)
            (FAILOVER = yes)
        )
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = db)
            (FAILOVER_MODE =
                (TYPE = select)
                (METHOD = preconnect)
                (RETRIES = 180)
                (DELAY = 10)
            )
        )
    )

and then in your configuration:

spring.datasource.url=jdbc:oracle:thin:@CONNECTION_WITH_FAILOVER 

Of course, you may not have or want to use tnsnames.ora, in which case you can use connect descriptor as a part of JDBC connection string:

spring.datasource.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=secondary.com)(PORT=1521))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=180)(DELAY=10))))

See these links for more details on connection descriptors, tnsnames.ora and configuring failover for Oracle DB:

• Local Naming Parameters
• Oracle and OCI & JDBC connection strings
• TNS_ADMIN - for configuring the location of tnsnames.ora
• Description of failover mode in Oracle DB (pdf) - contains description of parameter FAILOVER_MODE

Note that the service name must be the same on all databases, so I replaced db1 and db2 from your configuration with db.

If you want to have different service names you must configure separate data sources programmatically (as described by Sheetal Mohan Sharma).

Edit:

• The error you got indicates that you are trying to connect to a service name that does not exist on the server - more here

• I reread the documentation (specifically the PDF I linked above) more carefully today and it seems that it is possible to specify secondary service name in the connect string, so in your case entry in tnsnames.ora would be:

  CONNECTION_WITH_FAILOVER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primary.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = secondary.com)(PORT = 1521)) (LOAD_BALANCE = no) (FAILOVER = yes) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1) (FAILOVER_MODE = (TYPE = select) (METHOD = preconnect) (RETRIES = 180) (DELAY = 10) (BACKUP = db2) ) ) )

and as a JDBC URL in application.properties:

spring.datasource.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=secondary.com)(PORT=1521))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db1)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=180)(DELAY=10)(BACKUP = db2))))

• I do not have such setup currently configured, so I put this in my tnsnames.ora:

  CONNECTION_WITH_FAILOVER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = google.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = my-actual-database)(PORT = my-db-port)) (LOAD_BALANCE = no) (FAILOVER = yes) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = my-service-name) (FAILOVER_MODE = (TYPE = select) (METHOD = preconnect) (RETRIES = 1) (DELAY = 1) ) ) )

to simulate primary connection failure (because obviously there is no Oracle DB running on google.com) and managed to connect to my database with DataGrip using connection URL: jdbc:oracle:thin:@CONNECTION_WITH_FAILOVER

I also tried this with connect descriptor directly in the JDBC URL:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=google.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=my-actual-database)(PORT=my-db-port))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=my-service-name)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=1)(DELAY=5))))

and it worked as well, although both times it took quite some time for the connection to be established (but that may be related to my network configuration or possibly the connection timeout value for the driver)

• Make sure you configure FAILOVER_MODE parameter to suit your needs - consider especially the RETIRES and DELAY values -in the example I provided I used 180 retries and 10 second delay between retries, add to that the connection timeout on every retry and it might take a very long time before the driver actually switches to the failover connection.

Wanton

Could you give me working example? I tried with below single line change with same host for primary ans secondary spring.datasource.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=primary.com)(PORT=1521))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=180)(DELAY=10)))) but exception comes as ORA-12514, TNS:listener does not currently know of service requested in connect descriptor – Quinquagesima

Thanks which was helpful. – Quinquagesima

Answer 2

You need to define 2 set of properties and refer them separately - notice the difference in url keys

#set1
app.datasource.url=jdbc:mysql://localhost/test
app.datasource.username=dbuser
app.datasource.password=dbpass
app.datasource.pool-size=30

#set2
app.datasource.jdbc-url=jdbc:mysql://localhost/test
app.datasource.username=dbuser
app.datasource.password=dbpass
app.datasource.maximum-pool-size=30

But you can mark one of them as @Primary if you are using the default auto-configuration for JDBC or JPA (then that one will be picked up by any @Autowired injections).

ref - Spring docs

@Bean
@Primary
@ConfigurationProperties(prefix="datasource.primary")
public DataSource primaryDataSource() {
    return DataSourceBuilder.create().build();
}

@Bean
@ConfigurationProperties(prefix="datasource.secondary")
public DataSource secondaryDataSource() {
    return DataSourceBuilder.create().build();
}

You can also refer to step by step guide here.

Actin

But how will you include multiple datasource in entitymanager here? – Quinquagesima

Based on step by step guide it is saying for multiple tables not for failover – Quinquagesima