Stop Criteria JPA Like query converting NVARCHAR field to Varchar(255)-CodePudding

I am using JPA Criteria to run a like query on a NVARCHAR column. The column(events) can hold maximum(2000) characters. Now this is the query :

if (Objects.nonNull(eventStatus)) {
        String eStat = "\"event_status\"";
        String val ="\"" eventStatus "\"";
    predicates.add(cb.like(item.get("events").as(String.class), "%" eStat "%" " " val "%"));
    }

Now this is not generating expected result and when I saw query log I saw this :

Hibernate: 
    select
        count(itemadditi0_.item_additional_info_id) as col_0_0_ 
    from
        Allocation.item_additional_info itemadditi0_ 
    where
        itemadditi0_.tenant_id=? 
        and (
            cast(itemadditi0_.events as varchar(255)) like ?
        )

So I guess I am not getting the result because of this line : cast(itemadditi0_.events as varchar(255)) like ?

In SSMS this query working just fine:

(select item_nbr,item_status,events from Allocation.item_additional_info  where events like '%"event_status"%"Past"%');

In Model we are saving events as follow

@Column(name="events",columnDefinition="nvarchar")
    @Convert(converter = EventConverterJson.class)
    private List<EventInfo> events;

So can you please help me on how resolve issue like in this scenario ? Or how to stop JPA Criteria from that casting?

CodePudding user response：

Why are you casting the object to String?

This should work just fine:

// Example: '%event_status% Past%'
final String likePattern =  "%" eStat "%" val " " "%";
predicates.add(cb.like(item.get("events"), likePattern));

Assuming the item is of type Root<...>.

Also, the pattern you are using for the like predicate is different than the one you are showing as working (it contains an empty space and won't contain "). If you want to generate that exact pattern, you can do it with:

// Example: '%"event_status"%"Past"%'
final String likePattern =  "%\""   eStat   "\"%\""   val  "\"%";