Primary/secondary datasource failover in Spring MVC

Question

I have a java web application developed on Spring framework which uses mybatis. I see that the datasource is defined in beans.xml. Now I want to add a secondary data source too as a backup. For e.g, if the application is not able to connect to the DB and gets some error, or if the server is down, then it should be able to connect to a different datasource. Is there a configuration in Spring to do this or we will have to manually code this in the application?

I have seen primary and secondary notations in Spring boot but nothing in Spring. I could achieve these in my code where the connection is created/retrieved, by connecting to the secondary datasource if the connection to the primary datasource fails/timed out. But wanted to know if this can be achieved by making changes just in Spring configuration.

user_7209547 · Answer

Let me clarify things one-by-one-

Spring Boot has a @Primary annotation but there is no @Secondary annotation.
The purpose of the @Primary annotation is not what you have described. Spring does not automatically switch data sources in any way. @Primary merely tells the spring which data source to use in case we don't specify one in any transaction. For more detail on this- https://www.baeldung.com/spring-data-jpa-multiple-databases

Now, how do we actually switch datasources when one goes down-

Most people don't manage this kind of High-availability in code. People usually prefer to 2 master database instances in an active-passive mode which are kept in sync. For auto-failovers, something like keepalived can be used. This is also a high subjective and contentious topic and there are a lot of things to consider here like can we afford replication lag, are there slaves running for each master(because then we have to switch slaves too as old master's slaves would now become out of sync, etc. etc.) If you have databases spread across regions, this becomes even more difficult(read awesome) and requires yet more engineering, planning, and design.
Now since, the question specifically mentions using application code for this. There is one thing you can do. I don't advice to use it in production though. EVER. You can create an ASPECTJ advice around your all primary transactional methods using your own custom annotation. Lets call this annotation @SmartTransactional for our demo.

Sample Code. Did not test it though-

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface SmartTransactional {}


public class SomeServiceImpl implements SomeService {
    @SmartTransactional
    @Transactional("primaryTransactionManager")
    public boolean someMethod(){
        //call a common method here for code reusability or create an abstract class
    }
}

public class SomeServiceSecondaryTransactionImpl implements SomeService {
@Transactional("secondaryTransactionManager")
    public boolean usingTransactionManager2() {
        //call a common method here for code reusability or create an abstract class
    }
}


@Component
@Aspect
public class SmartTransactionalAspect {

    @Autowired
    private ApplicationContext context;

    @Pointcut("@annotation(...SmartTransactional)")
    public void smartTransactionalAnnotationPointcut() {
    }

    @Around("smartTransactionalAnnotationPointcut()")
    public Object methodsAnnotatedWithSmartTransactional(final ProceedingJoinPoint joinPoint) throws Throwable {
        Method method = getMethodFromTarget(joinPoint);
        Object result = joinPoint.proceed();
        boolean failure = Boolean.TRUE;// check if result is failure
        if(failure) {
            String secondaryTransactionManagebeanName = ""; // get class name from joinPoint and append 'SecondaryTransactionImpl' instead of 'Impl' in the class name
            Object bean = context.getBean(secondaryTransactionManagebeanName);
            result = bean.getClass().getMethod(method.getName()).invoke(bean);
        }
        return result;
    }
}

Primary/secondary datasource failover in Spring MVC

1 Answers