Writing database 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 QueryDSL.

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

• selectUniqueValue(String sql, Object... params) — This function executes the specified SELECT SQL query (sql) and returns the unique value or null if the query returns no values. If there are multiple values that satisfy the SELECT condition, an error is returned. This function caches its results to speed up performance. Use selectUniqueValue(false, sql, params) to get a non-cached result each time.
• selectFirstValue(String sql, Object... params) — This function executes the specified SELECT SQL query (sql) and returns the first value returned or null if the query returns no values. This function will not throw an error if there are multiple valid values, it will just return the first value from the result set. It caches its results to speed up performance. Use selectFirstValue(false, sql, params) to get a non-cached result each time.

The example:

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

You can see that you are typing the common SQL query here.

Note that if you want to break the query into multiple lines (as shown below), you should end the line with the space symbol, then the quote symbol ", then the concatenation symbol +, and then start the text on the new line with the " symbol. This is what a valid multi-line query should look like in AnyLogic:

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

• List<T> selectValues(String sql, Object... params)<T> — This function executes the given SELECT SQL query (sql) and returns the list of values. The given SQL query must return a single column. An empty list will be returned if there are no results.
• ResultSet selectResultSet(String sql, Object... params) — This function executes the given SQL query (sql) and returns its results as a JDBC result set.

In QueryDSL, you call the function selectFrom() and use the cascading methods of the SQLQuery interface: where, groupBy, having, orderBy, join, and so on.

The example:

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

If you need to add an additional operator to the select query, put a period and then place the call to the corresponding cascading function, as here we call where() to define the select clause, and then uniqueResult() to allow returning only one unique result, and not accepting multiple valid results returned.

To learn how to write queries in QueryDSL, please see Use cases of reading data from AnyLogic database for the SELECT query examples.

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

Here is a quick reference to the cascading methods:

• innerJoin, join, leftJoin, fullJoin, on — Use these constructs to define join elements. For the join methods, the first argument is the join source and the second is the target (alias).
• where — Define the query filters, either in varargs form separated by commas, or cascaded using the and operator.
• groupBy — Define the GROUP BY arguments in varargs form.
• having — Define the having filter of the GROUP BY grouping as a varargs array of predicate expressions.
• orderBy — Define the order of the result as a varargs array of order expressions. Use asc() and desc() on numeric, string, and other similar expressions to access the OrderSpecifier instances.
• limit, offset, restrict — Specify the paging of the result. Use limit to set the maximum number of results, offset to skip rows, and restrict to define both in one call.
• list — List values only for the table columns specified as arguments to this function.
• all — Return values from all columns in the queried database table.
• uniqueResult — Return a unique result.
• firstResult — Return the first result for the given select query, or null if no result is found.

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

Specify the names of the columns to use for ordering. For each column, specify how the records should be sorted by adding the order specifier ASC or DESC. ASC means ascending order. DESC means descending order. If nothing is specified, SQL assumes ascending order.

In the following example, we will retrieve the records from the employees database. We want to display the employee’s name, salary, and age. We sort the records by name (in ascending order), then sort the records by salary (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 sort order by specifying asc(), or desc() for each column in the orderBy() list.

You can group the results of a SELECT query based on one or more columns by using the GROUP BY SQL operator. After the GROUP BY operator, you specify the column name, which may contain duplicate names. When you perform the grouping, you have only one record for each value in the specified column. Typically, you can use some SQL aggregate functions to display the sum, maximum, minimum, or count for the grouped records in other columns.

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

For example, suppose we have an awards table that stores data about the winners of the FIFA Golden Ball award, and we want to count the awards for each player in the table:

We want to count the number of awards for each player, and will use grouping to do so. In SQL, grouping can be done like this:

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:

The WHERE clause can be used in SELECT, UPDATE, and DELETE statements. Use the WHERE operator to specify the condition that identifies the exact records to select, update, or delete.

Use the relational operators to specify a condition:

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

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

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

You can combine multiple conditions using AND or OR operators.

• AND operator gets a record if both the first condition AND the second condition are true
• OR operator gets a record if either the first condition OR the second condition is true

In the next example, we have added another condition. We retrieve data for the factories with profit greater than 1000 located in France only:

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

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

You can build complex expressions by combining AND and OR operators. To combine multiple expressions, use parentheses. The next example demonstrates both AND and OR operators. Here we get all profitable factories from the Netherlands, Belgium, and Luxembourg.

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

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

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 can use the WHERE clause in UPDATE and DELETE statements.

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

Example query in SQL and QueryDSL syntax:

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

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

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

Let’s say we want to change the name of the specific person in our actors database table.

The SQL syntax for the UPDATE query is:

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

Here, we select certain records using the WHERE condition and set the new value 'Kaley Cuoco-Sweeting' in the name column for all the records we retrieve. In the WHERE clause, you can combine multiple 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();

That was a simple example. Let’s show you how to update multiple values at once.

Say we also want to update the marital status. It is obvious 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 an option of an option list. Suppose the status in the model is defined as MaritalStatus option list with alternate 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");

The SQL DELETE query is used to delete the existing records from a database table. Use the WHERE clause to specify the condition that identifies the records to be deleted. You can combine multiple number of conditions using AND or OR operators. For more information about the syntax of the WHERE clause, see WHERE clause.

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

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

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

executeStatement("DELETE FROM customers c");

deleteFrom(customers)
  .execute();

The QueryDSL API used by the AnyLogic database provides a convenient way to handle large numbers of database queries in a form of query batching. Batching combines multiple queries into one. This query is then executed, moving around all the necessary data at once, which in turn greatly increases the performance of the model.

To batch queries, use the subclasses of the QueryDSL AbstractSQLClause class. Each of these subclasses groups specific types of queries: there is a subclass for insert queries, a subclass for delete queries, and so on.

In the following example, we will use the SQLInsertClause class to create a batch of insert queries.

To create an instance of this class, use the following constructor:

SQLInsertClause batch = new SQLInsertClause(Connection connection, SQLTemplates template, RelationalPath path);

Let’s take a closer look at the arguments:

• connection — Specifies the database connection we are accessing. In the case of the internal database of the AnyLogic model, it can be retrieved with the getDatabaseConnection function.
• template — Specifies the database template. The internal database uses SQLTemplates.DEFAULT.
• path — Specifies the path to the target entity within the database, that is, a table or a column name.

When the batch is created, you must populate it with queries. The easiest way to do this is to use a combination of set(Path path, T value) and addBatch() functions:

batch.set(my_table.first_column, 2).set(my_table.second_column, 3).addBatch()

As you can see, it’s quite simple: just specify the necessary bindings as arguments to the set() function and add them to the batch.

Consider the following example:

  for ( int i = 0; i < 100; i++ ) {
    SQLInsertClause batch = new SQLInsertClause(  getDatabaseConnection(), SQLTemplates.DEFAULT, my_table );  // Creating a batch for the table named "table_name" 
    for ( int j = 0; j < 10000; j++ ) {
      insert.set(my_table.first_column, "key").set(my_table.second_column, "value").addBatch(); // Fill the batch with identical requests using a simple for loop
      // The "key" record is added to the first column of the table, and the "value" record is added to the second column
    } 
  insert.execute(); // Execute the batch of queries
}

Use the same approach to create batches of the update, merge, and delete queries, using the corresponding subclasses of AbstractSQLClause.

How can we improve this article?

Send