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
}