Writing select queries

In AnyLogic, you typically construct select queries using the handy visual query builder.

However, you can also write custom queries using one of two alternative syntaxes: Java SQL, or Query DSL.

Java SQL

Select one value from the database

You can write a query using common SQL. To execute SQL statement, use AnyLogic function selectUniqueValue(), or selectFirstValue(). Both functions have two arguments: the returned value type, and the SQL query text (of type String).

selectUniqueValue(String sql, Object... params) - The function executes specified SELECT SQL query (sql) and returns the unique value or null if the query produces no values. If there are several values satisfying the SELECT condition, the error will be raised. This function caches its results to speed up the behavior. Use selectUniqueValue(false, sql, params) to get non-cached result every time.

selectFirstValue(String sql, Object... params) - The function executes specified SELECT SQL query (sql) and returns the first produced value or null if the query produces no values. This function will not raise the error in case of several valid values, it will just return the first value from the result set. It caches its results to speed up the behavior. Use selectFirstValue(false, sql, params) to get non-cached result every time.

The example:

selectUniqueValue(false, double.class, "SELECT processing_time FROM processing_times WHERE part = agent.name;");

You can see that here you type the common SQL query. But note that breaking the query in multiple lines (as shown below) will raise an error.

selectUniqueValue(false, double.class, "SELECT processing_time
FROM processing_times WHERE part = agent.name;");

To continue writing a query from a new line, you should end the current line with the space symbol, then quote symbol ", then add the concatenation symbol , and then start the text on the new line with the symbol ". So the valid multi-line query in AnyLogic should look like:

selectUniqueValue(false, double.class, "SELECT processing_time "
"FROM processing_times WHERE part = agent.name;");

Select multiple values

List<T> selectValues(String sql, Object... params)<T> - The function executes specified SELECT SQL query (sql) and returns the list of produced values. Given sql query must return a single column. An empty list is returned for no results.

ResultSet selectResultSet(String sql, Object ... params) - The function executes specified SQL query (sql) and returns the produced results as JDBC result set.

QueryDSL. Cascading syntax style

In Query DSL, you call the function selectFrom() and use the cascading methods of the SQLQuery interface: where, groupBy, having, orderBy, join, etc.

The example:

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

If you need to add an additional operator for the select query, you put a dot, and then place the call of the corresponding cascading function like here we call where() to define the select clause, and then uniqueResult() to allow returning only one unique result, and do not accept several valid results returned.

To know how to write queries in Query DSL, please refer here for the SELECT query exampes.

There are also specific articles about the following SQL operators and their Query DSL analogs: ORDER BY, GROUP BY, WHERE, INSERT, UPDATE, DELETE.

Here is the quick reference for the cascading methods:

innerJoin, join, leftJoin, fullJoin, on : Define join elements using these constructs. For the join methods the first argument is the join source and the second the target (alias).

where : Define the query filters, either in varargs form separated via commas or cascaded via the and-operator.

groupBy : Define the group by arguments in varargs form.

having : Define the having filter of the "group by" grouping as an varags array of Predicate expressions.

orderBy : Define the ordering of the result as an varargs array of order expressions. Use asc() and desc() on numeric, string, and other comparable expression to access the OrderSpecifier instances.

limit, offset, restrict : Define the paging of the result. Limit for max results, offset for skipping rows and restrict for defining both in one call.

list : List values only for the table columns specified as this function's arguments

all : Return values from all columns of the queried database table

uniqueResult : Return unique result.

firstResult : Return first result for the given select query, or null if no result is found

Ordering (ORDER BY)

You can order the SELECT query results in ascending or descending order, based on one or several columns using the ORDER BY SQL operator.

You specify the names of the columns that should be used for ordering. For each column, you specify how you want the records to be sorted by adding ASC or DESC order specifier. ASC means sorting in ascending order. DESC means the descending order. In SQL, if nothing is specified, ascending order is assumed.

In the following example, we fetch the records from the employees database. We want to display employee's name, salary, and age. We sort the records by name (in ascending order), then we sort records by salaries (in descending order).

SELECT e.name, e.salary, e.age
FROM employees e
ORDER BY e.name ASC, c.salary DESC;

This query is equivalent to the following QueryDSL:

selectFrom(employees)
.orderBy(employees.name.asc(), employees.salary.desc())
.list(employees.name, employees.salary, employees.age);

In QueryDSL, you specify the sorting order by specifying asc(), or desc() for each column in the orderBy() list.

Grouping (GROUP BY)

You can group the SELECT query results, based on one or several columns using the GROUP BY SQL operator. After the GROUP BY operator, you specify the column name that possibly contains duplicate names. Performing grouping, you will have just one record for every value in the specified column. Typically you may use some SQL aggregate functions to display in other column(s) sum, max, min, or count for the grouped records.

In the SELECT statement, GROUP BY clause follows the WHERE condition and precedes the ORDER BY clause.

Assume we have awards table storing the data on FIFA Golden Ball award winners, and we want to count the awards for each player in the table:

year player
2010 Lionel Messi
2011 Lionel Messi
2012 Lionel Messi
2013 Cristiano Ronaldo
2014 Cristiano Ronaldo

We want to count the number of awards for each player, and will use grouping for this purpose. In SQL, grouping can be done in the following form:

SELECT player, COUNT(player) FROM awards GROUP BY player;

which is equivalent to the following QueryDSL:

selectFrom(awards)
.groupBy(awards.player)
.list(awards.player, awards.player.count());

The result will be:

Lionel Messi 3
Cristiano Ronaldo 2

WHERE clause

The WHERE clause can be used in SELECT, UPDATE and DELETE statements. Using the WHERE operator you specify the condition to identify the exact records to be selected / updated / deleted.

You specify a condition using the comparison operators:

Comparison operation

SQL operator

QueryDSL operator

Equal

=

eq()

Not equal

NOT

ne()

Greater than

>

gt()

Greater or equal

>=

goe()

Less than

<

lt()

Less or equal

<=

loe()

Using WHERE clause in SELECT query

In the following example, we fetch the name, country, and profit fields from the table factories where profit is greater than 1000:

SQL

SELECT name, country, profit FROM factories f WHERE f.profit > 1000;

QueryDSL

selectFrom(factories)
.where(factories.profit.goe(1000))
.list(factories.number, factories.country, factories.profit);

Combining clauses

You can combine several number of conditions using AND or OR operators.

In the next example, we added one more clause. We fetch data for the factories with profit greater than 1000 located in France only:

SQL

SELECT name, country, profit FROM factories f WHERE f.profit > 1000 AND country = 'France';

QueryDSL

selectFrom(factories)
.where(factories.profit.goe(1000).and(factories.country.eq("France")))
.list(factories.number, factories.country, factories.profit);


You can form complex conditions by combining AND and OR operators. To combine several clauses, use parenthesis. The next example demonstrates both AND or OR operators. Here we fetch all profitable factories from Netherlands, Belgium, and Luxembourg.

Let's select data from all table columns. To do this, we write SELECT * in SQL, and list() in QueryDSL.

SQL

SELECT * FROM factories f WHERE f.profit > 1000 AND (country = 'Netherlands' OR country = 'Belgium' OR country = 'Luxembourg');

QueryDSL

selectFrom(factories)
.where(
factories.profit.goe(1000).and(factories.country.eq("Netherlands").or(factories.country.eq("Belgium")).or(factories.country.eq("Luxembourg")))
.list();


Similarly, you use WHERE clause in UPDATE and DELETE statements.

INSERT query

The SQL INSERT INTO query is used to insert new data records into a database table.

Query example in SQL and QueryDSL syntax:

SQL

executeStatement("INSERT INTO eu (country, capital) VALUES ('Croatia', 'Zagreb')");

QueryDSL

insertInto(eu)
.columns(eu.country
, eu.capital)
.values(
"Croatia", "Zagreb")
.execute();

UPDATE query

The SQL UPDATE query is used to modify the existing record(s) in a database table.

Let's assume we are changing the name of the specific person in our actors database table.

The SQL syntax of UPDATE query is:

executeStatement("UPDATE actors SET name = 'Kaley Cuoco-Sweeting' WHERE name = 'Kaley Cuoco'");

Here we select specific records with the help of WHERE condition, and set new value 'Kaley Cuoco-Sweeting' in the name column for all fetched records. In WHERE clause, you can combine several conditions using AND or OR operators.

It is equivalent to the following function in QueryDSL:

update(actors)
.where(actors.name.eq(
"Kaley Cuoco"))
.set(actors.name, "Kaley Cuoco-Sweeting")
.execute();

Updating several values

It was a simple example. Let's show how to update several values at once.

Say, we want also to update the marital status. It is evident how to do this when it is defined as a String text, so now we will show how to update the value when it is defined as option of an option list. Assume the status in the model is defined as MaritalStatus option list with alternative options (SINGLE, MARRIED).

The queries will be:

update(actors)
.where(actors.name.eq(
"Kaley Cuoco"))
.set(actors.name, "Kaley Cuoco-Sweeting")
.set(actors.status, MARRIED)
.execute();

executeStatement("UPDATE actors SET name = ?, status = ? WHERE name = ?",
"Kaley Cuoco-Sweeting", MARRIED, "Kaley Cuoco");

DELETE query

The SQL DELETE query is used to delete the existing records from a database table. Using the WHERE clause you specify the condition to identify the records to be deleted. You can combine several number of conditions using AND or OR operators. Please refer here to know about WHERE clause syntax.

Delete statement examples:

SQL

executeStatement("DELETE FROM employees e WHERE e.age >= 60 AND e.gender = 'male'");

QueryDSL

deleteFrom(employees)
.where(
employees.age.goe(60).and(employees.gender.eq("male")))
.execute();

Deleting all records from the database table

To delete all the records from the table, use DELETE without any conditions:

SQL

executeStatement("DELETE FROM customers c");

QueryDSL

deleteFrom(customers)
.execute();