Use JDBC

SynxDB Elastic is a cloud-native database based on the Greenplum/PostgreSQL 14.4 kernel, featuring a separation of storage and compute. Because it is compatible with Greenplum and PostgreSQL, JDBC access methods are the same as those for Greenplum/PostgreSQL.

This guide explains how to connect to SynxDB Elastic using JDBC and perform database operations.

Prerequisites

Before connecting to SynxDB Elastic via JDBC, ensure you have:

A Java runtime environment (JDK 1.8 or later) installed.
The PostgreSQL JDBC driver (postgresql-<version>.jar) downloaded.
Connection details for SynxDB Elastic, including host, port, database name, username, and password.

Step 1. Download the JDBC driver

The JDBC driver can be downloaded from the official PostgreSQL website. Use the latest stable version compatible with SynxDB Elastic.

Example download command (for version 42.5.0):

wget https://jdbc.postgresql.org/download/postgresql-42.5.0.jar

Step 2. Connect to SynxDB Elastic

To connect to SynxDB Elastic, use the following connection string format in you Java program.

jdbc:postgresql://<host>:<port>/<database>?parameters

Common parameters:

user=<username>: Specifies the database username.
password=<password>: Specifies the database password.
ssl=<true|false>: Enables or disables SSL connection.
ApplicationName=<app_name>: Optional, used to identify the client application.

Example:

jdbc:postgresql://db.example.com:5432/mydb?user=myuser&password=mypass&ssl=true

The following example demonstrates how to connect to SynxDB Elastic using JDBC:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SynxDBElasticExample {
   public static void main(String[] args) {
      String url = "jdbc:postgresql://your-synxdb-host:5432/your_database";
      String user = "your_username";
      String password = "your_password";

      try (Connection conn = DriverManager.getConnection(url, user, password);
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery("SELECT version();")) {

         while (rs.next()) {
            System.out.println("Database Version: " + rs.getString(1));
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Step 3. Set up the environment

Download PostgreSQL JDBC driver

Before connecting to the database, download the PostgreSQL JDBC driver:

wget https://jdbc.postgresql.org/download/postgresql-42.5.0.jar

Compile Java code

After writing the Java program, compile it using the downloaded JDBC driver:

javac -cp postgresql-42.5.0.jar YourJavaProgram.java

Run the Java program

Execute the Java program with the classpath set to include the JDBC driver:

java -cp .:postgresql-42.5.0.jar YourJavaProgram

Execute SQL statements

To execute SQL statements through JDBC, you can refer to the following sections to add code to your java program.

Query data

String query = "SELECT id, name FROM users";
try (Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(query)) {
   while (rs.next()) {
      int id = rs.getInt("id");
      String name = rs.getString("name");
      System.out.println("ID: " + id + ", Name: " + name);
   }
}

Insert data

String insertSQL = "INSERT INTO users (id, name) VALUES (1, 'Alice')";
try (Statement stmt = conn.createStatement()) {
   int rowsAffected = stmt.executeUpdate(insertSQL);
   System.out.println("Rows inserted: " + rowsAffected);
}

Update data

String updateSQL = "UPDATE users SET name = 'Bob' WHERE id = 1";
try (Statement stmt = conn.createStatement()) {
   int rowsAffected = stmt.executeUpdate(updateSQL);
   System.out.println("Rows updated: " + rowsAffected);
}

Delete data

String deleteSQL = "DELETE FROM users WHERE id = 1";
try (Statement stmt = conn.createStatement()) {
   int rowsAffected = stmt.executeUpdate(deleteSQL);
   System.out.println("Rows deleted: " + rowsAffected);
}

Transaction management

JDBC allows explicit transaction control:

conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {
   stmt.executeUpdate("INSERT INTO users (id, name) VALUES (2, 'Charlie')");
   stmt.executeUpdate("UPDATE users SET name = 'Charlie Updated' WHERE id = 2");
   conn.commit();
} catch (SQLException e) {
   conn.rollback();
   e.printStackTrace();
}

Use connection pools

In production environments, using a connection pool (for example, HikariCP) improves performance.

Add HikariCP dependency

For Maven:

<dependency>
   <groupId>com.zaxxer</groupId>
   <artifactId>HikariCP</artifactId>
   <version>5.0.1</version>
</dependency>

Configure HikariCP

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://your-synxdb-host:5432/your_database");
config.setUsername("your_username");
config.setPassword("your_password");
config.setMaximumPoolSize(10);
HikariDataSource dataSource = new HikariDataSource(config);

Troubleshoot connection issues

If the connection fails, check:

Network accessibility to SynxDB Elastic.
Firewall or security group settings allowing PostgreSQL port (default 5432).
Correct JDBC URL, username, and password.
Database logs for detailed error messages.