一个JVM中的多个独立H2数据库 [英] Multiple independent H2 databases within one JVM

问题描述

是否可以在JVM中启动和关闭多个 H2 数据库？

我的目标是支持多租户给予每个用户/帐户自己的数据库。每个帐户的数据非常少。帐户之间的数据从不会一起访问，比较或分组;每个帐户完全独立于其他帐户。每个帐户只能每天或每月几次简单访问一次。因此，在单个数据库中存储数据的几个方面，以及一些严重的缺点。

所以我的想法是，当用户登录一个特定的帐户，该帐户的数据库已加载。当该用户注销或其网络应用会话（ Vaadin 应用）超时时，该帐户的数据库关闭，它的数据被刷新到存储，并且可能执行备份。

优点包括最大限度地减少在任何时候使用的内存量，用于缓存数据和索引，最小化锁定和其他争用，并允许平滑缩放。

我是新的H2，所以我不知道它的架构是否可以支持这一点。我要求拒绝或确认此功能，以及任何提示或警告。

解决方案

是的，这样做。每个数据库将包含自己的迷你环境，数据库之间没有可能的污染。

您可以使用基于用户ID或用户登录的jdbc url： / p>

• jdbc：h2：user1
  
• jdbc：h2：./ user1 in H2 1.4.x嵌入式模式


• dbc：h2：tcp：// localhost / user1 在tcp模式下

如果您的操作系统允许它：user1，user2等...或真正的登录名。

提示： strong>

• 使用服务器模式而不是嵌入式模式，允许来自多个会话/主机的同一用户多个连接


• 有一个模式迁移器（如 flyway ）来初始化每个新创建的数据库


• 确保您在应用程序的顶层管理名称冲突，并且可能将这些数据库和相应的登录信息存储在专用数据库中

警告：

• 不要使用连接池作为连接将难以重用


• 您必须确保服务器上未使用IFEXISTS = TRUE


• 避免在jdbc网址上使用tweaks，例如LOG = 0，UNDO_LOG = 0，etc ...


• 我不知道你是否会对你的操作系统或JVM有一个限制，可以打开多少个db文件。


• 我不知道这样的设置是否可以从手册页调整。我找不到一个。

请参阅 H2 manual 在网址参数疑问。

Is it possible to start up and shut down multiple H2 databases within a JVM?

My goal is to support multi-tenancy by giving each user/account their own database. Each account has very little data. Data between the accounts is never accessed together, compared, or grouped; each account is entirely separate from the others. Each account is only accessed briefly once a day or a few times a month. So there are few upsides to housing the data together in a single database, and some serious downsides.

So my idea is that when a user logs in for a particular account, that account’s database is loaded. When that user logs out, or their web app session (Vaadin app) times out, that account’s database is closed, it's data flushed to storage, and possibly a backup performed. This opening and closing would be happening for any number of databases in parallel.

Benefits include minimizing the amount of memory in use at any one time for caching data and indexes, minimizing locking and other contention, and allowing for smooth scaling.

I'm new to H2, so I'm not sure if its architecture can support this. I'm asking for a denial or confirmation of this capability, along with any tips or caveats.

解决方案

Yes it is possible to do so. Each database will contain its own mini environment, no possible pollution between databases.

You could for example use a jdbc url based on the user id or login from the user:

• jdbc:h2:user1 in H2 1.3.x embedded mode
• jdbc:h2:./user1 in H2 1.4.x embedded mode
• jdbc:h2:tcp://localhost/user1 in tcp mode

You can use any naming convention for the database name, provided your OS allows it: user1, user2, etc... or truly the name of the login.

Tips:

• use the server mode rather than the embedded mode, allowing for same user multiple connections from multiple sessions/hosts
• have a schema migrator (like flyway) to initialize each newly created db
• ensure you manage name collisions at the top level of your app, and possibly store these databases and corresponding logins in a dedicated database as well

Caveats:

• do not use a connection pool as connections will be difficult to reuse
• You must make sure IFEXISTS=TRUE is not used on the server
• avoid using tweaks on the jdbc url, like turning LOG=0, UNDO_LOG=0, etc...
• I do not know if you'll have a limitation from your OS or the JVM on how many db files could be opened like this.
• I do not know if such setting can be tweaked from the manual pages. I could not find one.

Please refer to H2 manual in doubts of url parameters.

这篇关于一个JVM中的多个独立H2数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！