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:
Java JDK
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:
Clone the S2-JDBC-Connector repository using the following command:
git clone https://github.com/memsql/S2-JDBC-Connector.git
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 thetarget
subdirectory. Install this file in a directory in your JAVACLASSPATH
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 ismaster
.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 thegetConnection()
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 |
---|---|
| 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. |
| 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 |
---|---|---|
| Username for the database. | |
| The password of the database user. | |
| The connection timeout value (in ms). Set to | 30000 |
| 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. |
|
| Allows users to load data from the local filesystem. See LOAD DATA for more information on loading data into SingleStore. |
|
TLS Parameters
Parameter | Description | Default Value |
---|---|---|
| Force enables SSL/TLS on the connection. Use |
|
| Enable this parameter to skip checking the server's certificate when used in conjunction with SSL/TLS. |
|
| Use this parameter to specify the server's certificate in DER format or specify the server's CA certificate. The server is added to You can specify the server certificate in one of the following formats:
| |
| Specifies the path of the keyStore file that contains the client private keys and associated certificates. | |
| Specifies the password for the | |
| Specifies the password for the private key in the client certificate | |
| Specifies the path of the | |
| Specifies the password for the trusted root certificate file. | |
| Disables hostname verification against the server's identity as specified in the server's certificate to prevent man-in-the-middle attacks. |
|
Pool Parameters
Parameter | Description | Default Value |
---|---|---|
| Specifies that the connection uses a connection pool. Enable this option to use a connection object, but not a |
|
| Specifies the pool name that allows identifying threads. The | SingleStore-pool-<pool-index> |
| Specifies the maximum number of physical connections for a connection pool. | 8 |
| Specifies the minimum number of physical connections that must always be available in a connection pool. |
|
| Disables connection state validation for connections that request a new connection within the time limit specified through | 1000 |
| Specifies the maximum amount of time (in | 600 |
| 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. |
|
Log Parameters
Parameter | Description | Default Value |
---|---|---|
| Enables information logging. |
|
| Specifies the size of the query statement to include in the log. | 1024 |
| Logs queries with execution time greater than the value specified using this parameter. | 1024 |
| Enables logging the query execution time. |
|
Other Parameters
Parameter | Description | Default Value |
---|---|---|
| Enables multi-query statements. |
|
| Enables logging the query string when an exception is thrown during query execution. |
|
| Specifies the name of the custom socket factory to use. Specify the full name of the class that implements | |
| Enables | |
| 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 | |
| Maps MySQL Tiny datatype to BIT (boolean) datatype. |
|
| Converts year values from numerical to |
|
| Sets SingleStore session variables specified as <var_name> = <value> pairs. | |
| Enables connecting to a database socket via a UNIX domain socket (if allowed by the server). | |
| Binds the specified hostname or IP address to a local (UNIX domain) socket. | |
| Specifies the network socket timeout (in ms). If set to | 0 |
| If |
|
| If | 250 |
| Set client information in | |
| Sets the |
|
| Adds thread dump to exception trace log in case of a deadlock exception. |
|
| Enables using a buffered |
|
| Calls the | 0 |
| Returns a blank value for table name in the |
|
| Uses the TLS | |
| Specifies the credential plugin type. The plugin must be added to | |
| If enabled, sends multiple rows in a single query. Note that, if the |
|
Failover and Load Balancing Parameters
Parameter | Description | Default Value |
---|---|---|
| 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 |