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.
In this topic:
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.
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> |
✓ | false |
Connect via load balancer (use only if exists, and check port). For example, cluster=true |
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
.
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();
}
}
|