Change database schema used by Spring Boot
Use for application.properties
:
spring.jpa.properties.hibernate.default_schema=your_scheme
OR for application.yaml
:
spring:
jpa:
properties:
hibernate.default_schema: your_scheme
From the Spring Boot reference guide:
all properties in
spring.jpa.properties.*
are passed through as normal JPA properties (with the prefix stripped) when the localEntityManagerFactory
is created
See http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-configure-jpa-properties
For a full list of available properties see http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-configure-jpa-properties
Change database schema during runtime based on logged in user and shared schema
Found two solutions, both solved my problem.
Solution 1 -
Created a hibernate interceptor and add dynamic schema in SQL query.
https://dzone.com/articles/hibernate-dynamic-table-routin
public class HibernateInterceptor extends EmptyInterceptor {
@Override
public String onPrepareStatement(String sql) {
String prepedStatement = super.onPrepareStatement(sql);
prepedStatement = prepedStatement.replaceAll("secure.identitymanagement", "my_dynamic_goodness");
return prepedStatement;
}
}
Solution 2 -
Configured two different schema - one for shared/common schema and another one for the user-specific schema.
This blog explains both "Database per tenant" and "Schema per tenant".
https://callistaenterprise.se/blogg/teknik/2020/09/19/multi-tenancy-with-spring-boot-part1/
Set default schema = SOMETHING in oracle using Spring Boot and Spring JDBC
Assuming you define your database connections using spring datasources, you can set the default schema when defining the datasource configuration:
spring.datasource.schema = #value for your default schema to use in database
You can find more info here: Spring Boot Reference Guide. Appendix A. Common application properties
After doing some research, looks like Oracle driver doesn't let you set a default schema to work with, as noted here:
Default Schema in Oracle Connection URL
From that post, you have two options:
Execute this statement before executing your statements:
ALTER SESSION SET CURRENT_SCHEMA=yourSchema
Create synonyms for your tables/views/etc (which I find really cumbersome if we're talking about lots of elements in your database).
I would advice using the first option. From what I see, Spring boot doesn't offer a simple way to execute a statement when retrieving the connection, so the best bet will be to use an aspect around the getConnection
method (or the method that retrieves the connection from the data source) and execute the statement there.
From your comment, an easier way to solve it is by using a script in spring.datasource.schema
:
spring.datasource.schema = schema.sql
And then a file squema.sql with the following:
ALTER SESSION SET CURRENT_SCHEMA=mySchema
Change database schema during runtime based on logged in user
Assumptions
Because I don't have the reputation yet to post a comment below your question, my answer is based on the following assumptions:
The current schema name to be used for the current user is accessible through a Spring JSR-330 Provider like
private javax.inject.Provider<User> user; String schema = user.get().getSchema();
. This is ideally a ThreadLocal-based proxy.To build a
DataSource
which is fully configured in a way you need it requires the same properties. Every time. The only thing which is different is the schema name. (It would easily possible to obtain other different parameters as well, but this would be too much for this answer)Each schema is already set up with the needed DDL, so there is no need for hibernate to create tables or something else
Each database schema looks completely the same except for its name
You need to reuse a DataSource every time the corresponding user makes a request to your application. But you don't want to have every DataSource of every user permanently in the memory.
My solution idea
Use a combination of ThreadLocal proxys to get the schema name and a Singleton-DataSource which behaves different on every user request. This solution is inspired by your hint to AbstractRoutingDataSource
, Meherzad's comments and own experience.
A dynamic DataSource
I suggest to facilitate the AbstractDataSource
of Spring and implement it like the AbstractRoutingDataSource
. Instead of a static Map
-like approach we use a Guava Cache to get an easy to use cache.
public class UserSchemaAwareRoutingDataSource extends AbstractDataSource {
private @Inject javax.inject.Provider<User> user;
private @Inject Environment env;
private LoadingCache<String, DataSource> dataSources = createCache();
@Override
public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return determineTargetDataSource().getConnection(username, password);
}
private DataSource determineTargetDataSource() {
String schema = user.get().getSchema();
return dataSources.get(schema);
}
private LoadingCache<String, DataSource> createCache() {
return CacheBuilder.newBuilder()
.maximumSize(100)
.expireAfterWrite(10, TimeUnit.MINUTES)
.build(
new CacheLoader<String, DataSource>() {
public DataSource load(String key) throws AnyException {
return buildDataSourceForSchema(key);
}
});
}
private DataSource buildDataSourceForSchema(String schema) {
// e.g. of property: "jdbc:postgresql://localhost:5432/mydatabase?currentSchema="
String url = env.getRequiredProperty("spring.datasource.url") + schema;
return DataSourceBuilder.create()
.driverClassName(env.getRequiredProperty("spring.datasource.driverClassName"))
[...]
.url(url)
.build();
}
}
Now you have a `DataSource´ which acts different for every user. Once a DataSource is created it's gonna be cached for 10 minutes. That's it.
Make the application aware of our dynamic DataSource
The place to integrate our newly created DataSource is the DataSource singleton known to the spring context and used in all beans e.g. the EntityManagerFactory
So we need an equivalent to this:
@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix="spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
but it has to be more dynamic, than a plain property based DataSourceBuilder:
@Primary
@Bean(name = "dataSource")
public UserSchemaAwareRoutingDataSource dataSource() {
return new UserSchemaAwareRoutingDataSource();
}
Conclusion
We have a transparent dynamic DataSource which uses the correct DataSource everytime.
Open questions
- What to do, when no user is logged in? Is there no database access allowed?
- Who sets up the schemes?
Disclaimer
I haven't tested this code!
EDIT:
To implement a Provider<CustomUserDetails>
with Spring you need to define this as prototype. You can utilize Springs support of JSR-330 and Spring Securitys SecurityContextHolder:
@Bean @Scope("prototype")
public CustomUserDetails customUserDetails() {
return return (CustomUserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
}
You don't need a RequestInterceptor
, the UserProvider
or the controller code to update the user anymore.
Does this help?
EDIT2
Just for the record: do NOT reference the CustomUserDetails
bean directly. Since this is a prototype, Spring will try to create a proxy for the class CustomUserDetails
, which is not a good idea in our case. So just use Provider
s to access this bean. Or make it an interface.
Related Topics
How to Decode a String Encoded with Openssl Aes-128-Cbc Using Java
How to Pass a JSON Array as a Parameter in Url
Log4J2 - Assigning File Appender Filename at Runtime
Compile Error: Package Javax.Servlet Does Not Exist
How to Pass C Structs Back and Forth to Java Code in Jni
Are There Any Other Java Libraries for Bonjour/Zeroconf Apart from Jmdns
Declaring Floats, Why Default Type Double
Maven "Build Path Specifies Execution Environment J2Se-1.5", Even Though I Changed It to 1.7
Retrieve an Image Stored as Blob on a MySQL Db
Is 1/0 a Legal Java Expression
Tomcat7 Starts Too Late on Ubuntu 14.04 X64 [Digitalocean]
How to Solve the "A Generic Array of T Is Created for a Varargs Parameter" Compiler Warning
How to Convert a Binary String to a Base 10 Integer in Java
Java's Virtual MAChine's Endianness
How to Configure Log4J to Log Different Log Levels to Different Files for the Same Logger