Skip to main content

SingleStoreDB v8.0

The SingleStore JDBC Driver

The SingleStore JDBC driver can be used to connect your SingleStore and MySQL databases to Java-based applications, like BI and ETL tools. It is a Type 4 JDBC 4.2 compatible driver licensed under GNU LGPL.

Driver Compatibility

The SingleStore JDBC connector is compatible with:

  • SingleStoreDB v7.1+

  • Java 11 and Java 8

  • JDBC 4.2

Note

To use the SingleStore JDBC driver in external applications, specify the JDBC Driver Class as com.singlestore.jdbc.Driver

Prerequisites

To build the SingleStore JDBC driver from source, you'll need:

Note

Maven is not an absolute requirement for using the driver with other applications.

Download and Install the SingleStore JDBC Driver

You can install the SingleStore JDBC driver with any of the following methods.

Note

To find the latest version of the driver, see SingleStore JDBC Driver Releases on GitHub.

Install with Maven

Include the following dependency in your pom.xml file, to install the driver using Maven. Be sure to replace the value in <version></version> with the version you want to use:

<dependency>
	<groupId>com.singlestore</groupId>
	<artifactId>singlestore-jdbc-client</artifactId>
	<version>x.x.x</version>
</dependency>

Install without Maven

If your project does not use Maven, you can download the .jar from GitHub or Maven Repository and include it in your project (install in your CLASSPATH) to use it.

Build from Source

To build the SingleStore JDBC connector from source, follow these steps:

  1. Clone the S2-JDBC-Connector repository using the following command:

    git clone https://github.com/memsql/S2-JDBC-Connector.git

  2. Execute the following command from the root of the repository:

    mvn -Dmaven.test.skip -Dmaven.javadoc.skip package

    This command generates a singlestore-jdbc-client-<version>.jar file in the target subdirectory. Install this file in a directory in your JAVA CLASSPATH to use the driver.

Use the SingleStore JDBC Driver

There are two methods to connect using the SingleStore JDBC Driver: using the DriverManager class or using a connection pool. You will also need a connection string to get a connection.

Connection String Format

The connection string must have the following format:

jdbc:singlestore:[loadbalance:|sequential:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]]

hostDescription:
    <host>[:port] | address=(host=<host>)[(port=<port>)][(type=(master|slave))]

  • The default for port is 3306. The default host type is master.

  • The host must be an IP address or DNS name. If the host is an IPv6 address, then it must be enclosed within square brackets.

See Connection String Parameters for more information. Here's a sample connection string:

"jdbc:singlestore://localhost:3306/test?user=root&password=myPassword"

Connect using the DriverManager Class

The DriverManager class automatically configures the application and loads the SingleStore connector. Here is some sample code that uses the DriverManager class:

Connection connection = DriverManager.getConnection("jdbc:singlestore://localhost:3306/test?user=root&password=myPassword");
Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery("SELECT NOW()");
rs.next();
System.out.println(rs.getTimestamp(1));

Connect using the Connection Pool

You can also get a connection with SingleStore JDBC connector via a connection pool. There are two DataSource implementations that you may choose from to use a connection pool.

  • The SingleStoreDataSource is a basic implementation that returns a new connection each time the getConnection() method is called.

  • The SingleStorePoolDataSource is a connection pool implementation that maintains a pool of connections. A connection is borrowed from the pool each time a new connection is requested.

Authenticate via JWTs

To enable JWT-based authentication, set the credentialType to JWT. The credentialType parameter specifies the authentication method.

Note

To authenticate your connection to the SingleStoreDB cluster via SingleStore JDBC driver using JWTs, the SingleStore user must connect via SSL and use JWT for authentication. To create a SingleStore user that can authenticate with a JWT, execute the following command:

CREATE USER 'email@example.com'@'%' IDENTIFIED WITH authentication_jwt REQUIRE SSL;

Using JWTs

You may authenticate your connection to the SingleStoreDB cluster using the SingleStore JDBC driver with a JWT. To use JWT-based authentication, add the following parameters to the connection string:

  • credentialType=JWT

  • user=<database-user>

  • password=<jwt-token>

