Java 如何在 localhost:8082 查看我的 h2 数据库中的所有表？

How to see all tables in my h2 database at localhost:8082?

提问by Volodymyr Levytskyi

I use JDBC and created h2 database called usaDB from sql script. Then I filled all tables with jdbc.

我使用 JDBC 并从 sql 脚本创建了名为 usaDB 的 h2 数据库。然后我用 jdbc 填充了所有表。

The problem is that after I connect to usaDB at localhost:8082 I cannot see on the left tree my tables. There is only INFORMATION_SCHEMA database and rootUserwhich I specified creating usaDB.

问题是，在我通过 localhost:8082 连接到 usaDB 后，我在左侧的树上看不到我的表。只有 INFORMATION_SCHEMA 数据库rootUser，我指定创建 usaDB。

How to view the content of tables in my h2 database?

如何查看我的 h2 数据库中表的内容？

I tried query SELECT * FROM INFORMATION_SCHEMA.TABLES.

我试过查询SELECT * FROM INFORMATION_SCHEMA.TABLES。

But it returned many table names except those I created. My snapshot:

但它返回了许多表名，除了我创建的那些。我的快照：

回答by rajeesh

Version of jar file and installed h2 database should be same.

jar 文件的版本和安装的 h2 数据库应该相同。

回答by Hoàng Long

I have met exactly this problem.

我遇到了这个问题。

From what you describe, I suppose that you connect your jdbc with the "real" h2 server, but you are connecting on web application to database by the wrong mode (embedded-in-memory mode, aka h2mem). It means that h2 will create a new database in-memory, instead of using your true database stored elsewhere.

根据您的描述，我假设您将 jdbc 与“真正的”h2 服务器连接，但是您以错误的模式（嵌入式内存模式，又名h2mem）将 Web 应用程序连接到数据库。这意味着 h2 将在内存中创建一个新数据库，而不是使用存储在其他地方的真实数据库。

Please make sure that when you connect to this database, you use the mode Generic H2 (Server), NOTGeneric H2 (Embedded). You can refer to the picture below.

请确保当您连接到此数据库时，您使用的是Generic H2 (Server)模式，而不是Generic H2 (Embedded) 模式。你可以参考下图。

回答by Le_Enot

I had the same issue and the answer seems to be really stupid: when you type your database name you shouldn't add ".h2.db" suffix, for example, if you have db file "D:\somebase.h2.db" your connection string should be like "jdbc:h2:file:/D:/somebase". In other way jdbc creates new empty database file named "somebase.h2.db.h2.db" and you see what you see: only system tables.

我有同样的问题，答案似乎很愚蠢：当你输入你的数据库名称时，你不应该添加“ .h2.db”后缀，例如，如果你有 db 文件“ D:\somebase.h2.db" 你的连接字符串应该像 " jdbc:h2:file:/D:/somebase"。以其他方式 jdbc 创建名为“ somebase.h2.db.h2.db”的新空数据库文件，您会看到您所看到的：只有系统表。

回答by Beezer

This problem drove me around the twist and besides this page I read many (many!) others until I solved it.
My Use Case was to see how a SpringBatch project created in STS using :: Spring Boot :: (v1.3.1.RELEASE)was going to behave with the H2 database; to do the latter, I needed to be able to get the H2 console running as well to query the DB results of the batch run.

这个问题驱使我绕过这个问题，除了这个页面之外，我还阅读了很多（很多！）其他的，直到我解决了它。
我的用例是查看使用:: Spring Boot :: (v1.3.1.RELEASE)在 STS 中创建的 SpringBatch 项目将如何与 H2 数据库一起运行；要执行后者，我还需要能够运行 H2 控制台以查询批处理运行的数据库结果。

This is what I did and found out:

这就是我所做的并发现：

1. Created an Web project in STS using Spring Boot:

   • Added the following to the pom.xml of the latter:
   • Added a Spring configuration file as follows to the project: This solves the Web project deficiencies in STS. If you run the project now, you can access the H2 console as follows: http://localhost:8080/console

2. Now create a SpringBatch project in STS as follows (the alternative method creates a different template missing most of the classes for persisting data. This method creates 2 projects: one Complete, and the other an initial. Use the Completein the following.):

   • The SpringBatch project created with STS uses an in memory H2 database that it CLOSES once the application run ends; once you run it, you can see this in the logging output.
   • So what we need is to create a new DataSource that overrides the default that ships with the project (if you are interested, just have a look at the log messages and you will see that it uses a default datasource...this is created from: o.s.j.d.e.EmbeddedDatabaseFactorywith the following parameters:
     Starting embedded database: url='jdbc:hsqldb:mem:testdb', username='sa')
   • So, it starts an in memory, and then closes it. You have no chance of seeing the data with the H2 console; it has come and gone.
   • So, create a DataSource as follows:
   • You can of course use a properties file to map the parameters, and profiles for different DataSourceinstances...but I digress.
   • Now, make sure you set the bit that the red arrow in the picture is pointing to, to a location on your computer where a file can be persisted.
   • Running the SpringBatch (Completeproject) you should now have a db file in that location after it runs (persisting Person data)
   • Run the Web project you configured previously in these steps, and you WILL :=) see your data, and all the Batch job and step run data (et voila!): Painful but rewarding. Hope it helps you to really BOOTSTRAP :=)

1. 使用 Spring Boot 在 STS 中创建了一个 Web 项目：

   • 在后者的 pom.xml 中添加了以下内容：
   • 项目中添加了如下Spring配置文件： 解决了Web项目在STS中的不足。如果现在运行项目，可以通过如下方式访问H2控制台：http://localhost:8080/console

