可以让H2在内存中数据库中自动创建模式吗?

(I've already seen the H2 database In memory - Init schema via Spring/Hibernate question; it is not applicable here.)

(通过Spring/Hibernate问题，我已经在内存- Init模式中看到了H2数据库;这里不适用)

I'd like to know if there's a setting in H2 that will allow me to auto-create a schema upon connecting to it. If it helps, I'm only interested in the in-memory case.

我想知道H2中的设置是否允许我在连接到它时自动创建模式。如果有帮助，我只对内存中的情况感兴趣。

H2 supports various semicolon-separated modifiers at the end of the URL, but I didn't find one for automatically creating a schema. Is there such a feature?

H2在URL的末尾支持各种分号分隔的修饰符，但是我没有找到自动创建模式的修饰符。有这样的特点吗?

4 个解决方案

#1

118

Yes, H2 supports executing SQL statements when connecting. You could run a script, or just a statement or two:

是的，H2支持在连接时执行SQL语句。你可以运行一个脚本，或者只是一两句话:

String url = "jdbc:h2:mem:test;" + 
             "INIT=CREATE SCHEMA IF NOT EXISTS TEST"
String url = "jdbc:h2:mem:test;" + 
             "INIT=CREATE SCHEMA IF NOT EXISTS TEST\\;" + 
                  "SET SCHEMA TEST";
String url = "jdbc:h2:mem;" + 
             "INIT=RUNSCRIPT FROM '~/create.sql'\\;" + 
                  "RUNSCRIPT FROM '~/populate.sql'";

Please note the double backslash (\\) is only required within Java. The backslash(es) before ; within the INIT is required.

请注意，只有在Java中才需要双反斜杠(\\ \)。反斜杠(es);在INIT中是必需的。

#2

"By default, when an application calls DriverManager.getConnection(url, ...) and the database specified in the URL does not yet exist, a new (empty) database is created."—H2 Database.

默认情况下，当应用程序调用DriverManager时。当url中指定的数据库不存在时，将创建一个新的(空)数据库。“h2数据库。

Addendum: @Thomas Mueller shows how to Execute SQL on Connection, but I sometimes just create and populate in the code, as suggested below.

附录:@Thomas Mueller展示了如何在连接上执行SQL，但有时我只是在代码中创建和填充，如下所示。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/** @see http://*.com/questions/5225700 */
public class H2MemTest {

    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
        Statement st = conn.createStatement();
        st.execute("create table customer(id integer, name varchar(10))");
        st.execute("insert into customer values (1, 'Thomas')");
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select name from customer");
        while (rset.next()) {
            String name = rset.getString(1);
            System.out.println(name);
        }
    }
}

#3

If you are using spring with application.yml the following will work for you

如果您正在使用spring与应用程序。yml:以下内容对你有效

spring: datasource: url: jdbc:h2:mem:mydb;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL;INIT=CREATE SCHEMA IF NOT EXISTS calendar

spring: datasource: url: jdbc:h2:mem:mydb;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL

#4

What Thomas has written is correct, in addition to that, if you want to initialize multiple schemas you can use the following. Note there is a \\; separating the two create statements.

托马斯所写的内容是正确的，除此之外，如果您想初始化多个模式，您可以使用以下内容。注意有一个\\;分离两个create语句。

    EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
                    .setType(EmbeddedDatabaseType.H2)
                    .setName("testDb;DB_CLOSE_ON_EXIT=FALSE;MODE=Oracle;INIT=create " +
                            "schema if not exists " +
                            "schema_a\\;create schema if not exists schema_b;" +
                            "DB_CLOSE_DELAY=-1;")
                    .addScript("sql/provPlan/createTable.sql")
                    .addScript("sql/provPlan/insertData.sql")
                    .addScript("sql/provPlan/insertSpecRel.sql")
                    .build();

ref : http://www.h2database.com/html/features.html#execute_sql_on_connection

裁判:http://www.h2database.com/html/features.html # execute_sql_on_connection

#1

118