Spring boot N+1 problem
Mike Barnes <mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected]> Tue, 6 Mar 2018 12:03:32 -0800
| Newsgroups | gmane.org.user-groups.ajug.members |
|---|---|
| Message-ID | <CAA71U-5GthjskTMFAUbGu17edJMh0A6xV=Ohs-ELVch0ngwEcA@mail.gmail.com> |
--===============5952708637770814170==
Content-Type: multipart/alternative; boundary="001a114256eae8ac110566c3efcc"
--001a114256eae8ac110566c3efcc
Content-Type: text/plain; charset="UTF-8"
I am using sping boot and trying to solve a couple of issues. One of the
issues is the famous N+1 performance problem and the second is filtering
based on a child object. From everything I have found so far, I need to be
using Specification classes for both of these problems. Unfortunately, I am
having no luck getting these to work. Any help would be greatly appreciated.
class User {
private String userName;
private String password;
private Set<Role> roles;
}
class Role {
private String name;
private User user;
}
public class UserSpecification implements Specification<User> {
private Long userId;
/**
* @param userId
*/
public UserSpecification(Long userId) {
this.userId = userId;
}
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> cq,
CriteriaBuilder cb) {
// Create predicate list
cq.distinct(true);
root.fetch("roles", JoinType.INNER);
List<Predicate> predicates = new ArrayList<Predicate>();
// Add 'active' check (not null and equals true)
predicates.add(getActivePredicate(root, cq, cb));
// Add UserId check
getUserIdPredicate(root, cq, cb).ifPresent(predicates::add);
if (predicates.size() == 1) {
return predicates.get(0);
} else {
return cb.and(predicates.toArray(new Predicate[0]));
}
}
private Predicate getActivePredicate(Root<User> root, CriteriaQuery<?>
cq, CriteriaBuilder cb) {
return cb.equal(root.<Boolean>get("active"), Boolean.TRUE);
}
private Optional<Predicate> getUserIdPredicate(Root<User> root,
CriteriaQuery<?> cq, CriteriaBuilder cb) {
Predicate accountIdPredicate =
userId != null
? cb.and(cb.isNotNull(root.get("id")),
cb.equal(root.<Long>get("id"), userId))
: null;
return Optional.ofNullable(accountIdPredicate);
}
}
test:
{
@Autowired
UserRepository repo;
public static main(String[] args) {
UserSpecification spec = new UserSpecification();
repo.findOne(spec);
}
}
When running the test, I get a select for each role associated with the
user.
I need to also be able to filter based on role.name
Thanks
--
Michael D. Barnes
Sr Software Engineer
Office: +1 404-960-9621
E-mail: mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected]
Web: www.site1001.com
Follow Site 1001 on: LinkedIn <https://linkedin.com/company/site1001> |
Instagram <http://instagram.com/site1001> | Twitter
<https://twitter.com/site1001> | Facebook <http://www.facebook.com/site1001>
| YouTube <https://www.youtube.com/channel/UCmQBqlisUgdd0HjQRVFD6WQ>
20772 Carmen Loop, Suite 130
<https://www.google.com/maps/place/20772+Carmen+Loop,+Suite+130+Bend,+OR+97702>
Bend, OR 97702
<https://www.google.com/maps/place/20772+Carmen+Loop,+Suite+130+Bend,+OR+97702>
--001a114256eae8ac110566c3efcc
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr"><div><div><div><div><div><div><div><div><div><div><div><di=
v>I am using sping boot and trying to solve a couple of issues. One of the =
issues is the famous N+1 performance problem and the second is filtering ba=
sed on a child object. From everything I have found so far, I need to be us=
ing Specification classes for both of these problems. Unfortunately, I am h=
aving no luck getting these to work. Any help would be greatly appreciated.=
<br><br></div>class User {<br></div>private String userName;<br></div>priva=
te String password;<br><br></div>private Set<Role> roles;<br><br>}<br=
><br></div>class Role {<br></div>private String name;<br></div>private User=
user;<br><br>}<br><br></div>public class UserSpecification implements Spec=
ification<User> {<br>=C2=A0=C2=A0=C2=A0 <br>=C2=A0=C2=A0=C2=A0 privat=
e Long userId;<br><br>=C2=A0=C2=A0=C2=A0 /**<br>=C2=A0=C2=A0=C2=A0 =C2=A0* =
@param userId<br>=C2=A0=C2=A0=C2=A0 =C2=A0*/<br>=C2=A0=C2=A0=C2=A0 public U=
serSpecification(Long userId) {<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 th=
is.userId =3D userId;<br>=C2=A0=C2=A0=C2=A0 }<br><br>=C2=A0=C2=A0=C2=A0 @Ov=
erride<br>=C2=A0=C2=A0=C2=A0 public Predicate toPredicate(Root<User> =
root, CriteriaQuery<?> cq, CriteriaBuilder cb) {=C2=A0=C2=A0=C2=A0 =
=C2=A0=C2=A0=C2=A0 <br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 // Create pred=
icate list<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 cq.distinct(true);<br>=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 root.fetch("roles", Jo=
inType.INNER);<br>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <br>=C2=A0=C2=
=A0=C2=A0 =C2=A0=C2=A0=C2=A0 List<Predicate> predicates =3D new Array=
List<Predicate>();<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 <br>=C2=
=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 // Add 'active' check (not null =
and equals true)<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 <br>=C2=A0=C2=A0=
=C2=A0 =C2=A0=C2=A0=C2=A0 predicates.add(getActivePredicate(root, cq, cb));=
<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 <br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=
=A0=C2=A0 // Add UserId check<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 <br>=
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 getUserIdPredicate(root, cq, cb).ifPr=
esent(predicates::add);<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 <br>=C2=A0=
=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 if (predicates.size() =3D=3D 1) {<br>=C2=A0=
=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 return predicates.get(0)=
;<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 } else {<br>=C2=A0=C2=A0=C2=A0 =
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 return cb.and(predicates.toArray(new =
Predicate[0]));<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 }<br>=C2=A0=C2=A0=
=C2=A0 }<br><br>=C2=A0=C2=A0=C2=A0 private Predicate getActivePredicate(Roo=
t<User> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {<br>=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 return cb.equal(root.<Boolean>=
;get("active"), Boolean.TRUE);<br>=C2=A0=C2=A0=C2=A0 }<br><br>=C2=
=A0=C2=A0=C2=A0 private=C2=A0 Optional<Predicate> getUserIdPredicate(=
Root<User> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {<br>=
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 Predicate accountIdPredicate =3D <br>=
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=
userId !=3D null<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=
=A0 =C2=A0=C2=A0=C2=A0 ? cb.and(cb.isNotNull(root.get("id")),<br>=
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0cb.equal(root.<Long>get(=
"id"), userId))<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=
=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 : null;<br>=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=
=C2=A0 =C2=A0=C2=A0=C2=A0 return Optional.ofNullable(accountIdPredicate);<b=
r>=C2=A0=C2=A0=C2=A0 }<br><br>}<br><br></div>test:<br>{<br></div><div>@Auto=
wired<br></div><div>UserRepository repo;<br><br></div>public static main(St=
ring[] args) {<br></div>=C2=A0=C2=A0 UserSpecification spec =3D new UserSpe=
cification();<br></div>=C2=A0=C2=A0 repo.findOne(spec);<br><div><div>=C2=A0=
=C2=A0 }<br>}<br><br></div><div>When running the test, I get a select for e=
ach role associated with the user.<br><br></div><div>I need to also be able=
to filter based on <a href=3D"http://role.name">role.name</a><br><br></div=
><div>Thanks<br></div><div><div><div><div><div><div><div><div><div><div><di=
v><div><div><div>-- <br><div class=3D"gmail_signature"><div dir=3D"ltr"><p =
dir=3D"ltr" style=3D"line-height:1.38;margin-top:0pt;margin-bottom:0pt"><sp=
an style=3D"font-size:9.5pt;font-family:Verdana;color:rgb(68,68,68);backgro=
und-color:rgb(255,255,255);font-weight:700;font-style:normal;font-variant:n=
ormal;text-decoration:none;vertical-align:baseline">Michael D. Barnes<br></=
span></p><p dir=3D"ltr" style=3D"line-height:1.38;margin-top:0pt;margin-bot=
tom:0pt"><span style=3D"font-size:9.5pt;font-family:Verdana;color:rgb(68,68=
,68);background-color:transparent;font-weight:400;font-style:normal;font-va=
riant:normal;text-decoration:none;vertical-align:baseline">Sr Software Engi=
neer<br></span></p><p dir=3D"ltr" style=3D"line-height:1.38;margin-top:0pt;=
margin-bottom:0pt"><span style=3D"font-size:9.5pt;font-family:Verdana;color=
:rgb(68,68,68);background-color:transparent;font-weight:400;font-style:norm=
al;font-variant:normal;text-decoration:none;vertical-align:baseline"><img s=
rc=3D"https://lh4.googleusercontent.com/Sp9xnvepxJBESkxLRN40IGyXFPHgUMicv8n=
VNkx8viNjvY7U7ITrO50HyJSkzL-8Z1WjvjTrBm2sS6f-aiJw4Yh5EYTYJW3Cn41oKj5tYSERDj=
2Valjzuffry1aNsBvjbIdCC4c4" style=3D"border-color: currentcolor; border-sty=
le: none; border-width: medium; border-image: none 100% / 1 / 0 stretch;" w=
idth=3D"146" height=3D"35"></span></p><p dir=3D"ltr" style=3D"line-height:1=
.38;margin-top:0pt;margin-bottom:0pt"><span style=3D"font-size:9.5pt;font-f=
amily:Verdana;color:rgb(68,68,68);background-color:transparent;font-weight:=
400;font-style:normal;font-variant:normal;text-decoration:none;vertical-ali=
gn:baseline">Office: +1 404-960-9621<br></span></p><p dir=3D"ltr" style=3D"=
line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style=3D"font-size=
:9.5pt;font-family:Verdana;color:rgb(68,68,68);background-color:transparent=
;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none=
;vertical-align:baseline">E-mail: </span><span style=3D"font-size:9.5pt;fon=
t-family:Verdana;color:rgb(17,85,204);background-color:transparent;font-wei=
ght:400;font-style:normal;font-variant:normal;text-decoration:none;vertical=
-align:baseline"><a href=3D"mailto:mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected]" target=3D"_bla=
nk">mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected]</a></span></p><p dir=3D"ltr" style=3D"line-hei=
ght:1.38;margin-top:0pt;margin-bottom:0pt"><span style=3D"font-size:9.5pt;f=
ont-family:Verdana;color:rgb(68,68,68);background-color:transparent;font-we=
ight:400;font-style:normal;font-variant:normal;text-decoration:none;vertica=
l-align:baseline">Web: </span><a href=3D"http://www.site1001.com/" style=3D=
"text-decoration:none" target=3D"_blank"><span style=3D"font-size:9.5pt;fon=
t-family:Verdana;color:rgb(17,85,204);background-color:transparent;font-wei=
ght:400;font-style:normal;font-variant:normal;text-decoration:underline;ver=
tical-align:baseline">www.site1001.com</span></a></p><br><p dir=3D"ltr" sty=
le=3D"line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style=3D"fon=
t-size:10pt;font-family:Arial;color:rgb(34,34,34);background-color:transpar=
ent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:n=
one;vertical-align:baseline">Follow Site 1001 on: </span><a href=3D"https:/=
/linkedin.com/company/site1001" style=3D"text-decoration:none" target=3D"_b=
lank"><span style=3D"font-size:10pt;font-family:Arial;color:rgb(17,85,204);=
background-color:transparent;font-weight:700;font-style:normal;font-variant=
:normal;text-decoration:underline;vertical-align:baseline">LinkedIn</span><=
/a><span style=3D"font-size:10pt;font-family:Arial;color:rgb(34,34,34);back=
ground-color:transparent;font-weight:700;font-style:normal;font-variant:nor=
mal;text-decoration:none;vertical-align:baseline"> | </span><a href=3D"http=
://instagram.com/site1001" style=3D"text-decoration:none" target=3D"_blank"=
><span style=3D"font-size:10pt;font-family:Arial;color:rgb(17,85,204);backg=
round-color:transparent;font-weight:700;font-style:normal;font-variant:norm=
al;text-decoration:underline;vertical-align:baseline">Instagram</span></a><=
span style=3D"font-size:10pt;font-family:Arial;color:rgb(34,34,34);backgrou=
nd-color:transparent;font-weight:700;font-style:normal;font-variant:normal;=
text-decoration:none;vertical-align:baseline"> | </span><a href=3D"https://=
twitter.com/site1001" style=3D"text-decoration:none" target=3D"_blank"><spa=
n style=3D"font-size:10pt;font-family:Arial;color:rgb(17,85,204);background=
-color:transparent;font-weight:700;font-style:normal;font-variant:normal;te=
xt-decoration:underline;vertical-align:baseline">Twitter</span></a><span st=
yle=3D"font-size:10pt;font-family:Arial;color:rgb(34,34,34);background-colo=
r:transparent;font-weight:700;font-style:normal;font-variant:normal;text-de=
coration:none;vertical-align:baseline"> | </span><a href=3D"http://www.face=
book.com/site1001" style=3D"text-decoration:none" target=3D"_blank"><span s=
tyle=3D"font-size:10pt;font-family:Arial;color:rgb(17,85,204);background-co=
lor:transparent;font-weight:700;font-style:normal;font-variant:normal;text-=
decoration:underline;vertical-align:baseline">Facebook</span></a><span styl=
e=3D"font-size:10pt;font-family:Arial;color:rgb(34,34,34);background-color:=
transparent;font-weight:700;font-style:normal;font-variant:normal;text-deco=
ration:none;vertical-align:baseline"> | </span><a href=3D"https://www.youtu=
be.com/channel/UCmQBqlisUgdd0HjQRVFD6WQ" style=3D"text-decoration:none" tar=
get=3D"_blank"><span style=3D"font-size:10pt;font-family:Arial;color:rgb(17=
,85,204);background-color:transparent;font-weight:700;font-style:normal;fon=
t-variant:normal;text-decoration:underline;vertical-align:baseline">YouTube=
</span></a></p><a href=3D"https://www.google.com/maps/place/20772+Carmen+Lo=
op,+Suite+130+Bend,+OR+97702" style=3D"text-decoration:none" target=3D"_bla=
nk"><span style=3D"font-size:9pt;font-family:Verdana;color:rgb(17,85,204);b=
ackground-color:transparent;font-weight:400;font-style:normal;font-variant:=
normal;text-decoration:underline;vertical-align:baseline">20772 Carmen Loop=
, Suite 130</span></a><p dir=3D"ltr" style=3D"line-height:1.38;margin-top:0=
pt;margin-bottom:0pt"><a href=3D"https://www.google.com/maps/place/20772+Ca=
rmen+Loop,+Suite+130+Bend,+OR+97702" style=3D"text-decoration:none" target=
=3D"_blank"><span style=3D"font-size:9pt;font-family:Verdana;color:rgb(17,8=
5,204);background-color:transparent;font-weight:400;font-style:normal;font-=
variant:normal;text-decoration:underline;vertical-align:baseline">Bend, OR =
97702</span></a></p></div></div>
</div></div></div></div></div></div></div></div></div></div></div></div></d=
iv></div></div></div>
--001a114256eae8ac110566c3efcc--
--===============5952708637770814170==
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: base64
Content-Disposition: inline
X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KYWp1Zy1tZW1i
ZXJzIG1haWxpbmcgbGlzdAphanVnLW1lbWJlcnNAbGlzdHMuYWp1Zy5vcmcKTWFuYWdlIHlvdXIg
c3Vic2NyaXB0aW9uIGFuZCB1bnN1YnNjcmliZSBodHRwOi8vbGlzdHMuYWp1Zy5vcmcvbWFpbG1h
bi9saXN0aW5mby9hanVnLW1lbWJlcnM=
--===============5952708637770814170==--