H2 Database ( In Memory Database)

H2 database is a RDBMS , IN Memory database. Its a java SQL database. Main Features of H2 database are :-

1. Very fast ,open source , JDBC Api
2. Can have both embedded and server modes
3. Browser based console application.
4. Small footprint of 1MB size jar file.
5. Commercial support is available.

Maximum Size of H2 Database :-

1. Database File Size Limit :- 4 TB or higher ( using default page size 2KB).
2. Maximum rows per table :- 2^64
3. Memory Requirements for IN memory database :- Larger Database needs more memory. As 1.1 version storage mechanism, minimum memory required for 12 GB database is 240 MB.

There are two modes of H2 database :-

1. Embedded Mode :- In this mode, application and database are in same Virtual Machine( VM )  . Application open database within same VM. It is the fastest and easiest connection mode. But in this mode database will be open in one VM only. Both persistent as well as IN memory database can be opened. NO Limit is there on number of database open currently and number of open connections.

H2 database in Embedded , persistent mode :-

bash-3.2$ pwd
/home/oracle/h2/bin
bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:~/amit
[Enter]   org.h2.Driver
Driver
[Enter]   sa
User      amit
[Enter]   Hide
Password  amit
Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> show tables;
TABLE_NAME | TABLE_SCHEMA
AA         | PUBLIC
AMIT       | PUBLIC
(2 rows, 23 ms)

H2 database in Embedded , Memory mode :-

bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:mem:
[Enter]   org.h2.Driver
Driver
[Enter]   sa
User      amit_mem
[Enter]   Hide
Password  amit_mem
Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> show tables;
TABLE_NAME | TABLE_SCHEMA
(0 rows, 221 ms)
sql>

2. Server Mode :-  In this mode an application open a database residing on another server. A server can be started on same or another virtual machine. Many applications can connect to same database at same time . Internally server process open database in Embedded mode. Server process is slower than embedded mode as data is transferred over TCP/IP.

H2 database in Server mode (persistent mode) :-

1. First start the server 

bash-3.2$ pwd
/home/oracle/h2/bin
bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Server
Web Console server running at http://10.10.7.11:8082 (only local connections)
Failed to start a browser to open the URL http://10.10.7.11:8082: Browser detection failed and system property h2.browser not set
TCP server running at tcp://10.10.7.11:9092 (only local connections)
PG server running at pg:// 10.10.7.11:5435 (only local connections)

2. open local connections to this server from another session :-

bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)

Exit with Ctrl+C

[Enter]   jdbc:h2:~/test

URL       jdbc:h2:tcp://10.10.7.11:9092/~/amit

[Enter]   org.h2.Driver

Driver

[Enter]   sa

User      incms

[Enter]   Hide

Password  incms

Connected

Commands are case insensitive; SQL statements end with ';'

help or ?      Display this help

list           Toggle result list / stack trace mode

maxwidth       Set maximum column width (default is 100)

autocommit     Enable or disable autocommit

history        Show the last 20 statements

quit or exit   Close the connection and exit

sql> 

show tables;

TABLE_NAME | TABLE_SCHEMA

AA         | PUBLIC

AMIT       | PUBLIC

(2 rows, 64 ms)

H2 database in Server mode (Memory mode) :- We can open a IN memory H2 database in Server mode also. Its called a Named Memory H2 database. We can open Named memory H2 database from multiple sessions :-

bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)

Exit with Ctrl+C

[Enter]   jdbc:h2:~/test

URL       jdbc:h2:tcp://10.10.7.11:9092/mem:memory

[Enter]   org.h2.Driver

Driver

[Enter]   sa

User      incms

[Enter]   Hide

Password  incms121

Connected

Commands are case insensitive; SQL statements end with ';'

help or ?      Display this help

list           Toggle result list / stack trace mode

maxwidth       Set maximum column width (default is 100)

autocommit     Enable or disable autocommit

history        Show the last 20 statements

quit or exit   Close the connection and exit

sql>  create table aaa

...> ( id number, name varchar2(12));

(Update count: 0, 0 ms)

sql>

sql> insert into aaa values ('1' ,'amit');

(Update count: 1, 60 ms)

sql> insert into aaa values ('21' ,'sunil');

(Update count: 1, 0 ms)

sql> commit;

(Update count: 0, 0 ms)

sql> select * from aaa;

ID | NAME

1  | amit

21 | sunil

(2 rows, 150 ms)

sql>

Open this memory database from another session :-

bash-3.2$ java -cp h2-1.3.170.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.170 (2012-11-30)

Exit with Ctrl+C

[Enter]   jdbc:h2:~/test

URL       jdbc:h2:tcp://10.10.7.11:9092/mem:memory

[Enter]   org.h2.Driver

Driver

[Enter]   sa

User      incms

[Enter]   Hide

Password  incms121

Connected

Commands are case insensitive; SQL statements end with ';'

help or ?      Display this help

list           Toggle result list / stack trace mode

maxwidth       Set maximum column width (default is 100)

autocommit     Enable or disable autocommit

history        Show the last 20 statements

quit or exit   Close the connection and exit

sql> select * from aaa;

.ID | NAME

1  | amit

21 | sunil

(2 rows, 139 ms)

For more clarifications related to H2 database visit http://www.h2database.com//html/main.html 

I hope this article helped you.

Regards,

Amit Rath