Reputation: 599
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
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