java.lang.Object
- com.anylogic.engine.connectivity.ConnectivityBase
- - com.anylogic.engine.connectivity.Database

All Implemented Interfaces:: com.anylogic.engine.internal.Child, java.io.Serializable

public final class Database
extends ConnectivityBase
implements com.anylogic.engine.internal.Child

Database connection manager class

Author:: AnyLogic North America, LLC https://anylogic.com
See Also:: Serialized Form

Field Summary

Fields
Modifier and Type	Field	Description
`static java.lang.String`	`DRIVER_EXCEL_SQLSHEET`	Excel driver
`static java.lang.String`	`DRIVER_JDBC_MICROSOFT_SQLSERVER`	Microsoft SQL Server driver for Java External jar-file is needed to use this driver Driver may be downloaded from www.microsoft.com (use search by `"SQL Server Driver for JDBC"`)
`static java.lang.String`	`DRIVER_JTDS_JDBC`	Open-source JTDS-JDBC driver External jar-file is needed to use this driver Driver may be downloaded from jtds.sourceforge.net (see `"Download"` section)
`static java.lang.String`	`DRIVER_UCANACCESS`	Pure Java JDBC driver

Constructor Summary

Constructors
Constructor	Description
`Database(Presentable owner, java.lang.String name, java.lang.String fileName)`	Creates new `Database` object for interaction with file-based (Access or Excel) database
`Database(Presentable owner, java.lang.String name, java.lang.String fileName, java.lang.String login, char[] password)`	Creates new `Database` object for interaction with file-based (Access or Excel) database
`Database(Presentable owner, java.lang.String name, java.lang.String jdbcDriver, java.lang.String connectionURL, java.lang.String login, char[] password)`	Creates new `Database` object for interaction with database
`Database(Presentable owner, java.lang.String name, java.lang.String sqlJdbcDriver, java.lang.String host, java.lang.String databaseName, java.lang.String login, char[] password)`	Creates new `Database` object for interaction with MS SQL Server database

Method Summary

All Methods Static Methods Instance Methods Concrete Methods Deprecated Methods
Modifier and Type	Method	Description
`void`	`closeResultSet(ResultSet rs)`	Deprecated. call `ResultSet.close()` method instead
`boolean`	`connect()`	The method connects a data source, specified by constructor parameters.
`void`	`disconnect()`	Disconnects currently connected database This method call has no effect if this `Database` object is not connected It is strongly recommended that user explicitly commits or rolls back an active transaction (if any has been explicitly opened via `Connection.setAutoCommit(boolean)`) prior to calling the this method.
`java.sql.Connection`	`getConnection()`	Returns existing `Connection` to the database or connects to the database if not connected If any error occurs, throws `RuntimeException`
`DatabaseDescriptor`	`getDbDescriptor()`
`java.lang.Integer`	`getFieldType(java.lang.String sTableName, java.lang.String sFieldName)`	The methods returns SQL-type of specified field.
`java.lang.Object`	`getMatrix(java.lang.String sqlQuery, java.lang.String type)`	The method executes specified SQL query and returns the produced values as 2D array of values of specified type.
`ResultSet`	`getQueryResultSet(java.lang.String queryText, java.lang.String listOfFields, java.lang.String keyField, java.lang.String keyFieldValue)`	The method returns result set produced by querying data from specified fields and rows of specified query.
`ResultSet`	`getResultSet(java.lang.String sqlQuery)`	The method executes specified SQL query and returns the produced result as `ResultSet` object.
`java.util.Map<java.lang.String,java.lang.String>`	`getRow(java.lang.String sqlQuery)`	The method executes specified SQL query and returns the produced values.
`static java.sql.ResultSet`	`getSQLResultSet(ResultSet rs)`
`static java.sql.Statement`	`getSQLStatement(Statement statement)`	Returns Java SQL `Statement`, backed by given `Statement` object
`Statement`	`getStatement()`	The method creates and returns each time new statement Please call `Statement.close()` method after all needed operations are completed
`ResultSet`	`getTableResultSet(java.lang.String tableName, java.lang.String listOfFields, java.lang.String keyField, java.lang.String keyFieldValue)`	The method returns result set produced by querying data from specified fields and rows of specified table.
`java.lang.String`	`getValue(java.lang.String sqlQuery)`	The method executes specified SQL query and returns the produced value.
`boolean`	`modify(java.lang.String sqlQuery)`	The method executes specified SQL query.
`void`	`releaseStatement(Statement statement)`	Deprecated. call `Statement.close()` method instead
`void`	`restoreOwner(java.lang.Object owner)`	Deprecated.
`void`	`setExcelStreamingMode(DatabaseExcelStreamingMode mode)`	Sets excel streaming mode to improve speed while working with large excel files DatabaseExcelStreamingMode.READ allows to read huge collection of data a bit faster Reconnects database
`java.lang.String`	`toString()`	Returns formatted database connection properties (excluding password value)

