Spring Data JPQL doesn't filter associated entity of OneToMany relationship
0
I am working on spring data and using Spring Boot 2.1.0.RELEASE and using JPQL to query the associated/embedded entity field for @OneToMany relation for the associated entity class filter but it retrieve all embedded object also used the fetch = FetchType.EAGER but not filtering associated object and loads all the object. Below are the entity and repo class.
First Entity class
@Entity
@Table(name = "USER")
public class UserEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, unique = true)
private String username;
@OneToMany(mappedBy = "user", targetEntity = AuthorityEntity.class, fetch = FetchType.LAZY, cascade=CascadeType.ALL)
private List<AuthorityEntity> authorities;
//Setter getter
}
Second Entity class
@Entity
@Table(name = "AUTHORITY")
public class AuthorityEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String authorityType;
private String textType;
@JsonBackReference
@ManyToOne(fetch = FetchType.LAZY, optional = false)
private UserEntity user;
//Setter getter
}
Repository interface
@Repository
public interface UserRepo extends JpaRepository<UserEntity, Long> {
UserEntity findByUsernameAndAuthoritiesAuthorityType(String username, String authority);
}
I also tried HQL @Query("select u from UserEntity u JOIN FETCH AuthorityEntity a on u.id = a.user where u.username = ?1 and a.authorityType = ?2") but it also fetching all child object not the filtering child.
Let me know where I am wrong, does any configuration required?
hibernate spring-boot spring-data-jpa spring-data hibernate-mapping
asked Nov 24 '18 at 13:00
Nawal SahNawal Sah
214
|
show 1 more comment
0
I am working on spring data and using Spring Boot 2.1.0.RELEASE and using JPQL to query the associated/embedded entity field for @OneToMany relation for the associated entity class filter but it retrieve all embedded object also used the fetch = FetchType.EAGER but not filtering associated object and loads all the object. Below are the entity and repo class.
First Entity class
@Entity
@Table(name = "USER")
public class UserEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, unique = true)
private String username;
@OneToMany(mappedBy = "user", targetEntity = AuthorityEntity.class, fetch = FetchType.LAZY, cascade=CascadeType.ALL)
private List<AuthorityEntity> authorities;
//Setter getter
}
Second Entity class
@Entity
@Table(name = "AUTHORITY")
public class AuthorityEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String authorityType;
private String textType;
@JsonBackReference
@ManyToOne(fetch = FetchType.LAZY, optional = false)
private UserEntity user;
//Setter getter
}
Repository interface
@Repository
public interface UserRepo extends JpaRepository<UserEntity, Long> {
UserEntity findByUsernameAndAuthoritiesAuthorityType(String username, String authority);
}
I also tried HQL @Query("select u from UserEntity u JOIN FETCH AuthorityEntity a on u.id = a.user where u.username = ?1 and a.authorityType = ?2") but it also fetching all child object not the filtering child.
Let me know where I am wrong, does any configuration required?
hibernate spring-boot spring-data-jpa spring-data hibernate-mapping
asked Nov 24 '18 at 13:00
Nawal SahNawal Sah
214
A where clause is not use to filter what is returned in the entities. It's used to select which entities should be returned. If I execute a query finding all users who have a w in their name, I'll get back a list of users, and you, Nawal, you will be part of this list, since you have a w in your name. Then, once I've found you, if I ask you "give me the letters in your name", you won't just say "w". You'll say "N, a, w, a, l". Same here.
– JB Nizet
Nov 24 '18 at 13:06
1
If you want to select only specific AuthorityEntity instances, then you should query for AuthorityEntity instances, i.e. have them in the select clause of the query. That won't change the fact that if the user u has 3 authorities, then it has 3 authorities, and asking it for its authorities will, always, return its 3 authorities.
– JB Nizet
Nov 24 '18 at 13:09
@JBNizet If it retrieve all the child then I have to filter the associated object and it make my code dirty. According to the Spring Data JPA, a custom method findByUsernameAndAuthoritiesAuthorityType(String username, String authority); can be developed to do such query but it is not doing well and performing two queries at backed one for UserEntity and another to select all AuthorityEntity. Can I have any code sniped to perform filter on associated object.
– Nawal Sah
Nov 24 '18 at 14:28
I've told you what you need to do in my previous comments. Read them again.
– JB Nizet
Nov 24 '18 at 14:30
If I understands correctly, I have to make another query to select the AuthorityEntity other than UserEntity, if it so then I am not using here the Spring Data JPQL feature and writing two query. The reason I want to perform only one query for the performance.
– Nawal Sah
Nov 25 '18 at 4:43
|
show 1 more comment
0
0
0
1
I am working on spring data and using Spring Boot 2.1.0.RELEASE and using JPQL to query the associated/embedded entity field for @OneToMany relation for the associated entity class filter but it retrieve all embedded object also used the fetch = FetchType.EAGER but not filtering associated object and loads all the object. Below are the entity and repo class.
First Entity class
@Entity
@Table(name = "USER")
public class UserEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, unique = true)
private String username;
@OneToMany(mappedBy = "user", targetEntity = AuthorityEntity.class, fetch = FetchType.LAZY, cascade=CascadeType.ALL)
private List<AuthorityEntity> authorities;
//Setter getter
}
Second Entity class
@Entity
@Table(name = "AUTHORITY")
public class AuthorityEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String authorityType;
private String textType;
@JsonBackReference
@ManyToOne(fetch = FetchType.LAZY, optional = false)
private UserEntity user;
//Setter getter
}
Repository interface
@Repository
public interface UserRepo extends JpaRepository<UserEntity, Long> {
UserEntity findByUsernameAndAuthoritiesAuthorityType(String username, String authority);
}
I also tried HQL @Query("select u from UserEntity u JOIN FETCH AuthorityEntity a on u.id = a.user where u.username = ?1 and a.authorityType = ?2") but it also fetching all child object not the filtering child.
Let me know where I am wrong, does any configuration required?
hibernate spring-boot spring-data-jpa spring-data hibernate-mapping
asked Nov 24 '18 at 13:00
Nawal SahNawal Sah
214
I am working on spring data and using Spring Boot 2.1.0.RELEASE and using JPQL to query the associated/embedded entity field for @OneToMany relation for the associated entity class filter but it retrieve all embedded object also used the fetch = FetchType.EAGER but not filtering associated object and loads all the object. Below are the entity and repo class.
First Entity class
@Entity
@Table(name = "USER")
public class UserEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, unique = true)
private String username;
@OneToMany(mappedBy = "user", targetEntity = AuthorityEntity.class, fetch = FetchType.LAZY, cascade=CascadeType.ALL)
private List<AuthorityEntity> authorities;
//Setter getter
}
Second Entity class
@Entity
@Table(name = "AUTHORITY")
public class AuthorityEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String authorityType;
private String textType;
@JsonBackReference
@ManyToOne(fetch = FetchType.LAZY, optional = false)
private UserEntity user;
//Setter getter
}
Repository interface
@Repository
public interface UserRepo extends JpaRepository<UserEntity, Long> {
UserEntity findByUsernameAndAuthoritiesAuthorityType(String username, String authority);
}
I also tried HQL @Query("select u from UserEntity u JOIN FETCH AuthorityEntity a on u.id = a.user where u.username = ?1 and a.authorityType = ?2") but it also fetching all child object not the filtering child.
Let me know where I am wrong, does any configuration required?
hibernate spring-boot spring-data-jpa spring-data hibernate-mapping
hibernate spring-boot spring-data-jpa spring-data hibernate-mapping
asked Nov 24 '18 at 13:00
Nawal SahNawal Sah
214
asked Nov 24 '18 at 13:00
Nawal SahNawal Sah
214
asked Nov 24 '18 at 13:00
Nawal SahNawal Sah
214
asked Nov 24 '18 at 13:00
Nawal SahNawal Sah
214
asked Nov 24 '18 at 13:00
Nawal SahNawal Sah
214
214
A where clause is not use to filter what is returned in the entities. It's used to select which entities should be returned. If I execute a query finding all users who have a w in their name, I'll get back a list of users, and you, Nawal, you will be part of this list, since you have a w in your name. Then, once I've found you, if I ask you "give me the letters in your name", you won't just say "w". You'll say "N, a, w, a, l". Same here.
– JB Nizet
Nov 24 '18 at 13:06
1
If you want to select only specific AuthorityEntity instances, then you should query for AuthorityEntity instances, i.e. have them in the select clause of the query. That won't change the fact that if the user u has 3 authorities, then it has 3 authorities, and asking it for its authorities will, always, return its 3 authorities.
– JB Nizet
Nov 24 '18 at 13:09
@JBNizet If it retrieve all the child then I have to filter the associated object and it make my code dirty. According to the Spring Data JPA, a custom method findByUsernameAndAuthoritiesAuthorityType(String username, String authority); can be developed to do such query but it is not doing well and performing two queries at backed one for UserEntity and another to select all AuthorityEntity. Can I have any code sniped to perform filter on associated object.
– Nawal Sah
Nov 24 '18 at 14:28
I've told you what you need to do in my previous comments. Read them again.
– JB Nizet
Nov 24 '18 at 14:30
If I understands correctly, I have to make another query to select the AuthorityEntity other than UserEntity, if it so then I am not using here the Spring Data JPQL feature and writing two query. The reason I want to perform only one query for the performance.
– Nawal Sah
Nov 25 '18 at 4:43
|
show 1 more comment
A where clause is not use to filter what is returned in the entities. It's used to select which entities should be returned. If I execute a query finding all users who have a w in their name, I'll get back a list of users, and you, Nawal, you will be part of this list, since you have a w in your name. Then, once I've found you, if I ask you "give me the letters in your name", you won't just say "w". You'll say "N, a, w, a, l". Same here.
– JB Nizet
Nov 24 '18 at 13:06
1
If you want to select only specific AuthorityEntity instances, then you should query for AuthorityEntity instances, i.e. have them in the select clause of the query. That won't change the fact that if the user u has 3 authorities, then it has 3 authorities, and asking it for its authorities will, always, return its 3 authorities.
– JB Nizet
Nov 24 '18 at 13:09
@JBNizet If it retrieve all the child then I have to filter the associated object and it make my code dirty. According to the Spring Data JPA, a custom method findByUsernameAndAuthoritiesAuthorityType(String username, String authority); can be developed to do such query but it is not doing well and performing two queries at backed one for UserEntity and another to select all AuthorityEntity. Can I have any code sniped to perform filter on associated object.
– Nawal Sah
Nov 24 '18 at 14:28
I've told you what you need to do in my previous comments. Read them again.
– JB Nizet
Nov 24 '18 at 14:30
If I understands correctly, I have to make another query to select the AuthorityEntity other than UserEntity, if it so then I am not using here the Spring Data JPQL feature and writing two query. The reason I want to perform only one query for the performance.
– Nawal Sah
Nov 25 '18 at 4:43
A where clause is not use to filter what is returned in the entities. It's used to select which entities should be returned. If I execute a query finding all users who have a w in their name, I'll get back a list of users, and you, Nawal, you will be part of this list, since you have a w in your name. Then, once I've found you, if I ask you "give me the letters in your name", you won't just say "w". You'll say "N, a, w, a, l". Same here.
– JB Nizet
Nov 24 '18 at 13:06
A where clause is not use to filter what is returned in the entities. It's used to select which entities should be returned. If I execute a query finding all users who have a w in their name, I'll get back a list of users, and you, Nawal, you will be part of this list, since you have a w in your name. Then, once I've found you, if I ask you "give me the letters in your name", you won't just say "w". You'll say "N, a, w, a, l". Same here.
– JB Nizet
Nov 24 '18 at 13:06
1
1
If you want to select only specific AuthorityEntity instances, then you should query for AuthorityEntity instances, i.e. have them in the select clause of the query. That won't change the fact that if the user u has 3 authorities, then it has 3 authorities, and asking it for its authorities will, always, return its 3 authorities.
– JB Nizet
Nov 24 '18 at 13:09
If you want to select only specific AuthorityEntity instances, then you should query for AuthorityEntity instances, i.e. have them in the select clause of the query. That won't change the fact that if the user u has 3 authorities, then it has 3 authorities, and asking it for its authorities will, always, return its 3 authorities.
– JB Nizet
Nov 24 '18 at 13:09
@JBNizet If it retrieve all the child then I have to filter the associated object and it make my code dirty. According to the Spring Data JPA, a custom method findByUsernameAndAuthoritiesAuthorityType(String username, String authority); can be developed to do such query but it is not doing well and performing two queries at backed one for UserEntity and another to select all AuthorityEntity. Can I have any code sniped to perform filter on associated object.
– Nawal Sah
Nov 24 '18 at 14:28
@JBNizet If it retrieve all the child then I have to filter the associated object and it make my code dirty. According to the Spring Data JPA, a custom method findByUsernameAndAuthoritiesAuthorityType(String username, String authority); can be developed to do such query but it is not doing well and performing two queries at backed one for UserEntity and another to select all AuthorityEntity. Can I have any code sniped to perform filter on associated object.
– Nawal Sah
Nov 24 '18 at 14:28
I've told you what you need to do in my previous comments. Read them again.
– JB Nizet
Nov 24 '18 at 14:30
I've told you what you need to do in my previous comments. Read them again.
– JB Nizet
Nov 24 '18 at 14:30
If I understands correctly, I have to make another query to select the AuthorityEntity other than UserEntity, if it so then I am not using here the Spring Data JPQL feature and writing two query. The reason I want to perform only one query for the performance.
– Nawal Sah
Nov 25 '18 at 4:43
If I understands correctly, I have to make another query to select the AuthorityEntity other than UserEntity, if it so then I am not using here the Spring Data JPQL feature and writing two query. The reason I want to perform only one query for the performance.
– Nawal Sah
Nov 25 '18 at 4:43
|
show 1 more comment
1 Answer
1
active
oldest
votes
0
After working hard I found a solution as JPA Entity Graph
@NamedEntityGraph(name = "USER.AUTHORITY", attributeNodes = { @NamedAttributeNode("authorities") })
public class UserEntity
and
@EntityGraph(value = "USER.AUTHORITY", type = EntityGraphType.LOAD)
UserEntity findByUsernameAndAuthoritiesAuthorityType(String username, AuthorityType authority);
this worked perfectly for OneToMany and filter the child object as well but it also failing in case of ManyToMany relationship.
answered Dec 1 '18 at 3:27
Nawal SahNawal Sah
214
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
});
}
});
draft saved
draft discarded
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%2f53458396%2fspring-data-jpql-doesnt-filter-associated-entity-of-onetomany-relationship%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
0
After working hard I found a solution as JPA Entity Graph
@NamedEntityGraph(name = "USER.AUTHORITY", attributeNodes = { @NamedAttributeNode("authorities") })
public class UserEntity
and
@EntityGraph(value = "USER.AUTHORITY", type = EntityGraphType.LOAD)
UserEntity findByUsernameAndAuthoritiesAuthorityType(String username, AuthorityType authority);
this worked perfectly for OneToMany and filter the child object as well but it also failing in case of ManyToMany relationship.
answered Dec 1 '18 at 3:27
Nawal SahNawal Sah
214
add a comment |
0
After working hard I found a solution as JPA Entity Graph
@NamedEntityGraph(name = "USER.AUTHORITY", attributeNodes = { @NamedAttributeNode("authorities") })
public class UserEntity
and
@EntityGraph(value = "USER.AUTHORITY", type = EntityGraphType.LOAD)
UserEntity findByUsernameAndAuthoritiesAuthorityType(String username, AuthorityType authority);
this worked perfectly for OneToMany and filter the child object as well but it also failing in case of ManyToMany relationship.
answered Dec 1 '18 at 3:27
Nawal SahNawal Sah
214
add a comment |
0
0
0
After working hard I found a solution as JPA Entity Graph
@NamedEntityGraph(name = "USER.AUTHORITY", attributeNodes = { @NamedAttributeNode("authorities") })
public class UserEntity
and
@EntityGraph(value = "USER.AUTHORITY", type = EntityGraphType.LOAD)
UserEntity findByUsernameAndAuthoritiesAuthorityType(String username, AuthorityType authority);
this worked perfectly for OneToMany and filter the child object as well but it also failing in case of ManyToMany relationship.
answered Dec 1 '18 at 3:27
Nawal SahNawal Sah
214
After working hard I found a solution as JPA Entity Graph
@NamedEntityGraph(name = "USER.AUTHORITY", attributeNodes = { @NamedAttributeNode("authorities") })
public class UserEntity
and
@EntityGraph(value = "USER.AUTHORITY", type = EntityGraphType.LOAD)
UserEntity findByUsernameAndAuthoritiesAuthorityType(String username, AuthorityType authority);
this worked perfectly for OneToMany and filter the child object as well but it also failing in case of ManyToMany relationship.
answered Dec 1 '18 at 3:27
Nawal SahNawal Sah
214
answered Dec 1 '18 at 3:27
Nawal SahNawal Sah
214
answered Dec 1 '18 at 3:27
Nawal SahNawal Sah
214
answered Dec 1 '18 at 3:27
Nawal SahNawal Sah
214
214
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53458396%2fspring-data-jpql-doesnt-filter-associated-entity-of-onetomany-relationship%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
A where clause is not use to filter what is returned in the entities. It's used to select which entities should be returned. If I execute a query finding all users who have a w in their name, I'll get back a list of users, and you, Nawal, you will be part of this list, since you have a w in your name. Then, once I've found you, if I ask you "give me the letters in your name", you won't just say "w". You'll say "N, a, w, a, l". Same here.
– JB Nizet
Nov 24 '18 at 13:06
1
If you want to select only specific AuthorityEntity instances, then you should query for AuthorityEntity instances, i.e. have them in the select clause of the query. That won't change the fact that if the user u has 3 authorities, then it has 3 authorities, and asking it for its authorities will, always, return its 3 authorities.
– JB Nizet
Nov 24 '18 at 13:09
@JBNizet If it retrieve all the child then I have to filter the associated object and it make my code dirty. According to the Spring Data JPA, a custom method findByUsernameAndAuthoritiesAuthorityType(String username, String authority); can be developed to do such query but it is not doing well and performing two queries at backed one for UserEntity and another to select all AuthorityEntity. Can I have any code sniped to perform filter on associated object.
– Nawal Sah
Nov 24 '18 at 14:28
I've told you what you need to do in my previous comments. Read them again.
– JB Nizet
Nov 24 '18 at 14:30
If I understands correctly, I have to make another query to select the AuthorityEntity other than UserEntity, if it so then I am not using here the Spring Data JPQL feature and writing two query. The reason I want to perform only one query for the performance.
– Nawal Sah
Nov 25 '18 at 4:43