An embedded database means that the database is integrated as an inseparable part of an application software. A Java application, in particular, accesses the database using a JDBC driver. The database engine runs as a cohort inside the same JVM while the application is running. The storage acts like any library extension, similar to a jar file attached to the Java application. The end user gets no hint of the underlying persistence storage. However, interactive behavior implementation between the Java application and the database is similar to any JDBC application. This article explores the choices available to us in integrating a database engine and goes hands on in illustrating some intricacies involved in actually implementing them in Java.
In-Memory Database
An embedded database should not be confused with an in-memory database such as SQLite. In an in-memory database, the data is stored entirely in the main memory for faster response with no permanent storage. So, in a way it can be said it is a no-disk database. These type of databases are lightweight, simple, fast, and come with bare minimum features.
Embedded Database
An embedded database, on the other hand, is incorporated into the software program by the developer in such a way that it is supposed to be invisible to the application end user. Being oblivious of the underlying database, there is no need of any external interface to maintain the database separately. So here, our only interest is the database engine; if that can be embedded and run with the application, we get what we want—an embedded database application.
Figure 1: The structure of an embedded database
The downside of using an embedded database is its cohesiveness, apart from an assumed difficulty in database maintenance. In fact, embedded databases are not built or expected to deal with such issues. It perfectly suits the applications that solely require a repository to persist its own transaction without any end user’s intervention. The necessity of a full blown database manager (as found in a standalone RDBMS package) is very minimum or may require only special circumstances. However, to keep even this subset of interest in vogue, embedded databases nowadays provide quite sophisticated features and can do almost everything expected from any standard database system. There are even databases that can be used both in client/server applications and embedded database mode with only the slightest change in the JDBC connectivity.
Standalone Database
This is in contrast to the standalone database model, where a dedicated manager is provided to deal with the maintenance issues. This, however, is commonly perceivable from the name itself, such as Relational Database Management System, as if the stress is on the manager who provides an interface to interact with the database. Client/Server databases are standalone and obviously the most powerful and heavyweight. By the way, they are heavyweight not because of the data content but because of the additional processes that acts as the keeper of the database. Now, rip off those processes and take only the database engine; that is what the core of an embedded database is concerned about.
What Choice Do We Have?
Plenty! To the extent that variety is the crisis of life. But, be sure to apply pragmatism while picking one because each have their own pros and cons. Ratiocinate on specific requirements, though base features are all same, pick one. A few of the dominant providers are H2, HyperSQL, Apache Derby, Berkley DB, Java DB, ObjectDB, and so forth.
HyperSQL conforms to the SQL:2011 standard and JDBC 4 specification and supports every classical feature expected from a modern relational database. It can be run either in embedded and server mode. The database is quite stable, trusted by open source projects such as OpenOffice and LibreOffice as an embedded back-end persistent engine. Version 2.3.x onwards supports two-phase locking along with multi version concurrency control (MVCC) mechanism. As HyperSQL is built in Java, it runs seamlessly in JVM, providing a JDBC interface for data access. The package contains a jar file called hsqldb.jar under the /lib directory which contains the required component: HyperSQL RDBMS Engine and the JDBC driver for embedding HyperSQL in a Java application.
Connection can be established as follows:
Connection c = DriverManager.getConnection
("jdbc:hsqldb:file:/home/mano/testdb",
"u1", "123");
ObjectDB also comes in both flavours: client/server and embedded model. But, it is a different breed than relational databases. It is a Object Oriented Database with built-in support for the JPA2 specification. As a result, using an abstraction like Hibernate has better performance over any other databases. By the way, due to its default support of JPA, the use of an ORM layer simply can be eliminated to leverage performance.
Java DB and Apache Derby are very similar; in fact, Java DB is built upon the Derby database engine. It is written completely in Java and can be tightly embedded in any Java application. All the standard features of a relational database are supported by Derby. It can be deployed either in simple embedded mode or Network Server mode. To embed a Derby database into a Java application, include the jar file named derby.jar, located in /lib directory. This file contains the database engine as well as a JDBC driver component to access the database from Java code.
A Quick Example
Let’s see a quick example on how to embed a Derby database in a Java application:
package org.mano.example;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.derby.jdbc.EmbeddedDriver;
public class EmbeddedDatabaseDemo {
public static void main(String[] args) {
EmbeddedDatabaseDemo e =
new EmbeddedDatabaseDemo();
e.testDerby();
}
public void testDerby() {
Connection conn = null;
PreparedStatement pstmt;
Statement stmt;
ResultSet rs = null;
String createSQL = "create table person ("
+ "id integer not null generated always as"
+ " identity (start with 1, increment by 1), "
+ "name varchar(30) not null, email varchar(30),
phone varchar(10),"
+ "constraint primary_key primary key (id))";
try {
Driver derbyEmbeddedDriver = new EmbeddedDriver();
DriverManager.registerDriver(derbyEmbeddedDriver);
conn = DriverManager.getConnection
("jdbc:derby:testdb1;create=true", "pass123");
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.execute(createSQL);
pstmt = conn.prepareStatement("insert into person
(name,email,phone) values(?,?,?)");
pstmt.setString(1, "Hagar the Horrible");
pstmt.setString(2, "hagar@somewhere.com");
pstmt.setString(3, "1234567890");
pstmt.executeUpdate();
rs = stmt.executeQuery("select * from person");
while (rs.next()) {
System.out.printf("%d %s %s %sn",
rs.getInt(1), rs.getString(2),
rs.getString(3), rs.getString(4));
}
stmt.execute("drop table person");
conn.commit();
} catch (SQLException ex) {
System.out.println("in connection" + ex);
}
try {
DriverManager.getConnection
("jdbc:derby:;shutdown=true");
} catch (SQLException ex) {
if (((ex.getErrorCode() == 50000) &&
("XJ015".equals(ex.getSQLState())))) {
System.out.println("Derby shut down
normally");
} else {
System.err.println("Derby did not shut down
normally");
System.err.println(ex.getMessage());
}
}
}
}
Conclusion
Working with an embedded database in Java is similar to creating a plain old JDBC application in client/server mode. There is no difference in the database access mechanism, either. The jar files that come with the database which, when included, contain all the necessary components for CRUD operation. Simply adding that to the Java projects is what id required to embed a database in a Java application. That’s it.
