h2 is fast light weight jdbc api which can run in both embedded and server mode, and found quite easy to start with.
we can use jdbc api to get the connection
String uri = "jdbc:h2:~/test"; //~/ directs to user home folder we can give full path as well "jdbc:h2:c:/myapp/data/test
JdbcConnectionPool connectionPool = JdbcConnectionPool.create(uri, "test", "test@123");
Connection connection = connectionPool.getConnection();
or
Class.forName("org.h2.Driver");
Connection
connection = DriverManager.getConnection("jdbc:h2:~/test");
for me every time getting connection was taking around 2 ms, its already quite efficient even though decided to use connection pool due to frequent db excess in batches.
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();
}
}
no 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