Sometimes, just one database isn’t enough. That’s why you need to configure multiple data sources in your Spring Boot app.

Unfortunately, it’s not really easy to figure out how to do that.

Usually, you’ll configure a single data source in your application properties file with something that looks like this:

spring.datasource.url=jdbc:mysql://myhost.com:3306/fishingsupplies
spring.datasource.username=bubba
spring.datasource.password=password

That’s fine and dandy if you only want a single database. But what if you need to access a few of them? Or three of them?

Fortunately, there’s an easy answer. It involves the use of the AbstractRoutingDataSource class.

In this guide, we’ll explain how to implement that class so you can use multiple data sources.

As always, feel free to check out the code in GitHub.

 

The Use Case

You’re minding your own business in your office one day when your boss, Smithers, pops in. He’s clearly exasperated.

Smithers sits down, sighs, and explains that he’s got multiple e-commerce sites that all use different databases. However, he says, they all have the same customer info.

He wants you to develop a single Spring Boot app that can update customer info to all of the e-commerce databases.

Oh, and he says he needs it done by tomorrow.

Then he leaves, muttering something about the complexities of managing a fishing supplies website and a tech gadget website at the same time.

 

The Tech Stack

Your boss already told you that you need to use Spring Boot to develop the app.

You’ll use a simple microservices architecture here. That means the user will handle customer updates via an Angular app while Spring Boot does the heavy lifting in terms of persistence.

Requests will be sent to the Spring Boot app using REST.

If the user is creating a new customer, then the Angular app will send a POST with details about the new customer in the request body. Those details will be in JSON format.

The Spring Boot app will handle interaction with the MySQL database.

Additionally, the Spring Boot app will determine which database needs to be updated based on the URL path in the REST request.

 

The Customer Table

Here’s the code that creates the Customer table:

