Java with SQLite embedded database

If your java desktop application needs to store data in a database between sessions then a good solution is to embed a database into your solution.  What this gives you is an application which installs the database as part of the application, builds the database tables and populates it with any initial data.  This approach avoids the need for the user to install a database separately from the application.

Let’s takes a look at how to you might go about this.

The challenge

First off – what are we setting out to achieve.  We want to create an application which when it starts checks to see if we already have a database and a user table.  If there is no database it will be created, if there is no user table it will be created and prepopulated with 2 users.  All the users in the table will then be written to the standard output.

When the application runs, if the database and table will both exist, the application will simply write to the standard output device the first and last names of all the users in the table.

Our database will contain a single table called ‘users’.  It will have three columns, the first an id which will be the primary key, then an fname to hold the persons first name and then an lname to hold the last name.  The SQL used to create the table will be:

CREATE TABLE user(id INTERGER, fName VARCHAR(60), 
lname VARCHAR(60), PRIMARY KEY(id));

We will need

So now we know what we are looking to achieve lets set about it.

For our example we will use SQLite a well-known and popular database

For this example we will need to download SQLite Java Database Connection (JDBC) drivers.  This is what we will use in Java to interact with the database.  The JDBC download at the time of writing could be found at https://bitbucket.org/xerial/sqlite-jdbc/downloads and I selected sqlite-jdbc-3.8.11.2.jar which was released in October 2015.

As with pervious examples I will use Eclipse Java SE development environment to produce my Java code.

Getting started

Power up Eclipse and create a new Java Project, I called mine SQLite1.

Next open up the properties for the project and navigate to the Libraries tab.  Press the ‘Add External JAR’ button and navigate to the location the where the SQLite JDBC jar file was placed and add this to the project.

SQLiteTest class

Create a class and place it in the ‘softwarepulse.app’ package and call this class ‘SQLiteTest’.

Starting with the SQLite class we will add a couple of properties, one a handle to the database connection and the other a Boolean value to show if we already have a user table.

public class SQLiteTest {
private static Connection con;
private static boolean hasData = false;
}

Let’s take a look at the public interface our class will provide.

Display users

We want a method to display all the user in the database.  For this we will not pass any parameters and will return an SQL ResultSet.  On entering the method we will check to see if we have a connection to the database and if not we will get one by calling our private method ‘getConnection()’.  Once we have a connection we will use this to create an SQL statement and assign it the SQL query “SELECT fname, lname FROM user”.  There are no parameters in this query it will simply bring back all the first and last names from the user table.

public ResultSet displayUsers() throws SQLException, ClassNotFoundException {
		 if(con == null) {
			 // get connection
			 getConnection();
		 }
		 Statement state = con.createStatement();
		 ResultSet res = state.executeQuery("select fname, lname from user");
		 return res;
	 }

Add Users

We will want a means of adding extra users.  Whilst we will not use this method for this example we will move on to adding users later so that’s why this is presented here.  The addUser method takes two parameters, both strings, the first name and last name of the user.  A check is made that we have a connection to the database and if not then we get a connection.  Once we have established a connection then we create a prepared statement which inserts data into the user table.  The SQL statement takes three parameters and we only pass the second and third parameters, being the first name and last name, the first parameter will be assigned the next primary key value.

	public void addUser(String firstname, String lastname) throws ClassNotFoundException, SQLException {
		 if(con == null) {
			 // get connection
			 getConnection();
		 }
		  PreparedStatement prep = con
				    .prepareStatement("insert into user values(?,?,?);");
				  prep.setString(2, firstname);
				  prep.setString(3, lastname);
				  prep.execute();
		 
	 }

 

These are the only two public methods available.  The other two methods are both private.

Get Connection

The getConnection method takes no parameters and returns nothing.  When called it attempts to locate the JDBC class for SQLite and registers that with the Driver manager.  Once the class is obtained the Driver Manager is then used to locate and connect to the database.  In my case I have called it ‘SQLiteTest1.db’.  If the database cannot be located it is created.  The initialise method is then called.

	 private void getConnection() throws ClassNotFoundException, SQLException {
		  // sqlite driver
		  Class.forName("org.sqlite.JDBC");
		  // database path, if it's new database, it will be created in the project folder
		  con = DriverManager.getConnection("jdbc:sqlite:SQLiteTest1.db");
		  initialise();
	 }

Initialise

The initialise method is used to check to see if the User database table is already present and if not then it creates it and prepopulates it with data for two people.  On entering the method a check is made of the class boolean property ‘hasData’; if this is set to true then no action is taken as the application has already check for the presence of the User table.  If ‘hasData’ is false then an SQL statement is created to query the SQLite master table looking for the presence of a table called ‘user’.

SELECT name FROM sqlite_master WHERE type='table' AND name='user';

If the result set returned from the query is not empty then there is already a table in the database called ‘user’ so we need not create it.  If the result set is empty there is no user table in the database so we need to create it and populate it with data.  A new statement is created and assigned the SQL statement to create the user table.

CREATE TABLE user(id INTEGER, fName VARCHAR(60), 
lname VARCHAR(60), PRIMARY KEY(id));

We then create two prepared statements which insert into the user table the names of two people.

	 private void initialise() throws SQLException {
		 if( !hasData ) {
			 hasData = true;
			 // check for database table
			 Statement state = con.createStatement();
			 ResultSet res = state.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='user'");
			 if( !res.next()) {
				 System.out.println("Building the User table with prepopulated values.");
				 // need to build the table
				  Statement state2 = con.createStatement();
				  state2.executeUpdate("create table user(id integer,"
				    + "fName varchar(60)," + "lname varchar(60)," + "primary key (id));");

				  // inserting some sample data
				  PreparedStatement prep = con.prepareStatement("insert into user values(?,?,?);");
				  prep.setString(2, "John");
				  prep.setString(3, "McNeil");
				  prep.execute();
				  
				  PreparedStatement prep2 = con.prepareStatement("insert into user values(?,?,?);");
				  prep2.setString(2, "Paul");
				  prep2.setString(3, "Smith");
				  prep2.execute();
			 }
			 
		 }
	 }

That concludes the code for the SQLiteTest class.  Now let’s take a look at the main application class

App Class

Next create a class under the source folder and include the main method; I called mine App and placed it in the softwarepulse.app package.

We will create two properties, one to hold the SQLiteTest class and the other to hold the ResultSet returned from the displayUsers method call.  The SQLite property is initialised with a new instance of the SQLiteTest class.  We then call the displayUsers() method and assign the return ResultSet to our other property.

To loop through all the users returned we set up a while loop calling the ResultSet.Next() method.  So long as there is another row to process we will go through the loop.  Each time we go through the loop we write out to the standard output the first and last name from the result set.  All other this is surrounded by a try/catch block because the method in the SQLiteTest class all throw exceptions if something fails whilst connecting to the database.  If that occurs we write the error out to the standard out and terminate the application.

public class App {

	public static void main(String[] args) {
		
		SQLiteTest test = new SQLiteTest();
		ResultSet rs;
		
		try {
			  rs = test.displayUsers();
			  
			  while (rs.next()) {
				     System.out.println(rs.getString("fname") + " " + rs.getString("lname"));
				  }
			  
		  } catch (Exception e) {
			  e.printStackTrace();
		  }

	}

}

So there you are we now have a basic Java application which creates an embedded database and configures the application on first use and then reuses the database on subsequent sessions.

Output when first run

Building the User table with prepopulated values.
John McNeil
Paul Smith

Output on subsequent runs

John McNeil
Paul Smith

Download the complete source code from here

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.