Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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_ 
        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?
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+ "\"%";
                The query is wrong. Make sure that the string in the second parameter of the like predicate is correct. I don't know what it should be because the one you use in the java example is different than the one you say it's working. You should also enable the SQL logs so that you can make sure that's the actual query is correct
– Davide D'Alto
                Nov 1, 2022 at 11:21
                In SQL log that explicit typecasting is not happening that I checked. However in the Model class we are saving events as object I have updated my question as well.
– SUVAM ROY
                Nov 1, 2022 at 11:32
                I don't understand what you are saying. It doesn't make any difference. Add in the question the exact SQL query you want to run and I can tell you how to generate it with criteria.
– Davide D'Alto
                Nov 1, 2022 at 11:36
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.