Use cases of reading data from AnyLogic database

Here we will give some examples of SELECT queries. All these examples are taken from the Corporate Education example model. Some of them can be easily constructed visually using our handy Query Constructor. This document aims to teaching you the Query DSL syntax that you may use to construct more complex queries.

Demo model: Corporate Education

Reading a single value

Need to read a particular course duration from the table like this:

 

And use the duration as a transition timeout value. The code is this:

(double)selectFrom( courses ).
      where( courses.course.eq( courseName ) ).
      uniqueResult( courses.duration_days )

Note using uniqueResult() function and explicit conversion to (double): the returned result is Integer and does not automatically cast to double.

Retrieving multiple records from DB table in the form of a sorted list of tuples

Assume we need to load multiple DB records into Java , sorted by a particular column, and analyze them. Say, the table is this:

 

And we need to get all courses for a particular employee type and sort by the column “Not earlier than…”. Then the code is:

List< Tuple > plan =
      selectFrom( education_plans ).
            where( education_plans.employee_type.eq( type ) ).
            orderBy( education_plans.not_earlier_than_w_day.asc() ).
            list();

Note the call of orderBy() that precedes the final call of list(). Working with the list of tuples is easy, just iterate and use the method get() to access a particular column:

for( Tuple tup : plan ) {
      …
      tup.get( education_plans.course );
      tup.get( education_plans.not_earlier_than_w_day );
      tup.get( education_plans.not_later_than_w_day );
      …
}

Getting all values in a particular column in the form of a Java list

This is very simple: you just specify the column in the call of the list() function:

List< String > courseslist = selectFrom( courses ).list( courses.course );

The table in this case was:

 

And of course you can add a condition by putting the where() function in the middle, like in the following example where we need to get all courses that can be taught by a particular trainer type:

List< String > complist = selectFrom( trainer_competences ).
            where( trainer_competences.trainer_type.eq( type ) ).
            list( trainer_competences.course );


The above code works with this table:

 

Getting all values in a particular column and adding them to a collection

Assume you have a collection employeeTypes in the model and want to load all values from a column type in a DB table employee_types. The following code does that unconditionally:

employeeTypes.addAll(
      selectFrom( employee_types ).
            list( employee_types.type )
);

And again you can add a condition by adding the where() function.

Checking if a particular record exists in the DB

Let’s say you need to check whether a trainer of a given type can teach a particular course. The trainer competences are contained in this table:

 

And the query will look like:

selectFrom( trainer_competences ).
      where( trainer_competences.trainer_type.eq( type ),
trainer_competences.course.eq( course ) ).
      exists();

Notice the two conditions in the where() function specified as two parameters (separated by a comma) – they are interpreted as “AND”, and the finalizing call to the exists() function that returns a boolean result.