2

we have this code where the same service method will call different daos each using a different datasource (and different JdbcTemplates). We would like to use @Transactional annotation, but as far as I know, this annotation is always linked to a specific TransactionManager (and thus, to a specific DataSource).

So, my question is, is there a way to choose dynamically which DataSource (or TransactionManager) using when calling a @Transactional method so I can reuse that method to attack different databases?

didgewind
  • 524
  • 1
  • 3
  • 10
  • Maybe it shouldn't use declarative transaction management if you want it to work with different datasources? You want to reuse the method, but that doesn't mean there's a hard requirement of using `@Transactional`, is there? – Kayaman Feb 26 '21 at 15:27
  • Yep, you are right, there is not. We could do with `PlatformTransactionManager` and using programatic transactions, but I was wondering if there was a 'declarative' possibility. – didgewind Feb 26 '21 at 15:29
  • Well, SpEL could've been your saviour, but the annotation doesn't [support it](https://stackoverflow.com/q/19222092/2541560), or does it...that's from 2013? Nah...no support. – Kayaman Feb 26 '21 at 15:38
  • 1
    But I don't see a reason not to use programmatic transactions. This is the kind of place where you need the additional control (vs. the ease-of-use of `@Transactional`), just make sure you write it nice and clean, maintainable and well documented. – Kayaman Feb 26 '21 at 15:44
  • 1
    @didgewind This other [question](https://stackoverflow.com/questions/21261869/transaction-management-for-multiple-datasources-created-manually) could point you in the right direction – Lino Feb 26 '21 at 15:46

2 Answers2

2

The @Transactional annotation doesn't allow dynamic evaluation of the value attribute which selects the TransactionManager (possibly by design, at least it doesn't look like it's going to change any time soon). So you can't have something like @Transactional("#{@getTxManager}") which would resolve the tx manager at call time.

In simple cases you might be able to get away with the following, but it would only be worth considering when for example you have a primary DS, and a secondary DS that's used only in some cases. Otherwise you'd be peppering the code that selects between calling foo/bar all around, and that wouldn't look clean at all

// TX boundary on the "top" abstraction layer
@Transactional("foo")
public void foo() {
    doWork();
}
@Transactional("bar")
public void bar() {
    doWork();
}

private void doWork() {
    // Work done here, no concern for tx management
}

For more complex cases like multitenancy, AbstractRoutingDataSource is an easy and robust choice if you haven't considered it yet. Although depending on how much switching you need, it may require tweaking or be even unsuitable.

Finally, you might be able to create your own annotation that does choose the DS dynamically (although I don't guarantee it), but that would be the riskiest approach for possible very little gains.

Kayaman
  • 72,141
  • 5
  • 83
  • 121
-2

The safest way for you would be to create separate services for each dao... I wouldn't like to be debugging such code. Think about maintaining this code and possible failures that might happen.

If I were you I'd ask myself following questions:

1.) Why separate dbs?

2.) Isn't the context somehow mixed up? Maybe create some boundaries between them?

3.) Do my queries really need to be transactional?

I probably don't know the context of your problem but for me it seems that you've modeled your application in a wrong way and I'd focus on it.

Skoq95
  • 65
  • 2
  • What if the application needs to do something completely generic on a given database? Then you can't possibly create multiple services for that because you don't really know what you're working with – Lino Feb 26 '21 at 15:41
  • 1
    "Do my queries really need to be transactional?" Are you suggesting that you leave out the `@Transactional` attribute and the transaction boundary? This answer has more questions than it has answers. – Kayaman Feb 26 '21 at 15:41
  • I've seen plenty of solutions that didn't really required transactions and only caused problems when debugging. Furthermore, some of these microservices didn't even need transactional database. – Skoq95 Feb 26 '21 at 15:46
  • @Skoq95 rollbacks are only really feasible when using transactions, and sometimes you **need** to have a rollback mechanism in place. No other way around it – Lino Feb 26 '21 at 15:52
  • 1
    You don't need to worry about debugging the OP's code, and nowhere are microservices mentioned. You seem to be assuming a bit too much here. Maybe you just don't understand transactions if you have trouble debugging them? – Kayaman Feb 26 '21 at 15:53
  • @Lino You can implement compensation mechanism for the failures. There's always a solution. Kayaman: You're right, I might be. Trust me I do understand them. If ppl use this annotation everywhere it might happen to be a nested transaction somewhere. This ain't a funny thing ;p – Skoq95 Feb 26 '21 at 16:00
  • 1
    @Skoq95 the standard `@Transactional` with default propagation joins an existing transaction if one exists, so there's no nested transaction unless you use `Propagation.NESTED` (which would probably be in legacy code to be honest, I wouldn't create overcomplicated transaction boundaries either). Poor developers can cause problems no matter what the tools, and it's never funny. – Kayaman Feb 26 '21 at 16:06