我可以让 h2在内存数据库中自动创建一个模式吗？

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

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

H2支持 URL 末尾的各种分号分隔的修饰符，但是我没有找到一个用于自动创建模式的修饰符。有这种特色吗？

153197 次浏览
小开

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

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

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


/** @see http://stackoverflow.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);
}
}
}
小开
最佳答案

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

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.

小开

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

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

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. 

    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

小开

If you are using Spring Framework with application.yml and having trouble to make the test find the SQL file on the INIT property, you can use the classpath: notation.

For example, if you have a init.sql SQL file on the src/test/resources, just use:

url=jdbc:h2:~/test;INIT=RUNSCRIPT FROM 'classpath:init.sql';DB_CLOSE_DELAY=-1;
小开

If you are using spring and xml configuration, here is an example how this should be done:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close" depends-on="h2Server">
<property name="driverClassName" value="org.h2.Driver"/>
<property name="url"
value="jdbc:h2:mem:testdb;INIT=RUNSCRIPT FROM 'classpath:db/create_tables.sql'\;RUNSCRIPT FROM 'classpath:db/insert.sql';TRACE_LEVEL_FILE=4;TRACE_LEVEL_SYSTEM_OUT=3;"/>
<property name="username" value="sa"/>
<property name="password" value=""/>

提交答案