ScalarDB SQL API Guide

This guide describes how to use ScalarDB SQL API.

Add ScalarDB SQL API to your project

To add the dependencies on ScalarDB SQL API by using Gradle, use the following, replacing <VERSION> with the versions of ScalarDB SQL API and the related library, respectively, that you are using:

dependencies {
    implementation 'com.scalar-labs:scalardb-sql:<VERSION>'
    implementation 'com.scalar-labs:scalardb-cluster-java-client-sdk:<VERSION>'
}

To add the dependencies by using Maven, use the following, replacing ... with the version of ScalarDB SQL API that you are using:

<dependencies>
    <dependency>
        <groupId>com.scalar-labs</groupId>
        <artifactId>scalardb-sql</artifactId>
        <version>...</version>
    </dependency>
    <dependency>
        <groupId>com.scalar-labs</groupId>
        <artifactId>scalardb-cluster-java-client-sdk</artifactId>
        <version>...</version>
    </dependency>
</dependencies>

SqlSessionFactory

In ScalarDB SQL API, you execute all operations through a SqlSession instance, which is instantiated with SqlSessionFactory. This section explains how to use them.

Before explaining SqlSessionFactory, we start with the explanation for Connection mode and Transaction mode.

Transaction mode

Also, ScalarDB SQL offers two transaction modes: Transaction mode and Two-phase Commit Transaction mode. Transaction mode exposes only commit interface to users and runs two-phase commit behind the scene, while Two-phase Commit Transaction mode exposes two-phase commit style interfaces (prepare and commit) to users.

You can specify the default transaction mode in your configuration file or when you build SqlSessionFactory. And you also can change it with the setTransactionMode() method of SqlSession.

Build SqlSessionFactory

You can build SqlSessionFactory with a properties file as follows:

SqlSessionFactory sqlSessionFactory = SqlSessionFactory.builder()
    .withPropertiesFile("<your configuration file>")
    // If you need to set custom properties, you can specify them with withProperty() or withProperties()
    .withProperty("<custom property name>", "<custom property value>")
    .build();

Please see ScalarDB Cluster SQL client configurations for the details of the configurations.

Get a SqlSession instance

You can get a SqlSession instance with SqlSessionFactory as follows:

SqlSession sqlSession = sqlSessionFactory.createSqlSession();

Note that SqlSession is not thread-safe. Please don't use it from multiple threads at the same time.

Close a SqlSession instance

Once all operations are done with a SqlSession instance, you should close the SqlSession instance:

sqlSession.close();

Close a SqlSessionFactory instance

sqlSessionFactory should also be closed once it's no longer needed:

sqlSessionFactory.close();

Execute SQLs

You can execute a SQL with SqlSession as follows:

ResultSet resultSet = sqlSession.execute("<SQL>");

You can also execute a Statement object with SqlSession as follows:

// Build a statement
Statement statement = StatementBuilder.<factory method>...;

// Execute the statement
ResultSet resultSet = sqlSession.execute(statement);

Statement objects can be built by StatementBuilder that has factory methods for corresponding SQLs. For more details, see the StatementBuilder page in the Javadoc and ScalarDB SQL Grammar.

Handle ResultSet objects

As the result of the SQL execution, SqlSession returns a ResultSet object. Here, we describe how to handle ResultSet objects.

If you want to get results one by one from the ResultSet object, you can use the one() method as follows:

Optional<Record> record = resultSet.one();

Or, if you want to get results all at once as a List, you can use the all() method as follows:

List<Record> records = resultSet.all();

Also, as ResultSet implements Iterable, you can use it in a for-each loop as follows:

for (Record record : resultSet) {
    ...
}

If you want to get the metadata of the ResultSet object, you can use the getColumnDefinitions() method as follows:

ColumnDefinitions columnDefinitions = resultSet.getColumnDefinitions();

For more details, see the ColumnDefinition page in the Javadoc.

Handle Record objects

As mentioned, a ResultSet object returns Record objects that represent records of the database.

You can get a column value of a result with getXXX("<column name>") or getXXX(<column index>) methods (XXX is a type name) as follows:

// Get a BOOLEAN value of a column
boolean booleanValueGottenByName = record.getBoolean("<column name>");
boolean booleanValueGottenByIndex = record.getBoolean(<column index>);

// Get an INT value of a column
int intValueGottenByName = record.getInt("<column name>");
int intValueGottenByIndex = record.getInt(<column index>);

// Get a BIGINT value of a column
long bigIntValueGottenByName = record.getBigInt("<column name>");
long bigIntValueGottenByIndex = record.getBigInt(<column index>);

// Get a FLOAT value of a column
float floatValueGottenByName = record.getFloat("<column name>");
float floatValueGottenByIndex = record.getFloat(<column index>);

// Get a DOUBLE value of a column
double doubleValueGottenByName = record.getDouble("<column name>");
double doubleValueGottenByIndex = record.getDouble(<column index>);

// Get a TEXT value of a column
String textValueGottenByName = record.getText("<column name>");
String textValueGottenByIndex = record.getText(<column index>);

// Get a BLOB value of a column (as a ByteBuffer)
ByteBuffer blobValueGottenByName = record.getBlob("<column name>");
ByteBuffer blobValueGottenByIndex = record.getBlob(<column index>);

// Get a BLOB value of a column as a byte array
byte[] blobValueAsBytesGottenByName = record.getBlobAsBytes("<column name>");
byte[] blobValueAsBytesGottenByIndex = record.getBlobAsBytes(<column index>);

