Spring JPA CriteriaBuilder N+1 problem. | Together Java | Page 1

median lindenBOT Sep 8, 2023, 4:09 PM

#

Detected code, here are some useful tools:

Formatted code

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<SomeBean> cq = cb.createQuery(SomeBean.class );
Set<Predicate> critera = someMagicCriteriaReturningMethod();
Predicate finalPredicate = cq.and(criteria.toArray(new Predicate[0] ));
TypedQuery<SomeBean> tq = entityManager.createQuery(cq.where(finalPredicate));
List<SomeBean> beanList = tq.getResultList();

#

<@&1004656351647117403> please have a look, thanks.

median lindenBOT Sep 8, 2023, 4:10 PM

#

median linden <@&1004656351647117403> please have a look, thanks.

#

While you are waiting for getting help, here are some tips to improve your experience:

Code is much easier to read if posted with syntax highlighting and proper formatting.

If nobody is calling back, that usually means that your question was not well asked and hence nobody feels confident enough answering. Try to use your time to elaborate, provide details, context, more code, examples and maybe some screenshots. With enough info, someone knows the answer for sure.

Don't forget to close your thread using the command </help-thread close:1027500463647621170> when your question has been answered, thanks.

hidden pond Sep 8, 2023, 4:38 PM

#

Hey there

#

hmm

#

based on the details provided, I understand you're using the Criteria API, which is integrated in JPA

#

Criteria API could be complex and hard to read, but it's effective to do programmatic and typed queries

#

i think you are missing the root here like this

#

Root<SomeBean> someBeanRoot = cq.from(SomeBean.class);

tranquil sentinel Sep 8, 2023, 4:43 PM

#

Oh yeah, I missed that line. Yes, I do use a root in the 'magic criteria returning method'

hidden pond Sep 8, 2023, 4:44 PM

#

try to not use that method

#

put all the code as one so I can help you

tranquil sentinel Sep 8, 2023, 4:44 PM

#

Uhh, it's a very long method.

#

It's also on another computer. Give me a sec.

hidden pond Sep 8, 2023, 4:45 PM

#

the problem might be there

tranquil sentinel Sep 8, 2023, 4:45 PM

#

But the gist of it is: It takes parts of the HTTP request

#

Sorry, let me take a step back.

#

This is a part of a search form, where users search for SomeBeans.

#

The method creates Predicates based on the search form.

hidden pond Sep 8, 2023, 4:46 PM

#

yeah thats what is for commonly

tranquil sentinel Sep 8, 2023, 4:48 PM

#

Give me a bit to double check for information I need to redact

hidden pond Sep 8, 2023, 4:48 PM

#

hmm something strange too

#

cq.and()

#

i think that criteriaQuery doesn''t have such method

#

it should be cb.and()

tranquil sentinel Sep 8, 2023, 4:49 PM

#

My bad, I was re-typing it

hidden pond Sep 8, 2023, 4:49 PM

#

are you learning it ?

#

i'd suggest to take a look at something easier

#

there is a cool library called QueryDSL

#

http://querydsl.com/

tranquil sentinel Sep 8, 2023, 4:51 PM

#

No, this is code that I inherited.

hidden pond Sep 8, 2023, 4:53 PM

#

what do you mean ?

tranquil sentinel Sep 8, 2023, 4:53 PM

#

The previous programmer who wrote this has since left the company.

hidden pond Sep 8, 2023, 4:55 PM

#

tranquil sentinel The previous programmer who wrote this has since left the company.

ah that means you cannot use another alternative

tranquil sentinel Sep 8, 2023, 4:55 PM

#

No, I have the authority to do so.

#

But not the expertiese to determine if I should.

hidden pond Sep 8, 2023, 4:56 PM

#

alright.

median lindenBOT Sep 8, 2023, 4:56 PM

#

Detected code, here are some useful tools:

Formatted code

