Here are 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 teach you the Query DSL syntax, which you can use to construct more complex queries.

Demo model: Corporate Education Open the model page in AnyLogic Cloud. There you can run the model or download it (by clicking Model source files).

Suppose you need to read a specific course duration from the table like this:

You want to use the duration as a transition timeout value. The code for this is as follows:

String courseName = "Product Line";
double timeout = (double)selectFrom(courses).
  where(courses.course.eq(courseName)).
  uniqueResult( courses.duration_days);

When using the uniqueResult() function and explicit conversion to double, keep in mind that the returned result is int and is not automatically converted to double. That’s why you have to type cast it manually.

In addition, there may be cases where you need to evaluate the returned value to get the value of the actual type you need to use in your model. In this case, use the executeExpression() function and pass a value from the database as a parameter to this function.

For example, if the table above contains a boolean value in the taking_place_now column, you must manually evaluate the value to retrieve it as an actual boolean value instead of a string:

String courseName = "Product Line";
String durationString = selectFrom(courses).
  where( courses.course.eq(courseName)).
  uniqueResult(courses.taking_place_now);

boolean duration = executeExpression(durationString);

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

And we need to get all the courses for a particular employee type and sort by the Not earlier than… column. 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 orderBy() call that precedes the final list() call. Working with the list of tuples is simple, just iterate and use the get() function 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 );
  …
}

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

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, as in the following example where we need to get all the courses that can be taught by a certain type of trainer:

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

The above code works with this table:

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

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

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

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

And the query will look like this:

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

Note the two conditions in the where() function specified as two parameters (separated by a comma) — they are interpreted as “AND”, and the final call to the exists() function, which returns a boolean result.

How can we improve this article?

Send