Mixing
With JPA Mapped Entities with one-to-many relationships, how can I add join criterias
Given the situation: The database was created when Hibernate wasn't born yet, we talk true legacy here.
I have tables that never have deleted records, just setting an active flag on the row to mark an entry as currently in use. Queries usually look like:
SELECT * from A left join B on A.id = B.a_id and B.active = 1
With Entity A and B defined in Java, and A has a property
@OneToMany
private Set<B> b;
How do I annotate the property to add the criteria "and B.active=1"?
JPA-solution preferred, but if necessary we can use hibernate annotations too.
In a similar way we have cases where entries have an active date range, so we would have to add a criteria similar to
"and B.active_From >= now() and (B.active_to is null or B.active_to < now())"
hibernate jpa
asked Nov 20 '18 at 5:22
KaiKai
7871127
add a comment |
Given the situation: The database was created when Hibernate wasn't born yet, we talk true legacy here.
I have tables that never have deleted records, just setting an active flag on the row to mark an entry as currently in use. Queries usually look like:
SELECT * from A left join B on A.id = B.a_id and B.active = 1
With Entity A and B defined in Java, and A has a property
@OneToMany
private Set<B> b;
How do I annotate the property to add the criteria "and B.active=1"?
JPA-solution preferred, but if necessary we can use hibernate annotations too.
In a similar way we have cases where entries have an active date range, so we would have to add a criteria similar to
"and B.active_From >= now() and (B.active_to is null or B.active_to < now())"
hibernate jpa
asked Nov 20 '18 at 5:22
KaiKai
7871127
Are you using Spring Framework with JPA?
– Gaurav Srivastav
Nov 20 '18 at 5:24
Hibernate specific solution is the only option here. See the paragraph on@Wherehere: baeldung.com/hibernate-dynamic-mapping. Hibernate also offers dynamic Filters however@Whereseems to be what you are looking for. docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/…
– Alan Hay
Nov 20 '18 at 9:16
add a comment |
Given the situation: The database was created when Hibernate wasn't born yet, we talk true legacy here.
I have tables that never have deleted records, just setting an active flag on the row to mark an entry as currently in use. Queries usually look like:
SELECT * from A left join B on A.id = B.a_id and B.active = 1
With Entity A and B defined in Java, and A has a property
@OneToMany
private Set<B> b;
How do I annotate the property to add the criteria "and B.active=1"?
JPA-solution preferred, but if necessary we can use hibernate annotations too.
In a similar way we have cases where entries have an active date range, so we would have to add a criteria similar to
"and B.active_From >= now() and (B.active_to is null or B.active_to < now())"
hibernate jpa
asked Nov 20 '18 at 5:22
KaiKai
7871127
Given the situation: The database was created when Hibernate wasn't born yet, we talk true legacy here.
I have tables that never have deleted records, just setting an active flag on the row to mark an entry as currently in use. Queries usually look like:
SELECT * from A left join B on A.id = B.a_id and B.active = 1
With Entity A and B defined in Java, and A has a property
@OneToMany
private Set<B> b;
How do I annotate the property to add the criteria "and B.active=1"?
JPA-solution preferred, but if necessary we can use hibernate annotations too.
In a similar way we have cases where entries have an active date range, so we would have to add a criteria similar to
"and B.active_From >= now() and (B.active_to is null or B.active_to < now())"
hibernate jpa
hibernate jpa
asked Nov 20 '18 at 5:22
KaiKai
7871127
asked Nov 20 '18 at 5:22
KaiKai
7871127
asked Nov 20 '18 at 5:22
KaiKai
7871127
asked Nov 20 '18 at 5:22
KaiKai
7871127
asked Nov 20 '18 at 5:22
KaiKai
7871127
7871127
Are you using Spring Framework with JPA?
– Gaurav Srivastav
Nov 20 '18 at 5:24
Hibernate specific solution is the only option here. See the paragraph on@Wherehere: baeldung.com/hibernate-dynamic-mapping. Hibernate also offers dynamic Filters however@Whereseems to be what you are looking for. docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/…
– Alan Hay
Nov 20 '18 at 9:16
add a comment |
Are you using Spring Framework with JPA?
– Gaurav Srivastav
Nov 20 '18 at 5:24
Hibernate specific solution is the only option here. See the paragraph on@Wherehere: baeldung.com/hibernate-dynamic-mapping. Hibernate also offers dynamic Filters however@Whereseems to be what you are looking for. docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/…
– Alan Hay
Nov 20 '18 at 9:16
Are you using Spring Framework with JPA?
– Gaurav Srivastav
Nov 20 '18 at 5:24
Are you using Spring Framework with JPA?
– Gaurav Srivastav
Nov 20 '18 at 5:24
Hibernate specific solution is the only option here. See the paragraph on
@Where here: baeldung.com/hibernate-dynamic-mapping. Hibernate also offers dynamic Filters however @Where seems to be what you are looking for. docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/…– Alan Hay
Nov 20 '18 at 9:16
Hibernate specific solution is the only option here. See the paragraph on
@Where here: baeldung.com/hibernate-dynamic-mapping. Hibernate also offers dynamic Filters however @Where seems to be what you are looking for. docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/…– Alan Hay
Nov 20 '18 at 9:16
add a comment |
1 Answer
1
active
oldest
votes
You can use Hibernate's @Where annotation to filter elements from an association. You can find an example and explanation in this Hibernate Tip.
Here's the short version of it:
You can set an SQL snippet as the value of the clause attribute of the @Where annotation. So, your mapping should look like this:
@OneToMany
@Where(clause = "active = 1")
private Set<B> b;
And you can use the same approach to exclude the ones that are not within the time range
@OneToMany
@Where(clause = "active_from >= now() and (active_to is null or active_to < now())")
private Set<B> b;
answered Nov 20 '18 at 16:57
Thorben JanssenThorben Janssen
26516
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%2f53386687%2fwith-jpa-mapped-entities-with-one-to-many-relationships-how-can-i-add-join-crit%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use Hibernate's @Where annotation to filter elements from an association. You can find an example and explanation in this Hibernate Tip.
Here's the short version of it:
You can set an SQL snippet as the value of the clause attribute of the @Where annotation. So, your mapping should look like this:
@OneToMany
@Where(clause = "active = 1")
private Set<B> b;
And you can use the same approach to exclude the ones that are not within the time range
@OneToMany
@Where(clause = "active_from >= now() and (active_to is null or active_to < now())")
private Set<B> b;
answered Nov 20 '18 at 16:57
Thorben JanssenThorben Janssen
26516
add a comment |
You can use Hibernate's @Where annotation to filter elements from an association. You can find an example and explanation in this Hibernate Tip.
Here's the short version of it:
You can set an SQL snippet as the value of the clause attribute of the @Where annotation. So, your mapping should look like this:
@OneToMany
@Where(clause = "active = 1")
private Set<B> b;
And you can use the same approach to exclude the ones that are not within the time range
@OneToMany
@Where(clause = "active_from >= now() and (active_to is null or active_to < now())")
private Set<B> b;
answered Nov 20 '18 at 16:57
Thorben JanssenThorben Janssen
26516
add a comment |
You can use Hibernate's @Where annotation to filter elements from an association. You can find an example and explanation in this Hibernate Tip.
Here's the short version of it:
You can set an SQL snippet as the value of the clause attribute of the @Where annotation. So, your mapping should look like this:
@OneToMany
@Where(clause = "active = 1")
private Set<B> b;
And you can use the same approach to exclude the ones that are not within the time range
@OneToMany
@Where(clause = "active_from >= now() and (active_to is null or active_to < now())")
private Set<B> b;
answered Nov 20 '18 at 16:57
Thorben JanssenThorben Janssen
26516
You can use Hibernate's @Where annotation to filter elements from an association. You can find an example and explanation in this Hibernate Tip.
Here's the short version of it:
You can set an SQL snippet as the value of the clause attribute of the @Where annotation. So, your mapping should look like this:
@OneToMany
@Where(clause = "active = 1")
private Set<B> b;
And you can use the same approach to exclude the ones that are not within the time range
@OneToMany
@Where(clause = "active_from >= now() and (active_to is null or active_to < now())")
private Set<B> b;
answered Nov 20 '18 at 16:57
Thorben JanssenThorben Janssen
26516
edited Nov 20 '18 at 17:17
answered Nov 20 '18 at 16:57
Thorben JanssenThorben Janssen
26516
answered Nov 20 '18 at 16:57
Thorben JanssenThorben Janssen
26516
answered Nov 20 '18 at 16:57
Thorben JanssenThorben Janssen
26516
26516
add a comment |
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.
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%2f53386687%2fwith-jpa-mapped-entities-with-one-to-many-relationships-how-can-i-add-join-crit%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
Are you using Spring Framework with JPA?
– Gaurav Srivastav
Nov 20 '18 at 5:24
Hibernate specific solution is the only option here. See the paragraph on
@Wherehere: baeldung.com/hibernate-dynamic-mapping. Hibernate also offers dynamic Filters however@Whereseems to be what you are looking for. docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/…– Alan Hay
Nov 20 '18 at 9:16