Java criteria builder query not null or empty

Question

I'm trying to check if a column in the db is not an empty string or not null but I can't figure out how to do it with criteria builder queries in order to get actual objects back. This sql works:

sampleName is not null and sampleName != ''

But when I try to do it with criteria builder like this:

// this is in a private method filterBySampleNotEmpty

cb.notEqual(root.get("sampleName"), "");

called by this in another method

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Sample> query = criteriaBuilder.createQuery(Sample.class);
Root model = query.from(Sample.class);
List<Predicate> predicates = new ArrayList<>();
predicates.add(filterBySampleNotEmpty(model, criteriaBuilder));
query.select(model).where(predicates.toArray(new Predicate[]{}));

It returns the whole list still.

Can you show a bit more of your code? How have you built/configured cb and root? — Eddie Curtis, Dec 05 '16 at 16:58
In the 'where' clause, can you try `predicates.toArray(new Predicate[predicates.size()]` and see if that works? — VHS, Dec 05 '16 at 17:10

score 22 · Answer 1 · edited Jun 12 '17 at 14:57

22

Try to use this code:

criteriaBuilder.isNotNull(model.get(entity.name))

edited Jun 12 '17 at 14:57

SilverNak

3,283
4
28
44

answered Jun 12 '17 at 14:46

gfxfunclub

221
2
3

2

Try to give a bit more context in your answer. See [here](https://stackoverflow.com/help/how-to-answer) – SilverNak Jun 12 '17 at 14:51
3

Thank you. Your answer is correct. notEqual (entity, null) may throw NPE. – Andrew Gans Oct 08 '18 at 13:40
1

What if the column has just a blank space as the string value? – SebastianX Sep 15 '21 at 09:51
Then it won't be considered `NULL`. – user1438038 May 24 '22 at 12:41

Tom Taylor · Accepted Answer · 2016-12-05T18:06:38.163

4

Please make a try with the following

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Sample> query = criteriaBuilder.createQuery(Sample.class);
MetaModel m = em.getMetaModel();
EntityType<Sample> entity = m.entity(Sample.class);
Root model = query.from(Sample.class);
query.where(criteriaBuilder.notEqual(model.get(entity.name), “”)).and(criteriaBuilder.notEqual(model.get(entity.name), null));

edited Dec 05 '16 at 18:06

answered Dec 05 '16 at 17:18

Tom Taylor

3,344
2
38
63

the notEqual cannot be called on the query. – Crystal Dec 05 '16 at 17:33
Yeah, made a mistake @Crystal.. notEquals must be on `CriteriaBuilder` have edited my answer please check the same – Tom Taylor Dec 05 '16 at 17:37
Please refer the following java doc https://docs.oracle.com/cd/E19798-01/821-1841/gjixa/index.html - to know more.. – Tom Taylor Dec 05 '16 at 17:38

score 0 · Answer 3 · answered Dec 05 '16 at 17:14

0

The criteria builder "notEqual" criteria testing that two expressions are not equal. I can not see the criteria to filter null value unless I missed something.

answered Dec 05 '16 at 17:14

fabfas

2,200
1
21
21

yes i was trying for the empty string first but could not get that to work. – Crystal Dec 05 '16 at 17:16

score 0 · Answer 4 · answered Dec 05 '16 at 17:14

0

In the 'where' clause, try predicates.toArray(new Predicate[predicates.size()] and see if that works. When you are converting the List to Array, you are specifying the size as 0 since you are passing an empty array.

answered Dec 05 '16 at 17:14

VHS

9,534
3
19
43

No, the array will be resized automatically. – WesternGun Sep 22 '21 at 07:15

Java criteria builder query not null or empty

4 Answers4