How to Create a Spring JPA Repository Findby Query Using a Property That Contains a Keyword

How do you create a Spring JPA repository findBy query using a property that contains a keyword?

To overcome this problem, I've defined the query manually using the @Query annotation. I'll happily accept anyone else's answer if they find a solution that doesn't require a manual query.

public interface ThingRepository extends JpaRepository<ThingEntity, Long> {

    @Query("SELECT t FROM Thing t WHERE t.fooIn = ?1 AND t.bar = ?2")
    ThingEntity findByFooInAndBar(String fooIn, String bar);
}

Spring Data JPA repository method with property name containing a special word

A workaround is to rename the Java field to avoid special words, while preserving the column name using the @Column annotation.

@Column(name = "nameOrId")
public String nameId;

The JPA method is renamed accordingly.

MyObj findByNameId(String nameOrId);

How can I search a keyword in entity's list?

So, you want to find a single Product, containing keyword? But findByKeywordsContaining can potentially find several Products. So return type should be List<Product> .

If you want to return Optional<Product>, then your method should be called something like findFirstByKeywordsContaining

Here's a query, Hibernate makes

select
       product0_.id as id1_1_,
       product0_.price as price2_1_,
       product0_.title as title3_1_
from
     products product0_
where
      ? in (
          select
                 keywords1_.keywords
          from
               product_keywords keywords1_
          where
                product0_.id=keywords1_.product_id
          )
;

The query is ok and if you substitute ? for a keyword, it gives right results. But Spring substitutes it for %keyword% (surrounded with percent sign) and it doesn't work of course.

There's another way to what you want.

public interface ProductRepository extends JpaRepository<Product, Long> {
    List<Product> findByKeywordsIn(List<String> keywords);
}

This forms an sql query, which actually gets results, even though you have to provide list of keywords, instead of a single keyword

Hibernate generates another query for that

select
       product0_.id as id1_1_,
       product0_.price as price2_1_,
       product0_.title as title3_1_
from
     products product0_
         left outer join product_keywords keywords1_
             on product0_.id=keywords1_.product_id

where 
     keywords1_.keywords in (?)

And Spring passeds an array to ? parameter, so everything works.

As far as I understand, both queries are roughly equivalent, because RDBMS engine should optimize them to the same execution plan. But it depends on RMBMS implementation and indices.

Query by Boolean properties in spring-data-jpa without using method parameters

The JPA repository section query creation has the following methods.

True    findByActiveTrue()  … where x.active = true
False   findByActiveFalse() … where x.active = false

My guess would be to use

@Query
public Iterable<Entity> findByEnabledTrue();

spring boot CrudRepository findby function could not mapped

This is a tough one, I would highly suggest that you adhere to standard Java conventions such as starting all of your field names with a lower case letter (camelCase format). I believe that is the reason that it is not able to find the field.

You will want it to look like this in the Repository

`List<HolidaySchedule> findByHDay(String hDay);`

and

@Column(name="HSD_DAY")
private String hDay;

in your model.

Start with this and see where it gets you.

See below for Spring Reference

https://docs.spring.io/spring-data/jpa/docs/1.11.6.RELEASE/reference/html/#jpa.query-methods.query-creation

Spring CrudRepository findByInventoryIds(List<Long> inventoryIdList) - equivalent to IN clause

findByInventoryIdIn(List<Long> inventoryIdList) should do the trick.

The HTTP request parameter format would be like so:

Yes ?id=1,2,3
No  ?id=1&id=2&id=3

The complete list of JPA repository keywords can be found in the current documentation listing. It shows that IsIn is equivalent – if you prefer the verb for readability – and that JPA also supports NotIn and IsNotIn.

---

Related Topics