Using in-memory db with Spring

[gm...@muitropolis.com]

Hi,

I'm new to H2 and had 2 questions that would help me get started with
a project I'm working on.

I'd like to use H2 as an embedded, in-memory database for keeping
track of data within my Java server app. I'm using the Spring
framework and was wondering if in general, people just initialize a
datasource in their Spring context files like the following:

<bean id="dataSource"

class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
<property name="driverClassName"> <value>org.h2.Driver</value></
property>
<property name="url"><value>jdbc:h2:mem:example</value></property>
<property name="username"><value>sa</value></property>
<property name="password"><value></value></property>
<property name="autoCommit"><value>false</value></property>
</bean>

Or do people also create any kind of Spring Bean to help manage the
instantiation and shutdown of the H2 server?

My second question is about how to initialize the database schema. In
HSQLDB, it seemed like in-memory databases still generated scripts
that would automatically initialize the database schema when it was
restarted. So schema was persistent while all contents were only
stored in memory. I haven't seen any mention of similar functionality
for H2, and have seen comments in posts that say they manually
recreate the schema in their code. Should I just create a schema
creation script and run that using the above datasource as a first
step?

TIA for any suggestions. I'm looking forward to getting to learn how
to use H2!

Gary

[Thomas Mueller]

Hi,

> I'm using the Spring framework

I didn't use the Spring framework so far so I can't help you here.

> My second question is about how to initialize the database schema. In
> HSQLDB, it seemed like in-memory databases still generated scripts
> that would automatically initialize the database schema when it was
> restarted.

There are different kinds of in-memory databases. In HSQLDB, by
default the data is kept fully in memory because the default table
type is 'in-memory table'. In H2 the default table type is 'persistent
table' (H2 also support 'in-memory table' however it is not exactly
the same as in HSQLDB).

> I haven't seen any mention of similar functionality
> for H2

See
http://www.h2database.com/html/features.html#connection_modes
http://www.h2database.com/html/features.html#database_url
http://www.h2database.com/html/features.html#memory_only_databases
http://www.h2database.com/html/grammar.html#setdefaulttabletype

> Should I just create a schema
> creation script and run that using the above datasource as a first
> step?

It depends if you want to use 'memory only databases'.
I suggest you read the docs first.

Regards,
Thomas

[Mingfai]

hi,

Attached at the bottom is the Spring beans I use to startup in memory H2. Given Spring is so popular, it might be a good idea to add a section in the official documentation to help ppl to use Spring.

Regards,
mingfai

                   
    <bean     id="org.h2.tools.Server"
            class="org.h2.tools.Server"
            scope="singleton"
            factory-method="createTcpServer"
            init-method="start"
            depends-on="org.h2.tools.Server-WebServer">
            <constructor-arg value="-tcp,-tcpAllowOthers,true,-tcpPort,9092"/>
    </bean>
    <bean     id="org.h2.tools.Server-WebServer"
            class="org.h2.tools.Server"
            scope="singleton"
            factory-method="createWebServer"
            init-method="start">
            <constructor-arg value="-web,-webAllowOthers,true,-webPort,8082"/>
    </bean>
    <bean id="H2DatabaseJDBCDriver" class="org.h2.Driver"
        scope="singleton" init-method="load" depends-on="org.h2.tools.Server" />

    <bean id="H2InMemoryDB"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource"
        depends-on="org.h2.tools.Server">
        <property name="driverClassName" value="org.h2.Driver" />
        <property name="url" value="jdbc:h2:mem:appdb1;DB_CLOSE_DELAY=-1" />
        <!-- ;TRACE_LEVEL_FILE=3;TRACE_LEVEL_SYSTEM_OUT=3 -->
        <property name="username" value="sa" />
        <property name="password" value="" />
    </bean>

    <bean id="H2InMemoryDBPool" class="org.apache.commons.pool.impl.GenericObjectPool">
        <!-- Two connections: InMemoryEntityManagerFactory and transactionManager -->
        <property name="minIdle" value="1"/>
        <property name="maxWait" value="10"/>
        <property name="maxActive" value="10"/>
        <property name="maxIdle" value="10"/>
        <property name="minEvictableIdleTimeMillis" value="300000"/>
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
    </bean>

    <bean id="H2InMemoryDBDSConnFactory" class="org.apache.commons.dbcp.DataSourceConnectionFactory">
        <constructor-arg><ref bean="H2InMemoryDB"/></constructor-arg>
    </bean>

    <bean id="H2InMemoryDBPoolableConnFactory" class="org.apache.commons.dbcp.PoolableConnectionFactory">
        <constructor-arg index="0"><ref bean="H2InMemoryDBDSConnFactory"/></constructor-arg>
        <constructor-arg index="1"><ref bean="H2InMemoryDBPool"/></constructor-arg>
        <constructor-arg index="2"><null/></constructor-arg>
        <constructor-arg index="3"><null/></constructor-arg>
        <constructor-arg index="4"><value>false</value></constructor-arg>
        <constructor-arg index="5"><value>true</value></constructor-arg>
    </bean>

    <bean id="pooledInMemoryDB" class="org.apache.commons.dbcp.PoolingDataSource" depends-on="H2InMemoryDBPoolableConnFactory">
        <constructor-arg><ref bean="H2InMemoryDBPool"/></constructor-arg>     
    </bean>   

