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
– gipinani

2014-04-06 15:21:52 +00:00
Commented Apr 6, 2014 at 15:21 — gipinani
– gipinani, Commented Apr 6, 2014 at 15:21
I need to populate db.

Flavio
– Flavio

2014-04-06 22:10:34 +00:00
Commented Apr 6, 2014 at 22:10 — Flavio
– Flavio, Commented Apr 6, 2014 at 22:10

Jean-Philippe Bond · Accepted Answer · 2014-04-06 16:19:05Z

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 6, 2014 at 16:19

answered Apr 6, 2014 at 15:59

Jean-Philippe Bond

10.9k4 gold badges37 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Flavio Over a year ago

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]

Jean-Philippe Bond Over a year ago

It seem that the database was already running or something. try to kill the process.

Flavio Over a year ago

I killed it. Now OK. But where I should add jdbc:h2:~/myDB;INIT=runscript from '~/resource/db/data.sql'? To hibernate.cfg.xml?

Jean-Philippe Bond Over a year ago

<property name="hibernate.connection.url">jdbc:h2:~/myDB;INIT=runscript from '~/resource/db/data.sql'</property>

Vadzim Over a year ago

See stackoverflow.com/questions/4490138/…

|

Bassem Reda Zohdy · Accepted Answer · 2014-04-06 15:49:54Z

4

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

answered Apr 6, 2014 at 15:49

Bassem Reda Zohdy

13k3 gold badges37 silver badges40 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related