Methods inherited from class com.anylogic.engine.connectivity.ConnectivityBase

destroy, getName

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

DRIVER_JDBC_MICROSOFT_SQLSERVER

public static final java.lang.String DRIVER_JDBC_MICROSOFT_SQLSERVER

Microsoft SQL Server driver for Java
External jar-file is needed to use this driver
Driver may be downloaded from www.microsoft.com (use search by "SQL Server Driver for JDBC")

See Also:: Constant Field Values

DRIVER_JTDS_JDBC

public static final java.lang.String DRIVER_JTDS_JDBC

Open-source JTDS-JDBC driver
External jar-file is needed to use this driver
Driver may be downloaded from jtds.sourceforge.net (see "Download" section)

See Also:: Constant Field Values

DRIVER_UCANACCESS

public static final java.lang.String DRIVER_UCANACCESS

Pure Java JDBC driver

See Also:: Constant Field Values

DRIVER_EXCEL_SQLSHEET

public static final java.lang.String DRIVER_EXCEL_SQLSHEET

Excel driver

See Also:: Constant Field Values

Constructor Detail

Database

public Database(Presentable owner,
                java.lang.String name,
                java.lang.String jdbcDriver,
                java.lang.String connectionURL,
                java.lang.String login,
                char[] password)

Creates new Database object for interaction with database

Parameters:: owner - this object's owner (agent or experiment); name - object name (decoration purpose); jdbcDriver -; connectionURL -; login -; password - the password is copied to internal variable, then given array is wiped

Database

public Database(Presentable owner,
                java.lang.String name,
                java.lang.String fileName)

Creates new Database object for interaction with file-based (Access or Excel) database

Parameters:: owner - this object's owner (agent or experiment); name - object name (decoration purpose); fileName - file name with extension .xls or .mdb

Database

public Database(Presentable owner,
                java.lang.String name,
                java.lang.String fileName,
                java.lang.String login,
                char[] password)

Creates new Database object for interaction with file-based (Access or Excel) database

Parameters:: owner - this object's owner (agent or experiment); name - object name (decoration purpose); fileName - file name with extension .xls or .mdb; login -; password -

Database

public Database(Presentable owner,
                java.lang.String name,
                java.lang.String sqlJdbcDriver,
                java.lang.String host,
                java.lang.String databaseName,
                java.lang.String login,
                char[] password)

Creates new Database object for interaction with MS SQL Server database

Parameters:

owner - this object's owner (agent or experiment)

name - object name (decoration purpose)

sqlJdbcDriver - one of

#DRIVER_SUN_JDBC_ODBC
DRIVER_JDBC_MICROSOFT_SQLSERVER
DRIVER_JTDS_JDBC

host -

databaseName -

login - user name or null for Windows authentication

password - password or null for Windows authentication
the password is copied to internal variable, then given array is wiped

Method Detail

toString

public java.lang.String toString()

Returns formatted database connection properties (excluding password value)

Overrides:: toString in class ConnectivityBase

disconnect

public void disconnect()

Disconnects currently connected database
This method call has no effect if this Database object is not connected
It is strongly recommended that user explicitly commits or rolls back an active transaction (if any has been explicitly opened via Connection.setAutoCommit(boolean)) prior to calling the this method. If this method is called and there is an active transaction, the results are generally unpredictable.
If any error occurs, throws RuntimeException

getConnection

public java.sql.Connection getConnection()

Returns existing Connection to the database or connects to the database if not connected
If any error occurs, throws RuntimeException

Returns:: database connection, never null

connect

public boolean connect()

The method connects a data source, specified by constructor parameters. The connection process is performed only if the object has no connection, otherwise it do nothing.

Returns:: true if connection is set properly or already exists, false otherwise.

getStatement

public Statement getStatement()

The method creates and returns each time new statement
Please call Statement.close() method after all needed operations are completed

Returns:: statement

getValue

public java.lang.String getValue(java.lang.String sqlQuery)

The method executes specified SQL query and returns the produced value. The method is designed for SQL queries that return only one value (for example, SQL aggregate functions - MAX, MIN, and so on).

Parameters:: sqlQuery - SQL query to be executed
Returns:: the value produced by the query or null if an error occurs or if the query produces no values

getRow

public java.util.Map<java.lang.String,java.lang.String> getRow(java.lang.String sqlQuery)

The method executes specified SQL query and returns the produced values. The method is designed for SQL queries that return only one row. Results are returned as a Map that contains {"Field name"; "Field value"} String pairs.

Parameters:: sqlQuery - SQL query to be executed
Returns:: Map that contains {"Field name"; "Field value"} String pairs or null if an error occurs or if the query produces no values

getMatrix

public java.lang.Object getMatrix(java.lang.String sqlQuery,
                                  java.lang.String type)

The method executes specified SQL query and returns the produced values as 2D array of values of specified type. The method supports all Java primitive types and String.

Parameters:

sqlQuery - SQL query to be executed