[Gary Mui]

Hi Thomas,

Thanks for the reply and pointers to the docs. I have read through those
and understand the ways to instantiate the in-memory database. I am looking
for an instance where all data is contained in-memory (for the 'high
performance operations') and am able to start the instance of the database
using the named, in-memory datasource url (jdbc:h2:mem:example). When doing
this, I guess my question is will that database always be initialized with
no user schema? I understand that it will have no data, but are there ways
to persist the schema?

Actually, I just read through the HSQLDB documentation and saw that the
contents as well as the structure are written to the <dbname>.script file.
So if I'm looking for the fastest performance (for reads, inserts, and
updates) and I can reconstruct the contents each time the database is
started myself, would you recommend the in-memory database only and then on
each startup, run a script to create all tables, indexes, etc?

Also, for performance, if I'm looking for the fastest read/insert/update
operations and will need to transaction wrap updates/inserts and I'm not
concerned about persisting it to disk, is H2 better suited for that
application than HSQLDB?

Thanks so much for your advice and for all the work into H2 (and HSQLDB)!

Gary

[Gary Mui]

Thanks!  I will try this out tonight.

 

Gary

----- Original Message -----

From: Mingfai

To: h2-da...@googlegroups.com

Sent: Sunday, April 06, 2008 8:19 AM

Subject: Re: Using in-memory db with Spring

[Gary Mui]

Oh - also, the data being stored will grow to be pretty large. Probably
about 2 GB of data.

[Thomas Mueller]

Hi,

> Oh - also, the data being stored will grow to be pretty large. Probably
> about 2 GB of data.

I don't think you should use an in-memory database then. There is
quite a large overhead for in-memory databases. I suggest you try
using regular persistent databases first, and maybe create some of the
(smaller) tables in memory using CREATE MEMORY TABLE for performance.

Regards,
Thomas

[Mingfai]

On Wed, Oct 15, 2008 at 10:45 PM, TiKi <pratik....@gmail.com> wrote:

Hi mingfai,

   I am a newbie and want to use H2 in Server Mode along with spring.
If i where using these for not in memory database and in server mode,
do i just have to change the url to "jdbc:h2:~/
perfomanceevaluation;DB_CLOSE_DELAY=-1"? and how to make sure that
server is shutdown when the H2 database is running in Server Mode?

Regards,
TiKi

hi Tiki,

u'd better ask question in the the h2-database group.

it's not the first email i got about H2 Spring. In case any one has interest or research about how to use H2 with Spring and with c3p0 connection pool, you may consider the following configuration that is cleaner than the version I posted on this group in Apr.

    <bean id="org.h2.tools.Server" class="org.h2.tools.Server" scope="singleton" factory-method="createTcpServer"
          init-method="start" depends-on="org.h2.tools.Server-WebServer">
        <constructor-arg value="-tcp,-tcpAllowOthers,true,-tcpPort,9092"/>
    </bean>

    <bean id="org.h2.tools.Server-WebServer" class="org.h2.tools.Server" scope="singleton" factory-method="createWebServer" init-method="start">
        <constructor-arg value="-web,-webAllowOthers,true,-webPort,8082"/>
    </bean>

    <!-- notice that loading the Driver as a bean is unnecessary is most cases! u could safely remove this and the depends-on in the next bean -->

    <bean id="H2DatabaseJDBCDriver" class="org.h2.Driver" scope="singleton" init-method="load" depends-on="org.h2.tools.Server"/>

    <bean id="h2databasePoolingDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" depends-on="H2DatabaseJDBCDriver" destroy-method="close">
        <property name="driverClass" value="org.h2.Driver"/>
        <property name="jdbcUrl" value="jdbc:h2:mem:appdb1;DB_CLOSE_DELAY=-1;TRACE_LEVEL_FILE=4"/>
        <!-- ;TRACE_LEVEL_FILE=3;TRACE_LEVEL_SYSTEM_OUT=3 -->
        <property name="user" value="sa"/>

        <property name="password" value=""/>

        <property name="maxPoolSize" value="20"/>
        <property name="minPoolSize" value="5"/>
        <property name="acquireIncrement" value="5"/>

        <property name="maxConnectionAge" value="28800"/>
        <property name="preferredTestQuery" value="SELECT 1"/>
    </bean>


I somehow doubt if it is necessary to use connection pool for inmemory mode. but i suppose it won't be very harmful to do so.

regards,
mingfai