Embedded db h2 storing java object

Embedded database as in databases that do not need a server, and are embedded in an application. This means everything is managed by the application. I have explored on embedded databases found h2 db is more suitable for my current requirements. H2 is fast & light weight provides full support for jdbc api which can run in both embedded and server mode, and found quite easy to start with.

Connecting to h2 db.

String uri = "jdbc:h2:~/test"; 
JdbcConnectionPool connectionPool = JdbcConnectionPool.create(uri, "test", "test@123");  
Connection connection = connectionPool.getConnection();

as an alternative

Class.forName("org.h2.Driver");  
Connection connection = DriverManager.getConnection("jdbc:h2:~/test");

for my setup connection time was around 2 ms, its already quite efficient even though decided to use connection pool to avoid connection initialization overhead.

Now to store objects we should create column of type “other”, java object need to put into db should be serializable. now we can store objects,

String query = "create table properties (key varchar(255) primary key, value other)";
PreparedStatement pstmt = null;  
      if(connection != null) {  
           try {  
                String insertQuery = "insert into properties(key, value) values(?, ?)";  
                pstmt = connection.prepareStatement(insertQuery);  
                pstmt.setString(1, key);  
                pstmt.setObject(2, value, Types.OTHER);  
                pstmt.executeUpdate();  
           } catch (SQLException e) {  
                throw e;  
           } finally {  
                pstmt.close();  
                connection.close();  
           }  
      }

We do not need to specify type (OTHER or JAVA_OBJECT) while accessing, desealization is taken care care in api.

Object value = resultSet.getObject("value");

source code for example is available here