Vault-Named Queries

Vault-named queries enable you to query the Corda database. The user can define the following:

• The name of the query
• The query functionality (the type that the query works on and the WHERE clause)
• Optional filtering logic of the result set
• Optional transformation logic of the result set
• Optional collection logic of the result set

The query creator must follow pre-defined steps so that the query is registered and usable.

This section contains the following:

• Representing a State in the Database
• Creating and Registering a Vault-Named Query
• Vault-Named Query Operators

Representing a State in the Database

Each state An immutable object representing a fact known by one or more participants at a specific point in time. You can use states to represent any type of data, and any kind of fact. type can be represented as a pre-defined JSON string (custom_representation column in the database). Use this JSON representation to write the vault-named queries. Implement the net.corda.v5.ledger.utxo.query.json.ContractStateVaultJsonFactory<T> interface. The <T> parameter is the type of the state that we want to represent.

For example, a state type called TestState and a simple contract called TestContract, would look like the following:

package com.r3.corda.demo.contract
class TestContract : Contract {
    override fun verify(transaction: UtxoLedgerTransaction) {}
}
@BelongsToContract(TestContract::class)
class TestState(
    val testField: String,
    private val participants: List<PublicKey>
) : ContractState {
    override fun getParticipants(): List<PublicKey> = participants
}

package com.r3.corda.demo.contract;
class TestContract implements Contract {
    @Override
    public void verify(@NotNull UtxoLedgerTransaction transaction) {}
}
@BelongsToContract(TestContract.class)
public class TestState implements ContractState {
    private final List<PublicKey> participants;
    private final String testField;
    public TestState(@NotNull String testField, @NotNull List<PublicKey> participants) {
        this.testField = testField;
        this.participants = participants;
    }
    @NotNull
    @Override
    public List<PublicKey> getParticipants() {
        return new LinkedList<>(this.participants);
    }
    @NotNull
    public String getTestField() {
        return testField;
    }
}

To represent a state as a JSON string, use ContractStateVaultJsonFactory as follows:

class TestStateJsonFactory : ContractStateVaultJsonFactory<TestState> {
    override fun getStateType(): Class<TestState> = TestState::class.java
    override fun create(state: TestState, jsonMarshallingService: JsonMarshallingService): String {
        return jsonMarshallingService.format(state)
    }
}

class TestUtxoStateJsonFactory implements ContractStateVaultJsonFactory<TestUtxoState> {
    @NotNull
    @Override
    public Class<TestUtxoState> getStateType() {
        return TestUtxoState.class;
    }
    @Override
    @NotNull
    public String create(@NotNull TestUtxoState state, @NotNull JsonMarshallingService jsonMarshallingService) {
        return jsonMarshallingService.format(state);
    }
}

After the output state finalizes, it is represented as the following in the database (custom_representation column) with a stateRef field stored under the ContractState JSON object:

{
  "net.corda.v5.ledger.utxo.ContractState" : {
    "stateRef": "<TransactionID>:<StateIndex>"
  },
  "com.r3.corda.demo.contract.TestState" : {
    "testField": ""
  }
}

Use this representation to create the vault-named queries in the next section.

Creating and Registering a Vault-Named Query

Registration means that the query is stored on sandbox An execution environment within a JVM process that provides isolation for a CorDapp. It shields it from outside threats but it also restricts what it can do so that running potentially dangerous code cannot harm others. creation time and can be executed later. Vault-named queries must be part of a contract CPK. Corda installs the vault-named query when the contract CPK is uploaded. To create and register a query, the net.corda.v5.ledger.utxo.query.VaultNamedQueryFactory interface must be implemented.

Basic Vault-Named Query Registration Example

A simple vault-named query implementation for this TestState would look like this:

class DummyCustomQueryFactory : VaultNamedQueryFactory {
    override fun create(vaultNamedQueryBuilderFactory: VaultNamedQueryBuilderFactory) {
        vaultNamedQueryBuilderFactory.create("DUMMY_CUSTOM_QUERY")
            .whereJson(
                "WHERE visible_states.custom_representation -> 'com.r3.corda.demo.contract.TestState' " +
                        "->> 'testField' = :testField"
            )
            .register()
    }
}

public class DummyCustomQueryFactory implements VaultNamedQueryFactory {
    @Override
    public void create(@NotNull VaultNamedQueryBuilderFactory vaultNamedQueryBuilderFactory) {
        vaultNamedQueryBuilderFactory.create("DUMMY_CUSTOM_QUERY")
                .whereJson(
                        "WHERE visible_states.custom_representation -> 'com.r3.corda.demo.contract.TestState' " +
                                "->> 'testField' = :testField"
                )
                .register();
    }
}

