如何配置Spring Boot以便与两个数据库一起工作? [英] How to configure Spring boot for work with two databases?
问题描述
我正在使用 Spring Boot 2.X 和 Hibernate 5 在不同服务器上连接两个不同的MySQL数据库(Bar和Foo).我试图从REST控制器中的方法列出实体的所有信息(拥有属性和 @OneToMany 和 @ManyToOne 关系).
I am using Spring Boot 2.X with Hibernate 5 to connect two different MySQL databases (Bar and Foo) on different servers. I am trying to list all the information of an entity (own attributes and @OneToMany and @ManyToOne relations) from a method in a REST Controller.
我遵循了一些教程来做到这一点，因此，我能够获取我的 @Primary 数据库(Foo)的所有信息，但是，我的二级数据库总是会出现一个异常(栏)，当检索 @OneToMany 集时.如果将 @Primary 批注交换到Bar数据库，则可以从Bar数据库获取数据，但不能从Foo数据库获取数据.有办法解决这个问题吗?
I have followed several tutorials to do this, thus, I am able to get all the information for my @Primary database (Foo), however, I always get an exception for my secondary database (Bar) when retrieving the @OneToMany sets. If I swap the @Primary annotation to the Bar database, I able to get the data from the Bar database but not for the Foo database . Is there a way to resolve this?
这是我得到的例外:
...w.s.m.s.DefaultHandlerExceptionResolver :
Failed to write HTTP message: org.springframework.http.converter.HttpMessageNotWritableException:
Could not write JSON document: failed to lazily initialize a collection of role:
com.foobar.bar.domain.Bar.manyBars, could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]-com.foobar.bar.domain.Bar["manyBars"]);
nested exception is com.fasterxml.jackson.databind.JsonMappingException:
failed to lazily initialize a collection of role:
com.foobar.bar.domain.Bar.manyBars, could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]->com.foobar.bar.domain.Bar["manyBars"])
我的application.properties:
My application.properties:
# MySQL DB - "foo"
spring.datasource.url=jdbc:mysql://XXX:3306/foo?currentSchema=public
spring.datasource.username=XXX
spring.datasource.password=XXX
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# MySQL DB - "bar"
bar.datasource.url=jdbc:mysql://YYYY:3306/bar?currentSchema=public
bar.datasource.username=YYYY
bar.datasource.password=YYYY
bar.datasource.driver-class-name=com.mysql.jdbc.Driver
# JPA
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
我的 @Primary 数据源配置:
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory",
transactionManagerRef = "transactionManager",
basePackages = {"com.foobar.foo.repo"})
public class FooDbConfig {
@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
EntityManagerFactoryBuilder builder, @Qualifier("dataSource") DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages("com.foobar.foo.domain")
.persistenceUnit("foo")
.build();
}
@Primary
@Bean(name = "transactionManager")
public PlatformTransactionManager transactionManager(
@Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
}
我的辅助数据源配置:
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "barEntityManagerFactory",
transactionManagerRef = "barTransactionManager", basePackages = {"com.foobar.bar.repo"})
public class BarDbConfig {
@Bean(name = "barDataSource")
@ConfigurationProperties(prefix = "bar.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "barEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean barEntityManagerFactory(
EntityManagerFactoryBuilder builder, @Qualifier("barDataSource") DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages("com.foobar.bar.domain")
.persistenceUnit("bar")
.build();
}
@Bean(name = "barTransactionManager")
public PlatformTransactionManager barTransactionManager(
@Qualifier("barEntityManagerFactory") EntityManagerFactory barEntityManagerFactory) {
return new JpaTransactionManager(barEntityManagerFactory);
}
}
REST控制器类:
@RestController
public class FooBarController {
private final FooRepository fooRepo;
private final BarRepository barRepo;
@Autowired
FooBarController(FooRepository fooRepo, BarRepository barRepo) {
this.fooRepo = fooRepo;
this.barRepo = barRepo;
}
@RequestMapping("/foo")
public List<Foo> listFoo() {
return fooRepo.findAll();
}
@RequestMapping("/bar")
public List<Bar> listBar() {
return barRepo.findAll();
}
@RequestMapping("/foobar/{id}")
public String fooBar(@PathVariable("id") Integer id) {
Foo foo = fooRepo.findById(id);
Bar bar = barRepo.findById(id);
return foo.getName() + " " + bar.getName() + "!";
}
}
Foo/Bar存储库:
The Foo/Bar repositories:
@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {
Foo findById(Integer id);
}
@Repository
public interface BarRepository extends JpaRepository<Bar, Long> {
Bar findById(Integer id);
}
@Primary 数据源的实体.第二个数据源的实体相同(仅更改类名):
The entities for the @Primary datasource. The entities of the second datasource are the same (only changing the class names):
@Entity
@Table(name = "foo")
public class Foo {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id", unique = true, nullable = false)
private Integer id;
@Column(name = "name")
private String name;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "foo")
@JsonIgnoreProperties({"foo"})
private Set<ManyFoo> manyFoos = new HashSet<>(0);
// Constructors, Getters, Setters
}
@Entity
@Table(name = "many_foo")
public class ManyFoo {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id", unique = true, nullable = false)
private Integer id;
@Column(name = "name")
private String name;
@ManyToOne(fetch = FetchType.LAZY)
@JsonIgnoreProperties({"manyFoos"})
private Foo foo;
// Constructors, Getters, Setters
}
最后，我的应用程序主要是:
Finally, my application main:
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
重要的一点是，该解决方案应为两个数据库都保留Lazy属性，以保持最佳性能.
如果两个目录(MySQL术语中的数据库")都在同一个数据库(服务器")中，那么Rick James解决方案就可以使用！
Edit 1: If both catalogs ("databases" in MySQL terminology) are in same database ("server") the Rick James solution works!!
问题仍然存在(当目录(MySQL数据库)位于不同的数据库(服务器)中并且试图保持惰性属性时
The problem remains when catalogs (MySQL databases) are in different databases (servers) and it is tried to keep Lazy the property
非常感谢.
推荐答案
*默认情况下，ToMany Collections在Hibernate&中是惰性的.JPA.该错误是因为关闭实体管理器(即休眠状态下的会话)时，杰克逊正尝试序列化OneToMany.因此，无法检索惰性集合.
*ToMany Collections are lazy by default in Hibernate & JPA. The error is because Jackson is trying to serialize the OneToMany when the entity manager (aka session in hibernate-speak) is closed. Hence, lazy collections cannot be retrieved.
默认情况下，使用JPA的Spring Boot为主要EM提供 OpenEntityManagerInViewFilter .这允许只读数据库访问，但是默认情况下仅适用于主EM.
Spring Boot with JPA by default provides an OpenEntityManagerInViewFilter for the primary EM. This allows for read-only DB access, but, by default only works for the primary EM.
您有3个选择:
1)您可以添加联接提取，例如 FetchMode如何在Spring Data JPA中工作
1) You can add a join fetch, e.g. How does the FetchMode work in Spring Data JPA
2)您可以为非主要实体管理器添加一个OpenEntityManagerInViewFilter并将其添加到您的上下文中.
2) You can add a OpenEntityManagerInViewFilter for the non primary entity manager and add it to your context.
请注意，这意味着一个挑战，对于每个Bar和Foo实例，您的应用程序将返回数据库以检索OneToMany.这部分不适用于Bar，但适用于Foo.这意味着存在可伸缩性问题(有人称N + 1问题)，因为对于每个foo和bar，您都要运行一个附加查询，这对于非平凡数量的Foos和Bars会变慢.
Please note that this implies a challenge, for each Bar and Foo instance, your app will go back to the database to retrieve the OneToMany. This is the part that isn't working for Bar, but is for Foo. This implies a scalability problem (called the N + 1 problem by some) as for each foo and bar, you run an additional query, which will get slow for non-trivial amounts of Foos and Bars.
3)一种替代方法是使您的收藏集中在Bar and Foo上(请参阅
3) An alternative is to make your collection on Bar and Foo eager (see this https://docs.oracle.com/javaee/7/api/javax/persistence/OneToMany.html#fetch-- ) but this needs to be analyzed carefully if scalability is a concern at all for you.
我建议选择#1.
这篇关于如何配置Spring Boot以便与两个数据库一起工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
