JDBC Drivers YSQL

• YugabyteDB JDBC Smart Driver
• PostgreSQL JDBC Driver

The PostgreSQL JDBC driver is the official JDBC driver for PostgreSQL, and can be used for connecting to YugabyteDB YSQL. YugabyteDB YSQL has full compatibility with the PostgreSQL JDBC Driver, allowing Java programmers to connect to YugabyteDB database to execute DMLs and DDLs using the JDBC APIs. The driver supports the SCRAM-SHA-256 authentication method.

For building a sample Java application with the PostgreSQL JDBC driver, see Connect an application.

Download the driver dependency

To download binary JAR files, go to PostgreSQL JDBC driver – Downloads. Because Java is platform neutral, download the appropriate JAR file and drop it into the classpath.

PostgreSQL JDBC Driver is available as a maven dependency, and you can download the driver by adding the following dependency into the Java project.

Maven dependency

To get the driver from Maven, add the following dependencies to the Maven project:

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.3.4</version>
</dependency>

Gradle dependency

To get the driver, add the following dependencies to the Gradle project:

// https://mvnrepository.com/artifact/org.postgresql/postgresql
implementation 'org.postgresql:postgresql:42.2.14'

Fundamentals

Learn how to perform common tasks required for Java application development using the PostgreSQL JDBC driver.

Connect to YugabyteDB database

Java applications can connect to and query the YugabyteDB database using the java.sql.DriverManager class. The java.sql.* package includes all the JDBC interfaces required for working with YugabyteDB.

Use the DriverManager.getConnection method to create a connection object to perform DDLs and DMLs against the database.

JDBC Connection String

jdbc:postgresql://hostname:port/database

Example JDBC URL for connecting to YugabyteDB can be seen below.

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5433/yugabyte","yugabyte", "yugabyte");

The following table describes the connection parameters required to connect to the YugabyteDB database.

Create tables

Create database tables using the java.sql.Statement interface, which is used to execute the CREATE TABLE DDL statement.

For example:

CREATE TABLE IF NOT EXISTS employee (id int primary key, name varchar, age int, language text)

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5433/yugabyte","yugabyte", "yugabyte");
Statement stmt = conn.createStatement();
try {
  stm.execute("CREATE TABLE IF NOT EXISTS employee" +
                    "  (id int primary key, name varchar, age int, language text)");

} catch (SQLException e) {
  System.err.println(e.getMessage());
}

java.sql.Statement throws the java.sql.SQLException exception, which needs to handled in the Java code. Read more on designing Database schemas and tables.

Read and write data

Insert data

To write data to YugabyteDB, execute the INSERT statement using the java.sql.Statement interface.

For example:

INSERT INTO employee VALUES (1, 'John', 35, 'Java')

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5433/yugabyte","yugabyte", "yugabyte");
Statement stmt = conn.createStatement();
try {
  String insertStr = "INSERT INTO employee VALUES (1, 'John', 35, 'Java')";
  stmt.execute(insertStr);

} catch (SQLException e) {
  System.err.println(e.getMessage());
}

When inserting data using JDBC clients, it is good practice to use java.sql.PreparedStatement for executing INSERT statements.

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5433/yugabyte","yugabyte", "yugabyte");
Statement stmt = conn.createStatement();
try {

  PreparedStatement pstmt = connection.prepareStatement("INSERT INTO employees (id, name, age, language) VALUES (?, ?, ?, ?)");
  pstmt.setInt(1, 1);
  pstmt.setString(2, "John");
  pstmt.setInt(3, 35);
  pstmt.setString(4, "Java");
  pstmt.execute();

} catch (SQLException e) {
  System.err.println(e.getMessage());
}

Query data

To query data in YugabyteDB tables, execute the SELECT statement using the java.sql.statement interface. Query results are returned using the java.sql.ResultSet interface, which can be iterated using the resultSet.next() method for reading the data. Refer to ResultSet in the Java documentation.

For example:

SELECT * from employee;

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5433/yugabyte","yugabyte", "yugabyte");
Statement stmt = conn.createStatement();
try {

  ResultSet rs = stmt.executeQuery("SELECT * FROM employee");
  while (rs.next()) {
    System.out.println(String.format("Query returned: name = %s, age = %s, language = %s", rs.getString(2), rs.getString(3), rs.getString(4)));
  }

} catch (SQLException e) {
  System.err.println(e.getMessage());
}

Configure SSL/TLS

To build a Java application that communicates securely over SSL, get the root certificate (ca.crt) of the YugabyteDB Cluster. If certificates are not generated yet, follow the instructions in Create server certificates.

Generally, when configuring the Java client to use SSL, all the certificates required for connecting to the database are available on the Classpath of the Java application, or in the default PostgreSQL working directory (~/.postgresql/) of the VM or container where the application is hosted.

Example JDBC URL for connecting to a secure YugabyteDB cluster can be seen below.

