如何在JPA中编写动态sql查询来查询jsonb列中的数据

Question

我想写一个动态JPA查询来查询jsonb列中的数据吗？

示例：

recordid |id | typename | useridentifier | value | tags                                                                                                                                                                             
4793 | 8d7af416-3940-4b7e-9dbb-972b5a31c4fb | AF.ConnectionRecord | z6MkiXvHoNWQEs9RWpLy8mibb9BBQxrsAqiWbZmmAxNBQurq | {"Id": "8d7af416-3940-4b7e-9dbb-972b5a31c4fb", "Alias": {"Name": "did:dtx:z6Mkw87LmKzcDty6ht5L636kzxHH7DjiTVJuuaGbTMpWYwF9"}, "State": "Connected", "Endpoint": {"uri": "http://127.0.0.1:5000/api/sharedagent/endpoint?identifier=HfrJB5kAtMUdbPEdQU8v9rjHHeTs3c4ZDZMfd5rVdiTm-user", "verkey": ["HfrJB5kAtMUdbPEdQU8v9rjHHeTs3c4ZDZMfd5rVdiTm"]}, "MiscData": {"MyOrgDid": "did:dtx:z6MkiXvHoNWQEs9RWpLy8mibb9BBQxrsAqiWbZmmAxNBQurq", "TheirOrgDid": "did:dtx:z6Mkw87LmKzcDty6ht5L636kzxHH7DjiTVJuuaGbTMpWYwF9"}, "threadId": "f8a815a5-05ab-4198-8613-a57641cab9ca", "CreatedAtUtc": "2021-07-14T05:25:10.7722507", "UpdatedAtUtc": "2021-07-14T05:28:23.752349"} | {"MyVk": "HJaPNSqrJm1yCLbibt5YidnMd7CZtZq4VuJNT1G5uwBq", "MyDid": "did:dtx:z6MkvkqRxh6HeJWSJqSRHT3PZjLMSgURJT5RBvDJHHE6q9yD", "State": "Connected", "TheirVk": "Ec4by1o9UVW91kEGRij5Cs2bSEHY3p8wLE2JMAGe4fwW", "TheirDid": "did:dtx:z6Mkt4KeZG3ap2zc8F4y7Hgv3xabFoZPThPJ2EwEBSEeytit", "threadId": "f8a815a5-05ab-4198-8613-a57641cab9ca", "connectionKey": "SQd4RocaX5QJi9TJ2bgpLht5zU8r5YjwZM1xgV2Dbwn", "~CreatedAtUtc": "637618371107722507", "~UpdatedAtUtc": "637618373037523490", "MultiPartyInvitation": "False", "autoAcceptConnection": "true"}

现在，我的查询将是

select * from nonsecretrecords where typename = 'AF.ConnectionRecord' 
AND useridentifier = 'z6MkiXvHoNWQEs9RWpLy8mibb9BBQxrsAqiWbZmmAxNBQurq' 
AND tags ->> 'MyVk' = 'HJaPNSqrJm1yCLbibt5YidnMd7CZtZq4VuJNT1G5uwBq' 
AND tags ->> 'connectionKey' = 'SQd4RocaX5QJi9TJ2bgpLht5zU8r5YjwZM1xgV2Dbwn';

这里的"tags"是一个jsonb类型的列，所以我的查询与上面的查询类似。这是一个示例查询，我想创建一个动态查询，这样标记中的部分(此处为"MyVk"one_answers"connectionKey"(将是动态的，并且可以更改。

如何使用Spring JPA实现这一点？

感谢

你好@nikolai

你能提出更多的想法吗。这是我在服务中的规范：-

public static Specification<NonSecretRecordEntity> findBySpecification(String typename) {
return (root, query, builder) -> {
return builder.equal(root.get("typeName"), typename);
};
}

我想根据"；标签"；列，但我还没有为该标记列定义一个类，它是使用ObjectMapper的泛型。

Answer 1

您的规范的新版本如下所示：

public static Specification<NonSecretRecordEntity> findBySpecification(
String typename, 
String useridentifier, 
Map<String, String> tags
) {
return (root, query, builder) -> {
List<Predicate> predicates = new ArrayList<>();
predicates.add(builder.equal(root.get("typeName"), typename));
predicates.add(builder.equal(root.get("useridentifier"), useridentifier));
tags.forEach((tagName, tagValue) -> {
predicates.add(builder.equal(
builder.function("jsonb_extract_path_text", 
String.class, 
root.get("tags"), 
builder.literal(tagName)),
tagValue
));
});
return builder.and(predicates.toArray(new Predicate[0]));
};
}

并像这个一样使用

String typeName = "AF.ConnectionRecord";
String useridentifier = "z6MkiXvHoNWQEs9RWpLy8mibb9BBQxrsAqiWbZmmAxNBQurq";
Map<String, String> tags = new HashMap<>();
tags.put("MyVk", "HJaPNSqrJm1yCLbibt5YidnMd7CZtZq4VuJNT1G5uwBq");
tags.put("connectionKey", "SQd4RocaX5QJi9TJ2bgpLht5zU8r5YjwZM1xgV2Dbwn");
Specification<NonSecretRecordEntity> spec = findBySpecification(typeName, useridentifier, tags);