Here’s a sample connection string that authenticates using JWT:

“jdbc:singlestore://<hostname-ip-address>/testDB?credentialType=JWT&useSsl=true&user=s2user&password=eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkphbmUgRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.masHflNUxp_i3apk46E6Xzj5FVnfiXS3gcXZxWig6fs0FPxZ6F0A4pY-xoOOR17bPmhD9Ul5QtFzdngZundOHSsh5XQyf38od4lzgwQG63d_T3SVYN5MyAgsREZWOJKwOPS25_Ah45hAXf395vGR29JZWYtox80-wIe4Hyk--9PZaqNagGgQnm6izFKZBlXOeZE_J3yORK7pvBGw9xCCS-RN1DZ3gWor70MHeUKa1jqqIAD3lptb0PHSQnQTQG8coV5HFwpY3v0o4frruP6peMTu_JuzAfs_U2k2-6gNCW67yeoj-8XuT7nBuggD-AOIQ3WpiIIo1WmJtK-7ClJJdA”

See Authenticate via JWTs for more information.

Failover and Load-Balancing Modes

The SingleStore JDBC connector supports the following failover/load-balancing modes:

Mode

Description

sequential

In this mode, the connector tries to connect with the hosts in the order in which they are declared in the connection string. Hence, the first available host is used for all the queries.

loadbalance

The connector randomly selects a host from the connection string, for each connection, to perform load-balancing on all the queries. Hence, the queries are load-balanced by randomly distributing the connections across all the hosts.

Connection String Parameters

The SingleStore JDBC connector supports the following parameters in the connection string.

Essential Parameters

Parameter

Description

Default Value

user

Username for the database.

password

The password of the database user.

connectTimeout

The connection timeout value (in ms). Set to 0 to disable connection timeout.

30000

useServerPrepStmts

Enables using PrepareStatements. PrepareStatements are pre-compiled on the server-side before executing. Applications that use the same queries repeatedly can enable this option. In general, it's preferable to leave this parameter as FALSE, because SingleStore has wider support of queries in text protocol and has internal mechanisms of query parameterization.

FALSE

allowLocalInfile

Allows users to load data from the local filesystem. See LOAD DATA for more information on loading data into SingleStore.

FALSE

TLS Parameters

Parameter

Description

Default Value

useSsl

Force enables SSL/TLS on the connection. Use useSSL as an alias for this parameter.

FALSE

trustServerCertificate

Enable this parameter to skip checking the server's certificate when used in conjunction with SSL/TLS.

FALSE

serverSslCert

Use this parameter to specify the server's certificate in DER format or specify the server's CA certificate. The server is added to trustStor, which allows the connection to trust a self-signed certificate.

You can specify the server certificate in one of the following formats:

  • Specify the complete path: serverSslCert=/full_path/cert.pem

  • Specify a relative path: serverSslCert=classpath:relative/cert.pem

  • Specify a DER-encoded certificate string: "------BEGIN CERTIFICATE-----"

keyStore

Specifies the path of the keyStore file that contains the client private keys and associated certificates.

keyStorePassword

Specifies the password for the keyStore file.

keyPassword

Specifies the password for the private key in the client certificate keyStore file.

trustStore

Specifies the path of the trustStore file, and uses the specified file for trusted root certificates. If this parameter is set, it overrides the serverSslCert parameter.

trustStorePassword

Specifies the password for the trusted root certificate file.

disableSslHostnameVerification

Disables hostname verification against the server's identity as specified in the server's certificate to prevent man-in-the-middle attacks.

false

Pool Parameters

Parameter

Description

Default Value

pool

Specifies that the connection uses a connection pool. Enable this option to use a connection object, but not a DataSource object.

FALSE

poolName

Specifies the pool name that allows identifying threads. The poolName is auto-generated by default.

SingleStore-pool-<pool-index>

maxPoolSize

Specifies the maximum number of physical connections for a connection pool.

8

minPoolSize

