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.

The JDBC page includes the following sections:

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

SQream provides the JDBC driver as a zipped JAR file, available for download from the client drivers download page. This JAR file can be integrated into your Java-based applications or projects.

Extracting the ZIP Archive

Run the following command to extract the JAR file from the ZIP archive:

$ unzip sqream-jdbc-4.3.0.zip

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-4.3.0.jar, the following command is used to run application:

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

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

$ java -classpath .:/home/sqream/sqream-jdbc-4.3.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>sqream;[<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>	Mandatory	None	Username of a role to use for connection. For example, username=rhendricks
password=<password>	Mandatory	None	Specifies the password of the selected role. For example, password=Tr0ub4dor&3
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 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.

Connection String Examples

The following is an example of a SQream cluster with 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 for 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 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

Sample Java Program

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
    }


    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();  
    }  
}