Connection conn = DriverManager.getConnection(jdbc:postgresql://localhost:5433/yugabyte?ssl=true&sslmode=verify-full&sslrootcert=~/.postgresql/root.crt", "yugabyte", "yugabyte");

SSL modes

JDBC client identity authentication

YugabyteDB cluster can be configured to authenticate the identity of the JDBC clients connecting to the cluster. In such cases, server certificate (yugabytedb.crt) and server key (yugabytedb.key) are required along with root certificate (ca.crt).

Set up SSL certificates for Java applications

Steps for configuring the JDBC client for server authentication are as follows:

1. Download the certificate (yugabytedb.crt, yugabytedb.key, and ca.crt) files (see Copy configuration files to the nodes).

2. If you do not have access to the system cacerts Java truststore you can create your own truststore.

   $ keytool -keystore ybtruststore -alias ybtruststore -import -file ca.crt
   

   Enter a password when you're prompted to enter one for your keystore.

3. Export the truststore. In the following command, replace <YOURSTOREPASS> with your keystore password.

   $ keytool -exportcert -keystore ybtruststore -alias ybtruststore -storepass <YOURSTOREPASS> -file ybtruststore.crt
   

4. Convert and export to PEM format with ybtruststore.pem.

   $ openssl x509 -inform der -in ybtruststore.crt -out ybtruststore.pem
   

5. Verify the yugabytedb.crt client certificate with ybtruststore.

   $ openssl verify -CAfile ybtruststore.pem -purpose sslclient yugabytedb.crt
   

6. Convert the client certificate to DER format.

   $ openssl x509 –in yugabytedb.crt -out yugabytedb.crt.der -outform der
   

7. Convert the client key to pk8 format.

   $ openssl pkcs8 -topk8 -inform PEM -in yugabytedb.key -outform DER -nocrypt -out yugabytedb.key.pk8
   

SSL certificates for a cluster in Kubernetes (Optional)

Steps for configuring the JDBC client for server authentication in a Kubernetes cluster are as follows:

1. Create a minikube cluster by adding tls.enabled=true to the command line described in Quick start.

   $ kubectl create namespace yb-demo
   $ helm install yb-demo yugabytedb/yugabyte \
   --version 2.23.1 \
   --set resource.master.requests.cpu=0.5,resource.master.requests.memory=0.5Gi,\
   resource.tserver.requests.cpu=0.5,resource.tserver.requests.memory=0.5Gi,\
   replicas.master=1,replicas.tserver=1,tls.enabled=true --namespace yb-demo
   

2. Verify that SSL is enabled using ysqlsh.

    $ ysqlsh
   

   ysqlsh (11.2-YB-2.23.1.0-b0)
   SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
   Type "help" for help.
   

3. Check for the key and certificate files in yb-tserver.

   $ kubectl exec -n yb-demo -it yb-tserver-0 -- bash
   [root@yb-tserver-0 cores]# ls -al /root/.yugabytedb/
   

   total 4
   drwxrwxrwt 3 root root  140 Oct 22 06:04 .
   dr-xr-x--- 1 root root 4096 Oct 22 06:19 ..
   drwxr-xr-x 2 root root  100 Oct 22 06:04 ..2021_10_22_06_04_46.596961191
   lrwxrwxrwx 1 root root   31 Oct 22 06:04 ..data -> ..2021_10_22_06_04_46.596961191
   lrwxrwxrwx 1 root root   15 Oct 22 06:04 root.crt -> ..data/root.crt
   lrwxrwxrwx 1 root root   21 Oct 22 06:04 yugabytedb.crt -> ..data/yugabytedb.crt
   lrwxrwxrwx 1 root root   21 Oct 22 06:04 yugabytedb.key -> ..data/yugabytedb.key
   

4. Download these files to your system and proceed to step 2 under Set up SSL certificates.

   % mkdir YBClusterCerts; cd YBClusterCerts
   % kubectl exec -n "yb-demo" "yb-tserver-0" -- tar -C "/root/.yugabytedb" -cf - . |tar xf -
   Defaulted container "yb-tserver" out of: yb-tserver, yb-cleanup
   % ls
   root.crt yugabytedb.crt yugabytedb.key
   

Copy SSL certificates

1. Create an ssl resource directory in your java application using the following command:

   $ mkdir -p src/main/resources/ssl
   

2. Copy the yugabytedb.crt.der and yugabytedb.key.pk8 certificates into the ssl directory.

Update the connection string used by DriverManager.getConnection to include the ssl certificates.

The following is an example JDBC URL for connecting to a secure YugabyteDB cluster:

Connection conn = DriverManager.getConnection(jdbc:postgresql://localhost:5433/yugabyte?ssl=true&sslmode=verify-full&sslcert=src/main/resources/ssl/yugabytedb.crt.der&sslkey=src/main/resources/ssl/yugabytedb.key.pk8", "yugabyte", "yugabyte");

Transaction and isolation levels

YugabyteDB supports transactions for inserting and querying data from the tables. YugabyteDB supports different isolation levels for maintaining strong consistency for concurrent data access.

JDBC Driver java.sql.Connection interface provides connection.setAutoCommit(), connection.commit() and connection.rollback() methods for enabling transactional access to YugabyteDB Database.

For example:

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5433/yugabyte","yugabyte", "yugabyte");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
try {

  PreparedStatement pstmt = connection.prepareStatement("INSERT INTO employees (id, name, age, language) VALUES (?, ?, ?, ?)");
  pstmt.setInt(1, 1);
  pstmt.setString(2, "John");
  pstmt.setInt(3, 35);
  pstmt.setString(4, "Java");
  pstmt.execute();

  conn.commit();

} catch (SQLException e) {
  System.err.println(e.getMessage());
}

By default PostgreSQL JDBC driver will have auto-commit mode enabled which means each SQL statement is treated as a transaction and is automatically committed. If one or more SQL statements are encapsulated in a transaction, auto-commit mode must be disabled by using setAutoCommit() method of the Connection object.