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

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

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

$ java -classpath .:/home/sqream/sqream-jdbc-5.0.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
 1import java.sql.Connection;  
 2import java.sql.DatabaseMetaData;  
 3import java.sql.DriverManager;  
 4import java.sql.Statement;  
 5import java.sql.ResultSet;  
 6
 7import java.io.IOException;  
 8import java.security.KeyManagementException;  
 9import java.security.NoSuchAlgorithmException;  
10import java.sql.SQLException;  
11
12
13
14public  class  SampleTest  {  
15
16    // Replace with your connection string
17    static  final  String  url  =  "jdbc:Sqream://sqream.mynetwork.co:3108/master;user=rhendricks;password=Tr0ub4dor&3;ssl=true;cluster=true";  
18
19    // Allocate objects for result set and metadata
20    Connection  conn    =  null;  
21    Statement  stmt  =  null;  
22    ResultSet  rs  =  null;  
23    DatabaseMetaData  dbmeta  =  null;  
24
25    int  res  =  0;  
26
27    public  void  testJDBC()  throws  SQLException,  IOException  {  
28
29        // Create a connection
30        conn  =  DriverManager.getConnection(url,"rhendricks","Tr0ub4dor&3");  
31
32        // Create a table with a single integer column
33        String sql  =  "CREATE TABLE test (x INT)";
34        stmt = conn.createStatement();  // Prepare the statement
35        stmt.execute(sql); // Execute the statement
36        stmt.close(); // Close the statement handle
37
38        // Insert some values into the newly created table
39        sql = "INSERT INTO test VALUES (5),(6)";
40        stmt = conn.createStatement();
41        stmt.execute(sql);
42        stmt.close();
43
44        // Get values from the table
45        sql = "SELECT * FROM test";
46        stmt = conn.createStatement();
47        rs  = stmt.executeQuery(sql);
48        // Fetch all results one-by-one
49        while(rs.next()) {
50            res = rs.getInt(1);
51            System.out.println(res); // Print results to screen
52        }
53        rs.close(); // Close the result set
54        stmt.close(); // Close the statement handle
55        conn.close();
56    }
57
58
59    public  static  void  main(String[]  args)  throws  SQLException,  KeyManagementException,  NoSuchAlgorithmException,  IOException,  ClassNotFoundException{  
60
61        // Load SQream DB JDBC driver
62        Class.forName("com.sqream.jdbc.SQDriver");  
63
64        // Create test object and run
65        SampleTest  test  =  new  SampleTest();  
66        test.testJDBC();  
67    }  
68}

Prepared Statements

Prepared statements, also known as parameterized queries, are a feature of JDBC that enable the use of parameters to optimize query execution, enhance security, and enable query template reuse with different parameter values in Java applications.

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.