Criteria Query with filter of non String type - JPA/Hibernate Spring Boot-CodePudding

I am trying to fetch the count of rows using Criteria Query with filter. This filter is a JsonNode with key as the column in entity and value can be of type String or JsonNode depending on the filter passed by user.

This is my code -

    public Long getCountForMyEntity(JsonNode filter) {
        CriteriaBuilder criteriaBuilder = sessionFactory.getCriteriaBuilder();
        CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
        Root<MyEntity> root = query.from(MyEntity.class);
        query.select(criteriaBuilder.count(root));

        Iterator<Map.Entry<String, JsonNode>> filterFields = filter.fields();

        while(filterFields.hasNext()) {
            Map.Entry<String, JsonNode> field = filterFields.next();
            String   fieldName  = field.getKey();
            JsonNode fieldValue = field.getValue();
            Object value = fieldValue;

            System.out.println(fieldValue   " : "   fieldValue.getNodeType());
            if (fieldValue.getNodeType() == JsonNodeType.STRING) {
                value = fieldValue.asText();
            }

            query.where(criteriaBuilder.equal(root.get(fieldName), value));
        }

        return sessionFactory.getCurrentSession().createQuery(query).getSingleResult();
    }

This works for fetching count when filter value is string but when filter value is json, I get the following error -

44- [WARN ] 2021-10-26 14:42:35 [http-nio-9090-exec-7] o.h.e.j.s.SqlExceptionHelper [][][][] - SQL Error: 0, SQLState: 42883
45- [ERROR] 2021-10-26 14:42:35 [http-nio-9090-exec-7] o.h.e.j.s.SqlExceptionHelper [][][][] - ERROR: operator does not exist: json = unknown
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 121
46- [WARN ] 2021-10-26 14:42:35 [http-nio-9090-exec-7] o.s.w.s.m.m.a.ExceptionHandlerExceptionResolver [][][][] - Resolved [org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet]

I have tried directly passing fieldValue like so, but still the same error -

query.where(criteriaBuilder.equal(root.get(fieldName), fieldValue));

Any idea where I'm going wrong? Is there a good way of filtering json/non-string values?

CodePudding user response：

use JSONB instead of JSON or cast JSON to JSONB. You cannot compare JSON values. You can compare text values instead. you can solve this problem using type JSONB, which is stored in a decomposed binary format. Values of this type can be compared.

SELECT 
'["dramatic" ,"women", "political"]'::json::text =  
'["dramatic","women","political"]'::json::text      -- yields false!


SELECT 
'["dramatic" ,"women", "political"]'::jsonb =  
'["dramatic","women","political"]'::jsonb           -- yields true

so your query should work like this to eliminate the error.

SELECT * 
FROM movie_test 
WHERE tags::jsonb = '["dramatic","women","political"]'::jsonb

CodePudding user response：

The @JoinColumn annotation specifies the name of the column being used as the foreign key on the targeted entity. The first field should be id maybe there is this issue so try this or may it was happening because the data field here can have an empty value and empty is not a Double and also empty is not null. Here is you can go through

Here see for the data field