KarthikaSrinivasan
KarthikaSrinivasan

Reputation: 599

Even after closing the connection, data still exists in h2 database tables

As I surfed in many H2 database tutorials it was given that by default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost.

I have created Employee1 table, inserted records and closed the connection. But still I am able to retrieve the Employee1 data if I reconnect the same db after sometime. Why the data still existing?

package connection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Connector {

static Connection conn = null;
static Statement stmt = null;

public static void main(String[] args) {

    System.out.println("Welcome!");
    Connector connector = new Connector();
    connector.createConnection();
    connector.createTable("Employee2");
    connector.insertRecord("Employee2");
    connector.readRecord("Employee2");
    connector.readRecord("Employee1"); //Employee1 Table which is created in previous execution but still it reads the data
    connector.closeConnection();
}

public void createConnection() {
    try {
        System.out.println("Creating connection");
        // STEP 1: Register JDBC driver
        Class.forName("org.h2.Driver");
        // STEP 2: Open a connection
        System.out.println("Connecting to database...");
        conn = DriverManager.getConnection("jdbc:h2:mem/db1", "sa", "");
    } catch (SQLException se) {
        // Handle errors for JDBC
        se.printStackTrace();
    } catch (Exception e) {
        // Handle errors for Class.forName
        e.printStackTrace();
    }

}

public void createTable(String tableName) {
    try {
        // STEP 3: Execute a query
        System.out.println("Creating table in given database with the name of ..." + tableName);
        stmt = conn.createStatement();
        String sql = "CREATE TABLE " + tableName + "(id INTEGER not NULL, " + " first VARCHAR(255), "
                + " last VARCHAR(255), " + " age INTEGER, " + " PRIMARY KEY ( id ))";
        stmt.executeUpdate(sql);
        System.out.println("Created table in given database...");

    } catch (SQLException se) {
        // Handle errors for JDBC
        se.printStackTrace();
    } catch (Exception e) {
        // Handle errors for Class.forName
        e.printStackTrace();
    }
}

public void insertRecord(String tableName) {
    try {
        // STEP 3: Execute a query
        stmt = conn.createStatement();
        String sql = "INSERT INTO " + tableName + " VALUES (500, 'Zara', 'Ali', 18)";

        stmt.executeUpdate(sql);
        sql = "INSERT INTO " + tableName + " VALUES (501, 'Mahnaz', 'Fatma', 25)";

        stmt.executeUpdate(sql);
        sql = "INSERT INTO " + tableName + " VALUES (502, 'Zaid', 'Khan', 30)";

        stmt.executeUpdate(sql);
        sql = "INSERT INTO " + tableName + " VALUES(503, 'Sumit', 'Mittal', 28)";

        stmt.executeUpdate(sql);
        System.out.println("Inserted records into the table...");
    } catch (SQLException se) {
        // Handle errors for JDBC
        se.printStackTrace();
    } catch (Exception e) {
        // Handle errors for Class.forName
        e.printStackTrace();
    }
}

public void readRecord(String tableName) {
    try {
        System.out.println("Reading data from "+tableName);
        stmt = conn.createStatement();
        String sql = "SELECT id, first, last, age FROM " + tableName;
        ResultSet rs = stmt.executeQuery(sql);

        // STEP 4: Extract data from result set
        while (rs.next()) {
            // Retrieve by column name
            int id = rs.getInt("id");
            int age = rs.getInt("age");
            String first = rs.getString("first");
            String last = rs.getString("last");

            // Display values
            System.out.print("ID: " + id);
            System.out.print(", Age: " + age);
            System.out.print(", First: " + first);
            System.out.println(", Last: " + last);
        }
        // STEP 5: Clean-up environment
        rs.close();
    } catch (SQLException se) {
        // Handle errors for JDBC
        se.printStackTrace();
    } catch (Exception e) {
        // Handle errors for Class.forName
        e.printStackTrace();
    } finally {
        // finally block used to close resources
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException se2) {
        } // nothing we can do
    } // end try
}

public void closeConnection() {
    try {
        if (conn != null) {
            conn.close();
            System.out.println("Connection Closed..");
        }
    } catch (SQLException se) {
        se.printStackTrace();
    } // end finally try
}
}

Output of above program:

Welcome!
Creating connection
Connecting to database...
Creating table in given database with the name of ...Employee2
Created table in given database...
Inserted records into the table...
Reading data from Employee2
ID: 500, Age: 18, First: Zara, Last: Ali
ID: 501, Age: 25, First: Mahnaz, Last: Fatma
ID: 502, Age: 30, First: Zaid, Last: Khan
ID: 503, Age: 28, First: Sumit, Last: Mittal
Reading data from Employee1
ID: 400, Age: 18, First: freeze, Last: Ali
ID: 401, Age: 25, First: dora, Last: Fatma
ID: 402, Age: 30, First: xer, Last: Khan
ID: 403, Age: 28, First: kilo, Last: Mittal
Connection Closed..

Upvotes: 1

Views: 1482

Answers (1)

Oleg
Oleg

Reputation: 6314

As you can see here

jdbc:h2:mem/db1

Connects to a local database file with a relative path of mem/db1 so the data still exists because it's saved id a file.

For an in memory database the connection String should be:

jdbc:h2:mem:db1

Notice the difference between / and :

Upvotes: 4

Related Questions