jparepository查询所有_Spring JPA Repository动态查询

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 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 + "))";

}

解决方案

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 findByFilterText(Set words);

}

Create an implementation:

@Repository

public class MyEntityRepositoryImpl implements MyEntityRepositoryCustom {

@PersistenceContext

private EntityManager entityManager;

public List findByFilterText(Set words) {

// implementation below

}

}

Extend the new interface in your existing Repository interface:

public interface MyEntityRepository extends JpaRepository, MyEntityRepositoryCustom {

// other query methods

}

Finally, call the method somewhere else:

dao.findByFilterText(new HashSet(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 findByFilterText(Set words) {

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

CriteriaQuery q = cb.createQuery(User.class);

Root user = q.from(User.class);

Path namePath = user.get("name");

Path userTypeClassTypeDisplayName =

user.get("userType").get("classType").get("displayName");

Path userTypeModel = user.get("userType").get("model");

List predicates = new ArrayList<>();

for(String word : words) {

Expression 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();

}