# The eXtremeSQL JDBC API

This topic describes the eXtremeSQL JDBC Driver implementation and how to use the JDBC driver to connect to existing eXtremeDB databases.

The eXtremeSQL JDBC driver provides access for Java applications to the eXtremeSQL server using a widely applicable industry connectivity standard. The eXtremeSQL JDBC driver is a pure-java 'Type 3' driver designed for JDK/JRE 1.6.x that interacts with the eXtremeSQL server-side middle-ware that then accesses the eXtremeDB database.

The JDBC driver is very similar to the ODBC driver working in remote SQL mode. It actually implements the client-side of a remote SQL protocol in pure-java and translates JDBC API calls into requests to the eXtremeSQL server. This is similar to the effect of using the xSQL utility as a remote SQL server or using a custom C or C++ application with the RemoteSqlEngine API as demonstrated in SDK sample `api_sql_10_rsql_client`. However, unlike the ODBC driver, the JDBC driver needs no installation step. It is implemented in the Java archive file `extremedb_jdbc.jar` in the`target/bin` directory.

## Examples

### SimpleJDBC

> The SDK sample `SimpleJDBC` demonstrates how a client application connects to the eXtremeSQL JDBC driver and issues simple SQL statements. The connection string syntax is:

>         jdbc:extremedb:host:port:connection_timeout:datetime_precision:modes_mask:trace_file

> where the components are as follows:

> - `host:port` - the IP address and port number of the xSQL server or `SqlServer` application (the default is `localhost:5001`)
> - `connection_timeout` - the database connection timeout (the default is `90000`)
> - `datetime_precision` - the datetime resolution for date/time fields (the default is `1` which means seconds)
> - `modes_mask` - the compatibility modes(the default is 0; possible values are 1 for "mimic PostgeSQL", which is necessary for the WebFocus business edition)
> - trace_file - the filename for tracing output (default is no tracing)

> It is possible to specify multiple SQL servers via the JDBC URL that enables the connection to switch between these servers without the need to explicitly reconnect. The JDBC driver is connection can be switched in two ways:

> - automatically when a connection error occurs; the JDBC driver will try to connect and execute the next SQL statement using the next server in a specified list;
> - by explicitly specifying the zero-based identifier of a server with command `set_server Id`'.

> One important application of this functionality could be to switch between an HA_Overview.htm master and replica application, or between cluster nodes.

> Some examples of different `url` specifications follow:

>         jdbc:extremedb:localhost:9001:90000:1000:1

> This sets the server address to `localhost` on port `9001`, with `connection_timeout` of `90` seconds, datetime resolution in milliseconds and with the "mimic PostgeSQL" option enabled.

>         jdbc:extremedb:localhost

> This sets the server address to `localhost` on default port `5001`, with default `connection_timeout` of `90` seconds, and default datetime resolution in seconds.

>         jdbc:extremedb:localhost:::1000

> This sets the server address to `localhost` on default port `5001`, with default `connection_timeout` of `90` seconds, and datetime resolution in milliseconds.

>         jdbc:extremedb:192.168.49.1:8301::::trace.dbg

> This sets the server address to `192.168.49.1` on port `8301`, with default `connection_timeout` of `90` seconds, default datetime resolution in seconds and tracing output to file `trace.dbg`.

> Alternatively, it is possible to specify driver properties (`property_name = value` tuples) in the driver manager for JDBC-enabled utilities. Custom JDBC driver manager programs should use the DriverManager method `getConnection(String url, Properties info)` to obtain the Properties class instance `info`; then set the following properties in the `info` object: `connection_timeout`, `datetime_precision`, `modes_mask` and `trace_file`. Also, the driver property `encoding` can be set; it is `UTF-8` by default. When specified in the connection `url`, any of these connection parameters override the driver property in the configuration file.

### JDBCBatch

> The SDK sample `JDBCBatch` demonstrates batch processing with JDBC.

## JDBC and eXtremeDB High Availability

When using JDBC with eXtremeDB High Availability it is possible to specify multiple SQL servers via the JDBC URL in order to be able to switch between these servers within a single connection and without any additional re-connect procedure. This capability can be used to switch between eXtremeDB High Availability master and replica applications or between nodes in an eXtremeDB Cluster network. The current server the JDBC driver is connected to can be switched in two ways:

- automatically when a connection error occurs: the JDBC driver will try to connect and execute the next SQL statement using the next server in a list; or
- by specifying the identifier (zero-based) of a server in the list by executing command `set_server Id`.

The list of servers is specified by adding a semicolon delimiter between host:port pairs in the URL. For example:

        jdbc:extremedb:host1:port1;host2:port2;host3:port3

## Data Compression with JDBC

The JDBC driver supports data compression by setting the property "compression_level". Valid values for the compression level are 0 through 9: 0 = no compression; 1 = best speed, 9 - best compression.

For example:

        java.util.Properties props = new java.util.Properties();
        props.setProperty("compression_level", "9");
        con = DriverManager.getConnection(url, props);