CODEWITHSUNDEEP
javamysqleclipselinkjpa-2.0criteria-api
Ashish Ratan
Ashish Ratan

Reputation: 2870

Fetch results using JPA Criteria Builder where contains numeric fields only where attribute having alphanumeric values in MySQL

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

Upvotes: 2

Views: 2040

Answers (3)

jabu.10245
jabu.10245

Reputation: 1892

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)

Upvotes: 1

Sai prateek
Sai prateek

Reputation: 11906

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:]]');

Upvotes: 0

Facepalmed
Facepalmed

Reputation: 761

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.

Upvotes: 1

Related Questions