JDBC

The SQream DB JDBC driver allows many Java applications and tools connect to SQream DB. This tutorial shows how to write a Java application using the JDBC interface.

The JDBC driver requires Java 1.8 or newer.

Installing the JDBC Driver

Prerequisites

The SQream DB JDBC driver requires Java 1.8 or newer. We recommend either Oracle Java or OpenJDK.

Oracle Java

Download and install Java 8 from Oracle for your platform

https://www.java.com/en/download/manual.jsp

OpenJDK

For Linux and BSD, see https://openjdk.java.net/install/

For Windows, SQream recommends Zulu 8 https://www.azul.com/downloads/zulu-community/?&version=java-8-lts&architecture=x86-64-bit&package=jdk

Getting the JAR file

The JDBC driver is provided as a zipped JAR file, available for download from the client drivers download page. This JAR file can integrate into your Java-based applications or projects.

Extract the ZIP Archive

Extract the JAR file from the zip archive

$ unzip sqream-jdbc-4.3.0.zip

Setting up the Class Path

To use the driver, the JAR named sqream-jdbc-<version>.jar (for example, sqream-jdbc-4.3.0.jar) needs to be included in the class path, either by putting 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 application should be run as follows:

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

An alternative method is to pass -classpath to the Java executable:

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

Connect to SQream DB with a JDBC Application

Driver Class

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

Connection String

JDBC drivers rely on a connection string. Use the following syntax for SQream DB

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

Connection Parameters

Item Optional Default Description
<host and port> ✗ None Hostname and port of the SQream DB worker. For example, 127.0.0.1:5000, sqream.mynetwork.co:3108
<database name> ✗ None Database name to connect to. For example, master
username=<username> ✗ None Username of a role to use for connection. For example, username=rhendricks
password=<password> ✗ None Specifies the password of the selected role. For example, password=Tr0ub4dor&3
service=<service> ✓ sqream Specifices service queue to use. For example, service=etl
<ssl> ✓ false Specifies SSL for this connection. For example, ssl=true
<cluster> ✓ true Connect via load balancer (use only if exists, and check port).

Connection String Examples

For a SQream DB 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

Minimal example for a local, standalone SQream DB

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

For a SQream DB 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

Download this file by right clicking and saving to your computer sample.java.

JDBC application sample
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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();  
    }  
}