H2 - Introduction

1. What is H2?

H2 is a super fast in-memory database. It is mainly used for integration testing.

2. Why do we use H2?

Since it is an in-memory database, it is faster than other database engines. It's foot print is very less (~1 MB). In a build environment we don't need a sql server instance to run integration tests, instead this H2 database simulates the sql server instance. H2 instances will be destroyed once the VM is closed. The following links are comparing H2 with other in-memory databases
H2-vs-SQLite
H2 and SQLite

3. Who manages H2 instance for the application?

In general any build tool. In our case, gradle creates the H2 database and fills data in it. A batch file also can manage H2 instance. Example H2 with Gradle and Flyway Program.

4. How do I configure H2 in my local?

a. Install H2 from download link
b. Default port number for H2 is 8082.
c. Then follow the quick start tutorial Quick Start. When launching the "H2 Console", if it says "Unable to access the network", then replace the IP address with "localhost" such as,

    http://192.168.112.13:8082/
    as
    http://localhost:8082/

    Reason: Because of the network settings in the office. I didn't face this error at my home.
d. Check the CLASSPATH environment variable, if it doesn't include the H2 jar file location, append it in the CLASSPATH variable. My CLASSPATH is variable is .;C:\Program Files (x86)\Java\jre7\lib\ext\QTJava.zip;C:\Program Files (x86)\H2\bin\h2-1.3.174.jar;
e. default username is : sa
f: default password is: <empty> (don't give any password)

5. What connection option should I use?

Although there are many ways to connect the database as mentioned here Database Urls. But for integration tests we must use Server Mode.
Example: jdbc:h2:tcp://localhost/mem:TestDatabaseName;

6. How to enable Sql Server Mode?

Append the following option in the connection string.
MODE=MSSQLServer; Now the url will look like
jdbc:h2:tcp://localhost/mem:TestDatbaseName;MODE=MSSQLServer

7. How long this database will persist?

Unless you mention DB_CLOSE_DELAY=-1; The database will be destroyed from the in-memory as soon as you disconnect it. In order to access other java programs out of gradle, you must mention close delay option also.
Example: jdbc:h2:tcp://localhost/mem:TestDatbaseName;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;
To know more about DB_CLOSE_DELAY Close Delay

8. What is "Driver Not Found" error?

As far as I know, there are two reasons.
a. If you miss the H2 jar file or H2 dll. In our case it is h2-1.3.174.jar or h2-1.3.174.dll. Set the right CLASSPATH as mentioned in Question No 4: d.
b. if you give wrong connection string, such as,
tcp://localhost/mem:TestDatbaseName;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;
The above connection string misses "jdbc:h2:" in the front. So it could not find the driver.

9. What is the driver for H2?

"org.h2.Driver"
in .net you should use it as
org.h2.Driver.load();

10. How JVM deamon behaves for H2?

Since H2 is an in-memory database, the H2 database is per JVM deamon instance. So the database created by one JVM deamon cannot be accessed by the other one. To access a database by all the JVM deamons we must use "server mode" (tcp) option . Within gradle it uses only one JVM deamon. But when we access data generator tool, the data generator will create a new JVM deamon instance. Then data generator tool will search for a database within it's own JVM deamon process, if it doesn't find the database already, it creates a new database on it's own.

11. How do I verify if the same database is used by all the JVM deamons?

If you are using non-tcp connections, this problem may arise.
Create a database without IFEXISTS=TRUE option
jdbc:h2:mem:TestDatbaseName;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;
Add tables in it.
Now in the another process, which access the same database, use IFEXISTS=TRUE option in the connection string. Now you shouldn't throw "database not found" error.
jdbc:h2:mem:TestDatbaseName;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;IFEXISTS=TRUE;
To know more about it Exists

12. Are there any difference between in sql query for H2?

Yes, Here is the list of differences H2 - Tips and Tricks

13. What is H2 Sharp? Any example c#.net program to connect H2?

H2 Sharp is a .net API to access H2 from .net. You need IKVM binaries to use H2 Sharp.
H2 Sharp from github has a sample program.
H2 Sharp download h2sharp
IKVM binaries download IKVM binaries
As far as I know, only the above links are in working condition. The H2 Sharp, I downloaded from the google code version didn't work.

14. Exampe gradle file for H2 with flyway.

Example H2 with Gradle and Flyway Program.