Possible to call "use warehouse" without using Routing DataSource ?

[Rick Reumann]

I have a Spring RestController and we're now using Snowflake as our DB, and within that RestController, upon certain requests, we need to call "use warehouse ${x}  for the underlying connection the Mapper uses, and I was wondering if there was a way I could implement things in such a way that other threads aren't influenced by the change.

I know I can wire up a routing data source and define the different warehouses up front (the jdbc connection is the same other than the warehouse that we can change on the fly)  and then switch that way, but I was wondering if what I am trying to do was possible without taking that approach.  

Pseudo code to illustrate, what I'd like to do...

@RestController
public class SomeController {
       @Autowried
        private OurMapper ourMapper;

@GetMapping(value="/testWarehouse")
public String testWarehouse(@RequestParam String warehouse) throws Exception {
doOperationUsingWarehouse(warehouse);
}

        //tried @Transactaional
public void doOperationUsingWarehouse(String warehouse) {
ourMapper.useWarehouse(warehouse); //sql: use warehouse ${warehouse}
ourMapper.queryForStuff();
 
}

[Iwao AVE!]

Hello Rick,

I think @Transactional must be on the testWarehouse() method to be effective.
Did you try that?

As a different approach, this answer explains how to switch catalog/schema using an interceptor (a.k.a. plugin).
https://stackoverflow.com/a/60789865/1261766
I’m not sure if you can switch the warehouse in the similar manner, but something like this might work.

Object[] args = invocation.getArgs();
Connection con = (Connection) args[0];
SnowflakeConnectionV1 sfCon = con.unwrap(SnowflakeConnectionV1.class);
sfCon.getSfSession().setWarehouse(warehouse);

Interceptor might not be a good fit if you want to change the warehouse only when executing particular statements.

Regards,
Iwao

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/mybatis-user/f997a74f-d50d-4077-a209-a7d8d5a66624n%40googlegroups.com.

[Rick Reumann]

Thanks Iwao!

Marking the endpoint itself as @Transactional as you suggested worked.

Really appreciated that help.