Specify Oracle Schema SpringBoot 2.x/Apache Camel - Multiple Datasource

Question

I am having a hard time changing the oracle datasource schema for my springboot app, that will eventually be used by my camel routes. I am logging in as user readonly, but all the data is in schema mydata. Readonly has read rights to the mydata schema.

I have tried calling ALTER SESSION SET CURRENT_SCHEMA=mydata against the datasource (by autowiring, and then getting the connection object from the datasource) and it doesn't work, I have no issue running selects from statement objects I create off the connection (see code below)

If I create a rest endpoint that executes ALTER SESSION SET CURRENT_SCHEMA=mydata and if I call that from postman or a browser, that will change my schema and my other endpoints will work, but I would prefer not to do it that way since I will have to call that endpoint. I guess I could call that endpoint in my springboot app when it loads but it just seems like the wrong way to do it.

I also do not want to hardcode/prefix all my tables with the schema name since different regions have different schema names, I'd like to configure the schema name in the properties file.

Here is my application.properties, I have tried various ways to set the schema in the properties file based on other stack overflow posts, and so far none of them work.

spring.datasource.first.url=jdbc:oracle:thin:@myserver:10100:db9
spring.datasource.first.username=readonly
spring.datasource.first.password=readonlypass
## DOESNT WORK ->spring.datasource.hikari.schema=mydata
## DOESNT WORK ->spring.datasource.hikari.first.schema=mydata
    
#sync database
spring.datasource.second.driverClassName=oracle.jdbc.OracleDriver
spring.datasource.second.url = jdbc:oracle:thin:myserver2:10100:db15
spring.datasource.second.username = eam
spring.datasource.second.password = eampass

Here is the code from my springboot application:

/**
 * A spring-boot application that includes a Camel route builder to setup the Camel routes
 */
@SpringBootApplication
@ImportResource({"classpath:spring/camel-context.xml"})
public class Application extends RouteBuilder {

    int workorderSyncFrequency = 5000;
      
    //Autowired the first datasource in attempts to alter the session to set my schema name.
    @Autowired
    DataSource firstDataSource;

    // must have a main method spring-boot can run
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
        
    }


    //setup first datasource
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first")
    public DataSourceProperties firstDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.first.configuration")
    public DataSource firstDataSource() {
        return firstDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    //setup second data source 
    @Bean
    @ConfigurationProperties("spring.datasource.second")
    public DataSourceProperties secondDataSourceProperties() {
        return new DataSourceProperties();
    }
    @Bean

    @ConfigurationProperties("spring.datasource.second.configuration")
    public DataSource secondDataSource() {
        return firstDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();

    }

    @Override
    public void configure() throws Exception {
        
        Connection con = DataSourceUtils.getConnection(firstDataSource);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select count(*) from mydata.ASSET");
        rs.next();
        //simply testing I am using the correct datasource and I can query from the second schema and this works.
        System.out.println("++++++++++++++++++++++ASSET COUNT+++++++++++++++++++"+rs.getInt(1));
        
        //Tried both of these statements, neither works.
        //stmt.executeQuery("ALTER SESSION SET CURRENT_SCHEMA=mydata");
        //stmt.executeUpdate("ALTER SESSION SET CURRENT_SCHEMA=mydata");    
    
        //Connection is defaulted to autocommit tried this just in case.
        con.commit();

        //ASSET table doesnt exist on the readonly schema, only on the mydata schema
        //if I call test3 I will get a table or view does not exist, unless I first call the "schema"
                //endpoint below.
        rest()
        .get("test3")
        .produces(MediaType.APPLICATION_JSON_VALUE)
        .route()
        .to("sql:SELECT * FROM ASSET where rownum < 10"
                + "?dataSource=firstDataSource&outputType=SelectList");  
        
        //This works if I call this route, but its a weird way to make this work.
        rest()
        .get("schema")
        .produces(MediaType.APPLICATION_JSON_VALUE)
        .route()
        .to("sql:ALTER SESSION SET CURRENT_SCHEMA=mydata"
                + "?dataSource=firstDataSource&outputType=SelectList");  
    }