Query criteria for matching 3 of 6 fields

Question

I would like to select rows in the Corda (M14) database with a criteria that matches at least 3 of 6 fields and sort these results by matching fields.

Here is the SQL syntax to select fields:

WHERE (field1 = ?) + (field2 = ?) + (... = ?) > 3

and to order it:

ORDER BY ((field1 = ?) + (field2 = ?) + (... = ?)) DESC

Another way of doing it :

SELECT *, ((field1 = @inputFirst) + (field2 = @inputLast)) as Matches
FROM mytable
HAVING Matches > 1
ORDER BY Matches DESC

I started to create the criteria:

vaultCriteria
            .or(QueryCriteria.VaultCustomQueryCriteria(field1))
            .or(QueryCriteria.VaultCustomQueryCriteria(field12))
            .or(QueryCriteria.VaultCustomQueryCriteria(field3))

But I am stuck now on how to GROUP theses results by fields' matching number and SORT these, any ideas ?

Thank you,

Loup

Answer 1

For M14 release you have 2 options:

1) Get a jdbcSession directly from the DatabaseTransactionManager:

val jdbcSession1 = DatabaseTransactionManager.current().connection

2) Get a jdbcSession indirectly from a RequeryConfiguration object:

val jdbcSession2 = RequeryConfiguration(<dataSourceProperties>).jdbcSession()

where <dataSourceProperties> looks something like this:

private fun makePersistentDataSourceProperties(): Properties {
    val props = Properties()
    props.setProperty("dataSourceClassName", "org.h2.jdbcx.JdbcDataSource")
    props.setProperty("dataSource.url", "jdbc:h2:~/test/vault_query_persistence;DB_CLOSE_ON_EXIT=TRUE")
    props.setProperty("dataSource.user", "sa")
    props.setProperty("dataSource.password", "")
    return props
}

Answer 2

For advanced custom SQL queries it is recommended you use a standard JDBCSession, obtainable from the ServiceHub.

Please see https://docs.corda.net/head/api-persistence.html?highlight=jdbcsession

Answer 3

I haven't tested how far it's possible to take the API, but I've had success (on current Master) by writing custom SQL - e.g.

        val session = services.jdbcSession()
        val consensusQuery = """
        SELECT COUNT(*) - COUNT(NULLIF(factObject, ?)), COUNT(*)
        FROM submission_states
        WHERE factSubject = ? AND factPredicate = ?
        """
        val consensusStatement = session.prepareStatement(consensusQuery)

        consensusStatement.setString(1, factConsensusQuery.factObject)
        consensusStatement.setString(2, factConsensusQuery.factSubject)
        consensusStatement.setString(3, factConsensusQuery.factPredicate)

        log.info("SQL to execute: " + consensusStatement.toString())
        val rs = consensusStatement.executeQuery()