type - one of:

"byte"
"short"
"int"
"long"
"float"
"double"
"boolean"
"String"

Returns:

the values produced by the query or null if an error occurs or if the query produces no values or if specified type is invalid

getTableResultSet

public ResultSet getTableResultSet(java.lang.String tableName,
                                   java.lang.String listOfFields,
                                   java.lang.String keyField,
                                   java.lang.String keyFieldValue)

The method returns result set produced by querying data from specified fields and rows of specified table.

Parameters:: tableName - Table name; listOfFields - List of fields (ignored if null); keyField - Name of the key field (ignored if null or if sListOfFields is null); keyFieldValue - Value of the key field (ignored if null or if sListOfFields is null)
Returns:: result set produced by querying data from specified fields and rows of specified table.
Please call ResultSet.close() method after all needed operations are completed

getQueryResultSet

public ResultSet getQueryResultSet(java.lang.String queryText,
                                   java.lang.String listOfFields,
                                   java.lang.String keyField,
                                   java.lang.String keyFieldValue)

The method returns result set produced by querying data from specified fields and rows of specified query.

Parameters:: queryText - Query text; listOfFields - List of fields (ignored if null); keyField - Name of the key field (ignored if null or if sListOfFields is null); keyFieldValue - Value of the key field (ignored if null or if sListOfFields is null)
Returns:: result set produced by querying data from specified fields and rows of specified query.
Please call ResultSet.close() method after all needed operations are completed

getResultSet

public ResultSet getResultSet(java.lang.String sqlQuery)

The method executes specified SQL query and returns the produced result as ResultSet object.

Parameters:: sqlQuery - SQL query to be executed
Returns:: ResultSet produced by specified query or null if an error occurs
Please call ResultSet.close() method after all needed operations are completed

modify

public boolean modify(java.lang.String sqlQuery)

The method executes specified SQL query. The method is designed for SQL queries that modify data in the data source.

Parameters:: sqlQuery - SQL query to be executed
Returns:: boolean value that indicates whether the query execution completed successfully

getFieldType

public java.lang.Integer getFieldType(java.lang.String sTableName,
                                      java.lang.String sFieldName)

The methods returns SQL-type of specified field.

Parameters:: sTableName - Table name; sFieldName - Field name
Returns:: type of specified field or null if an error occurs

restoreOwner

@AnyLogicInternalCodegenAPI
@Deprecated
public void restoreOwner(java.lang.Object owner)

Deprecated.

This method normally should not be called by user
This method restores owner of this object
The method is used in snapshot saving/loading

Specified by:: restoreOwner in interface com.anylogic.engine.internal.Child
Parameters:: owner - owner of this object, usually Agent, Experiment or ShapeGroup

releaseStatement

@Deprecated
public void releaseStatement(Statement statement)

Deprecated.

call Statement.close() method instead

Releases Statement
This method should be called by user, previously called getStatement()

Parameters:: statement - a Statement previously obtained through getStatement()
See Also:: getStatement()

closeResultSet

@Deprecated
public void closeResultSet(ResultSet rs)

Deprecated.

call ResultSet.close() method instead

Closes given ResultSet, obtained using preceding getTableResultSet(String, String, String, String), getQueryResultSet(String, String, String, String) or getResultSet(String) call
Also all opened ResultSets are closed automatically on this Database destroy

Parameters:: rs -

getSQLStatement

public static java.sql.Statement getSQLStatement(Statement statement)

Returns Java SQL Statement, backed by given Statement object

Parameters:: statement - the Statement object, obtained by getStatement()
Returns:: Java SQL Statement, used in the given statement

getSQLResultSet

public static java.sql.ResultSet getSQLResultSet(ResultSet rs)

setExcelStreamingMode

public void setExcelStreamingMode(DatabaseExcelStreamingMode mode)

Sets excel streaming mode to improve speed while working with large excel files DatabaseExcelStreamingMode.READ allows to read huge collection of data a bit faster Reconnects database

Parameters:: mode - streaming mode, NONE for default mode

getDbDescriptor

@AnyLogicInternalAPI
public DatabaseDescriptor getDbDescriptor()

How can we improve this article?

Class Database

Field Summary

Constructor Summary

Method Summary

Methods inherited from class com.anylogic.engine.connectivity.ConnectivityBase

Methods inherited from class java.lang.Object

Field Detail

DRIVER_JDBC_MICROSOFT_SQLSERVER

DRIVER_JTDS_JDBC

DRIVER_UCANACCESS

DRIVER_EXCEL_SQLSHEET

Constructor Detail

Database

Database

Database

Database

Method Detail

toString

disconnect

getConnection

connect

getStatement

getValue

getRow

getMatrix

getTableResultSet

getQueryResultSet

getResultSet

modify

getFieldType

restoreOwner

releaseStatement

closeResultSet

getSQLStatement

getSQLResultSet

setExcelStreamingMode

getDbDescriptor