How to Connect using JDBC Driver

You can connect to and execute queries against SnappyData cluster using JDBC driver. The connection URL typically points to one of the locators. The locator passes the information of all available servers, based on which the driver automatically connects to one of the servers.

To connect to the SnappyData cluster using JDBC, use URL of the form jdbc:snappydata://<locatorHostName>:<locatorClientPort>/

Where the <locatorHostName> is the hostname of the node on which the locator is started and <locatorClientPort> is the port on which the locator accepts client connections (default 1527).

Dependencies: Use the Maven/SBT dependencies for the latest released version of SnappyData.

Example: Maven dependency

<!-- https://mvnrepository.com/artifact/io.snappydata/snappydata-store-client -->
<dependency>
    <groupId>io.snappydata</groupId>
    <artifactId>snappydata-jdbc_2.11</artifactId>
    <version>1.0.2.1</version>
</dependency>

Example: SBT dependency

// https://mvnrepository.com/artifact/io.snappydata/snappydata-store-client
libraryDependencies += "io.snappydata" % "snappydata-jdbc_2.11" % "1.0.2.1"

Note

If your project fails when resolving the above dependency (that is, it fails to download javax.ws.rs#javax.ws.rs-api;2.1), it may be due an issue with its pom file.
As a workaround, add the below code to the build.sbt:

val workaround = {
  sys.props += "packaging.type" -> "jar"
  ()
}

For more details, refer https://github.com/sbt/sbt/issues/3618.

Code Example

Connect to a SnappyData cluster using JDBC on default client port

The code snippet shows how to connect to a SnappyData cluster using JDBC on default client port 1527. The complete source code of the example is located at JDBCExample.scala

val url: String = s"jdbc:snappydata://localhost:1527/"
val conn1 = DriverManager.getConnection(url)

val stmt1 = conn1.createStatement()
// Creating a table (PARTSUPP) using JDBC connection
stmt1.execute("DROP TABLE IF EXISTS APP.PARTSUPP")
stmt1.execute("CREATE TABLE APP.PARTSUPP ( " +
     "PS_PARTKEY     INTEGER NOT NULL PRIMARY KEY," +
     "PS_SUPPKEY     INTEGER NOT NULL," +
     "PS_AVAILQTY    INTEGER NOT NULL," +
     "PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL)" +
    "USING ROW OPTIONS (PARTITION_BY 'PS_PARTKEY')")

// Inserting records in PARTSUPP table via batch inserts
val preparedStmt1 = conn1.prepareStatement("INSERT INTO APP.PARTSUPP VALUES(?, ?, ?, ?)")

var x = 0
for (x <- 1 to 10) {
  preparedStmt1.setInt(1, x*100)
  preparedStmt1.setInt(2, x)
  preparedStmt1.setInt(3, x*1000)
  preparedStmt1.setBigDecimal(4, java.math.BigDecimal.valueOf(100.2))
  preparedStmt1.addBatch()
}
preparedStmt1.executeBatch()
preparedStmt1.close()

Note

If the tool does not automatically select a driver class, you may have the option of selecting a class from within the JAR file. In this case, select the io.snappydata.jdbc.ClientDriver class.

Connecting with JDBC Client Pool Driver

JDBC client pool driver provides built-in connection pooling and relies on the non-pooled JDBC driver. The driver initializes the pool when the first connection is created using this driver. Thereafter, for every request, the connection is returned from the pool instead of establishing a new connection with the server. We recommend using the pooled driver for low latency operations such as point lookups and when using the Spark JDBC data source API (see example below). When you access SnappyData from Java frameworks such as Spring, we recommend using pooling provided in the framework and switch to using the non-pooled driver.

Important

The underlying pool is uniquely associated with the set of properties that are passed while creating the connection. If any of the properties change, a new pool is created.

To connect to SnappyData Cluster using JDBC client pool driver, use the url of the form:
jdbc:snappydata:pool://<host>:<port>
Where <host> is the hostname of the node on which the locator is started and <port> is the port on which the locator accepts client connections (default 1527).

The client pool driver class name is io.snappydata.jdbc.ClientPoolDriver.

The following pool related properties can be used to tune the JDBC client pool driver:

Property Description
pool.user The username to be passed to the JDBC client pool driver to establish a connection.
pool.password The password to be passed to the JDBC client pool driver to establish a connection.
pool.initialSize The initial number of connections that are created when the pool is started. Default value is max(256, availableProcessors * 8).
pool.maxActive The maximum number of active connections that can be allocated from this pool at a time. The default value is max(256, availableProcessors * 8).
pool.minIdle The minimum number of established connections that should be maintained in the client pool. Default value is 1.
pool.maxIdle The maximum number of connections that should be maintained in the client pool. Default value is maxActive:max(256, availableProcessors * 8). Idle connections are checked periodically, if enabled, and the connections that are idle for more than the time set in minEvictableIdleTimeMillis are released.
pool.maxWait (int) The maximum waiting period, in milliseconds, for establishing a connection after which an exception is thrown. Default value is 30000 (30 seconds).
pool.removeAbandoned Flag to remove the abandoned connections, in case they exceed the settings for removeAbandonedTimeout. If set to true a connection is considered abandoned and eligible for removal, if its no longer in use than the settings for removeAbandonedTimeout. Setting this to true can recover db connections from applications that fail to close a connection. The default value is false.
pool.removeAbandonedTimeout Timeout in seconds before an abandoned connection, that was in use, can be removed. The default value is 60 seconds. The value should be set to the time required for the longest running query in your applications.
pool.timeBetweenEvictionRunsMillis Time period required to sleep between runs of the idle connection validation/cleaner thread. You should always set this value above one second. This time period determines how often we check for idle and abandoned connections and how often to validate the idle connections. The default value is 5000 (5 seconds).
pool.minEvictableIdleTimeMillis The minimum time period, in milliseconds, for which an object can be idle in the pool before it qualifies for eviction. The default value is 60000 (60 seconds).
driver io.snappydata.jdbc.ClientPoolDriver
This should be passed through Spark JDBC API for loading and using the driver.
pool.testOnBorrow Indicates if the objects are validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and will attempt to borrow another. In order to have a more efficient validation, see pool.validationInterval. Default value is true.
pool.validationInterval Avoid excess validation, only run validation at most at this frequency - time in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The default value is 10000 (10 seconds).

Example Code Snippet:

val properties = new Properties()
properties.setProperty("pool.user", "user")
properties.setProperty("pool.password", "pass")
properties.setProperty("driver", ““io.snappydata.jdbc.ClientPoolDriver””)

val builder = SparkSession
.builder.
appName("app")
.master("local[*]")

val spark: SparkSession = builder.getOrCreate

val df = spark.read.jdbc(“jdbc:snappydata:pool://localhost:1527”, "Table_X", properties)

Limitations

If you set any of the following properties for a pooled connection, it gets auto-reset to the default values whenever you obtain a new pooled connection.

  • setAutoCommit
  • setTransactionIsolation
  • setReadOnly

However, if you have set any of the other properties (e.g. spark or snappy AQP related properties), it does not get auto-reset when you obtain a new pooled connection.