Database API

AnyLogic Professional provides the user with a set of tools (Insert, Update, Query, Key-Value Table), making it easier to work with databases. Using them, you will no longer need to write SQL queries and expressions yourself — now you can easily construct them using the listed above elements.

However, if you are AnyLogic University Researcher or AnyLogic PLE user, all this functionality is still available, but in that case you will have only Database tool at your hand. You should create Database element, connect it to the actual database and then work with this database with the help of the functions listed below.

Functions

Function	Description
public boolean connect()	The function connects to a data source, specified by constructor parameters. The connect operation is performed only if the object has no connection, otherwise it does nothing. Returns true if the connection is set properly or already exists, false otherwise.
public void disconnect()	Disconnects the currently connected database. This function call has no effect if this Database object is not connected. It is strongly recommended that the user explicitly commits or rolls back an active transaction (if one has been explicitly opened with Connection.setAutoCommit(boolean)) before to calling this function. If this function is called and there is an active transaction, the results are generally unpredictable. If an error occurs, RuntimeException is thrown.
public java.sql.Connection getConnection()	Returns existing Connection to the database or connects to the database if not already connected. If an error occurs, it throws RuntimeException.
public ResultSet getResultSet(String sqlQuery)	Executes the specified SQL query (sqlQuery) and returns the result as a result set (or null if an error occurs). Make sure to call the ResultSet.close() function after all necessary operations are completed.
public boolean modify(String sqlQuery)	Executes the specified SQL query (sqlQuery). The function is intended for SQL queries that modify data in the data source. Returns Boolean value indicating whether the query execution was successful.
public Integer getFieldType(String sTableName, String sFieldName)	Returns the SQL type of the specified field or null on error. sTableName — Table name. sFieldName — Field name.
public String getValue(String sqlQuery)	Executes the specified SQL query (sqlQuery) and returns the value. The function is intended for SQL queries that return only one value (for example, SQL aggregate functions — MAX, MIN, and so on). Returns the value produced by the query or null if an error occurs or the query does not return any value.
public java.util.Map<java.lang.string,java.lang.string> getRow(String sqlQuery)	Executes the specified SQL query (sqlQuery) and returns the value, or null if an error occurs or the query does not return any value. The function is designed for SQL queries that return a single row. The result is returned as a Map containing {"Field name"; "Field value"} pairs of strings.
public Object getMatrix(String sqlQuery, String type)	Executes the specified SQL query and returns the values as a 2D array of values of the specified type, or null if an error occurs, or if the query does not return any value, or if the specified type is invalid. The function supports all Java primitive types and String.
public ResultSet getTableResultSet(String tableName, String listOfFields, String keyField, String keyFieldValue)	Returns the result set produced by retrieving data from specified fields and rows of specified table. Make sure to call the ResultSet.close() function after all necessary operations are completed. tableName — Name of the table. listOfFields — List of fields (ignored if null). keyField — Key field name (ignored if null or if sListOfFields is null). keyFieldValue — The value of the key field (ignored if null or if sListOfFields is null).
public ResultSet getQueryResultSet(String queryText, String listOfFields, String keyField, String keyFieldValue)	Returns the result set produced by retrieving data from specified fields and rows of specified query. Make sure to call the ResultSet.close() function after all necessary operations are completed. queryText — Text of the query. listOfFields — List of fields (ignored if null). keyField — Key field name (ignored if null or if sListOfFields is null). keyFieldValue — The value of the key field (ignored if null or if sListOfFields is null).

Database functions that retrieve the result sets (getResultSet(), getTableResultSet(), getQueryResultSet()) present them in the form of the ResultSet interface.

This is a built-in Java interface that exposes a set of functions that allow you to interact with the data it holds.

Let’s say we have a Database object that represents the database containing a PEOPLE table, which in turn stores records about, say, the employees of a company.

The simplest way to get the ResultSet interface from this table is as follows:

ResultSet peopleTable = Database.getTableResultSet("PEOPLE");

The resulting peopleTable object will contain the entire table. By default, it’s not updatable and has a “cursor” pointing to the first row of the table. To iterate through the rows, use the next() function:

peopleTable.next();

When the «cursor» is on a row, you can retrieve values from that row using the available getter functions:

String name = peopleTable.getString("NAME"); // Returns the person’s name in the string form from the «NAME» column
int age = peopleTable.getInt(3); // Returns the person’s age in the integer form from the third column 
int serialNumber = peopleTable.getRow(); // Returns the row number

As we have mentioned earlier, the default ResultSet interface is a read-only representation of the table. However, it is possible to create an interface that accepts new values and inserts them into the table. To do this, the statement you use to get the ResultSet object should have its type specified as CONCUR_UPDATABLE.

For more information about the available functions of the ResultSet interface, how to update it, and how to insert new records, see the official Java documentation: ResultSet interface.

How can we improve this article?

Send