H2 Database Java Servlet Connection Path issue

Question

Lately I have been looking into having a servlet with a local database. With a bit of research I found H2 Database Engine (Wikipedia). This is perfect for what I want but I am having trouble with the local path for my servlet.

Example:

I need to create the H2 Database in my WebContent folder so its apart of the project. However I cannot seem to get the code right to localise it.

Example CODE: - H2.Jar - Connection String to SQL Database

             String url = "jdbc:h2:"+request.getContextPath()+"/emailDB;IFEXISTS=TRUE";
         Class.forName("org.h2.Driver");
            Connection conn = DriverManager.
                getConnection(url, "adminuser", "pass");

Example CODE (ERROR): - H2.Jar - Connection String to SQL Database (OUTPUT)

org.h2.jdbc.JdbcSQLException: Database "C:/emailservlet/emailDB" not found [90013-174]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
at org.h2.message.DbException.get(DbException.java:172)
at org.h2.message.DbException.get(DbException.java:149)
at org.h2.engine.Engine.openSession(Engine.java:54)
at org.h2.engine.Engine.openSession(Engine.java:160)
at org.h2.engine.Engine.createSessionAndValidate(Engine.java:139)
at org.h2.engine.Engine.createSession(Engine.java:122)
at org.h2.engine.Engine.createSession(Engine.java:28)
at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:323)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:105)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:90)
at org.h2.Driver.connect(Driver.java:73)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at emailservlet.msdbcon(emailservlet.java:540)

As you can see the issue i am getting is that even though im requesting the contextpath i am still getting C:/ written before.

If you can help me figure out the error in my code that would be so helpful!

Thank you in advance!

Answer 1

The driver expects file system path where it can create files. It's converting the relative path to absolute by using root directory that is your C: drive. To get absolute path to WebContent folder you need to use ServletContext#getRealPath()

Also it's not a good idea to store H2 file's in WebContent folder you should store them in WEB-INF folder so that its not accessible to users.

Below is how the url should be formed

String path = getServletContext().getRealPath("/") + "/WEB-INF";
String url = "jdbc:h2:"+path+"/emailDB;IFEXISTS=TRUE";

This will create H2 files in WEB-INF folder.

Considerations taken from the Features page on H2Database.com site:

• The database URL for connecting to a local database is jdbc:h2:[file:][] . The prefix file: is optional. If no or only a relative path is used, then the current working directory is used as a starting point.
• The case sensitivity of the path and database name depend on the operating system, however it is recommended to use lowercase letters only.
• The database name must be at least three characters long (a limitation of File.createTempFile).
• The database name must not contain a semicolon.
• To point to the user home directory, use ~/, as in: jdbc:h2:~/test.