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 |
---|---|---|---|
|
✗ |
None |
Hostname and port of the SQream DB worker. For example, |
|
✗ |
None |
Database name to connect to. For example, |
|
✗ |
None |
Username of a role to use for connection. For example, |
|
✗ |
None |
Specifies the password of the selected role. For example, |
|
✓ |
|
Specifices service queue to use. For example, |
|
✓ |
|
Specifies SSL for this connection. For example, |
|
✓ |
|
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
.
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(); } } |