(I've already seen the H2 database In memory - Init schema via Spring/Hibernate question; it is not applicable here.)
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 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?
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 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
"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);
}
}
}
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
Source: Stackoverflow.com