Spring Boot：如何使用多个模式并动态选择在运行时为每个请求使用哪个模式 [英] Spring Boot: How to use multiple schemas and dynamically choose which one to use for every request at runtime

问题描述

前提：
我选择这样做是因为我最终可能会有几千个模式，每个模式都有（其中包括）1个表，有几百万个条目。另一种方法是在一个模式中拥有（以及其他）一个表，其中包含几十亿个条目。

详细说明这个问题的最佳方法是提供一个简单的例子。请考虑以下事项：

User.java

  @Entity（name =user）
公共类用户{
 @Id 
 @GeneratedValue 
 @Column（name =id）
 private Long id; 
 
 @Column（name =username）
 private String username; 
 
 // getter and setters ... 
} 
  

UserDao.java

  @Repository 
 public interface UserDao扩展CrudRepository< User，Long> {} 
  

UserService.java

  public interface UserService {
 User getUser（Long id）; 
} 
  

UserServiceBean.java

  @Transactional 
 @Service 
 public class UserServiceBean实现UserService {
 @Autowired 
 private UserDao dao; 
 
 @Override 
 public User getUser（Long id）{
 return dao.findOne（id）; 
} 
} 
  

UserController.java

  @RestController 
 public class UserController {
 
 @Autowired 
 private UserService userService; 
 
 @RequestMapping（
 value =/ api / users / {id}，
 method = RequestMethod.GET，
 produce = MediaType.APPLICATION_JSON_VALUE）
 public ResponseEntity< User> getUser（
 @PathVariable（id）long id）{
 
 User user = userService.getUser（id）; 
返回新的ResponseEntity< User>（用户，HttpStatus.OK）; 
} 
} 
  

我想扩展到以下功能：供应URL中的另一个ID，用于从不同的表返回用户数据。

UserController.java

  ... 
 @RequestMapping（
 value =/ api / users / {id}，
 method = RequestMethod.GET，
产生= MediaType.APPLICATION_JSON_VALUE）
 public ResponseEntity< User> getUser（
 @PathVariable（id）Long id，
 @RequestParam（value =tlbid）long tblId）{
 
 User user = userService.getUser（id， tblId）; 
返回新的ResponseEntity< User>（用户，HttpStatus.OK）; 
} 
  

现在，UserService会将该ID解码为可能对Spring有用的内容为了从不同的表中获取数据。

UserServiceBean.java

  ... 
 public User getUser（Long id，Long tblId）{
 Object o = doMagic（tblId）; 
 //应该是什么，我怎么能用它？ 
} 
  

所有表都具有相同的结构和名称，但条目不同。这些表必须位于不同的数据库中，或者位于同一数据库中，但位于不同的模式上。

我想知道：

a）我如何拥有一个数据库连接并为每个请求指定不同的模式。

b）如何创建新的数据库连接时必要的（我会保留它们用于进一步的请求），并指定每次请求应该在哪个连接上。

c）我的前提是错误的并且有数十亿条目在一个表和高并发性并没有显着降低查询速度。

解决方案

如果您使用的是hibernate实体类，那么你可以使用不同的模式用于相同的数据源，前提是其他模式可供Datasource中映射的特定用户访问。

您可以使用 schema 您的实体类中的表注释的属性。使用以下语法来使用不同的架构

@Table（name =TABLE_NAME，schema =SCHEMA2）

Premise: I chose to do this because I might end up having a few thousand schemas, each having (amongst others) 1 table with a few million entries. The alternative was having (amongst others) one table with a few billion entries in one schema.

The best way to elaborate on this question is providing a simple example. Consider the following:

User.java

@Entity(name = "user")
public class User {
    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;

    @Column(name = "username")
    private String username;

    // getters and setters...
}

UserDao.java

@Repository
public interface UserDao extends CrudRepository<User, Long> {}

UserService.java

public interface UserService {
    User getUser(Long id);
}

UserServiceBean.java

@Transactional
@Service
public class UserServiceBean implements UserService {
    @Autowired
    private UserDao dao;

    @Override
    public User getUser(Long id) {
        return dao.findOne(id);
    }
}

UserController.java

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping(
            value = "/api/users/{id}",
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<User> getUser(
            @PathVariable("id") Long id) {

        User user = userService.getUser(id);
        return new ResponseEntity<User>(user, HttpStatus.OK);
    }
}

I would like to extend to the following functionality: supplying another ID in the URL in order to return user data from a different table.

UserController.java

...
@RequestMapping(
            value = "/api/users/{id}",
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<User> getUser(
            @PathVariable("id") Long id,
            @RequestParam(value = "tlbid") Long tblId) {

        User user = userService.getUser(id, tblId);
        return new ResponseEntity<User>(user, HttpStatus.OK);
    }

Now the UserService will decode that ID into something that could be useful for spring in order to get the data from a different table.

UserServiceBean.java

    ...
    public User getUser(Long id, Long tblId) {
        Object o = doMagic(tblId);
        // What should 'o' be and how could I use this?
    }

All the tables have the same structure and names but different entries. The tables have to be on a different database, or in the same database but on a different schema.

I would like to know either:

a) How can I have one database connection and specify a different schema for every request.

b) How to create new database connections when necessary (I would maintain them for further requests), and specify on which connection should the request be made each time.

c) My premises are wrong and having billions of entries in a table and high concurrency does not significantly slow down query speeds.

解决方案

In case if you are using hibernate entity class then you can always use different schemas for same datasource provided that the other schemas are accessible for the particular user mapped in Datasource.

you can use schema attribute of Table annotation in your entity class. Use the below syntax for using different schema

@Table(name="TABLE_NAME",schema="SCHEMA2")

这篇关于Spring Boot：如何使用多个模式并动态选择在运行时为每个请求使用哪个模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！