Examples of queries with Corda

QueryExamples.kt

// 1. Use the PersistentState custom schema to filter by custom attribute
val customFilter = builder {
    StateSchemaV1.PersistentState::productId.equal(productId)
}
// 2. Create a CustomQueryCriteria using the above filter
val customCriteria = QueryCriteria.VaultCustomQueryCriteria(customFilter)


// 3. Easy and quick filter. This returns a list
val state = serviceHub.vaultService.queryBy<YourState>().states.filter {
    it.state.data.productId == productId
}

// 4. Get the total amount of issued FungibleToken filtered by account and tokenIdentifier
// Assumption: the state is Evolvable so tokenIdentifier = linearId
val criteria = QueryCriteria.VaultQueryCriteria().withExternalIds(listOf(accountIdentifier.id))

var total = 0
serviceHub.vaultService.queryBy<FungibleToken>(criteria).states.forEach { ref ->
    if(ref.state.data.issuedTokenType.tokenType.tokenIdentifier == linearId)
        total += (ref.state.data.amount.quantity).toInt()
}
println(total)

// Custom query using custom fields in queryable state
fun queryCustomUnconsumed(serviceHub: ServiceHub, id: Int) : Int{
    return builder {
        val generalQuery = QueryCriteria.VaultQueryCriteria(Vault.StateStatus.ALL)
        val index = CustomStateSchemaV1.PersistentCustomState::Id.equal(id)
        val customCriteria = QueryCriteria.VaultCustomQueryCriteria(index)
        val criteria = generalQuery.and(customCriteria)
        serviceHub.vaultService.queryBy<CustomState>(criteria)
    }.states.size
}

// Use JDBC to make SQL queries from inside a flow
@StartableByRPC
class GetFieldsFromDB(private val input: String) : FlowLogic<String>() {

    @Suspendable
    override fun call(): String {

        val query = "SELECT <FIELD1,FIELD2> FROM <TABLE_NAME> WHERE <FIELD> = '$input'"
        val statement = serviceHub.jdbcSession().prepareStatement(query).executeQuery()

        while (statement.next()) {
            // read from jdbc statement
            statement.getString("<FIELD1>"))
            statement.getString("<FIELD2>"))
        }
        return "result"
    }
}

// Use Corda Entity Manager to persist/find/delete/merge stuff off-ledger
// Be careful of the primary key configured in the liquidbase xml file.
serviceHub.withEntityManager {
    persist(object)
    merge(object)
    delete(object)
    find(object)
}