1

I'm using the Criteria API to create my queries, using Hibernate as the implementation. The database is a MS SQL Server 2014.

In my database I have a string field, which contains numbers as well as strings. I want to compare the numbers to a numerical value. In SQL it would be something like this:

select *
from table
where ISNUMERIC(table.value) and CONVERT(float, table.value) > 5

(Don't quote me on the function names.) There doesn't seem to be a direct way of doing this in Criteria queries, so I tried using the function() method:

Expression<Float> convertedExpression = criteria.function("CONVERT", Float.class, criteria.literal(Float.class), baseExpression);

But this approach gives somewhat unhelpful error messages à la "incorrect syntax near )" or "incorrect syntax near @P1". I'm thinking this is because of the first parameter passed to the method, which must be the type I want to convert to.

Any help getting the CONVERT function to work or an altogether different approach would be greatly appreciated.

kenny_k
  • 3,831
  • 5
  • 30
  • 41
Sebastian Lütge
  • 13
  • 4

2 Answers2

0

Model your column as String - that way you can count on criteria api to convert to String. If you expect particular format - do that - I mean format your float into particular string, and pass it to criteria.

Michał Zaborowski
  • 3,911
  • 2
  • 19
  • 39
  • The column is already mapped as String (and it is a String column in the database). But some entries (but not all) are actually numbers and I want to do <, >, <= or >= comparisons on those. – Sebastian Lütge May 04 '16 at 09:20
  • If you really need Criteria SqlProjection looks like the only option. Other is to write native query. – Michał Zaborowski May 04 '16 at 14:24
0

It seems like JPA and its restricting Criteria API simply won't let you do the thing you want, i. e. to pass a de-facto keyword, float in this case, as a parameter to a function call. Similarly, it doesn't support adding a "native SQL condition" to the query (so SQL standard CAST(...) cannot be used as well). This is hopefully for a good reason...

So the only way seems to solve this at the database level: either use an existing or create a custom conversion function per required type (e. g. FLOAT(str)) in the database, or define and use a number column instead for your table (and keep it up-to-date with the source string column via a trigger, for instance).

BTW This question seems to a duplicate of How to properly cast string to number with JPA2 Criteria API?.

Petr Bodnár
  • 496
  • 3
  • 14