For regulatory and security reasons I had to split the logic of my Spring boot application into two tools: One for administration with limited number of tables and one for the "real" user application. Therefore I have two MySQL databases instances on the server version 5.7. While the user tool only accesses one database with dozens of tables, the admin tool needs to access entities in both databases.
The tools are both JavaFX and Spring Boot based. Because of this architectural setup I have three maven packages: One for the admin tool and all the admin related entities, services and alike, one for the user tool and all related entities, services etc. only relevant for this user tool, and the third with all the entities that both tools share.
When I run the user tool it generates the tables in the shared database and uses the hibernate ImprovedNamingStrategy based on the configuration in its application.properties file. Hence the columns have an underscore where appropriate.
In the first place the admin tool wouldn't create any database tables at all using spring.jpa.hibernate.ddl-auto, but I had to use spring.jpa.generate-ddl.
Now, when I run the admin tool I would expect that it only creates the tables in the admin database since this datasource is annotated as @Primary. But it also creates columns in the user database with mixed case. Hence I have columns named e.g. "email_address" and "emailAddress" in user database.
I wonder whether any properties are used with my approach? Any ideas how to do it properly?
Please find following some source..
application.properties :
# Employee database
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.jdbcUrl=jdbc:mysql://127.0.0.1/agiletunesdb?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&characterSetResults=utf-8
spring.datasource.username=YYY
spring.datasource.password=XXXXXX
# Account database
security.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
security.datasource.jdbcUrl=jdbc:mysql://127.0.0.1/authenticationdb?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&characterSetResults=utf-8
security.datasource.username=YYY
security.datasource.password=XXXXXX
# create db schema, values are none, validate, update, create, and create-drop.
#spring.jpa.hibernate.ddl-auto=create
spring.jpa.hibernate.ddl-auto=update
#spring.jpa.hibernate.ddl-auto=none
spring.jpa.generate-ddl=true
# Naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
Databases Configurations :
import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
@Configuration
public class MultipleDbConfiguration {
/*
* Persistence of admin database
*/
@Bean(name = "securityDB")
@Primary
@ConfigurationProperties(prefix="security.datasource")
public DataSource securityDataSource() {
return DataSourceBuilder.create().build();
}
/*
*
* Persistence of user database
*/
@Bean(name = "organizationDB")
@ConfigurationProperties(prefix="spring.datasource")
public DataSource organizationDataSource() {
return DataSourceBuilder.create().build();
}
}
The user database configuration
import java.util.HashMap;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.annotation.PropertySources;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration
@EnableJpaRepositories(
entityManagerFactoryRef = "organizationEntityManagerFactory",
transactionManagerRef = "organizationTransactionManager",
basePackages = "com.agiletunes.domain.organization"
)
@EnableTransactionManagement
@PropertySources({ @PropertySource("classpath:application.properties") })
public class OrganizationConfig {
@Autowired
private Environment env; // Contains Properties Load by @PropertySources
@Bean(name = "organizationEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean organizationEntityManagerFactory(
EntityManagerFactoryBuilder builder, @Qualifier("organizationDB") DataSource dataSource) {
HashMap<String, Object> properties = new HashMap<>();
properties.put("spring.jpa.properties.hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.dialect"));
properties.put("spring.jpa.hibernate.ddl-auto", env.getProperty("spring.jpa.hibernate.ddl-auto"));
properties.put("spring.jpa.hibernate.naming-strategy", env.getProperty("spring.jpa.hibernate.naming-strategy"));
return builder
.dataSource(dataSource)
.packages("com.agiletunes.domain.organization")
.persistenceUnit("organizationPU")
.properties(properties)
.build();
}
@Bean(name="organizationTransactionManager")
public PlatformTransactionManager secondTransactionManager(@Qualifier("organizationEntityManagerFactory")
EntityManagerFactory organizationEntityManagerFactory) {
return new JpaTransactionManager(organizationEntityManagerFactory);
}
}
The trick was to use configuration classes which would have a
@PropertySources({ @PropertySource("classpath:application.properties") })
annotation. Then, in the method which creates the LocalContainerEntityManagerFactoryBean, you can pull and set values defined in your application.properties file with
properties.put("spring.jpa.hibernate.naming.physical-strategy", env.getProperty("spring.jpa.hibernate.naming.physical-strategy"));