How to Use H2 Database in Memory

[Bobby]

I want to use h2 as In memory Data base using with Spring.

I have tried using it with H2 data base as my local database and it
wotks fine, my requirement is that my Unit test should not connect to
the local h2 it shoudl connect to in memory data base and the tables
that I have created on my Local h2 shoudl be there in memory ( along
with some sample Data)

How I can do it

I see that I will have to change my url setting as follows -
driverClassName=org.h2.Driver
url=jdbc:h2:mem:test_mem

( the url I was uing before was url=jdbc:h2:~/test )

Now how do I create this test_mem ? so that my Junit can find the
tables.

becoz now when I run my Junit I see errors like -
WARN : JDBCExceptionReporter - SQL Error: 42102, SQLState: 42S02
ERROR: JDBCExceptionReporter - Table CUSTOMER not found; SQL
statement:

and that I understand becoz there is no table CUSTOMER ( nor data ) .

I wana knwo how can I do it ?' if some one have a sample code or step
by step documentaion , will be very helpfull I am new to spring as
well hibernate and H2.,


please if some one can help.

[Brish]

From the online docs at:
http://www.h2database.com/html/features.html

Memory-Only Databases

For certain use cases (for example: rapid prototyping, testing, high
performance operations, read-only databases), it may not be required
to persist (changes to) the data at all. This database supports the
memory-only mode, where the data is not persisted.

In some cases, only one connection to a memory-only database is
required. This means the database to be opened is private. In this
case, the database URL is jdbc:h2:mem: Opening two connections within
the same virtual machine means opening two different (private)
databases.

Sometimes multiple connections to the same memory-only database are
required. In this case, the database URL must include a name. Example:
jdbc:h2:mem:db1 . Accessing the same database in this way only works
within the same virtual machine and class loader environment.

It is also possible to access a memory-only database remotely (or from
multiple processes in the same machine) using TCP/IP or SSL/TLS. An
example database URL is: jdbc:h2:tcp://localhost/mem:db1 (using
private database remotely is also possible).

By default, when the last connection to a in-memory database is
closed, the contents are lost. This can be disabled by
adding ;DB_CLOSE_DELAY=-1 to the database URL. That means to keep the
contents of an in-memory database as long as the virtual machine is
alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

Brish

[elie684]

Hello Bobby, i have used H2 in memory,

When you make the connection using the url you specified, your
database is created in memory,
but this db is empty an do not contain any personnal table, thats why
your are having errors
so what you have to do is just create your tables by an sql
statement,and you can use an sql script file to init your db
this is a sample code that could help :

// load the driver class
Class.forName("org.h2.Driver");
Connection con = DriverManager.getConnection("jdbc:h2:mem:mytest",
"sa", "");
// here you create the table
String s = "CREATE TABLE test (id INTEGER, name char(50), last_name
char(50), age INTEGER)";
Statement sst = con.createStatement();
sst.executeUpdate(s);

Elie