How to populate H2 in-memory DB from a file (populate.sql)?

Question

I want to add H2 in-memory db to my Spring MVC application. For ORM I’m using Hibernate. Here is my configs:

[hibernate.cfg.xml]

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">org.h2.Driver</property>
        <property name="hibernate.connection.url">jdbc:h2:~/myDB</property>
        <property name="hibernate.dialect">org.hibernate.dialect.H2Dialect</property>
        <property name="hibernate.connection.username">sa</property>
        <property name="hibernate.connection.password">qwerty</property>
        <property name="show_sql">true</property>
        <property name="hbm2ddl.auto">create</property>
        <mapping class="de.alf.h2.entity.Student"/>
    </session-factory>
</hibernate-configuration>

[data.xml]

<bean:beans xmlns:bean="http://www.springframework.org/schema/beans"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:tx="http://www.springframework.org/schema/tx"
            xmlns:ctx="http://www.springframework.org/schema/context"
            xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/tx
            http://www.springframework.org/schema/tx/spring-tx.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context.xsd">

    <tx:annotation-driven transaction-manager="transactionManager"/>
    <ctx:component-scan base-package="de.alf.h2.repository"/>
    <bean:bean id="template" class="org.springframework.orm.hibernate3.HibernateTemplate">
        <bean:property name="sessionFactory" ref="sessionFactory"/>
    </bean:bean>
    <bean:bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <bean:property name="configLocation" value="classpath:/hibernate/hibernate.cfg.xml"/>
    </bean:bean>
    <bean:bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <bean:property name="sessionFactory" ref="sessionFactory"/>
    </bean:bean>
</bean:beans>

[web.xml]

<web-app version="2.4"
    xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee 
    http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

    <servlet>
        <servlet-name>mvc-dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>/WEB-INF/mvc.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>mvc-dispatcher</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/data.xml</param-value>
    </context-param>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
</web-app>

I need to create db from a file (resource/db/data.sql).

CREATE TABLE student {
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR (250)
}

How to do this in my code?

Do you need to populate a db or to create a db? – gipinani Apr 06 '14 at 15:21 — gipinani, Apr 06 '14 at 15:21

Jean-Philippe Bond · Answer 1 · 2014-04-06T16:19:05.787

6

If hbm2ddl.auto is set to create, Hibernate will generate DDL from mapped classes during the SessionFactory creation. You can also add a file named import.sql to your classpath to import some data.

If you are using M2 for testing purpose and you want to do it by yourself, you can also use the H2 Execute SQL on Connection feature :

jdbc:h2:~/myDB;INIT=runscript from '~/resource/db/data.sql'

** Note that the path to data.sql might be differents.

edited Apr 06 '14 at 16:19

answered Apr 06 '14 at 15:59

Jean-Philippe Bond

10,089
3
34
60

I'm getting an exception now `Database may be already in use: "Locked by another process". Possible solutions: close all other connection(s); use the server mode [90020-176]` – Flavio Apr 06 '14 at 18:05
It seem that the database was already running or something. try to kill the process. – Jean-Philippe Bond Apr 06 '14 at 20:35
I killed it. Now OK. But where I should add `jdbc:h2:~/myDB;INIT=runscript from '~/resource/db/data.sql'`? To `hibernate.cfg.xml`? – Flavio Apr 06 '14 at 21:50
jdbc:h2:~/myDB;INIT=runscript from '~/resource/db/data.sql' – Jean-Philippe Bond Apr 06 '14 at 21:53
It didn't work. When my app has been started it hasn't any populated data. – Flavio Apr 06 '14 at 22:01
Even if I'm using `jdbc:h2:~/myDB;INIT=runscript from 'classpath:/h2/populate.sql'` – Flavio Apr 06 '14 at 22:09
Are you sure that the path is OK? – Jean-Philippe Bond Apr 06 '14 at 22:48
1

See http://stackoverflow.com/questions/4490138/problem-with-init-runscript-and-relative-paths – Vadzim Jan 20 '17 at 12:45

score 4 · Answer 2 · answered Apr 06 '14 at 15:49

you can use JDBC namespace xmlns:jdbc="http://www.springframework.org/schema/jdbc" then you can add embedded database as

<jdbc:embedded-database id="dataSource">
    <jdbc:script location="classpath:db/schema.sql"/>
    <jdbc:script location="classpath:db/test-data.sql"/>
</jdbc:embedded-database>

then use this data source in JDBCTemplate or in JPA with hibernate

How to populate H2 in-memory DB from a file (populate.sql)?

2 Answers2