#Help me to understand search query

1 messages · Page 1 of 1 (latest)

dull monolith
#

I have a query in spring boot that works as jpql:

@Query("SELECT new Kindergarten(k.id, k.name , k.address, k.elderate, k.capacityAgeGroup2to3, k.capacityAgeGroup3to6) 
FROM Kindergarten k WHERE LOWER(k.name) LIKE LOWER(concat('%', ?1,'%'))")

What does it mean those three things: concat('%', ?1,'%') ?
Interesting is that this works and find and Kindergarten name what you enter. It finds the most similar to the one you print.
But how exactly does it work?
I want to understand how those three wildcards do the magic. What is the meaning of it?
Some theory: https://www.w3schools.com/Sql/sql_like.asp

hollow beacon
#

concat() concatenates several strings. ? is SQL parameter and it will be replaced by some string that you're searching for. For example it becomes concat('%', 'kindergarden'1, '%')
And after concatenation it becomes %kindergarden%. And this string is passed to LOWER() function. That way it will match such name even if there's something before or after "kindergarden"
But I'm not sure what that 1 means after ?. And can't google it. Maybe it's some kind of counter? Or maybe it becomes part of the parameter? Like 'kindergarden1'?

dull monolith
# hollow beacon `concat()` concatenates several strings. `?` is SQL parameter and it will be rep...
public interface KindergartenDAO extends JpaRepository<Kindergarten, String> {

    void deleteByName(String name);

    Kindergarten findByName(String name);
    
    List<Kindergarten> findAllByOrderByNameAsc();
    
    @Query("select k FROM Kindergarten k WHERE LOWER (k.name) LIKE LOWER(concat('%' || :searchString || '%'))" +
            "or lower(k.elderate) like lower(concat('%' || :searchString || '%'))")
    public List<Kindergarten> findByNameOrElderate(@Param("searchString") String searchString);

    @Query("SELECT new Kindergarten(k.id, k.name , k.address, k.elderate, k.capacityAgeGroup2to3, k.capacityAgeGroup3to6) FROM Kindergarten k WHERE LOWER(k.name) LIKE LOWER(concat('%', ?1,'%'))")
    Page<Kindergarten> findByNameContainingIgnoreCase(String name, Pageable pageable);

    @Query("SELECT DISTINCT(k.elderate) FROM Kindergarten k")
    Set<String> findDistinctElderates();

    @Query("SELECT k FROM Kindergarten k WHERE k.capacityAgeGroup2to3>0 OR k.capacityAgeGroup3to6>0")
    List<Kindergarten> findAllWithNonZeroCapacity(Sort ascending);
    
    //....
}

The ?1 in Java sequel means the first parameter from the function. In that particular example it is String name

#

The name

#

It determines how Hibernate should find a string in H2 database table.

#

Whatever word you search it will find it if there is such.

#

% behind and after means that there might be any character behind of after. Even no character at all which therefore means that a word might be in the middle or end or start