CODEWITHSUNDEEP
spring-data-jpa
AvaTaylor
AvaTaylor

Reputation: 717

How do I build this multi-valued query in a Spring Data JPA repository?

I have a Car database with a Spring Data JPA repository with a search method that looks like this:

@Repository
public interface CarRepo extends JpaRepository<Car, Integer> {

    @Query(value = "SELECT c FROM Car c WHERE c.make like :term or c.model like :term or c.trim like :term")
    List<CarListing> doSearch(@Param("term") String term);

}

If I search for "Jeep" I get all Jeeps because it matches on the make field.

If I search for "Grand Cherokee" I get all Grand Cherokees because it makes on the model field.

However, if I search for "Jeep Grand Cherokee" it matches nothing.

What I need to do is something like ...

WHERE 
make LIKE "Jeep" OR model LIKE "Jeep" OR trim LIKE "Jeep"
OR
make LIKE "Grand" OR model LIKE "Grand" OR trim LIKE "Grand"
OR
make LIKE "Cherokee" OR model LIKE "Cherokee" OR trim LIKE "Cherokee"

What is the appropriate way to implement this with a Spring Data JPA repository?

Is the right solution to dynamically build a SQL query and execute it natively?

Upvotes: 0

Views: 288

Answers (2)

Davide D&#39;Alto
Davide D&#39;Alto

Reputation: 8206

What you are trying to do is a full-text search.

Trying to do it with SQL is not simple because you will always run in all sorts of exceptional cases. You should check if your database has some form of full-text features or even better, use a purpose built library.

For example, Hibernate Search integrates with your JPA model and allows one to run the kind of queries you need. Here's the link to the documentation.

Upvotes: 1

AvaTaylor
AvaTaylor

Reputation: 717

Here's my solution, it was pretty easy to do, but I expect there's a more "proper" JPA Repository way of doing this.

I created a new DAO class to get the EntityManager and execute my own native query ...

@Component
public class CarDAO {

  @Autowired
  EntityManager em;

  public List<Integer> doSearch(String searchTerm) {
    StringBuilder sbSQL = new StringBuilder(); 
    // ... tokenize the search term and build the big custom SQL query as needed

    Query query = entityManager.createNativeQuery(sbSQL.toString());
    List<Integer> ids = query.getResultList();
    return ids;
  }
}

... then in my controller class I did this ...

@Autowired
private CarDAO carDAO; // my own simple DAO
@Autowired
private CarRepo carRepo; // the Spring Data JPA repository
    
/* ... */

List<Integer> ids = carDAO.doSearch(search);
List<Car> cars = carRepo.findAllById(ids);

Upvotes: 0

Related Questions