App in Java

This page contains a detailed description of the code of a test app that is available as part of the YDB Java SDK Examples.

Downloading SDK Examples and running the example

The following execution scenario is based on Git and Maven.

Create a working directory and use it to run from the command line the command to clone the GitHub repository:

git clone https://github.com/ydb-platform/ydb-java-examples

Then build the SDK Examples

( cd ydb-java-examples && mvn package )

Next, from the same working directory, run the command to start the test app. The command will differ depending on the database to connect to.

To connect to a locally deployed YDB database according to the Docker use case, run the following command in the default configuration:

(cd ydb-java-examples/basic_example/target && \
YDB_ANONYMOUS_CREDENTIALS=1 java -jar ydb-basic-example.jar grpc://localhost:2136?database=/local )

To run the example against any available YDB database, you need to know the endpoint and the database path.

If authentication is enabled in the database, you also need to select the authentication mode and get secrets (a token or username/password pair).

Run the command as follows:

( cd ydb-java-examples/basic_example/target && \
<auth_mode_var>="<auth_mode_value>" java -jar ydb-basic-example.jar <endpoint>?database=<database>)

where

  • <endpoint>: The endpoint.
  • <database>: The database path.
  • <auth_mode_var>: The environment variable that determines the authentication mode.
  • <auth_mode_value> is the authentication parameter value for the selected mode.

For example:

( cd ydb-java-examples/basic_example/target && \
YDB_ACCESS_TOKEN_CREDENTIALS="t1.9euelZqOnJuJlc..." java -jar ydb-basic-example.jar grpcs://ydb.example.com:2135?database=/somepath/somelocation)

Initializing a database connection

To interact with YDB, create an instance of the driver, client, and session:

  • The YDB driver lets the app and YDB interact at the transport layer. The driver must exist throughout the YDB access lifecycle and be initialized before creating a client or session.
  • The YDB client runs on top of the YDB driver and enables the handling of entities and transactions.
  • The YDB session contains information about executed transactions and prepared queries, and is part of the YDB client context.

Main driver initialization parameters

App code snippet for driver initialization:

this.transport = GrpcTransport.forConnectionString(connectionString)
        .withAuthProvider(CloudAuthHelper.getAuthProviderFromEnviron())
        .build();
this.tableClient = TableClient.newClient(transport).build();

We recommend that you use the SessionRetryContext helper class for all your operations with the YDB: it ensures proper retries in case the database becomes partially unavailable. Sample code to initialize the retry context:

this.retryCtx = SessionRetryContext.create(tableClient).build();

Creating tables

Creating tables to be used in operations on a test app. This step results in the creation of DB tables of the series directory data model:

  • Series
  • Seasons
  • Episodes

Once the tables are created, the method for getting information about data schema objects is called and the result of its execution is output.

To create tables, use the Session.createTable() method:

private void createTables() {
    TableDescription seriesTable = TableDescription.newBuilder()
        .addNullableColumn("series_id", PrimitiveType.Uint64)
        .addNullableColumn("title", PrimitiveType.Text)
        .addNullableColumn("series_info", PrimitiveType.Text)
        .addNullableColumn("release_date", PrimitiveType.Date)
        .setPrimaryKey("series_id")
        .build();

    retryCtx.supplyStatus(session -> session.createTable(database + "/series", seriesTable))
            .join().expectSuccess("Can't create table /series");

    TableDescription seasonsTable = TableDescription.newBuilder()
        .addNullableColumn("series_id", PrimitiveType.Uint64)
        .addNullableColumn("season_id", PrimitiveType.Uint64)
        .addNullableColumn("title", PrimitiveType.Text)
        .addNullableColumn("first_aired", PrimitiveType.Date)
        .addNullableColumn("last_aired", PrimitiveType.Date)
        .setPrimaryKeys("series_id", "season_id")
        .build();

    retryCtx.supplyStatus(session -> session.createTable(database + "/seasons", seasonsTable))
            .join().expectSuccess("Can't create table /seasons");

    TableDescription episodesTable = TableDescription.newBuilder()
        .addNullableColumn("series_id", PrimitiveType.Uint64)
        .addNullableColumn("season_id", PrimitiveType.Uint64)
        .addNullableColumn("episode_id", PrimitiveType.Uint64)
        .addNullableColumn("title", PrimitiveType.Text)
        .addNullableColumn("air_date", PrimitiveType.Date)
        .setPrimaryKeys("series_id", "season_id", "episode_id")
        .build();

    retryCtx.supplyStatus(session -> session.createTable(database + "/episodes", episodesTable))
            .join().expectSuccess("Can't create table /episodes");
}

You can use the Session.describeTable() method to view information about the table structure and make sure that it was properly created:

private void describeTables() {
    logger.info("--[ DescribeTables ]--");

    Arrays.asList("series", "seasons", "episodes").forEach(tableName -> {
        String tablePath = database + '/' + tableName;
        TableDescription tableDesc = retryCtx.supplyResult(session -> session.describeTable(tablePath))
                .join().getValue();

        List<String> primaryKeys = tableDesc.getPrimaryKeys();
        logger.info(" table {}", tableName);
        for (TableColumn column : tableDesc.getColumns()) {
            boolean isPrimary = primaryKeys.contains(column.getName());
            logger.info("     {}: {} {}", column.getName(), column.getType(), isPrimary ? " (PK)" : "");
        }
    });
}

Adding data

Adding data to the created tables using an UPSERT statement of YQL. A data update request is sent within a single request to the server with transaction auto-commit mode enabled.

Code snippet for data insert/update:

private void upsertSimple() {
    String query
            = "UPSERT INTO episodes (series_id, season_id, episode_id, title) "
            + "VALUES (2, 6, 1, \"TBD\");";

    // Begin new transaction with SerializableRW mode
    TxControl txControl = TxControl.serializableRw().setCommitTx(true);

    // Executes data query with specified transaction control settings.
    retryCtx.supplyResult(session -> session.executeDataQuery(query, txControl))
        .join().getValue();
}

Retrieving data with a Select

Retrieving data using a SELECT statement in YQL. Handling the retrieved data selection in the app.

To execute YQL queries, use the Session.executeDataQuery() method.
The SDK lets you explicitly control the execution of transactions and configure the transaction execution mode using the TxControl class.

In the code snippet below, the transaction is executed using the session.executeDataQuery() method. The TxControl txControl = TxControl.serializableRw().setCommitTx(true); transaction execution mode and setCommitTx(true) transaction auto complete flag are set. The query body is described using YQL syntax and is passed to the executeDataQuery method as a parameter.

private void selectSimple() {
    String query
            = "SELECT series_id, title, release_date "
            + "FROM series WHERE series_id = 1;";

    // Begin new transaction with SerializableRW mode
    TxControl txControl = TxControl.serializableRw().setCommitTx(true);

    // Executes data query with specified transaction control settings.
    DataQueryResult result = retryCtx.supplyResult(session -> session.executeDataQuery(query, txControl))
            .join().getValue();

    logger.info("--[ SelectSimple ]--");

    ResultSetReader rs = result.getResultSet(0);
    while (rs.next()) {
        logger.info("read series with id {}, title {} and release_date {}",
                rs.getColumn("series_id").getUint64(),
                rs.getColumn("title").getText(),
                rs.getColumn("release_date").getDate()
        );
    }
}

As a result of the query, an object of the DataQueryResult class is generated. It may contain several sets obtained using the getResultSet( <index> ) method. Since there was only one SELECT statement in the query, the result contains only one selection indexed as 0. The given code snippet prints the following text to the console at startup:

12:06:36.548 INFO  App - --[ SelectSimple ]--
12:06:36.559 INFO  App - read series with id 1, title IT Crowd and release_date 2006-02-03

Parameterized queries

Querying data using parameters. This query execution option is preferable as it allows the server to reuse the query execution plan for subsequent calls and also protects from such vulnerabilities as SQL Injection.

The code snippet below shows the use of parameterized queries and the Params class to generate parameters and pass them to the executeDataQuery method.

private void selectWithParams(long seriesID, long seasonID) {
    String query
            = "DECLARE $seriesId AS Uint64; "
            + "DECLARE $seasonId AS Uint64; "
            + "SELECT sa.title AS season_title, sr.title AS series_title "
            + "FROM seasons AS sa INNER JOIN series AS sr ON sa.series_id = sr.series_id "
            + "WHERE sa.series_id = $seriesId AND sa.season_id = $seasonId";

    // Begin new transaction with SerializableRW mode
    TxControl txControl = TxControl.serializableRw().setCommitTx(true);

    // Type of parameter values should be exactly the same as in DECLARE statements.
    Params params = Params.of(
            "$seriesId", PrimitiveValue.newUint64(seriesID),
            "$seasonId", PrimitiveValue.newUint64(seasonID)
    );

    DataQueryResult result = retryCtx.supplyResult(session -> session.executeDataQuery(query, txControl, params))
            .join().getValue();

    logger.info("--[ SelectWithParams ] -- ");

    ResultSetReader rs = result.getResultSet(0);
    while (rs.next()) {
        logger.info("read season with title {} for series {}",
                rs.getColumn("season_title").getText(),
                rs.getColumn("series_title").getText()
        );
    }
}

Scan queries

Making a scan query that results in a data stream. Streaming lets you read an unlimited number of rows and amount of data.

private void scanQueryWithParams(long seriesID, long seasonID) {
    String query
            = "DECLARE $seriesId AS Uint64; "
            + "DECLARE $seasonId AS Uint64; "
            + "SELECT ep.title AS episode_title, sa.title AS season_title, sr.title AS series_title "
            + "FROM episodes AS ep "
            + "JOIN seasons AS sa ON sa.season_id = ep.season_id "
            + "JOIN series AS sr ON sr.series_id = sa.series_id "
            + "WHERE sa.series_id = $seriesId AND sa.season_id = $seasonId;";

    // Type of parameter values should be exactly the same as in DECLARE statements.
    Params params = Params.of(
            "$seriesId", PrimitiveValue.newUint64(seriesID),
            "$seasonId", PrimitiveValue.newUint64(seasonID)
    );

    logger.info("--[ ExecuteScanQueryWithParams ]--");
    retryCtx.supplyStatus(session -> {
        ExecuteScanQuerySettings settings = ExecuteScanQuerySettings.newBuilder().build();
        return session.executeScanQuery(query, params, settings, rs -> {
            while (rs.next()) {
                logger.info("read episode {} of {} for {}",
                        rs.getColumn("episode_title").getText(),
                        rs.getColumn("season_title").getText(),
                        rs.getColumn("series_title").getText()
                );
            }
        });
    }).join().expectSuccess("scan query problem");
}

Multistep transactions

Multiple commands are executed within a single multistep transaction. The client-side code can be run between query executions. Using a transaction ensures that select queries made in its context are consistent with each other.

To ensure interoperability between the transactions and the retry context, each transaction must wholly execute inside the callback passed to SessionRetryContext. The callback must return after the entire transaction is completed.

Code template for running complex transactions inside SessionRetryContext

private void multiStepTransaction(long seriesID, long seasonID) {
    retryCtx.supplyStatus(session -> {
        // Multiple operations with session
        ...

        // return success status to SessionRetryContext
        return CompletableFuture.completedFuture(Status.SUCCESS);
    }).join().expectSuccess("multistep transaction problem");
}

The first step is to prepare and execute the first query:

    String query1
            = "DECLARE $seriesId AS Uint64; "
            + "DECLARE $seasonId AS Uint64; "
            + "SELECT MIN(first_aired) AS from_date FROM seasons "
            + "WHERE series_id = $seriesId AND season_id = $seasonId;";

    // Execute first query to get the required values to the client.
    // Transaction control settings don't set CommitTx flag to keep transaction active
    // after query execution.
    TxControl tx1 = TxControl.serializableRw().setCommitTx(false);
    DataQueryResult res1 = session.executeDataQuery(query1, tx1, Params.of(
            "$seriesId", PrimitiveValue.newUint64(seriesID),
            "$seasonId", PrimitiveValue.newUint64(seasonID)
    )).join().getValue();

After that, we can process the resulting data on the client side:

    // Perform some client logic on returned values
    ResultSetReader resultSet = res1.getResultSet(0);
    if (!resultSet.next()) {
        throw new RuntimeException("not found first_aired");
    }
    LocalDate fromDate = resultSet.getColumn("from_date").getDate();
    LocalDate toDate = fromDate.plusDays(15);

And get the current transaction id to continue processing within the same transaction:

    // Get active transaction id
    String txId = res1.getTxId();

The next step is to create the next query that uses the results of code execution on the client side:

    // Construct next query based on the results of client logic
    String query2
            = "DECLARE $seriesId AS Uint64;"
            + "DECLARE $fromDate AS Date;"
            + "DECLARE $toDate AS Date;"
            + "SELECT season_id, episode_id, title, air_date FROM episodes "
            + "WHERE series_id = $seriesId AND air_date >= $fromDate AND air_date <= $toDate;";

    // Execute second query.
    // Transaction control settings continues active transaction (tx) and
    // commits it at the end of second query execution.
    TxControl tx2 = TxControl.id(txId).setCommitTx(true);
    DataQueryResult res2 = session.executeDataQuery(query2, tx2, Params.of(
        "$seriesId", PrimitiveValue.newUint64(seriesID),
        "$fromDate", PrimitiveValue.newDate(fromDate),
        "$toDate", PrimitiveValue.newDate(toDate)
    )).join().getValue();

    logger.info("--[ MultiStep ]--");
    ResultSetReader rs = res2.getResultSet(0);
    while (rs.next()) {
        logger.info("read episode {} with air date {}",
                rs.getColumn("title").getText(),
                rs.getColumn("air_date").getDate()
        );
    }

The given code snippets output the following text to the console at startup:

12:06:36.850 INFO  App - --[ MultiStep ]--
12:06:36.851 INFO  App - read episode Grow Fast or Die Slow with air date 2018-03-25
12:06:36.851 INFO  App - read episode Reorientation with air date 2018-04-01
12:06:36.851 INFO  App - read episode Chief Operating Officer with air date 2018-04-08

Managing transactions

Transactions are managed through TCL Begin and Commit calls.

In most cases, instead of explicitly using Begin and Commit calls, it's better to use transaction control parameters in execute calls. This helps you avoid unnecessary requests to YDB and run your queries more efficiently.

Code snippet for beginTransaction() and transaction.commit() calls:

private void tclTransaction() {
    retryCtx.supplyStatus(session -> {
        Transaction transaction = session.beginTransaction(Transaction.Mode.SERIALIZABLE_READ_WRITE)
            .join().getValue();

        String query
                = "DECLARE $airDate AS Date; "
                + "UPDATE episodes SET air_date = $airDate WHERE title = \"TBD\";";

        Params params = Params.of("$airDate", PrimitiveValue.newDate(Instant.now()));

        // Execute data query.
        // Transaction control settings continues active transaction (tx)
        TxControl txControl = TxControl.id(transaction).setCommitTx(false);
        DataQueryResult result = session.executeDataQuery(query, txControl, params)
            .join().getValue();

        logger.info("get transaction {}", result.getTxId());

        // Commit active transaction (tx)
        return transaction.commit();
    }).join().expectSuccess("tcl transaction problem");
}
Previous