1
2
3
4
5
6
CREATE TABLE `customer` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`last_name` VARCHAR(50) NOT NULL,
`first_name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
)

Obviously, we’re keeping things simple here for the sake of the tutorial. Normally, a Customer table would include a lot more info beyond the ID, last name, and first name.

Keep in mind, though, that Customer table exists in two separate databases.

One database supports the fishing supplies website. It’s named “fishingsupplies.”

The other database supports the tech gadget website. It’s named “gadgetwarehouse.”

So even though the customer data is exactly the same for both websites, there are two Customer tables: one in each database.

 

CustomerManagementApplication

It’s easy to create a Spring Boot app. Here’s the code that gets the whole thing started:

1
2
3
4
5
6
7
@SpringBootApplication
public class CustomerManagementApplication {
     
    public static void main(String[] args) {
        SpringApplication.run(CustomerManagementApplication.class,args);
    }
}

Nothing fancy there. It’s all boilerplate stuff.

That’s the class you’ll run when you’re ready to test your new system.

 

The Model

Once you’ve built your skeleton Spring Boot app, it’s time to create a model class that reflects a single row in the Customer table. It will looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
@Entity
@Table(name="customer")
public class Customer {
 
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
     
    @Column(name="last_name")
    private String lastName;
     
    @Column(name="first_name")
    private String firstName;
     
     
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
}

As you can see, that’s not too complicated, either. It includes three fields that all correspond to the three columns in the table. Each field has its own getter and setter.

So far, so good!

But wait! There’s more!

 

The Tenant

You need to identify the tenant.

Wait. What’s the tenant?

For the purposes of this tutorial, you can think of it as a user associated with a specific data source.

There might be one admin, for example, who works with the fishing supplies website and another who works with the tech gadget website.

The whole concept of single-tenancy vs. multi-tenancy in applications is well out of scope for this guide. You should familiarize yourself with it, though.

Here, you’re handling data isolation with separate databases. That’s what makes your application a multi-tenant system.

The tenant will be identified in the URL. For example, if somebody wants to update info for a customer on the fishing supplies website, the relevant part of the URL path will look like this:

/app/fishingsupplies/customer

And if somebody wants to update info for a customer on the tech gadget website, the relevant part of the URL path will look like this:

/app/gadgetwarehouse/customer

As you can see, the only difference between those two URL paths is the name in the middle. That’s how the system will identify the data source that it will use to persist customer info.

 

The Repository

Now that you’ve created the model, it’s time to create the repository. Fortunately, that’s a breeze.

Here’s what the repository code looks like:

1
2
3
public interface CustomerRepository  extends CrudRepository<Customer,Long> {
 
}

Yep. That’s it.

Again, we’re keeping things simple here. We’re not going beyond the “basic” CRUD (Create, Read, Update, Delete) activities at this time. So there’s no need for hand-crafted methods.

You can do a simple save that persists new customer info with that interface. That’s all you need right now.

 

AppRoutingDataSource

Now it’s time to move on to the fun stuff. In this case, you’ll create a new class that extends AbstractRoutingDataSource.

That seems natural because AbstractRoutingDataSource is… abstract. It needs an extension.

If you’re unfamiliar with the AbstractRoutingDataSource class, it’s designed for exactly this purpose. It handles calls to get target data sources based on a lookup key.

Here’s the code for AppRoutingDataSource, the class that extends AbstractRoutingDataSource:

That’s not too complicated either, is it?

The whole thing starts off with a declaration of a ThreadLocal variable called currentTenant.

Why ThreadLocal? Because you need the context associated with a specific thread.

Next is the implementation of the determineCurrentLookupKey() from the abstract class. As you can see, the code just returns a String id that reflects the current tenant.

Finally, there’s a public setter method that allows another class to set the current tenant.

 

The Configuration

Next, you need to configure the data source info. That class looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Configuration
public class DataSourceConfig {
 
    @Bean
    public AppRoutingDataSource dataSource() {
        AppRoutingDataSource multiTenantDataSource = new AppRoutingDataSource();
         
        multiTenantDataSource.setTargetDataSources(new ConcurrentHashMap<>());
        multiTenantDataSource.setDefaultTargetDataSource(defaultDataSource());
        multiTenantDataSource.afterPropertiesSet();
         
        return multiTenantDataSource;
    }
     
     
    private DriverManagerDataSource defaultDataSource() {
        DriverManagerDataSource defaultDataSource = new DriverManagerDataSource();
        defaultDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        defaultDataSource.setUrl("jdbc:mysql://myhost.com:3306/fishingsupplies");
        defaultDataSource.setUsername("bubba");
        defaultDataSource.setPassword("password");
        return defaultDataSource;
    }
}

The whole class is annotated with @Configuration. That’s what tells Spring Boot it’s a config class.

Next, there’s a method that instantiates the AppRoutingDataSource class you created in the previous step. it’s annotated with @Bean so the instance can be autowired in other objects.

The dataSource() method itself does little besides set some defaults. One of those defaults is the default data source. You can see that defined in the method at the bottom.

Keep in mind: you’ll need to set the specifics of your own database connection there.

Another default is the empty ConcurrentHashMap you see in the second line of the dataSource() method. That means you haven’t yet defined the available data sources. You’ll see how to do that in the next step.

 

MultiTenantManager

Next, it’s time to create a class that’s quite a bit more complicated than the ones you’ve developed so far. It’s called MultiTenantManager.

Here’s what that code looks like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
@Component
public class MultiTenantManager implements ApplicationListener<ContextRefreshedEvent> {
 
    private static final Logger logger = LoggerFactory.getLogger(MultiTenantManager.class);
     
    private final Map<Object, Object> tenantDataSources = new ConcurrentHashMap<>();
     
    @Autowired
    private AppRoutingDataSource routingDataSource;
     
 
    public void setCurrentTenant(String tenantId)  {
        if (tenantIsAbsent(tenantId)) {
            throw new RuntimeException("No tenant with ID " + tenantId);
        }
         
        routingDataSource.setCurrentTenant(tenantId);
        logger.debug("Tenant '{}' set as current.", tenantId);
    }
 
     
    public void addTenant(String tenantId, String url, String username, String password) throws SQLException {
        DataSource dataSource = DataSourceBuilder.create()
                .driverClassName("com.mysql.cj.jdbc.Driver")
                .url(url)
                .username(username)
                .password(password)
                .build();
 
        try(Connection c = dataSource.getConnection()) {
            tenantDataSources.put(tenantId, dataSource);
            routingDataSource.setTargetDataSources(tenantDataSources);
            routingDataSource.afterPropertiesSet();
            logger.debug("Tenant '{}' added.", tenantId);
        }
    }
 
    public DataSource removeTenant(String tenantId) {
        Object removedDataSource = tenantDataSources.remove(tenantId);
        routingDataSource.setTargetDataSources(tenantDataSources);
        routingDataSource.afterPropertiesSet();
        return (DataSource) removedDataSource;
    }
 
    public boolean tenantIsAbsent(String tenantId) {
        return !tenantDataSources.containsKey(tenantId);
    }
 
    public Collection<Object> getTenantList() {
        return tenantDataSources.keySet();
    }  
     
    @Override
    public void onApplicationEvent(ContextRefreshedEvent event) {
        try {
            addTenant("fishingsupplies", "jdbc:mysql://myhost.com:3306/fishingsupplies", "bubba", "password");
            addTenant("gadgetwarehouse", "jdbc:mysql://myhost.com:3306/gadgetwarehouse", "bubba", "password");
        } catch (Exception e ) {
            e.printStackTrace();
        }
    }
}

There’s a lot going on there, so let’s go over it a little bit at a time.

At the very top, the code creates an empty ConcurrentHashMap. That map will associate keys (called tenant IDs) with data sources.

When the app gets a REST request, it will evaluate the URL to determine the tenant. It will use that tenant ID as a key to look up the data source in the map.

Next, the code uses dependency injection to instantiate the AppRoutingDataSource class that you created a couple of steps previously.

The setCurrentTenant() method is important. It determines which data source will be used to handle the current request.

The addTenant() method creates a new data source, associates it with a key, and adds it to the map.

As you can see, that method updates the AppRoutingDataSource object with the map that includes the just-added data source.

The code also invokes afterPropertiesSet() so the bean can reinitialize.

The removeTenant() method is pretty straightforward. It accepts a tenant ID and removes that data source from the map.

The tenantIsAbsent() method simply determines whether a data source identified by the given tenant ID even exists. It returns a boolean.

The getTenantList() method returns a collection that includes all tenant IDs.

Finally, the onApplicationEvent() method executes after the Spring context has initialized. It adds two data sources identified by the tenant IDs “fishingsupplies” and “gadgetwarehouse.”

The first parameter in those methods is the tenant ID. The next three parameters, in order, are: the URL to the MySQL database, the user name, and the password.

And, once again, you’ll need to specify your own MySQL connection info in that method.

The reason that onApplicationEvent() executes after the Spring context has loaded, by the way, is because the class implements ApplicationListener.

 

CustomerController

Next, it’s time to add a controller class. Here’s what that will look like.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@RestController
public class CustomerController {
 
    @Autowired
    CustomerRepository customerRepository;
     
    @Autowired
    MultiTenantManager multiTenantManager;
     
    @PostMapping("/app/{tenantId}/customer")
    public Customer createCustomer(@PathVariable String tenantId, @RequestBody Customer customer) {
        //set the correct database
        multiTenantManager.setCurrentTenant(tenantId);
         
        //persist the customer
        customer = customerRepository.save(customer);
         
        return customer;
    }
}

That one is much simpler than the previous class.

As you can see, CustomerController autowires a CustomerRepository object and a MultiTenantManager object.

The controller depends on a CustomerRepository object so that it can persist info and retrieve data from the database.

The controller depends on a MultiTenantManager object so it can set the correct data source before persisting or retrieving anything.

There’s only one mapping class in the controller: createCustomer().

Again, we’re keeping things simple with limited code.

The createCustomer() method is annotated with @PostMapping because it accepts a POST request.

That method is also using a URI Template. That’s the {tenantId} part of the URI.

When somebody submits a request, the {tenantId} will vary depending on which data source the requester wants to use.

That tenant ID, by the way, will be used to look up the data source. It’s a string that’s also one of the keys in the data source map.

Next, take a look at the first parameter in the method. It’s annotated with @PathVariable and named tenantId.

Unsurprisingly, that variable corresponds to the {tenantId} portion of the URI Template.

The next parameter is annotated with @RequestBody. That’s because it extracts info from the body of the request and not from the URL. In this case, it maps JSON data to an instance of the Customer class.

The createCustomer() method is pretty simple.

First, it sets the current tenant with the tenant ID.

Then, it persists the customer info in the data source associated with that tenant ID.

Finally, it returns the new customer object (with the ID) to the requester.

 

Testing It Out

At this point, the code is done. It’s time to test.

Within Eclipse, right-click on the CustomerManagementApplication class. Select “Run As…” from the context menu and choose “Java Application.”

runas

Wait for your app to start. It should only take a few seconds.

Next, you need to send a request.

Sorry, we aren’t going through the whole process of creating an Angular app just to test this out. That’s a subject for a different day.

Instead, test it by mimicking what an Angular app would do. Just POST a request with a JSON body.

There are a variety of tools that you can use to make that happen. One of the best is Fiddler.

Launch Fiddler and click on the Composer tab on the right-hand side of the screen.

fiddler1

Next, change the request type from GET to POST.

Also, specify the Content-Type as application/json in request header section.

fiddler2

Next, you need to enter the URL.

The URL path to create a customer, as defined in the controller class, is:

/app/{tenantId}/customer

where {tenantId} is the ID of the tenant.

There are two tenant IDs available. They’re both defined in MultiTenantManager in the onApplicationEvent() method:

  • fishingsupplies
  • gadgetwarehouse

Start your test with the fishingsupplies tenant ID. Your full URL will look like this:

http://localhost:8090/app/fishingsupplies/customer

All of the Spring Boot apps in these demos use port 8090. This one is no exception. If you’re using the default port of 8080, feel free to update the URL accordingly.

You’ll also need to add a request body. You do that in the lower part of the screen.

Remember, the request body must include properties that correspond to the Customer class. Here’s what it will look like:

{
"firstName" : "Bubba",
"lastName" : "Gump"
}

fiddler3

Now you’re ready for the test!

Just click the “Execute” button in the upper, right-hand corner of the screen. Then, look at the column on the left hand side of the screen.

After a few moments you should see that you received a 200 response from the Spring Boot application.

If the response is anything other than 200 and it’s in red, then something went wrong. Take a look at your console to find out what happened.

fiddler4

If you got the 200 response back, check the MySQL database table to make sure that the new customer got persisted. Here’s what that looks like in HeidiSQL:

heidi

You should also check the response body in Fiddler. Do that by click on the Inspectors tab on the right-hand side of the screen and then clicking on the JSON tab in the lower part of the screen.

fiddler5

As you can see, the data corresponds perfectly to the data in the database.

Now for the second test. And for this one, you’re on your own.

Go through that whole process again except persist a customer using the gadgetwarehouse tenant ID.

Here’s a hint: the URL will look like this:

http://localhost:8090/app/gadgetwarehouse/customer

 

Wrapping It Up

Now you know how to manage multiple data sources within a Spring Boot app.

If you’re having trouble with this code, there’s an entire working example on GitHub. Feel free to grab it and use it for your own purposes.

Have fun!