JPA: How do I LIKE with a NUMBER column in a static JPA MetaModel?
I have a static metamodel with a NUMBER column (actually, BigDecimal, don't ask why). Now I would like to make a LIKE query to this number column:
CriteriaBuilder cb;
cb.like(entity.get(Entity_.numbercol), "123%");
where entity.get(Entity_.numbercol)
returns a Path<BigDecimal>
. Naturally, I get a compilation error:...like(Expression<String>, ...) ... not applicable for the arguments (Path<BigDecimal>, ...)
Casting column with .as(String.class)
fails due to a bug in JPA, but I don't have an error number right now. However, it has not been fixed in the latest JPA / Hibernate release. Anyway, this results in an exception being thrown during the execution of some invalid SQL statement.
Now I just need a way to get the SQL API equivalent
... WHERE numbercol LIKE '123%';
Topic search already brought up the following answers which don't help because I have a static metamodel: NHibernate is the easiest way to do LIKE lookup against integer column with Criteria API and JPA / Criteria API - same as problem
Any ideas?
Thanks in advance Dominik
source to share
No, it does not crash due to an error, it works as stated (for example in the Javadoc ):
Output the expression for the expression, returning a new expression object. This method does not invoke type conversion: the runtime type is not changed. Warning: May crash at runtime.
The method you are using is casting and you need a transformation. In general, there is no support for converting from BigDecimal to String in JPA.
source to share
For people who are still looking for a solution.
Knowing that the HQL function str
is doing the job (at least for Hibernate v. 3.6.9.Final), you can implement your own FunctionExpression
as:
//plagiarized from org.hibernate.ejb.criteria.expression.function.CastFunction
public class StrFunction<Y extends Number> extends BasicFunctionExpression<String> implements FunctionExpression<String>, Serializable {
public static final String FCT_NAME = "str";
private final Selection<Y> selection;
public StrFunction(CriteriaBuilder criteriaBuilder, Selection<Y> selection) {
super((CriteriaBuilderImpl) criteriaBuilder, String.class, FCT_NAME);
this.selection = selection;
}
@Override
public void registerParameters(ParameterRegistry registry) {
Helper.possibleParameter(selection, registry);
}
@Override
public String render(CriteriaQueryCompiler.RenderingContext renderingContext) {
return FCT_NAME + '(' + ((Renderable) selection).render(renderingContext) + ')';
}
}
and then use it:
cb.like(new StrFunction<Long> (cb, root.get(MyObject_.id)), "%mySearchTerm%");
source to share