2

I wanted to know how to query with JPA CriteriaBuilder where the attribue userCode of type String represents a number.

I'm having :

Id    name    userCode
1     ABC     ABCD_123
2     XYZ     ABC_EFGH
3     TEST    7845
4     TEst2   85245

Now i just wanted to get result having userCode value only numeric.

Thanks

Ashish Ratan
  • 2,838
  • 1
  • 24
  • 50
  • Possible duplicate of [How to properly cast string to number with JPA2 Criteria API?](http://stackoverflow.com/questions/9394176/how-to-properly-cast-string-to-number-with-jpa2-criteria-api) – perissf Nov 24 '15 at 09:36
  • @perissf I don't think it's duplicate question. I'm not parsing string . My Requirement is different. – Ashish Ratan Nov 24 '15 at 10:45

3 Answers3

1

I believe there is no function in JPA to do this (JPA relationship through a collection will show all related elements no matter how they were numeric or not). Anyway, you can approach either using a native query to make database to return them filter or using any feature of your JPA implementation like this:

http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/j_regexp.htm

Which allow JPA to use regular expressions with retrieved rows.

Facepalmed
  • 751
  • 12
  • 14
  • 1
    Can't we use Criteria Builder for this rather JPQL ? – Ashish Ratan Nov 24 '15 at 07:36
  • Of course but i usually prefer jpql than criteria builder because, if annotated as named queries, they are compiled before execution and it prevents fails on execution time but it's your decision how to implement it – Facepalmed Nov 24 '15 at 08:29
  • I'm totally agree with you dear. But here is different scenario so i want it using Criteria Builder rather JPQL. And thanks for the link , it's beneficial – Ashish Ratan Nov 24 '15 at 10:50
1

You can call functions using CriteriaBuilder.function(name, ret_type, args).

You haven't said what DBMS your are using, so I'm assuming you have some function available that you could call in SQL like this:

SELECT * FROM user_account WHERE is_numeric(userCode);

You could represent that in Criteria API as follows:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = builder.createQuery(User.class);
Root<User> root = query.from(User.class);
Path<String> userCode = root.get(User_.userCode);
Expression<Boolean> numeric = builder.function("is_numeric", Boolean.class, userCode);

query.select(root).where(numeric);

See API Doc for function(...).

EDIT possible function in MySQL (untested)

CREATE FUNCTION is_numeric(val VARCHAR(1024)) 
RETURNS TINYINT(1) DETERMINISTIC 
RETURN val REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

(source)

jabu.10245
  • 1,884
  • 1
  • 11
  • 20
0

If you are using Eclipselink then you can use Regular expression REGEXP function (evaluates if the string matches the regular expression as of 2.4) or its better to use native query this requirement. In native query you can use REGEXP_LIKE function for example to find non numeric characters:

SELECT *
  FROM <table>
 WHERE REGEXP_LIKE(<column>, '[^[:digit:]]');
Sai prateek
  • 11,842
  • 9
  • 51
  • 66