我想在corda(m14)数据库中选择一个与至少6个字段中的3个字段相匹配的标准,并通过匹配字段对这些结果进行排序。
。这是选择字段的SQL语法:
WHERE (field1 = ?) + (field2 = ?) + (... = ?) > 3
并订购:
ORDER BY ((field1 = ?) + (field2 = ?) + (... = ?)) DESC
另一种做法:
SELECT *, ((field1 = @inputFirst) + (field2 = @inputLast)) as Matches
FROM mytable
HAVING Matches > 1
ORDER BY Matches DESC
我开始创建标准:
vaultCriteria
.or(QueryCriteria.VaultCustomQueryCriteria(field1))
.or(QueryCriteria.VaultCustomQueryCriteria(field12))
.or(QueryCriteria.VaultCustomQueryCriteria(field3))
,但我现在一直坚持如何按照字段的匹配编号对这些结果进行分组,然后对这些想法进行排序?
谢谢,
loup
对于M14版本,您有2个选项:
1)直接从DataBaseTransActionManager中获得JDBCSESSION:
val jdbcSession1 = DatabaseTransactionManager.current().connection
2)从RequerConfiguration对象中间接获得JDBCSESSION:
val jdbcSession2 = RequeryConfiguration(<dataSourceProperties>).jdbcSession()
<dataSourceProperties>
看起来像这样:
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
}
对于高级自定义SQL查询,建议您使用标准JDBCSession
,可从ServiceHub
获得。
请参阅https://docs.corda.net/head/api-persistence.html?highlight = jdbcsession
我尚未测试使用API的可能程度,但是我通过编写自定义SQL来取得成功(在当前主人上) - 例如。
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()