---
type: Development Guide
title: ScalarDB SQL API Guide
description: This guide describes how to use ScalarDB SQL API.
resource: https://scalardb.scalar-labs.com/docs/3.16/scalardb-sql/sql-api-guide/
tags:
- scalardb
- v3.16
- phase:implement
- section:develop
- edition:enterprise-premium
status: stable
product: scalardb
product_title: ScalarDB
version: '3.16'
patch_version: 3.16.6
doc_id: scalardb-sql/sql-api-guide
lifecycle_phase: implement
breadcrumb:
- Develop
- Run Transactions
- Reference
- Java Interface Guides
- SQL Interface Guides
editions:
- Enterprise Premium
generated:
  by: process:okf-build/1.0.0
  at: '2026-08-04T23:50:54Z'
sources:
- id: docs-scalardb
  resource: https://github.com/scalar-labs/docs-scalardb/blob/6126dfe2f56389351d88b134752618641f9771dd/versioned_docs/version-3.16/scalardb-sql/sql-api-guide.mdx
  title: ScalarDB documentation source (MDX)
  author: process:scalar-labs/docs-scalardb
  last_modified: '2026-08-04T15:05:02Z'
---

# 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:

```gradle
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:

```xml
<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:

```java
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](../scalardb-cluster/developer-guide-for-scalardb-cluster-with-java-api.md#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:

```java
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:

```java
sqlSession.close();
```

### Close a SqlSessionFactory instance

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

```java
sqlSessionFactory.close();
```

## Execute SQLs

You can execute a SQL with `SqlSession` as follows:

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

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

```java
// 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`](https://javadoc.io/static/com.scalar-labs/scalardb-sql/3.16.6/com/scalar/db/sql/statement/builder/StatementBuilder.html) page in the Javadoc and [ScalarDB SQL Grammar](./grammar.md).

### 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:
```java
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:
```java
List<Record> records = resultSet.all();
```

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

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

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

```java
ColumnDefinitions columnDefinitions = resultSet.getColumnDefinitions();
```

For more details, see the [`ColumnDefinition`](https://javadoc.io/static/com.scalar-labs/scalardb-sql/3.16.6/com/scalar/db/sql/ColumnDefinition.html) 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:

```java
// 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.

``` java
// 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`](https://javadoc.io/static/com.scalar-labs/scalardb-sql/3.16.6/com/scalar/db/sql/Record.html) page of the Javadoc.

### Prepared Statements

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

```java
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:

```java
// 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:

```java
// 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`](https://javadoc.io/static/com.scalar-labs/scalardb-sql/3.16.6/com/scalar/db/sql/PreparedStatement.html) 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:

```java
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](../two-phase-commit-transactions.md) to learn the concept of Two-phase commit transactions.

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

```java
String transactionId = sqlSession.begin();
```

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

```java
sqlSession.join(transactionId);
```

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

```java
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:

```java
Metadata metadata = sqlSession.getMetadata();
```

For more details, see the [`Metadata`](https://javadoc.io/static/com.scalar-labs/scalardb-sql/3.16.6/com/scalar/db/sql/metadata/Metadata.html) page of the Javadoc.

## References

- [ScalarDB SQL Grammar](./grammar.md)
- [Two-phase Commit Transactions](../two-phase-commit-transactions.md)
- [Javadoc for ScalarDB SQL](https://javadoc.io/doc/com.scalar-labs/scalardb-sql/3.16.6/index.html)
