In AnyLogic, you typically construct SELECT queries using the handy visual query builder.
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.
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.
Note that if you want to break the query in multiple lines (as shown below), you should end the 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;");
- 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.
In Query DSL, you call the function selectFrom() and use the cascading methods of the SQLQuery interface: where, groupBy, having, orderBy, join, etc.
(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 see Use cases of reading data from AnyLogic database for the SELECT query examples.
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 varargs 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.
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.
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:
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:
You specify a condition using the comparison operators:
|Comparison operation||SQL operator||QueryDSL operator|
|Greater or equal||>=||goe()|
|Less or equal||<=||loe()|
In the following example, we fetch the name, country, and profit fields from the table factories where profit is greater than 1000:
You can combine several number of conditions using AND or OR operators.
- AND operator fetches a record if both the first condition AND the second condition are true
- OR operator fetches a record if either the first condition OR the second condition is true
In the next example, we added one more clause. We fetch data for the factories with profit greater than 1000 located in France only:
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.
The SQL INSERT INTO query is used to insert new data records into a database table.
Query example in SQL and QueryDSL syntax:
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();
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");
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 to WHERE clause to learn about WHERE clause syntax.
To delete all the records from the table, use DELETE without any conditions:
How can we improve this article?