SQLite and AnyLogic

AnyLogic offers a Database module for exchanging data with an external database. However, only Access and MSSQL databases are predefined. Another useful database system is SQLite. It is under Public domain, widespread, and works without a database server. Therefore it is an ideal database system to work with simulation. This article describes how to use SQLite with AnyLogic.

Add JDBC Driver

To connect to any type of database, the Database module in AnyLogic makes use of the JDBC driver, sort of a general interface between Java and databases. JDBC is already implemented in AnyLogic. However, JDBC additionally needs a driver for each database type it is supposed to work with, which for SQLite is not yet loaded in AnyLogic. The first step is therefore to get a JDBC SQLite driver. Reference the file in the "Dependencies" tab of your project properties in AnyLogic.

Configure Database Module

Add a Database module "myDatabase" to the canvas. Set the following:

The first part of the connection URL reflects the driver and is therefore fixed, the name at the end can be chosen freely. If no DB file yet exists with that name in the model folder, that is no problem. One will the automatically be created at the first access to the database. The Database module is now ready to use.

Use the database

The actual use of the Database module is the same as with other database types, so the following instructions basically apply not only to SQLite connections. However, be aware that there are subtle differences in statement syntax and column formats between SQL implementations.

If you haven't ticked the "connect on startup" option in the database module, you'll have to do this manually before you access it. If the DB file didn't exist so far, it gets created now.

myDatabase.connect();

Now make sure that the table that you want to use exists. For this example I'll create a simple table with two columns of undefined format.

try{
	String statement = "CREATE TABLE IF NOT EXISTS myTableName(COLUMNA,COLUMNB);";
	database.modify(statement);
}
catch(Exception e){
	e.printStackTrace();
}

For reoccurring SQL statements (which you most likely have), it is good practice to use prepared statements. That is basically a template of an SQL statement where everything is ready, except the actual data is missing. This way statements can get precompiled and they get executed faster. To use the prepared statements, you'll have to import this first in your Imports section: import java.sql.*. On your canvas, create a variable myStatment of type "PreparedStatement" and have it filled with by following code:

try{
    myStatement = myDatabase.getConnection().prepareStatement(
		"insert into myTableName" +
		"(COLUMNA,COLUMNB)" +
		" Values(?, ?)");
} catch (Exception e) {
	e.printStackTrace();
}

In this statement, two yet unknown variables are defined to be set later for each execution of this query. The question marks stand for the locations of the data. To execute this statement, the two values have to be set first:

try{
    myStatement.setString(1,"Test1");
    myStatement.setString(2,"Test2"));
    myStatement.executeUpdate();
} catch (Exception e) {
	e.printStackTrace();
}

A word on performance: SQLite can do thousands of statements per second, but only some dozens transactions per second. Therefore it is important to bundle statements together as one transaction. This can be done by temporarily disabling auto comit:

myDatabase.getConnection().setAutoCommit(false);
//execute several statements here
...
myDatabase.getConnection().commit();
myDatabase.getConnection().setAutoCommit(true);

Conclusion

Preparing an AnyLogic model for SQLite takes less then a minute. If you incorporate the performance boosters prepared statements and bundled transactions you have a high-performance possibility now for import and export of large data quantities. To check, view or modify the SQLite database externally, there are many great free software packages, such as SQLite Studio.