2. 现在在 STS 中创建一个 SpringBatch 项目，如下所示（另一种方法创建了一个不同的模板，该模板缺少大多数用于持久化数据的类。该方法创建了 2 个项目：一个Complete，另一个是initial。在下面使用Complete。）：

   • 使用 STS 创建的 SpringBatch 项目使用内存中的 H2 数据库，一旦应用程序运行结束，它就会关闭；运行它后，您可以在日志输出中看到这一点。
   • 所以我们需要的是创建一个新的数据源来覆盖项目附带的默认值（如果您有兴趣，只需查看日志消息，您将看到它使用默认数据源......这是从: osjde EmbeddedDatabaseFactory带有以下参数：
     启动嵌入式数据库：url='jdbc:hsqldb:mem:testdb', username='sa')
   • 所以，它在内存中启动，然后关闭它。你没有机会用 H2 控制台看到数据；它来来去去。
   • 因此，创建一个 DataSource 如下：
   • 您当然可以使用属性文件来映射不同DataSource实例的参数和配置文件……但我离题了。
   • 现在，确保将图片中红色箭头指向的位设置为计算机上可以保留文件的位置。
   • 运行 SpringBatch（完整项目），运行后您现在应该在该位置有一个 db 文件（持久化个人数据）
   • 运行您之前在这些步骤中配置的 Web 项目，您将 :=) 看到您的数据，以及所有批处理作业和步骤运行数据（等等！）： 痛苦但有益。希望它可以帮助您真正启动 :=)

回答by Paul Vargas

You can use the SHOWcommand:

您可以使用以下SHOW命令：

Using this command, you can lists the schemas, tables, or the columns of a table. e.g.:

使用此命令，您可以列出模式、表或表的列。例如：

SHOW TABLES

回答by Zhenya

In my case the issue was caused by the fact that I didn't set the h2 username, password in java. Unfortunatelly, Spring didn't display any errors to me, so it was not easy to figure out. Adding this lines to dataSource method helped me fix the issue:

就我而言，问题是由于我没有在 java 中设置 h2 用户名和密码造成的。不幸的是，Spring 没有向我显示任何错误，因此不容易弄清楚。将此行添加到 dataSource 方法帮助我解决了这个问题：

dataSource.setUsername("sa");
dataSource.setPassword("");

Also, I should have specified the schema when creating tables in schema.sql

另外，我应该在 schema.sql 中创建表时指定架构

回答by Well Smith

Selecting Generic H2 (Server) solved for me. We tempted to use default Generic H2 (Embedded) which is wrong.

选择 Generic H2 (Server) 为我解决了。我们试图使用默认的 Generic H2（嵌入式），这是错误的。

回答by Memin

It is an old question, but I came across the same problem. Eventually I found out that the default JDBC URL is pointing a test server rather than my application. After correcting it, I could access the right DB.

这是一个老问题，但我遇到了同样的问题。最终我发现默认的 JDBC URL 指向一个测试服务器而不是我的应用程序。更正后，我可以访问正确的数据库。

I tried with both Generic H2 (Embedded) and the Generic H2 (Server) options, both worked as long as the JDBC URL: is provided correctly.

我尝试了 Generic H2 (Embedded) 和 Generic H2 (Server) 选项，只要JDBC URL: 正确提供，两者都可以工作。

回答by Gandhi

If in case you have created and populated H2 database table using maven dependency in spring boot, then please do change the JDBC URL as jdbc:h2:mem:testdbwhile connecting to H2 using web console.

如果您在 Spring Boot 中使用 maven 依赖项创建并填充了 H2 数据库表，那么请在jdbc:h2:mem:testdb使用 Web 控制台连接到 H2 时更改 JDBC URL 。

回答by Aleksander Stelmaczonek

For the people who are using H2 in embedded(persistent mode) and want to "connect" to it from IntelliJ(other IDEs probably apply too).

对于在嵌入式（持久模式）中使用 H2 并希望从 IntelliJ（其他 IDE 可能也适用）“连接”到它的人。

1. Using for example jdbc url as follows: jdbc:h2:./database.h2
2. Note, that H2 does not allow implicit relativepaths, and requires adding explicit ./
3. Relativepaths are relative to current workdir
4. When you run your application, your workdir is most likely set to your project's root dir
5. On the other hand, IDE's workdir is most likely notyour project's root
6. Hence, in IDE when "connecting" to your database you need to use absolute path like: jdbc:h2:/Users/me/projects/MyAwesomeProject/database.h2
7. For some reason IntelliJ by default also adds ;MV_STORE=false. It disables MVStore engine which in fact is currently used by default in H2.
8. So make sure that both your application and your IDE use the same store engine, as MVStore and PageStore have different file loyouts.
9. Note that you cannot "connect" to your database if your application is using it because of locking. The other way around applies too.

1. 使用例如 jdbc url 如下： jdbc:h2:./database.h2
2. 请注意，H2 不允许隐式相对路径，并且需要添加显式./
3. 相对路径是相对于当前工作目录的
4. 运行应用程序时，您的工作目录很可能设置为项目的根目录
5. 另一方面，IDE 的工作目录很可能不是您项目的根目录
6. 因此，在 IDE 中“连接”到您的数据库时，您需要使用绝对路径，例如： jdbc:h2:/Users/me/projects/MyAwesomeProject/database.h2
7. 出于某种原因，默认情况下 IntelliJ 也会添加;MV_STORE=false. 它禁用 MVStore 引擎，实际上目前在 H2 中默认使用该引擎。
8. 因此，请确保您的应用程序和 IDE 使用相同的存储引擎，因为 MVStore 和 PageStore 具有不同的文件 loyouts。
9. 请注意，如果您的应用程序由于锁定而使用它，则您无法“连接”到您的数据库。反过来也适用。