Currently I have been using following Spring JPA Repository base custom query and it works fine,
@Query("SELECT usr FROM User usr WHERE usr.configurable = TRUE " + "AND (" + "lower(usr.name) like lower(:filterText) OR lower(usr.userType.classType.displayName) like lower(:filterText) OR lower(usr.userType.model) like lower(:filterText)" + ")" + "") public List<User> findByFilterText(@Param("filterText") String filterText, Sort sort);
I need to modify this query when filter text going to be a comma separated value. But as following manner it will be a dynamic query and how can I execute it.
Dynamic query I need to build,
String sql = "SELECT usr FROM User usr WHERE usr.configurable = TRUE"; for(String word : filterText.split(",")) { sql += " AND (lower(usr.name) like lower(:" + word + ") OR lower(usr.userType.classType.displayName) like lower(:" + word + ") OR lower(usr.userType.model) like lower(:" + word + "))"; }
Using EntityManager methods createQuery or createNativeQuery , you can create a Query object dynamically at run time (see "Using Java"). Using the Query methods getResultList , getSingleResult , or executeUpdate you can execute the query (see "Executing a Query").
In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm.xml file.
Per JB Nizet and the spring-data documentation, you should use a custom interface + repository implementation.
Create an interface with the method:
public interface MyEntityRepositoryCustom { List<User> findByFilterText(Set<String> words); }
Create an implementation:
@Repository public class MyEntityRepositoryImpl implements MyEntityRepositoryCustom { @PersistenceContext private EntityManager entityManager; public List<User> findByFilterText(Set<String> words) { // implementation below } }
Extend the new interface in your existing Repository interface:
public interface MyEntityRepository extends JpaRepository<MyEntity, Long>, MyEntityRepositoryCustom { // other query methods }
Finally, call the method somewhere else:
dao.findByFilterText(new HashSet<String>(Arrays.asList(filterText.split(","))));
Query implementation
Your method of producing the sql
variable, namely by concatenating some strings into the query is bad. Do not do this.
The word
which you are concatenating must be a valid JPQL identifier, namely a :
followed by a java identifier start, optionally followed by some java identifier part. This means that if your CSV contains foo bar,baz
, you will attempt to use foo bar
as an identifier and you'll get an exception.
You can instead use CriteriaBuilder
to construct the query in a safe way:
public List<User> findByFilterText(Set<String> words) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<User> q = cb.createQuery(User.class); Root<User> user = q.from(User.class); Path<String> namePath = user.get("name"); Path<String> userTypeClassTypeDisplayName = user.get("userType").get("classType").get("displayName"); Path<String> userTypeModel = user.get("userType").get("model"); List<Predicate> predicates = new ArrayList<>(); for(String word : words) { Expression<String> wordLiteral = cb.literal(word); predicates.add( cb.or( cb.like(cb.lower(namePath), cb.lower(wordLiteral)), cb.like(cb.lower(userTypeClassTypeDisplayName), cb.lower(wordLiteral)), cb.like(cb.lower(userTypeModel), cb.lower(wordLiteral)) ) ); } q.select(doc).where( cb.and(predicates.toArray(new Predicate[predicates.size()])) ); return entityManager.createQuery(q).getResultList(); }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With