CODEWITHSUNDEEP
javadatabase-designspring-boot
Alin Valentin
Alin Valentin

Reputation: 547

Spring Boot: How to use multiple schemas and dynamically choose which one to use for every request at runtime

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.

Upvotes: 6

Views: 9175

Answers (2)

GaZ
GaZ

Reputation: 2406

It sounds like you're describing a multi-tenant solution. See the Hibernate documentation for a longer description and a few options for how you could partition your data.

Note: we are trying to implement the schema-based multi-tenant approach at the moment :)

Upvotes: 5

ashishraaj
ashishraaj

Reputation: 761

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")

Upvotes: 0

Related Questions