使用Spring JDBC创建多个数据库或模式

i'm currently working on a Spring MVC project where i need multiple databases, one for each person that signs up(i know it's gonna be heavy but the number is limited). So after a person logs in, he should have access only to his database, but how do i do that with Spring-JDBC ? ( i'm also using Spring Security for authentification) and how do i create a database after registration ? Using JDBC Template or something else ?

我目前正在开发一个Spring MVC项目,我需要多个数据库,每个人注册一个(我知道它会很重但数量有限)。因此,在一个人登录后,他应该只能访问他的数据库,但是我如何使用Spring-JDBC呢? (我也使用Spring Security进行身份验证)以及如何在注册后创建数据库?使用JDBC模板或其他?

PS : I was working with Spring JPA Hibernate and was forced to change to Spring-JDBC because i couldn't find a way to do this that way.

PS:我正在使用Spring JPA Hibernate并*改为Spring-JDBC,因为我找不到这样做的方法。

1 个解决方案

#1

Hibernate supports this approach out of the box. It's called multi-tenancy. Check out the docs here:

Hibernate支持这种开箱即用的方法。它被称为多租户。看看这里的文档:

http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html

But on it's own this is not going to get you where you want to go. You are most certainly going to need another database schema in addition to the per customer one to at the very least store the information about each customers database connection.

但就它本身而言,这并不能让你到达你想去的地方。除了每个客户之外,您肯定还需要另一个数据库模式,以至少存储有关每个客户数据库连接的信息。

You may want this schema to make use of hibernate entities, in which you'll have another Session Factory, or you may just want to call it through a JDBC template.

您可能希望此模式使用hibernate实体,您将在其中拥有另一个Session Factory,或者您可能只想通过JDBC模板调用它。

Either way, the creation of a database schema in sql is a matter of a few statements. You just have to be sure that the credentials you log in with have the appropriate permissions In postgres it's something like this, I'm sure it's similar in mysql:

无论哪种方式,在sql中创建数据库模式都是几个语句的问题。您只需要确保您登录的凭据具有适当的权限在postgres中它是这样的,我确信它在mysql中类似:

CREATE USER circl WITH PASSWORD 'circl';
CREATE DATABASE circl;
GRANT ALL PRIVILEGES ON DATABASE circl_test to circl;

You can run a script that looks like this in the spring jdbc template. Spring has a utility class that will do this for you as long as you don't expect a results set back: org.springframework.test.jdbc.JdbcTestUtils.

您可以在spring jdbc模板中运行如下所示的脚本。 Spring有一个实用程序类,只要你不期望得到结果就可以为你做这个:org.springframework.test.jdbc.JdbcTestUtils。

Or you can even do it through JPA, following the instructions here:

或者您甚至可以按照此处的说明通过JPA执行此操作:

How can I execute a native SQL script in JPA/Hibernate?

如何在JPA / Hibernate中执行本机SQL脚本?

#1