Spring Boot-动态更改连接 [英] Spring Boot - Change connection dynamically

问题描述

我有一个Spring Boot项目，其中包含多个不同年份的数据库，这些数据库具有相同的表，因此唯一的区别是年份(...，DB2016，DB2017).在应用程序的控制器中，我需要返回属于不同"年份的数据.此外，在未来的几年中还将创建其他数据库(例如，在2018年将有一个名为"DB2018"的数据库).因此，我的问题是如何在数据库之间切换连接而又不每隔一年创建一个新的数据源和一个新的存储库. 在我发布的另一个问题中( Spring Boot-Same存储库和用于不同数据库的相同实体)的答案是为每个现有数据库创建不同的数据源和不同的存储库，但是在这种情况下，我想根据当前年份从现有数据库返回数据.更具体地说:

I have a Spring Boot project with multiple databases of different years and these databases have same tables so the only difference is the year (..., DB2016, DB2017). In the controller of the application i need to return data that belong to "different" years. Moreover in future years other databases will be created (eg. in 2018 there's going to be a db named "DB2018"). So my problem is how to switch the connection among databases without creating a new datasource and a new repository every new year. In an other question posted by me (Spring Boot - Same repository and same entity for different databases) the answer was to create different datasources and different repositories for every existing database, but in this case i want to return data from existing databases on the basis of the current year. More specifically:

SomeEntity.java

SomeEntity.java

@Entity(name = "SOMETABLE")
public class SomeEntity implements Serializable {
@Id
@Column(name="ID", nullable=false)
private Integer id;

@Column(name="NAME")
private String name;
}

SomeRepository.java

SomeRepository.java

public interface SomeRepository extends PagingAndSortingRepository<SomeEntity, Integer> {
@Query(nativeQuery= true, value = "SELECT * FROM SOMETABLE WHERE NAME = ?1")
List<SomeEntity> findByName(String name);
}

SomeController.java

SomeController.java

@RequestMapping(value="/foo/{name}", method=RequestMethod.GET)
public ResponseEntity<List<SomeEntity>> findByName(@PathVariable("name") String name) {
List<SomeEntity> list = autowiredRepo.findByName(name);
return new ResponseEntity<List<SomeEntity>>(list,HttpStatus.OK);

}

application.properties

application.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/DB
spring.datasource.username=xxx
spring.datasource.password=xxx

因此，如果当前年份是2017年，我想要这样的东西:

So if the current year is 2017 i want something like this:

int currentyear = Calendar.getInstance().get(Calendar.YEAR);
int oldestDbYear = 2014;
List<SomeEntity> listToReturn = new LinkedList<SomeEntity>();
//the method getProperties is a custom method to get properties from a file
String url = getProperties("application.properties", "spring.datasource.url");
props.setProperty("user", getProperties("application.properties","spring.datasource.username"));
props.setProperty("password", getProperties("application.properties","spring.datasource.password"));
for (int i = currentYear, i>oldestDbYear, i--) {
//this is the connection that must be used by autowiredRepo Repository, but i don't know how to do this.
//So the repository uses different connection for every year.
Connection conn = getConnection(url+year,props);
List<SomeEntity> list_of_specific_year = autowiredRepo.findByName(name);
conn.close;
listToReturn.addAll(list_of_specific_year);
}
return listToReturn;

希望一切顺利

推荐答案

这里最适合您需求的是Spring的AbstractRoutingDataSource.您确实需要定义多个数据源，但只需要一个存储库.在这里，多个数据源不是问题，因为总有一种方法可以在运行时以编程方式创建DataSource bean并在应用程序上下文中注册它们.

The thing that is probably most suitable to your needs here is Spring's AbstractRoutingDataSource. You do need to define multiple DataSources but you will only need a single repository. Multiple data sources is not an issue here as there is always a way to create the DataSource beans programatically at run time and register them with the application context.

它的工作原理是您在创建AbstractRoutingDataSource @Bean时在@Configuration类中基本上注册了一个Map<Object, DataSource>，在这种情况下，查找键将是年份.

How it works is you basically register a Map<Object, DataSource> inside your @Configuration class when creating your AbstractRoutingDataSource @Bean and in this case the lookup key would be the year.

然后，您需要创建一个实现AbstractRoutingDataSource的类并实现determineCurrentLookupKey()方法.每次进行数据库调用时，都会在当前上下文中调用此方法以查找应返回DataSource的位置.在您的情况下，听起来好像您只是想在URL中以@PathVariable作为年份，然后在实现determineCurrentLookupKey()时从URL中获取@PathVariable(例如，在控制器中，您具有).

Then you need create a class that implements AbstractRoutingDataSource and implement the determineCurrentLookupKey() method. Anytime a database call is made, this method is called in the current context to lookup which DataSource should be returned. In your case it sounds like you simply want to have the year as a @PathVariable in the URL and then as the implementation of determineCurrentLookupKey() grab that @PathVariable out of the URL (e.g in your controller you have mappings like @GetMapping("/{year}/foo/bar/baz")).

    HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder
            .getRequestAttributes()).getRequest();

    HashMap templateVariables =
 (HashMap)request.getAttribute(HandlerMapping.URI_TEMPLATE_VARIABLES_ATTRIBUTE);

    return templateVariables.get("year");

我为产品编写测试工具时使用了这种方法，该产品中有许多实例在多个不同的服务器上运行，并且我希望从我的@Controller中获得一个统一的编程模型，但仍然希望它能够为正确的数据库打上基础.网址中的服务器/部署组合.像魅力一样工作.

I used this approach when writing a testing tool for a product where there were many instances running on multiple different servers and I wanted a unified programming model from my @Controllers but still wanted it to be hitting the right database for the server/deployment combination in the url. Worked like a charm.

如果您使用的是Hibernate，则缺点是所有连接都将通过单个SessionFactory，这意味着您无法充分利用Hibernate的第二级缓存，但我想这取决于您的需求.

The drawback if you are using Hibernate is that all connections will go through a single SessionFactory which will mean you can't take advantage of Hibernate's 2nd level caching as I understand it, but I guess that depends on your needs.

这篇关于Spring Boot-动态更改连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！