Can I have H2 autocreate a schema in an in-memory database

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[java] Can I have H2 autocreate a schema in an in-memory database?

Examples related to java

Examples related to sql

Examples related to database

Examples related to h2