Hibernate 5.2 CriteriaQuery Subquery
I have Java Code which uses Hibernate 5.2 CriteriaBuilder.
Code returns this SQL statement bellow
(JAVA)
CriteriaBuilder countBuilder = session.getCriteriaBuilder();
CriteriaQuery<Long> criteriaCount = builder.createQuery(Long.class);
Root<Transaction> transactions = criteriaCount.from(Transaction.class);
criteriaCount.select(builder.count(transactions));
criteriaCount.where(generatePredicate(tRequest, countBuilder, transactions, dateFrom, dateTo));
count = session.createQuery(criteriaCount).setMaxResults(rowNum).getSingleResult();
(SQL)
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY
I want to modify this java code so, that I got this sql statement bellow
SELECT COUNT(*)
FROM (SELECT *
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY)
Please let me know how to modify it.
Also, in Hibernate 3 there was option to add sql Restriction to "Criterion" like this
criterionFilter = Restrictions.and(criterionFilter, Restrictions.sqlRestriction("rownum <= ?", 1000, StandardBasicTypes.INTEGER))
is it possible in hibernate 5?
java sql oracle hibernate
asked Nov 12 '18 at 12:10
Jibo
13410
add a comment |
I have Java Code which uses Hibernate 5.2 CriteriaBuilder.
Code returns this SQL statement bellow
(JAVA)
CriteriaBuilder countBuilder = session.getCriteriaBuilder();
CriteriaQuery<Long> criteriaCount = builder.createQuery(Long.class);
Root<Transaction> transactions = criteriaCount.from(Transaction.class);
criteriaCount.select(builder.count(transactions));
criteriaCount.where(generatePredicate(tRequest, countBuilder, transactions, dateFrom, dateTo));
count = session.createQuery(criteriaCount).setMaxResults(rowNum).getSingleResult();
(SQL)
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY
I want to modify this java code so, that I got this sql statement bellow
SELECT COUNT(*)
FROM (SELECT *
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY)
Please let me know how to modify it.
Also, in Hibernate 3 there was option to add sql Restriction to "Criterion" like this
criterionFilter = Restrictions.and(criterionFilter, Restrictions.sqlRestriction("rownum <= ?", 1000, StandardBasicTypes.INTEGER))
is it possible in hibernate 5?
java sql oracle hibernate
asked Nov 12 '18 at 12:10
Jibo
13410
add a comment |
I have Java Code which uses Hibernate 5.2 CriteriaBuilder.
Code returns this SQL statement bellow
(JAVA)
CriteriaBuilder countBuilder = session.getCriteriaBuilder();
CriteriaQuery<Long> criteriaCount = builder.createQuery(Long.class);
Root<Transaction> transactions = criteriaCount.from(Transaction.class);
criteriaCount.select(builder.count(transactions));
criteriaCount.where(generatePredicate(tRequest, countBuilder, transactions, dateFrom, dateTo));
count = session.createQuery(criteriaCount).setMaxResults(rowNum).getSingleResult();
(SQL)
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY
I want to modify this java code so, that I got this sql statement bellow
SELECT COUNT(*)
FROM (SELECT *
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY)
Please let me know how to modify it.
Also, in Hibernate 3 there was option to add sql Restriction to "Criterion" like this
criterionFilter = Restrictions.and(criterionFilter, Restrictions.sqlRestriction("rownum <= ?", 1000, StandardBasicTypes.INTEGER))
is it possible in hibernate 5?
java sql oracle hibernate
asked Nov 12 '18 at 12:10
Jibo
13410
I have Java Code which uses Hibernate 5.2 CriteriaBuilder.
Code returns this SQL statement bellow
(JAVA)
CriteriaBuilder countBuilder = session.getCriteriaBuilder();
CriteriaQuery<Long> criteriaCount = builder.createQuery(Long.class);
Root<Transaction> transactions = criteriaCount.from(Transaction.class);
criteriaCount.select(builder.count(transactions));
criteriaCount.where(generatePredicate(tRequest, countBuilder, transactions, dateFrom, dateTo));
count = session.createQuery(criteriaCount).setMaxResults(rowNum).getSingleResult();
(SQL)
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY
I want to modify this java code so, that I got this sql statement bellow
SELECT COUNT(*)
FROM (SELECT *
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY)
Please let me know how to modify it.
Also, in Hibernate 3 there was option to add sql Restriction to "Criterion" like this
criterionFilter = Restrictions.and(criterionFilter, Restrictions.sqlRestriction("rownum <= ?", 1000, StandardBasicTypes.INTEGER))
is it possible in hibernate 5?
java sql oracle hibernate
java sql oracle hibernate
asked Nov 12 '18 at 12:10
Jibo
13410
asked Nov 12 '18 at 12:10
Jibo
13410
edited Nov 14 '18 at 9:59
asked Nov 12 '18 at 12:10
Jibo
13410
asked Nov 12 '18 at 12:10
Jibo
13410
asked Nov 12 '18 at 12:10
Jibo
13410
13410
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Considering the following answer/questions:
- Refactor native query to JPQL query
- JPA/hibernate subquery in from clause
it is not possible to do that in JPQL. Unfortunately, this is a strong limitation. You should consider using a native query to do that or other approaches like views.
answered Nov 12 '18 at 13:55
Lorelorelore
1,85061124
add a comment |
The generated query
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY
provides absolutely no sense, the select count(*) returns exactly one row, so there is no reason to limit the number of fetched row to 1000.
So the only correct action is to open a ticket by the hibernate folks. In the meantime use the workaround if you get a counthigher than 1000 simple cut it down to the 1000. You get the expected result.
answered Nov 13 '18 at 21:50
Marmite Bomber
7,2643832
Yes but, if there is 10000000 record in table for this restriction, it will check all of them and it will take very long time. If I had option to add rownum < 1000 in hibernate 5.2 "Predicate", in this case operation will be much more faster. because it will check only 1000 row and then stop. the workaround for this is to "fetch first 1000 rows only" but it works only when you are getting records from table not count(). so i have to add one more "select count() from (select...)" at the top.
– Jibo
Nov 14 '18 at 9:47
Sadly this is true. But in a larger sense (and I know it is not always easy with existing frameworks) you shoud not count the number of rows in the first page at all. The count sould be derived implicitely from the number of rows obtained in the fetch data of the first page.
– Marmite Bomber
Nov 14 '18 at 16:25
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53261917%2fhibernate-5-2-criteriaquery-subquery%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Considering the following answer/questions:
- Refactor native query to JPQL query
- JPA/hibernate subquery in from clause
it is not possible to do that in JPQL. Unfortunately, this is a strong limitation. You should consider using a native query to do that or other approaches like views.
answered Nov 12 '18 at 13:55
Lorelorelore
1,85061124
add a comment |
Considering the following answer/questions:
- Refactor native query to JPQL query
- JPA/hibernate subquery in from clause
it is not possible to do that in JPQL. Unfortunately, this is a strong limitation. You should consider using a native query to do that or other approaches like views.
answered Nov 12 '18 at 13:55
Lorelorelore
1,85061124
add a comment |
Considering the following answer/questions:
- Refactor native query to JPQL query
- JPA/hibernate subquery in from clause
it is not possible to do that in JPQL. Unfortunately, this is a strong limitation. You should consider using a native query to do that or other approaches like views.
answered Nov 12 '18 at 13:55
Lorelorelore
1,85061124
Considering the following answer/questions:
- Refactor native query to JPQL query
- JPA/hibernate subquery in from clause
it is not possible to do that in JPQL. Unfortunately, this is a strong limitation. You should consider using a native query to do that or other approaches like views.
answered Nov 12 '18 at 13:55
Lorelorelore
1,85061124
answered Nov 12 '18 at 13:55
Lorelorelore
1,85061124
answered Nov 12 '18 at 13:55
Lorelorelore
1,85061124
answered Nov 12 '18 at 13:55
Lorelorelore
1,85061124
1,85061124
add a comment |
add a comment |
The generated query
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY
provides absolutely no sense, the select count(*) returns exactly one row, so there is no reason to limit the number of fetched row to 1000.
So the only correct action is to open a ticket by the hibernate folks. In the meantime use the workaround if you get a counthigher than 1000 simple cut it down to the 1000. You get the expected result.
answered Nov 13 '18 at 21:50
Marmite Bomber
7,2643832
Yes but, if there is 10000000 record in table for this restriction, it will check all of them and it will take very long time. If I had option to add rownum < 1000 in hibernate 5.2 "Predicate", in this case operation will be much more faster. because it will check only 1000 row and then stop. the workaround for this is to "fetch first 1000 rows only" but it works only when you are getting records from table not count(). so i have to add one more "select count() from (select...)" at the top.
– Jibo
Nov 14 '18 at 9:47
Sadly this is true. But in a larger sense (and I know it is not always easy with existing frameworks) you shoud not count the number of rows in the first page at all. The count sould be derived implicitely from the number of rows obtained in the fetch data of the first page.
– Marmite Bomber
Nov 14 '18 at 16:25
add a comment |
The generated query
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY
provides absolutely no sense, the select count(*) returns exactly one row, so there is no reason to limit the number of fetched row to 1000.
So the only correct action is to open a ticket by the hibernate folks. In the meantime use the workaround if you get a counthigher than 1000 simple cut it down to the 1000. You get the expected result.
answered Nov 13 '18 at 21:50
Marmite Bomber
7,2643832
Yes but, if there is 10000000 record in table for this restriction, it will check all of them and it will take very long time. If I had option to add rownum < 1000 in hibernate 5.2 "Predicate", in this case operation will be much more faster. because it will check only 1000 row and then stop. the workaround for this is to "fetch first 1000 rows only" but it works only when you are getting records from table not count(). so i have to add one more "select count() from (select...)" at the top.
– Jibo
Nov 14 '18 at 9:47
Sadly this is true. But in a larger sense (and I know it is not always easy with existing frameworks) you shoud not count the number of rows in the first page at all. The count sould be derived implicitely from the number of rows obtained in the fetch data of the first page.
– Marmite Bomber
Nov 14 '18 at 16:25
add a comment |
The generated query
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY
provides absolutely no sense, the select count(*) returns exactly one row, so there is no reason to limit the number of fetched row to 1000.
So the only correct action is to open a ticket by the hibernate folks. In the meantime use the workaround if you get a counthigher than 1000 simple cut it down to the 1000. You get the expected result.
answered Nov 13 '18 at 21:50
Marmite Bomber
7,2643832
The generated query
SELECT COUNT(*)
FROM TRANSACTIONS
WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY
provides absolutely no sense, the select count(*) returns exactly one row, so there is no reason to limit the number of fetched row to 1000.
So the only correct action is to open a ticket by the hibernate folks. In the meantime use the workaround if you get a counthigher than 1000 simple cut it down to the 1000. You get the expected result.
answered Nov 13 '18 at 21:50
Marmite Bomber
7,2643832
answered Nov 13 '18 at 21:50
Marmite Bomber
7,2643832
answered Nov 13 '18 at 21:50
Marmite Bomber
7,2643832
answered Nov 13 '18 at 21:50
Marmite Bomber
7,2643832
7,2643832
Yes but, if there is 10000000 record in table for this restriction, it will check all of them and it will take very long time. If I had option to add rownum < 1000 in hibernate 5.2 "Predicate", in this case operation will be much more faster. because it will check only 1000 row and then stop. the workaround for this is to "fetch first 1000 rows only" but it works only when you are getting records from table not count(). so i have to add one more "select count() from (select...)" at the top.
– Jibo
Nov 14 '18 at 9:47
Sadly this is true. But in a larger sense (and I know it is not always easy with existing frameworks) you shoud not count the number of rows in the first page at all. The count sould be derived implicitely from the number of rows obtained in the fetch data of the first page.
– Marmite Bomber
Nov 14 '18 at 16:25
add a comment |
Yes but, if there is 10000000 record in table for this restriction, it will check all of them and it will take very long time. If I had option to add rownum < 1000 in hibernate 5.2 "Predicate", in this case operation will be much more faster. because it will check only 1000 row and then stop. the workaround for this is to "fetch first 1000 rows only" but it works only when you are getting records from table not count(). so i have to add one more "select count() from (select...)" at the top.
– Jibo
Nov 14 '18 at 9:47
Sadly this is true. But in a larger sense (and I know it is not always easy with existing frameworks) you shoud not count the number of rows in the first page at all. The count sould be derived implicitely from the number of rows obtained in the fetch data of the first page.
– Marmite Bomber
Nov 14 '18 at 16:25
Yes but, if there is 10000000 record in table for this restriction, it will check all of them and it will take very long time. If I had option to add rownum < 1000 in hibernate 5.2 "Predicate", in this case operation will be much more faster. because it will check only 1000 row and then stop. the workaround for this is to "fetch first 1000 rows only" but it works only when you are getting records from table not count(). so i have to add one more "select count() from (select...)" at the top.
– Jibo
Nov 14 '18 at 9:47
Yes but, if there is 10000000 record in table for this restriction, it will check all of them and it will take very long time. If I had option to add rownum < 1000 in hibernate 5.2 "Predicate", in this case operation will be much more faster. because it will check only 1000 row and then stop. the workaround for this is to "fetch first 1000 rows only" but it works only when you are getting records from table not count(). so i have to add one more "select count() from (select...)" at the top.
– Jibo
Nov 14 '18 at 9:47
Sadly this is true. But in a larger sense (and I know it is not always easy with existing frameworks) you shoud not count the number of rows in the first page at all. The count sould be derived implicitely from the number of rows obtained in the fetch data of the first page.
– Marmite Bomber
Nov 14 '18 at 16:25
Sadly this is true. But in a larger sense (and I know it is not always easy with existing frameworks) you shoud not count the number of rows in the first page at all. The count sould be derived implicitely from the number of rows obtained in the fetch data of the first page.
– Marmite Bomber
Nov 14 '18 at 16:25
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53261917%2fhibernate-5-2-criteriaquery-subquery%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
