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?

Answer 1

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.

Answer 2

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