Embedded Databases

Embedded Databases

An embedded database doesn’t need a server: data is stored on the same machine as the application that uses it (typically in a sub-folder of the application).

Advantages

  • Extremely fast read/write operations.
  • Not dependent on a database hosting service, avoiding all associated costs.
  • No need for a network connection because queries are executed locally.

Drawbacks

  • The database is not shared between different machines, and is therefore limited to storing local data.
  • Execution speed of queries is harder to predict because it depends on the local machine.

Java examples using Eclipse

For this example, we will use two popular databases:

  • H21: offers both embedded and server modes.
  • SQLite2: embedded only

Step 1

Download the latest “Java Database Connectivity” (JDBC) libraries in JAR form. For this example, we are using the following (click here for all H2 versions, and here for all SQLite versions):

Step 2

In Eclipse: Preferences>Java>Build Path>User Libraries>New

Name it “jdbc_h2”, click “Add External JARS” and select the file “h2-1.4.200.jar” that you downloaded in the previous step.

Do the same with the SQLite JDBC, using a different user library called “jdbc_sqlite”.

Step 3

Using a new Eclipse Java project, or an existing one:

  • Right click on the project name in the package explorer,
  • select “Build Path”, then: Configure Build Path>classpath>Add Library>User Library
  • select both “jdbc_h2” and “jdbc_sqlite
  • click “Finish

Creating and executing queries using H2

In your Eclipse project, create a new package called “embedded_db_test”, and inside it create a new class called “TestH2” consisting of the following:

package embedded_db_test;
import java.sql.*;

public class TestH2 {
    public static void main(String[] a)
            throws Exception {
        Connection connection = DriverManager.
            getConnection("jdbc:h2:./databases/h2_db1", "root", "");
        
        Statement statement = connection.createStatement();
        
        statement.execute("DROP TABLE IF EXISTS car;");
        
        String createQuery = "CREATE TABLE car ("
        		+ "   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,"
        		+ "   brand VARCHAR(50),"
        		+ "   model VARCHAR(20) NOT NULL"
        		+ ");";
        
        statement.execute(createQuery);
        
        statement.execute("INSERT INTO car(model) VALUES('Coccinelle')");
        statement.execute("INSERT INTO car(brand, model) VALUES('Renault', 'Clio')");
        var carSelection = statement.executeQuery("SELECT * FROM car");

        while ( carSelection.next() ) {
        	System.out.println("Car: " + carSelection.getString("model") + "(" + carSelection.getString("brand") + ")");
        }
        
        connection.close();
    }
}

Click here to download TestH2.java

When we run it, we obtain the following console output:

Car: Coccinelle(null)
Car: Clio(Renault)

The brand for the first car is null because we did not set it in the first insert statement.

Databases are automatically created if they don’t exist when a connection is opened: after we have run this code, we can find the following file in the « databases » folder located in the same folder as « src » (due to passing the path « ./databases/h2_db1 » to the method getConnection):

Most databases provide management tools, such as H2’s « H2 Console ». In the application connection dialog, we enter the absolute path of our database and the user name and password that we specified when calling the method getConnection:

We can now see and modify the « car » table:

Creating and executing queries using SQLite

Let’s do the same thing with SQLite. Create another class inside the same package called “TestSQLite” containing the following:

package embedded_db_test;
import java.sql.*;

public class TestSQLite {
    public static void main(String[] a)
            throws Exception {
        Connection connection = DriverManager.
            getConnection("jdbc:sqlite:./databases/sql_lite_db1.db", "root", "");

        Statement statement = connection.createStatement();
        
        statement.execute("DROP TABLE IF EXISTS car;");
        
        String createQuery = "CREATE TABLE car ("
        		+ "   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"
        		+ "   brand VARCHAR(50),"
        		+ "   model VARCHAR(20) NOT NULL"
        		+ ");";
        
        statement.execute(createQuery);
        
        statement.execute("INSERT INTO car(model) VALUES('Coccinelle')");
        statement.execute("INSERT INTO car(brand, model) VALUES('Renault', 'Clio')");
        var carSelection = statement.executeQuery("SELECT * FROM car");

        while ( carSelection.next() ) {
        	System.out.println("Car: " + carSelection.getString("model") + "(" + carSelection.getString("brand") + ")");
        }
        
        connection.close();
    }
}

Click here to download TestSQLite.java

When we run it it, we obtain the same console output as before and a new file is created in the « databases » folder:

Noticing differences

Aside from the getConnection string parameter, The second line of the “createQuery” string differs:

  • H2 : “id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  • SQLite : “id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,

The keyword “INT” is changed to “INTEGER” in SQLite for auto increment to work, and “AUTO_INCREMENT” becomes “AUTOINCREMENT”.

Conclusion

Knowing how to use embedded databases is crucial in application development. Most large projects use them to store user and application data. One should be aware that the syntax of queries may differ between different embedded database implementations, which could complicate a switch from one to another in the middle of a project.

References:


Erik Shea

0
0

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *