Search code examples
jpacriteriavarcharnvarchar

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

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?

Solution

  • I have found out from the official doc that its a limitation of 'Like' predicate to consider first 255 characters. Anything larger than that will be ignored.

    https://learn.microsoft.com/en-us/sql/odbc/microsoft/like-predicate-limitations?redirectedfrom=MSDN&view=sql-server-ver16