Importing data from external database — step-by-step guide

Simulation models are based on data, and when you use data from an external database, we recommend you import it into an internal (built-in) database in AnyLogic. Currently using a built-in database is the most convenient and efficient way to work with model data in AnyLogic.

Firstly, many model elements have visual controls for retrieving data from database tables. See an example of such controls in an article on Generating agent arrivals.

Secondly, connecting to an external data source during a model run may affect the model’s performance. Thanks to the built-in database option you can avoid it.

In this post, you’ll learn how to import data from an external database using a JDBC driver to connect a simulation model with PostgreSQL and create a schedule in AnyLogic.

Connection to an external database

You can import data from MS Excel/MS Access/MS SQL Server in a couple of clicks. But what if the data is stored in a database like MySQL, Oracle Database, or PostgreSQL?

First of all, you’ll need to establish a connection to the external database. You can learn how to do it using a real-life example.

Let’s say PostgreSQL stores a weekly staff schedule and based on this data, you need to define the availability of resource units.

To establish the connection, we recommend using the Database Connectivity Tool. However, it won’t be able to connect with the external database without an appropriate JDBC driver created specifically for PostgreSQL.

You can download the JDBC driver for PostgreSQL from their official website. Then copy all driver files into the model’s folder and import the driver’s JAR file (Java ARchive) into the Dependencies tab in the model’s properties.

Setting Database Connectivity Tool and using a function

With the JDBC driver, you can now configure the Database Connectivity Tool:

1. Give a meaningful name, say “postgreInput”.

2. Type org.postgresql.Driver in the JDBC driver property.

3. In the case of PostgreSQL, the Connection URL property takes one of the following forms:

   jdbc:postgresql://host/database
   jdbc:postgresql://host:port/database 
    

The parameters have the following meanings:

host — the host name of the server.
port — the port number the server is listening on. The PostgreSQL default port number is usually 5432.
database - the database name.

4. Specify login and password for the database, if any.

After setting the Database Connectivity Tool, you can use it to import the staff schedule data from PostgreSQL to the AnyLogic built-in database programmatically:

    ModelDatabase modelDB = getEngine().getModelDatabase();
    Connection sourceConnection = postgreInput.getConnection();
    Connection targetConnection = modelDB.getConnection();
    try {
       modelDB.importFromExternalDB(sourceConnection, “schedule_data”, 
“db_schedule”, true, false);
       targetConnection.commit();
    } catch (Exception e) {
       targetConnection.rollback();
    } finally {
       targetConnection.setAutoCommit(true);
     }
    

In this case, you’ll use an importFromExternalDB() function from ModelDatabase API:

    public void importFromExternalDB(java.sql.Connection sourceConnection,
                                     java.lang.String sourceTableName,
                                     java.lang.String targetTableName,
                                     boolean clearTargetTable,
                                     boolean autoCommit)
         

The function imports a single table from an external database. Column types should match or be convertible

Parameters:

• sourceConnection – a connection object for the external database
• sourceTableName – a table in the external database to copy data from ("schedule_data" in this case)
• targetTableName – a table in the built-in database where the data should be copied to ("db_schedule" in this case)
• clearTargetTable – if true, the target table will be cleared before the data is copied
• autoCommit – if false, this action will not open or commit any transaction (which may decrease performance in some cases).

How to create a weekly schedule?

When executing the code above, the table in the built-in database (“db_schedule”) will be filled with values from the table “schedule_data” in PostgreSQL. Now, everything is ready to retrieve data from the built-in database and form a weekly schedule in a simulation model. For that, AnyLogic has a Schedule element.

More about creating a weekly schedule – in AnyLogic Help.

In this post, we’ve covered three questions that we often receive from users:

1. How do I import data from an external database other than MS Excel, MS Access, or MS SQL Server?
2. How do I use a JDBC driver to connect AnyLogic with a database such as MySQL or PostgreSQL?
3. How do I create a weekly schedule using an internal database?

If you need further assistance with AnyLogic, our support team is always here to help you.

Contact us