The interface called VaultNamesQueryFactory has one method: void create(@NotNull VaultNamedQueryBuilderFactory vaultNamedQueryBuilderFactory);.

This function is called during start-up and it defines how a query operates in it with the following steps:

• To create a vault-named query with a given name, in this case it is DUMMY_CUSTOM_QUERY, call vaultNamedQueryBuilderFactory.create().

• To define how a query’s WHERE clause will work, call whereJson().

  • Always start with the actual WHERE statement and then write the rest of the clause. You must prefix fields with the visible_states. qualifier. Since visible_states.custom_representation is a JSON column, you can use some JSON-specific operations.
  • Parameters can be used in the query in a :parameter format. In this example, use a parameter called :testField which can be set when executing this query. This works similarly to popular Java SQL libraries such as Hibernate.

• To finalize query creation and to store the created query in the registry to be executed later, call register(). This call must be the last step when defining a query.

Complex Query Example

For example, to add some extra logic to the query:

• Only keep the results that have “Alice” in their participant list.
• Transform the result set to only keep the transaction A transaction is a proposal to update the ledger. IDs.
• Collect the result set into one single integer.

These optional logics are always applied in the following order:

1. Filtering
2. Transforming
3. Collecting

Filtering

To create a filtering logic, implement the net.corda.v5.ledger.utxo.query.VaultNamedQueryStateAndRefFilter<T> interface. The <T> type here is the state type that will be returned from the database, which in this case is TestState. This interface has only one function. This defines whether or not to keep the given element (row) from the result set. Elements returning true are kept and the rest are discarded.

In this example, keep the elements that have “Alice” in their participant list:

class DummyCustomQueryFilter : VaultNamedQueryStateAndRefFilter<TestState> {
    override fun filter(data: StateAndRef<TestUtxoState>, parameters: MutableMap<String, Any>): Boolean {
        return data.state.contractState.participantNames.contains("Alice")
    }
}

class DummyCustomQueryFilter implements VaultNamedQueryStateAndRefFilter<TestUtxoState> {
    @NotNull
    @Override
    public Boolean filter(@NotNull StateAndRef<TestUtxoState> data, @NotNull Map<String, Object> parameters) {
        return data.getState().getContractState().getParticipantNames().contains("Alice");
    }
}

Transforming

To create a transformer class, only keep the transaction IDs of each record. Transformer classes must implement the VaultNamedQueryStateAndRefTransformer<T, R> interface, where:

• <T> is the type of results returned from the database, which in this case is TestState.
• <R> is the type to transform the results into.
• Transaction IDs are specified as Strings.

This interface has one function:

@NotNull`
`R transform(@NotNull T data, @NotNull Map<String, Object> parameters);

This defines how each record (“row”) will be transformed (mapped):

class DummyCustomQueryTransformer : VaultNamedQueryStateAndRefTransformer<TestState, String> {
    override fun transform(data: StateAndRef<TestState>, parameters: MutableMap<String, Any>): String {
        return data.ref.transactionId.toString()
    }
}

class DummyCustomQueryMapper implements VaultNamedQueryStateAndRefTransformer<TestUtxoState, String> {
    @NotNull
    @Override
    public String transform(@NotNull StateAndRef<TestUtxoState> data, @NotNull Map<String, Object> parameters) {
        return data.getRef().getTransactionId().toString();
    }
}

Collecting

Collecting is used to collect results set into one single integer. For this, implement the net.corda.v5.ledger.utxo.query.VaultNamedQueryCollector<R, T> interface, where:

• <T> is the type collected into (in this case, an Int).
• <R> is the type of the original result set (in this case String because of transformation).

This interface has only one method:

@NotNull
Result<T> collect(@NotNull List<R> resultSet, @NotNull Map<String, Object> parameters);

This defines how to collect the result set. The collector class should look like the following:

class DummyCustomQueryCollector : VaultNamedQueryCollector<String, Int> {
    override fun collect(
        resultSet: MutableList<String>,
        parameters: MutableMap<String, Any>
    ): VaultNamedQueryCollector.Result<Int> {
        return VaultNamedQueryCollector.Result(
            listOf(resultSet.size),
            true
        )
    }
}

class DummyCustomQueryCollector implements VaultNamedQueryCollector<String, Integer> {
    @NotNull
    @Override
    public Result<Integer> collect(@NotNull List<String> resultSet, @NotNull Map<String, Object> parameters) {
        return new Result<>(
                List.of(resultSet.size()),
                true
        );
    }
}

