java - 在 Spring Boot 中动态设置 hibernate.dialect 属性
问题描述
我已经浏览了有关如何hibernate.dialect
正确设置属性的可用示例和教程,但没有找到适合我情况的方法。
本教程最适合我,但它缺乏动态设置属性的能力,hibernate.dialect
因为我有不同类型的数据库要连接:
- 微软 SQL
- 甲骨文
- H2
- MySQL
如果方言不正确,我的 JPA(删除/更新)查询会失败。
通过以下@Configuration
完美运行的实现,我如何能够hibernate.dialect
在运行时为每个数据源动态设置?
先感谢您。
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
basePackages = "com.example.multidb",
entityManagerFactoryRef = "multiEntityManager",
transactionManagerRef = "multiTransactionManager"
)
public class PersistenceConfiguration {
private final String PACKAGE_SCAN = "com.example.multidb";
@Primary
@Bean(name = "mainDataSource")
@ConfigurationProperties("app.datasource.main")
public DataSource mainDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean(name = "clientADataSource")
@ConfigurationProperties("app.datasource.clienta")
public DataSource clientADataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean(name = "clientBDataSource")
@ConfigurationProperties("app.datasource.clientb")
public DataSource clientBDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean(name = "multiRoutingDataSource")
public DataSource multiRoutingDataSource() {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.MAIN, mainDataSource());
targetDataSources.put(DBTypeEnum.CLIENT_A, clientADataSource());
targetDataSources.put(DBTypeEnum.CLIENT_B, clientBDataSource());
MultiRoutingDataSource multiRoutingDataSource = new MultiRoutingDataSource();
multiRoutingDataSource.setDefaultTargetDataSource(mainDataSource());
multiRoutingDataSource.setTargetDataSources(targetDataSources);
return multiRoutingDataSource;
}
@Bean(name = "multiEntityManager")
public LocalContainerEntityManagerFactoryBean multiEntityManager() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(multiRoutingDataSource());
em.setPackagesToScan(PACKAGE_SCAN);
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
em.setJpaProperties(hibernateProperties());
return em;
}
@Bean(name = "multiTransactionManager")
public PlatformTransactionManager multiTransactionManager() {
JpaTransactionManager transactionManager
= new JpaTransactionManager();
transactionManager.setEntityManagerFactory(
multiEntityManager().getObject());
return transactionManager;
}
@Primary
@Bean(name = "dbSessionFactory")
public LocalSessionFactoryBean dbSessionFactory() {
LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
sessionFactoryBean.setDataSource(multiRoutingDataSource());
sessionFactoryBean.setPackagesToScan(PACKAGE_SCAN);
sessionFactoryBean.setHibernateProperties(hibernateProperties());
return sessionFactoryBean;
}
private Properties hibernateProperties() {
Properties properties = new Properties();
properties.put("hibernate.show_sql", true);
properties.put("hibernate.format_sql", true);
//set hibernate.dialect for each datasource
return properties;
}
}
解决方案
我为您创建了一个工作示例,我将在此处对其进行描述,但是如果您想自己跳入代码,可以在此 GitHub 存储库中找到它。
就我而言,我创建了两个数据源,一个User
用于Item
.
这里的实体:
package com.marcosbarbero.so.multiple.ds.entity.user;
import lombok.Data;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Data
@Entity
@Table(schema = "user")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
private String name;
@Column(unique = true, nullable = false)
private String email;
private int age;
}
package com.marcosbarbero.so.multiple.ds.entity.item;
import lombok.Data;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Data
@Entity
@Table(schema = "item")
public class Item {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;
private String name;
}
请注意,为每个域提供不同的包很重要。然后我创建了Repositories
package com.marcosbarbero.so.multiple.ds.repository.user;
import com.marcosbarbero.so.multiple.ds.entity.user.User;
import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User, Integer> {
}
package com.marcosbarbero.so.multiple.ds.repository.item;
import com.marcosbarbero.so.multiple.ds.entity.item.Item;
import org.springframework.data.jpa.repository.JpaRepository;
public interface ItemRepository extends JpaRepository<Item, Integer> {
}
回购没有什么特别的。让我们转到最后一部分,配置。
- 我创建了一个
@ConfigurationProperties
类来外部化我的配置,请耐心等待,我知道命名不是最好的:)
package com.marcosbarbero.so.multiple.ds.config;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
import java.util.HashMap;
import java.util.Map;
@Data
@Component
@ConfigurationProperties(prefix = "multi-datasource")
public class MultipleDataSourceProperties {
private UserDataSourceProperties user = new UserDataSourceProperties();
private ItemDataSourceProperties item = new ItemDataSourceProperties();
@Data
public static class UserDataSourceProperties {
private HibernateProperties hibernate = new HibernateProperties();
}
@Data
public static class ItemDataSourceProperties {
private HibernateProperties hibernate = new HibernateProperties();
}
@Data
public static class HibernateProperties {
private Map<String, String> properties = new HashMap<>();
}
}
我们很快就会看到属性配置文件。
- 现在让我们
DataSource
为User
:
package com.marcosbarbero.so.multiple.ds.config;
import com.zaxxer.hikari.HikariDataSource;
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;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
@Configuration
@EnableJpaRepositories(
basePackages = "com.marcosbarbero.so.multiple.ds.repository.user",
entityManagerFactoryRef = "userEntityManager",
transactionManagerRef = "userTransactionManager"
)
public class UserDataSourceConfig {
private final MultipleDataSourceProperties properties;
public UserDataSourceConfig(MultipleDataSourceProperties properties) {
this.properties = properties;
}
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean userEntityManager() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(userDataSource());
em.setPackagesToScan("com.marcosbarbero.so.multiple.ds.entity.user");
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
em.setJpaPropertyMap(properties.getUser().getHibernate().getProperties());
return em;
}
@Primary
@Bean
@ConfigurationProperties("multi-datasource.user")
public DataSource userDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Primary
@Bean
public PlatformTransactionManager userTransactionManager() {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(userEntityManager().getObject());
return transactionManager;
}
}
在这个类中对你来说重要的部分是它从上面定义em.setJpaPropertyMap(properties.getUser().getHibernate().getProperties());
的类中获取用户的 Hibernate 配置属性。@ConfigurationProperties
- 现在让我们对
Item
:
package com.marcosbarbero.so.multiple.ds.config;
import com.zaxxer.hikari.HikariDataSource;
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;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
@Configuration
@EnableJpaRepositories(
basePackages = "com.marcosbarbero.so.multiple.ds.repository.item",
entityManagerFactoryRef = "itemEntityManager",
transactionManagerRef = "itemTransactionManager"
)
public class ItemDataSourceConfig {
private final MultipleDataSourceProperties properties;
public ItemDataSourceConfig(MultipleDataSourceProperties properties) {
this.properties = properties;
}
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean itemEntityManager() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(itemDataSource());
em.setPackagesToScan("com.marcosbarbero.so.multiple.ds.entity.item");
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
em.setJpaPropertyMap(properties.getItem().getHibernate().getProperties());
return em;
}
@Primary
@Bean
@ConfigurationProperties("multi-datasource.item")
public DataSource itemDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Primary
@Bean
public PlatformTransactionManager itemTransactionManager() {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(itemEntityManager().getObject());
return transactionManager;
}
}
- 这
application.properties
multi-datasource.item.jdbcUrl=jdbc:h2:mem:spring_jpa_item;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS ITEM
multi-datasource.item.username=sa
multi-datasource.item.password=sa
multi-datasource.item.hibernate.properties.hibernate.hbm2ddl.auto=create-drop
multi-datasource.item.hibernate.properties.hibernate.cache.use_second_level_cache=false
multi-datasource.item.hibernate.properties.hibernate.cache.use_query_cache=false
multi-datasource.item.hibernate.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
multi-datasource.user.jdbcUrl=jdbc:h2:mem:spring_jpa_user;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS USER
multi-datasource.user.username=sa
multi-datasource.user.password=sa
multi-datasource.user.hibernate.properties.hibernate.hbm2ddl.auto=create-drop
multi-datasource.user.hibernate.properties.hibernate.cache.use_second_level_cache=false
multi-datasource.user.hibernate.properties.hibernate.cache.use_query_cache=false
multi-datasource.user.hibernate.properties.hibernate.dialect=org.hibernate.dialect.OracleDialect
- 一些单元测试
package com.marcosbarbero.so;
import com.marcosbarbero.so.multiple.ds.entity.item.Item;
import com.marcosbarbero.so.multiple.ds.entity.user.User;
import com.marcosbarbero.so.multiple.ds.repository.item.ItemRepository;
import com.marcosbarbero.so.multiple.ds.repository.user.UserRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import static org.junit.jupiter.api.Assertions.assertNotNull;
@SpringBootTest
public class JPAMultipleDBTest {
@Autowired
private UserRepository userRepository;
@Autowired
private ItemRepository itemRepository;
@Test
public void whenCreatingUser_thenCreated() {
User user = new User();
user.setName("John");
user.setEmail("john@test.com");
user.setAge(20);
user = userRepository.save(user);
assertNotNull(userRepository.findById(user.getId()));
}
@Test
public void whenCreatingProduct_thenCreated() {
Item item = new Item();
item.setName("Book");
item.setId(2);
item = itemRepository.save(item);
assertNotNull(itemRepository.findById(item.getId()));
}
}
我认为还值得一提的是,为了使这一切正常工作,我禁用了DataSourceAutoConfiguration
,就这么简单:
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
再一次,这一切都可以在这个 GitHub repo 上找到。
推荐阅读
- node.js - 自发布的 NPM 包添加时不导入任何内容
- ruby-on-rails - 如何让 Sendgrid Inbound Parse Webhook 在生产中的 rails 应用程序中工作?
- python - 将每个实例掩码合并回原始图像 Python
- node.js - 如何在构建期间创建带有转储的 postgreSQL 数据库以在 gitlab-CI 中启动单元测试?
- ios - 如何使用带有 react-native-camera 的默认 iOS 相机控件
- swift - 路径中的 SwiftUI UIImage 不可见
- scala - Doobie for Scala / Play 框架中的事务
- loops - TestCafe 循环遍历元素并检查 Set 中是否存在类
- javascript - 为什么当我更新商品数量时,循环遍历购物车商品并返回商品数量的 Vuex getter 不更新?
- python - 使用布尔数组的逐项总和作为包含的“或”