public List<SomeBean> search(SearchForm searchForm) {
  CriteriaBuilder cb = entityManager.getCriteriaBuilder();
  CriteriaQuery<SomeBean> cq = cb.createQuery(SomeBean.class );
  Root<SomeBean> root = cq.from(SomeBean.class );
  Set<Predicate> criteria = new HashSet<>(8);
  Cycle cycle = currentCycleService.getCurrentCycle();
  Predicate cyclePredicate = cb.equal(root.get("cycle"), cycle);
  LOG.info("Search form stuff \n{}:", searchForm);
  //We always filter to only the current Cycle.
  criteria.add(cyclePredicate);
  String keywords = searchForm.getKeywords();
  if (keywords != null  && !keywords.isEmpty()) {
    LOG.info("Adding criterion keywords : {}", keywords);
    String keywordsSQL = ' % ' + keywords + ' % ';
    Predicate beanNameLike = cb.like(root.get("name"), keywordsSQL);
    Predicate beanFamilyLike = cb.like(root.get("family").get("name"), keywordsSQL);
    Predicate emailLike = cb.like(root.get("email"), keywordsSQL);
    Predicate allKeywordPreds = cb.or(beanNameLike, beanFamilyLike, emailLike);
    criteria.add(allKeywordPreds);
  }
  Set<Integer> beanSpecialIds = searchForm.getBeanSpecialSet();
  if (beanSpecialIds != null  && !beanSpecialIds.isEmpty()) {
    criteria.add(root.get("beanSpecial").get("id").in(beanSpecialIds));
  }
  Set<Integer> beanTypeSet = searchForm.getBeanTypeSet();
  if (beanTypeSet != null  && !beanTypeSet.isEmpty()) {
    criteria.add(root.get("beanType").get("id").in(beanTypeSet));
  }
  Predicate finalPredicate = cb.and(criteria.toArray(new Predicate[0] ));
  cq = cq.where(finalPredicate);
  TypedQuery<SomeBean> x = entityManager.createQuery(cq);
  x.setMaxResults(25);
  List<SomeBean> finalBeanList = x.getResultList();
  return finalBeanList;
}

hidden pond Sep 8, 2023, 4:57 PM

#

i need some time to read all this

#

peepoG

tranquil sentinel Sep 8, 2023, 4:57 PM

#

My apologies, I needed to rename certain classes, that's what took so long.

#

The setMaxResults isn't actually used, that's just a temporary testing line when running in development.

hidden pond Sep 8, 2023, 4:59 PM

#

the code looks fine

#

but can be improved!

#

for example the null checks are done in poor way

#

why not using Optional?

tranquil sentinel Sep 8, 2023, 5:00 PM

#

I've inherited this code.

#

I do not know why it was written the way it was.

hidden pond Sep 8, 2023, 5:00 PM

#

which Java version is this ?

tranquil sentinel Sep 8, 2023, 5:00 PM

#

Now, it's 11.

#

I believe it was originally written in 7 or 8

hidden pond Sep 8, 2023, 5:02 PM

#

well maybe the issue you're facing is with the entity relationships

#

you may need to verify your fetchtype

tranquil sentinel Sep 8, 2023, 5:03 PM

#

Let me check that.

#

I'm going through the SomeBean class right now to rename things, as I can't give out what I am working on

#

@Table(name="SomeBeanTable")
@Data
public class SomeBean{

#

But I don't think the problem can be the FetchType. I mean, maybe it's made worse by the fetchtype, but it's still making one query to the SomeBeanTable per SomeBean.

hidden pond Sep 8, 2023, 7:48 PM

#

👀

#

Dont use @Data with jpa

#

It hurts your system and might be the reason of the issue

#

Also, the code is hard to read, im using my phone rn. Try to make it prettier if u can pls

tranquil sentinel Sep 8, 2023, 8:02 PM

#

What should I use instead of Data?

#

Or should I just remove it?

hidden pond Sep 8, 2023, 8:03 PM

#

tranquil sentinel Or should I just remove it?

Consider using @Getter, @Setter and @ToString

#

Then try to generate with the help of your IDE

#

equals() and hascode()

#

Remove @Data

tranquil sentinel Sep 8, 2023, 8:25 PM

#

Understood. I have done so. Symptoms persist unchanged.

tranquil sentinel Sep 8, 2023, 9:29 PM

#

Issue resolved by a co-worker.
I do not understand what he did.

median lindenBOT Sep 8, 2023, 9:29 PM

#

Closed the thread.

median lindenBOT Sep 9, 2023, 10:16 AM

#

Closed the thread due to inactivity.

If your question was not resolved yet, feel free to just post a message to reopen it, or create a new thread. But try to improve the quality of your question to make it easier to help you 👍

#Spring JPA CriteriaBuilder N+1 problem.