如何使用JPA Criteria查询选择min(string_col) ?

Question

JPA Criteria似乎坚持min()(和max())只适用于数字字段。然而，情况并非如此，因为SQL数据库确实可以从字符串列中选择min()和max()值。我还可以使用JQL来选择字符串列的min()或max()值。

然而，当使用Criteria查询时，当min()与字符串列一起使用时，编译器会报错:绑定不匹配:criteribuilder类型的泛型方法min(Expression)不适用于参数(subquery.get("string_column")。推断的类型String不是有界参数

的有效替代品。这个查询有效:

"select new com.epsilon.totalfact.web.api.model.UPCDetails" +
"(u.id, d.name as dimension, c.name as category, u.name, u.description," +
" u.keywords, u.userNotes,  u.restrictedResellerFlag, u.hidden," +
" (select min(p.rateId) from u.platforms p)" +
"  || case when (select min(p.rateId) from u.platforms p) != (select max(p.rateId) from u.platforms p) then concat(' ~ ',(select max(p.rateId) from u.platforms p)) else '' end" +
"  as deRateId" +
" )" +
" from UPC u " +
" join u.category c" +
" join c.dimension d" +
" order by d.name, c.name, u.name",

，这个条件查询有上面提到的错误:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<UPCDetails> query = cb.createQuery(UPCDetails.class);
Root<UPC> u = query.from(UPC.class);
Join<UPC, Category> c = u.join("category");
Join<UPC, Dimension> d = c.join("dimension");
Subquery<String> sub = query.subquery(Number.class);
Root<UPC> subRoot = sub.from(UPC.class);
SetJoin<UPC,Platform> subPlatform = subRoot.join(UPC_.platforms);
sub.select(cb.min(subPlatform.get("rateId")));

Answer 1

mathguy提供了答案，对于字符串和其他类型，您需要使用least()和greatest()而不是min()和max()。