Registering a Complex Query

Register a complex query with a filter, a transformer, and a collector with the following example:

class DummyCustomQueryFactory : VaultNamedQueryFactory {
    override fun create(vaultNamedQueryBuilderFactory: VaultNamedQueryBuilderFactory) {
        vaultNamedQueryBuilderFactory.create("DUMMY_CUSTOM_QUERY")
            .whereJson(
                "WHERE visible_states.custom_representation -> 'com.r3.corda.demo.contract.TestState' " +
                        "->> 'testField' = :testField"
            )
            .filter(DummyCustomQueryFilter())
            .map(DummyCustomQueryMapper())
            .collect(DummyCustomQueryCollector())
            .register()
    }
}

public class JsonQueryFactory implements VaultNamedQueryFactory {
    @Override
    public void create(@NotNull VaultNamedQueryBuilderFactory vaultNamedQueryBuilderFactory) {
        vaultNamedQueryBuilderFactory.create("DUMMY_CUSTOM_QUERY")
                .whereJson(
                        "WHERE visible_states.custom_representation -> 'com.r3.corda.demo.contract.TestState' " +
                                "->> 'testField' = :testField"
                )
                .filter(new DummyCustomQueryFilter())
                .map(new DummyCustomQueryMapper())
                .collect(new DummyCustomQueryCollector())
                .register();
    }
}

Executing a Vault-Named Query

To execute a query use UtxoLedgerService. This can be injected to a flow Communication between participants in an application network is peer-to-peer using flows. via @CordaInject. To instantiate a query call the following:

utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Int::class.java)

utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Integer.class)

Provide the name of the query (in this case DUMMY_CUSTOM_QUERY) and the return type. Since the result set is collected into an integer in the complex query example, use Int (or Integer in Java). Before executing, define the following:

• How many results each page of the query should return (setLimit). The default value is Int.MAX (2,147,483,647).
• Named parameters that are in the query and the actual value for them (setParameter or setParameters). All parameters must be defined, otherwise the execution will fail.
• An upper limit (setTimestampLimit) to only return states that were inserted before a given time. Each state in the database has a timestamp value for when it was inserted.

It is not necessary to call ParameterizedQuery.setOffset as the query pages the results based on each state’s created timestamp.

In this case it would look like this:

val resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Int::class.java) // Instantiate the query
            .setLimit(1000) // Only return 1000 records per page
            .setParameter("testField", "dummy") // Set the parameter to a dummy value
            .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
            .execute() // execute the query

PagedQuery.ResultSet<Integer> resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Integer.class) // Instantiate the query
                .setLimit(1000) // Only return 1000 records per page
                .setParameter("testField", "dummy") // Set the parameter to a dummy value
                .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
                .execute(); // execute the query

Results can be acquired by calling getResults() on the ResultSet. Call hasNext() to check if there are more results to retrieve and next() to move onto the next page:

val resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Int::class.java) // Instantiate the query
            .setLimit(1000) // Only return 1000 records
            .setParameter("testField", "dummy") // Set the parameter to a dummy value
            .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
            .execute()
var results = resultSet.results
while (resultSet.hasNext()) {
    results = resultSet.next()
}

ResultSet<Integer> resultSet = utxoLedgerService.query("DUMMY_CUSTOM_QUERY", Integer.class) // Instantiate the query
                .setLimit(1000) // Only return 1000 records
                .setParameter("testField", "dummy") // Set the parameter to a dummy value
                .setCreatedTimestampLimit(Instant.now()) // Set the timestamp limit to the current time
                .execute();
List<Integer> results = resultSet.getResults();
while (resultSet.hasNext()) {
    results = resultSet.next();
}

Vault-Named Query Operators

The following are the standard operators for vault-named queries:

• IN
• LIKE
• IS NOT NULL
• IS NULL
• AS
• OR
• AND
• !=
• >
• <
• >=
• <=
• ->
• ->>
• ?
• ::

Where the behavior is not standard, the operators are explained with examples in the following table:

{
  "com.r3.corda.demo.ArrayObjState": [
    {"A": 1},
    {"B": 2}
  ]
}

{
  "A": 1
}

{
  "com.r3.corda.demo.TestState": {
    "testField": "ABC"
  }
}

{
  "testField": "ABC"
}

{
  "com.r3.corda.demo.ArrayState": [
    5, 6, 7
  ]
}

{
  "com.r3.corda.demo.TestState": {
    "testField": "ABC"
  }
}

{
  "com.r3.corda.demo.TestState": {
    "testField": "ABC"
  }
}