Skip to main content
Version: 3.6 (unsupported)

ScalarDB JDBC Guide

The usage of ScalarDB JDBC basically follows Java JDBC API. This guide describes several important topics that are specific to ScalarDB JDBC.

JDBC connection URL

The JDBC connection URL format of ScalarDB JDBC is as follows:

jdbc:scalardb:<configuration file path>?<property name>=<property value>&<property name>=<property value>&...

For example:

Only specify configuration file path:

jdbc:scalardb:/path/to/database.properties

Only specify properties:

jdbc:scalardb:?scalar.db.contact_points=localhost&scalar.db.username=cassandra&scalar.db.password=cassandra&scalar.db.storage=cassandra

Specify configuration file path and properties:

jdbc:scalardb:/path/to/database.properties?scalar.db.metadata.cache_expiration_time_secs=0

Data type mapping between ScalarDB and JDBC

Since ScalarDB doesn't support all the data types defined in JDBC, the following explains the data type mapping between ScalarDB and JDBC.

The data type mapping between ScalarDB and JDBC is as follows:

ScalarDB TypeJDBC (Java) Type
Booleanboolean or Boolean
Intint or Integer
BigIntlong or Long
Floatfloat or Float
Doubledouble or Double
TextString
Blobbyte[]

How to get the data from a java.sql.ResultSet object for each data type is as follows:

try (ResultSet resultSet = ...) {
resultSet.next();

// Get a Boolean value of a column
boolean booleanValue = resultSet.getBoolean("<column name>");

// Get an Int value of a column
int intValue = resultSet.getInt("<column name>");

// Get a BigInt value of a column
long bigIntValue = resultSet.getLong("<column name>");

// Get a Float value of a column
float floatValue = resultSet.getFloat("<column name>");

// Get a Double value of a column
double doubleValue = resultSet.getDouble("<column name>");

// Get a Text value of a column
String textValue = resultSet.getString("<column name>");

// Get a Blob value of a column
byte[] blobValue = resultSet.getBytes("<column name>");
}

How to set the data as a parameter for each data type for a java.sql.PreparedStatement object is as follows:

try (PreparedStatement preparedStatement = ...) {
// Set a Boolean value as parameter
preparedStatement.setBoolean(1, <Boolean value>);

// Set an Int value as parameter
preparedStatement.setInt(2, <Int value>);

// Set a BigInt value as parameter
preparedStatement.setLong(3, <BigInt value>);

// Set a Float value as parameter
preparedStatement.setFloat(4, <Float value>);

// Set a Double value as parameter
preparedStatement.setDouble(5, <Double value>);

// Set a Text value as parameter
preparedStatement.setString(7, "<Text value>");

// Set a Blob value as parameter
preparedStatement.setBytes(8, <Blob value>);

preparedStatement.execute();
}

Handle SQLException

The exception handling is basically the same as ScalarDB SQL API as follows:

// If you execute multiple statements in a transaction, you need to set auto-commit to false.
connection.setAutoCommit(false);

try {
// Execute statements (SELECT/INSERT/UPDATE/DELETE) in the transaction
...

// Commit the transaction
connection.commit();
} catch (SQLException e) {
if (e.getErrorCode() == 401) {
// The error code 401 indicates that you catch TransactionConflictException.
// If you catch TransactionConflictException, it indicates conflicts happen during a
// transaction so that you can retry the transaction in your application

// Rollback the transaction
connection.rollback();
} else if (e.getErrorCode() == 301) {
// The error code 301 indicates that you catch UnknownTransactionStatusException.
// If you catch UnknownTransactionStatusException when committing the transaction, you are
// not sure if the transaction succeeds or not. In such a case, you need to check if the
// transaction is committed successfully or not and retry it if it failed. How to identify a
// transaction status is delegated to users

} else {
// If you face an error code other than the above, it indicates that an unexpected failure
// happens, so you should cancel or retry the transaction after the failure/error is fixed

// Rollback the transaction
connection.rollback();
}
}

Please see also ScalarDB SQL API Guide for more details on exception handling.

References