Specifies the minimum number of physical connections that must always be available in a connection pool. minPoolSize should never be greater than maxPoolSize.

maxPoolSize

poolValidMinDelay

Disables connection state validation for connections that request a new connection within the time limit specified through poolValidMinDelay. The time limit must be specified in ms. If set to 0, each connection request is validated.

1000

maxIdleTime

Specifies the maximum amount of time (in seconds) a connection can stay in the pool in idle state. This parameter can be set to a minimum of 2 seconds.

600

useResetConnection

When enabled, resets the connection state, deletes the prepare command, resets the session variables, and removes the user variables. User variables are removed only when the server allows deleting them.

FALSE

Log Parameters

Parameter

Description

Default Value

log

Enables information logging.

FALSE

maxQuerySizeToLog

Specifies the size of the query statement to include in the log.

1024

slowQueryThresholdNanos

Logs queries with execution time greater than the value specified using this parameter.

1024

profileSql

Enables logging the query execution time.

FALSE

Other Parameters

Parameter

Description

Default Value

allowMultiQueries

Enables multi-query statements.

FALSE

dumpQueriesOnException

Enables logging the query string when an exception is thrown during query execution.

FALSE

socketFactory

Specifies the name of the custom socket factory to use. Specify the full name of the class that implements javax.net.SocketFactory.

tcpKeepAlive

Enables TCPKeepAlive for the JDBC client to keep idle TCP connections connected.

tcpAbortiveClose

Resets TCP connections (through abort or hard close) instead of orderly closing connections. This option is useful in environments where connections are opened and closed frequently. In such environments, it may not be possible to create new sockets because all ports are used by connections in TCP_WAIT state.

tinyInt1isBit

Maps MySQL Tiny datatype to BIT (boolean) datatype.

TRUE

yearIsDateType

Converts year values from numerical to DATE type.

TRUE

sessionVariable

Sets SingleStore session variables specified as <var_name> = <value> pairs.

localSocket

Enables connecting to a database socket via a UNIX domain socket (if allowed by the server).

localSocketAddress

Binds the specified hostname or IP address to a local (UNIX domain) socket.

socketTimeout

Specifies the network socket timeout (in ms). If set to 0, socket timeout is disabled.

0

cachePrepStmts

If useServerPrepStmts is set to TRUE, enabling cachePrepStmts caches prepared statements in a LRU cache to avoid preparing the query again, which allows the server from re-parsing a query.

TRUE

prepStmtCacheSize

If useServerPrepStmts is set to TRUE, prepStmtCacheSize specifies the prepared statement cache size.

250

connectionAttributes

Set client information in key:value pair format, when performance_schema is enabled. This is currently only enabled on the client side (does not send the key:value pairs to the server). Best practice is to use this parameter to identify the calling client for special handling in the driver and in future telemetry information for SingleStore.

autocommit

Sets the autocommit value to the default value on connection initialization.

TRUE

includeThreadDumpInDeadlockExceptions

Adds thread dump to exception trace log in case of a deadlock exception.

FALSE

useReadAheadInput

Enables using a buffered inputStream to read available socket data

TRUE

defaultFetchSize

Calls the setFetchSize(n) method with the value specified using the defaultFetchSize parameter.

0

blankTableNameMeta

Returns a blank value for table name in the getTableName metadata result set.

FALSE

tlsSocketType

Uses the TLS com.singlestore.jdbc.tls.TlsSocketPlugin plugin. The plugin must be added to CLASSPATH.

credentialType

Specifies the credential plugin type. The plugin must be added to CLASSPATH.

rewriteBatchedStatements

If enabled, sends multiple rows in a single query. Note that, if the useServerPrepStmts parameter is enabled, rewriteBatchedStatements has no effect (it is ignored). The queries must be specified in the INSERT INTO <table-name> (column-list) VALUES (...) format.

FALSE

Failover and Load Balancing Parameters

Parameter

Description

Default Value

retriesAllDown

Specifies the maximum time until the connector retries connection attempts before throwing an exception, when the connector is performing a failover and all hosts are down.

120

In this section: