Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Hibernate Criteria API - sort by regex and NULL FIRST/LAST
PostPosted: Mon Oct 30, 2017 5:59 pm 
Newbie

Joined: Mon Oct 30, 2017 5:55 pm
Posts: 2
I have to sort an alphanumeric column as if it were separate numeric and alpha values in an Oracle database using Hibernate. Here is the example using Oracle SQL.

Given this table...

Code:
CREATE TABLE code_list (code VARCHAR2(6));

INSERT INTO code_list VALUES ('1');
INSERT INTO code_list VALUES ('10');
INSERT INTO code_list VALUES ('100');
INSERT INTO code_list VALUES ('C1');
INSERT INTO code_list VALUES ('2');
INSERT INTO code_list VALUES ('3');
INSERT INTO code_list VALUES ('4');
INSERT INTO code_list VALUES ('C3');
INSERT INTO code_list VALUES ('F3');
INSERT INTO code_list VALUES ('F1');
INSERT INTO code_list VALUES ('F17');
INSERT INTO code_list VALUES ('C9');
INSERT INTO code_list VALUES ('C10');
INSERT INTO code_list VALUES ('C47');
INSERT INTO code_list VALUES ('C100');
INSERT INTO code_list VALUES ('C134');
INSERT INTO code_list VALUES ('A234');
INSERT INTO code_list VALUES ('C1245');
INSERT INTO code_list VALUES ('AU89');
INSERT INTO code_list VALUES ('BB111');
INSERT INTO code_list VALUES ('B4111');
INSERT INTO code_list VALUES ('BR111');
INSERT INTO code_list VALUES ('BB20');
INSERT INTO code_list VALUES ('ZX23');
INSERT INTO code_list VALUES ('ZX456');
INSERT INTO code_list VALUES ('Z16');
INSERT INTO code_list VALUES ('Z66');
INSERT INTO code_list VALUES ('ZX5');
INSERT INTO code_list VALUES ('ABCD12');
INSERT INTO code_list VALUES ('RRR-12');
INSERT INTO code_list VALUES ('A1B2C3');
INSERT INTO code_list VALUES ('A1B55');
INSERT INTO code_list VALUES ('A1B1');
INSERT INTO code_list VALUES ('A1B2C1');
INSERT INTO code_list VALUES ('M2-40');
COMMIT;


This query...

Code:
select
    code
from
    code_list
order by
    regexp_substr(code,'^\D+') nulls first,
    to_number(regexp_substr(code,'\d+')),
    to_number(regexp_substr(code,'\d+',1,2)) nulls last,
    regexp_substr(code,'[[:alpha:]]+',1,2) nulls last,
    to_number(regexp_substr(code,'\d+',1,3)) nulls last;


Returns the proper sort...

Code:
1
2
3
4
10
100
A1B1
A1B2C1
A1B2C3
A1B55
A234
ABCD12
AU89
B4111
BB20
BB111
BR111
C1
C3
C9
C10
C47
C100
C134
C1245
F1
F3
F17
M2-40
RRR-12
Z16
Z66
ZX5
ZX23
ZX456


But Oracle is abstracted behind the ORM, Hibernate, and I can't see how I may similarly chop up the sort column using the CriteriaBuilder nor how to utilized the "null first" or "null last" features.

Am I missing it, or is there a clever way?


Top
 Profile  
 
 Post subject: Re: Hibernate CriteriaBuilder - sort by regular expression, use
PostPosted: Tue Oct 31, 2017 5:53 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1515
Quote:
But Oracle is abstracted behind the ORM, Hibernate, and I can't see how I may similarly chop up the sort column using the CriteriaBuilder nor how to utilized the "null first" or "null last" features.


If you can use Hibernate to write a Criteria API query you can also use it just fine to run a Native Query, right?

Otherwise, why do you think you have the createNativeQuery in the EntityManager class?

So, just run the native SQL query to get the data you want, and use the Hibernate ResultTransformer to map the resulting Object[] array to the desired data structure you need.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate Criteria API - sort by regex and NULL FIRST/LAST
PostPosted: Tue Oct 31, 2017 10:36 am 
Newbie

Joined: Mon Oct 30, 2017 5:55 pm
Posts: 2
We have generalized the routine to handle multiple tables with various columns using the criteria builder. Many of them have this serial number column used for sorting.

What you suggest means I would have to code an exception for those tables with serial numbers to use a native query while those without would build via our current generalized routine.

While I was/am aware of "the createNativeQuery in the EntityManager class", my preference is to code as few exceptions as possible and unless absolutely necessary.


Top
 Profile  
 
 Post subject: Re: Hibernate Criteria API - sort by regex and NULL FIRST/LAST
PostPosted: Tue Oct 31, 2017 11:11 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1515
Quote:
What you suggest means I would have to code an exception for those tables with serial numbers to use a native query while those without would build via our current generalized routine.


Yes, because the JPA Criteria API does not support that functionality. It's not something we could fix in Hibernate.

The legacy Hibernate Criteria, which is now deprecated, supports custom SQL fragments, but we no longer maintain it.

Now, we will provide a custom Criteria API starting from Hibernate 6, but that will take some time.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.