JDBC

The SQream JDBC driver lets you connect to SQream using many Java applications and tools. This page describes how to write a Java application using the JDBC interface. The JDBC driver requires Java 1.8 or newer.

Installing the JDBC Driver

The Installing the JDBC Driver section describes the following:

Prerequisites

The SQream JDBC driver requires Java 1.8 or newer, and SQream recommends using Oracle Java or OpenJDK.:

• Oracle Java - Download and install Java 8 from Oracle for your platform.

• OpenJDK - Install OpenJDK

• Windows - SQream recommends installing Zulu 8

Getting the JAR file

The SQream JDBC driver is available for download from the client drivers download page. This JAR file can be integrated into your Java-based applications or projects.

Setting Up the Class Path

To use the driver, you must include the JAR named sqream-jdbc-<version>.jar in the class path, either by inserting it in the CLASSPATH environment variable, or by using flags on the relevant Java command line.

For example, if the JDBC driver has been unzipped to /home/sqream/sqream-jdbc-5.2.0.jar, the following command is used to run application:

$ export CLASSPATH=/home/sqream/sqream-jdbc-5.2.0.jar:$CLASSPATH
$ java my_java_app

Alternatively, you can pass -classpath to the Java executable file:

$ java -classpath .:/home/sqream/sqream-jdbc-5.2.0.jar my_java_app

Connecting to SQream Using a JDBC Application

You can connect to SQream using one of the following JDBC applications:

Driver Class

Use com.sqream.jdbc.SQDriver as the driver class in the JDBC application.

Connection String

JDBC drivers rely on a connection string.

The following is the syntax for SQream:

jdbc:Sqream://<host and port>/<database name>;user=<username>;password=<password>;[<optional parameters>; ...]

Connection Parameters

The following table shows the connection string parameters:

Item	State	Default	Description
<host and port>	Mandatory	None	Hostname and port of the SQream DB worker. For example, 127.0.0.1:5000, sqream.mynetwork.co:3108
<database name>	Mandatory	None	Database name to connect to. For example, master
username=<username>	Optional	None	Username of a role to use for connection. For example, username=SqreamRole
password=<password>	Optional	None	Specifies the password of the selected role. For example, password=SqreamRolePassword2023
service=<service>	Optional	sqream	Specifices service queue to use. For example, service=etl
<ssl>	Optional	false	Specifies SSL for this connection. For example, ssl=true
<cluster>	Optional	true	Connect via load balancer (use only if exists, and check port).
<fetchSize>	Optional	true	Enables on-demand loading, and defines double buffer size for the result. The fetchSize parameter is rounded according to chunk size. For example, fetchSize=1 loads one row and is rounded to one chunk. If the fetchSize is 100,600, a chunk size of 100,000 loads, and is rounded to, two chunks.
<insertBuffer>	Optional	true	Defines the bytes size for inserting a buffer before flushing data to the server. Clients running a parameterized insert (network insert) can define the amount of data to collect before flushing the buffer.
<loggerLevel>	Optional	true	Defines the logger level as either debug or trace.
<logFile>	Optional	true	Enables the file appender and defines the file name. The file name can be set as either the file name or the file path.
<idleconnectiontimeout>	Optional	0	Sets the duration, in seconds, for which a database connection can remain idle before it is terminated. If the parameter is set to its default value, idle connections will not be terminated. The idle connection timer begins counting after the completion of query execution.

Connection String Examples

The following is an example of a SQream cluster with a load balancer and no service queues (with SSL):

jdbc:Sqream://sqream.mynetwork.co:3108/master;user=rhendricks;password=Tr0ub4dor&3;ssl=true;cluster=true

The following is a minimal example of a local standalone SQream database:

jdbc:Sqream://127.0.0.1:5000/master;user=rhendricks;password=Tr0ub4dor&3

The following is an example of a SQream cluster with a load balancer and a specific service queue named etl, to the database named raviga

jdbc:Sqream://sqream.mynetwork.co:3108/raviga;user=rhendricks;password=Tr0ub4dor&3;cluster=true;service=etl

Java Program Sample

You can download the JDBC Application Sample File below by right-clicking and saving it to your computer.

JDBC Application Sample

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

import java.io.IOException;  
import java.security.KeyManagementException;  
import java.security.NoSuchAlgorithmException;  
import java.sql.SQLException;  



public  class  SampleTest  {  

    // Replace with your connection string
    static  final  String  url  =  "jdbc:Sqream://sqream.mynetwork.co:3108/master;user=rhendricks;password=Tr0ub4dor&3;ssl=true;cluster=true";  

    // Allocate objects for result set and metadata
    Connection  conn    =  null;  
    Statement  stmt  =  null;  
    ResultSet  rs  =  null;  
    DatabaseMetaData  dbmeta  =  null;  

    int  res  =  0;  

    public  void  testJDBC()  throws  SQLException,  IOException  {  

        // Create a connection
        conn  =  DriverManager.getConnection(url,"rhendricks","Tr0ub4dor&3");  

        // Create a table with a single integer column
        String sql  =  "CREATE TABLE test (x INT)";
        stmt = conn.createStatement();  // Prepare the statement
        stmt.execute(sql); // Execute the statement
        stmt.close(); // Close the statement handle

        // Insert some values into the newly created table
        sql = "INSERT INTO test VALUES (5),(6)";
        stmt = conn.createStatement();
        stmt.execute(sql);
        stmt.close();

        // Get values from the table
        sql = "SELECT * FROM test";
        stmt = conn.createStatement();
        rs  = stmt.executeQuery(sql);
        // Fetch all results one-by-one
        while(rs.next()) {
            res = rs.getInt(1);
            System.out.println(res); // Print results to screen
        }
        rs.close(); // Close the result set
        stmt.close(); // Close the statement handle
        conn.close();
    }


    public  static  void  main(String[]  args)  throws  SQLException,  KeyManagementException,  NoSuchAlgorithmException,  IOException,  ClassNotFoundException{  

        // Load SQream DB JDBC driver
        Class.forName("com.sqream.jdbc.SQDriver");  

        // Create test object and run
        SampleTest  test  =  new  SampleTest();  
        test.testJDBC();  
    }  
}

Prepared Statements

Prepared statements, also known as parameterized queries, are a safer and more efficient way to execute SQL statements. They prevent SQL injection attacks by separating SQL code from data, and they can improve performance by reusing prepared statements. In SQream, we use ? as a placeholder for the relevant value in parameterized queries. Prepared statements INSERT, SELECT, UPDATE and DELETE

Prepared Statement Sample

The following is a Java code snippet employing a JDBC prepared statement object to ingest a batch of one million records into SQreamDB.

You may download the Prepared statement by right-clicking and saving it to your computer.

Prepared Statement Limitations

• Prepared Statement do not support the use of Identifiers and Keywords as input parameters.

• SELECT, UPDATE and DELETE statements require the use of add_batch prior to each execution.