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) - 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