CODEWITHSUNDEEP
mysqlhibernatespring-data-jpanamed-query
kaffein
kaffein

Reputation: 1766

How to use "like" predicate and "%" in JPA/Hibernate @NamedNativeQuery and Spring Data

I am currently trying to implement an @NamedNativeQuery for one of my entity classes. My project uses Spring data and I have two tables in my DB : Pizza and Town (origin of the pizza). The query itself consists in finding the pizza by providing a town name. So, finding whether the town.name contains the provided parameter (a part of the town name in our case). So naturally, I have something like this :

@NamedNativeQueries({
@NamedNativeQuery(
    name = "Pizza.findByTown",
    query = "select p.* from pizza p join town t on p.town_id = t.id on where t.name like '%?1%'",
    resultClass = Pizza.class
)
})
public class Pizza {
}

So in my PizzaRepository, I have added the following

public interface PizzaRepository extends JpaRepository<Pizza, Long>, QueryDslPredicateExecutor<Pizza> {
    Pizza findByTown(final String townName)

It seems that the % does not work with the named query. Is there a work around for this ? Any help would be appreciated.

thanks

[EDIT] : I have added the annotated class Town

@Entity
@Table(name = "Town")
public class Town {
    @Id
    @Column(name = "town_id")
    private String townId;

    @Column(name = "name", nullable = false)
    private String name;

    @Lob
    @Column(name = "picture", nullable = false)
    private byte[] picture;
}

Upvotes: 1

Views: 3537

Answers (2)

willome
willome

Reputation: 3152

Do you really need a native query for this request. WIth JPQL/HQL it would be easier to use findByTownContaining : that would add directly your %. See Table 2.2. Supported keywords inside method names on spring data jpa documentation http://static.springsource.org/spring-data/jpa/docs/1.3.0.RELEASE/reference/html/jpa.repositories.html

Upvotes: 0

Nayan Wadekar
Nayan Wadekar

Reputation: 11622

You can try the below query

select p.* from pizza p join town t on p.town_id = t.id on where t.name like :param_1"

And then in code, can set parameter as below.

query.setParameter("param_1", "%"+param_value+"%");

Upvotes: 1

Related Questions