// Get a DATE value of a column as a LocalDate
LocalDate dateValueGottenByName = record.getDate("<column name>");
LocalDate dateValueGottenByName = record.getDate(<column index>);

// Get a TIME value of a column as a LocalTime
LocalTime timeValueGottenByName = record.getTime("<column name>");
LocalTime timeValueGottenByName = record.getTime(<column index>);

// Get a TIMESTAMP value of a column as a LocalDateTime
LocalDateTime timestampValueGottenByName = record.getTimestamp("<column name>");
LocalDateTime timestampValueGottenByName = record.getTimestamp(<column index>);

// Get a TIMESTAMPTZ value of a column as an Instant
Instant timestampTZValueGottenByName = record.getTimestampTZ("<column name>");
Instant timestampTZValueGottenByName = record.getTimestampTZ(<column index>);

And if you need to check if a value of a column is null, you can use the isNull("<column name>") or isNull(<column index>) method.

// Check if a value of a column is null
boolean isNullGottenByName = record.isNull("<column name>");
boolean isNullGottenByIndex = record.isNull(<column index>);

For more details, see the Record page of the Javadoc.

Prepared Statements

You can use PreparedStatement for queries that are executed multiple times in your application:

PreparedStatement preparedStatement = sqlSession.prepareStatement("<SQL>");
ResultSet result = preparedStatement.execute();

If you execute the same query a second time or later, the cached pre-parsed statement object is used. Thus, you can gain a performance advantage with PreparedStatement when you execute the query multiple times. If you execute a query only once, a prepared statement is inefficient because it requires extra processing. Consider using the sqlSession.execute() method instead in that case.

Also, you can use PreparedStatement with bind parameters. Parameters can be either positional or named:

// Positional parameters
PreparedStatement preparedStatement1 =
    sqlSession.prepareStatement("INSERT INTO tbl (c1, c2) VALUES (?, ?)");

// Named parameters
PreparedStatement preparedStatement2 =
    sqlSession.prepareStatement("INSERT INTO tbl (c1, c2) VALUES (:a, :b)");

You can set parameters first and execute it:

// Positional setters
preparedStatement1
    .setInt(0, 10)
    .setText(1, "value")
    .execute();

// Named setters
preparedStatement2
    .setInt("a", 10)
    .setText("b", "value")
    .execute();

For more details, see the PreparedStatement page of the Javadoc.

Execute transactions

In ScalarDB SQL, you can execute DML statements (SELECT/INSERT/UPDATE/DELETE) only in transactions. So before executing DML statements, you must begin a transaction.

Note that you cannot execute statements other than DML statements transactionally. So even if you execute a non-DML statement after beginning a transaction, it is executed immediately, and it doesn't affect the transaction you have begun.

This section describes how to execute a transaction for each transaction mode: Transaction mode and Two-phase Commit Transaction mode.

Transaction Mode

An example code for Transaction mode is as follows:

try {
  // Begin a transaction
  sqlSession.begin();

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

  // Commit the transaction
  sqlSession.commit();
} catch (UnknownTransactionStatusException e) {
  // If you catch `UnknownTransactionStatusException`, it indicates that the status of the 
  // transaction, whether it has succeeded or not, is unknown. 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
} catch (SqlException e) {
  // For other exceptions, you can try retrying the transaction

  // Rollback the transaction
  sqlSession.rollback();

  // For `TransactionRetryableException`, you can basically retry the transaction. However, for
  // the other exceptions, the transaction may still fail if the cause of the exception is
  // nontransient. For such a case, you need to limit the number of retries and give up retrying
}

If you catch UnknownTransactionStatusException, it indicates that the status of the transaction, whether it has succeeded or not, is unknown. In such a case, you need to check if the transaction is committed successfully or not and retry it if it fails. How to identify a transaction status is delegated to users. You may want to create a transaction status table and update it transactionally with other application data so that you can get the status of a transaction from the status table.

If you catch another exception, you can try retrying the transaction. For TransactionRetryableException, you can basically retry the transaction. However, for the other exceptions, the transaction may still fail if the cause of the exception is nontransient. For such a case, you need to limit the number of retries and give up retrying.

Two-phase Commit Transaction Mode

Before reading this, please read this document to learn the concept of Two-phase commit transactions.

To begin a transaction for a coordinator, you can do as follows:

String transactionId = sqlSession.begin();

And to join a transaction for participants, you can do as follows:

sqlSession.join(transactionId);

An example code of Two-phase Commit Transaction mode is as follows:

try {
  // Begin a transaction
  sqlSession.begin();

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

  // Prepare the transaction
  sqlSession.prepare();

  // Validate the transaction
  sqlSession.validate();

  // Commit the transaction
  sqlSession.commit();
} catch (UnknownTransactionStatusException e) {
  // If you catch `UnknownTransactionStatusException` when committing the transaction, it
  // indicates that the status of the transaction, whether it has succeeded or not, is unknown.
  // 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
} catch (SqlException e) {
  // For other exceptions, you can try retrying the transaction
    
  // Rollback the transaction
  sqlSession.rollback();

  // For `TransactionRetryableException`, you can basically retry the transaction. However, for
  // the other exceptions, the transaction may still fail if the cause of the exception is
  // nontransient. For that case, you need to limit the number of retries and give up retrying
}

The exception handling is the same as Transaction mode.

Get Metadata

You can get metadata with the SqlSession.getMetadata() method as follows:

Metadata metadata = sqlSession.getMetadata();

For more details, see the Metadata page of the Javadoc.

References

• ScalarDB SQL Grammar
• Two-phase Commit Transactions
• Javadoc for ScalarDB SQL