How to connect to H2 database from Java & H2 DB

Question

I am fairly new to Eclipse & Java and currently work on a project where I need to implement my first database.

Therefore, I tried to connect Eclipse and the H2 Database. While the H2 database part works just fine by itself, I can't figure out how to connect it to Eclipse.

I created the following class and tried to do everything as stated on the website:

package srpTracking;

import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;

public class DatabaseConnector {

    public static void main(String... args) throws Exception {
        DeleteDbFiles.execute("~", "test", true);

        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:~/test");
        Statement stat = conn.createStatement();


        stat.execute("create table test(id int primary key, name varchar(255))");
        stat.execute("insert into test values(1, 'Hello')");
        ResultSet rs;
        rs = stat.executeQuery("select * from test");
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
        stat.close();
        conn.close();
 } 

I get identifier errors for the last two lines of code, but it also doesn't connect anything.

I copied the H2.jar file into the project folder in a subfolder called lib.

Unfortunately I can't for some reason install the DTP plugin, because I am apparently missing a 'org.eclipse.core.runtime' file.

What do I need to change about my code connect Java and H2? Also, do I need to copy and H2 files into specific folders?

Solution

Consider these changes to your main. By using try-with-resource you delegate the close to the JVM - with the added benefit that even in cases of exceptions, you wouldn't need to close them as well.

Why it didn't work before? Your variables ran out of scope - you tried to close after main() had ended.

public static void main(String... args) throws Exception {
    DeleteDbFiles.execute("~", "test", true);

    Class.forName("org.h2.Driver");
    try (Connection conn = DriverManager.getConnection("jdbc:h2:~/test"); 
            Statement stat = conn.createStatement()) {
        stat.execute("create table test(id int primary key, name varchar(255))");
        stat.execute("insert into test values(1, 'Hello')");
        try (ResultSet rs = stat.executeQuery("select * from test")) {
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Comments

[Pieter De Bie]

Whats the use of Class.forName? Checking if the driver exists?

[Jan]

Until Java 6 this triggered loading the Driver class initializing all static variables and code-blocks, allowing the Driver to register itself with JDBC. Nowadays it would merely trigger a more telling Exception (Class not found) instead of some obscure no driver found for JDBC URL.

[Pieter De Bie]

Thanks for the clarification!