Java : Repeatly running the child mapping query individually in Spring Boot Application

Question

I have a spring boot application. I have created a Specification for a search. My Entity Product contains

@OneToOne
@JoinColumn(name="manufacturerID")
private Manufacturer manufacturer;

@OneToMany
@JoinColumn(name="key")
private List<Productattribute> productattributes;

@OneToMany
@JoinColumn(name="key")
private List<DsStatus> dsStatus;

@OneToMany
@JoinColumn(name="key")
private List<Media> medias;

@OneToMany
@JoinColumn(name="key")
private List<Distributormedia> distributormedias;

My Search query while running the Specification

select
    .....(fields)...........
from
    product product0_ 
inner join
    manufacturer manufactur1_ 
        on product0_.manufacturerID=manufactur1_.ManufacturerID 
inner join
    ds_status dsstatus2_ 
        on product0_.key=dsstatus2_.key 
left outer join
    media medias3_ 
        on product0_.key=medias3_.key 
left outer join
    distributormedia distributo4_ 
        on product0_.key=distributo4_.key 
where
    product0_.IsDeleted=0 
    and (
        product0_.serial in (
            select
                product5_.serial 
            from
                product product5_ 
            where
                product5_.IsDeleted=? 
                and (
                    product5_.Status in (
                        ? , ? , ? , ?
                    )
                ) 
            group by
                product5_.serial 
            having
                count(product5_.serial)>1
        )
    ) 
group by
    product0_.key

After running the this query..there a lot of sub query for fetching the details from the Join table. If the total number of product is 10 then 10 subquery for the child table is running. Example:

select
    manufactur0_.ManufacturerID as Manufact1_7_0_,
    manufactur0_.Address as Address2_7_0_
from
    manufacturer manufactur0_ 
where
    manufactur0_.ManufacturerID=?

Actually I don't want details from the join tables. I only want the data from parent table (product). Because of this it takes more time to return the result.

Adding the Specification

    public Specification<Product> findByCriteria(List<Map<CriteriaSpecAttrKeys,Object>> searchCriteria) throws Exception {
               List<Predicate> predicates = new ArrayList<>();
               return (root, query, cb) -> {
                 try {
                   Join<Product, Manufacturer> manufacturer = root.join("manufacturer",JoinType.INNER);
                   Join<Product, DsStatus> dsStatus = root.join("dsStatus",JoinType.INNER);
                   Join<Product, Media> media = root.join("medias",JoinType.LEFT);
                   Join<Product, Distributormedia> distributormedia = root.join("distributormedias",JoinType.LEFT);
                   predicates.add(cb.equal(root.get("isDeleted"), false));
                   Subquery<Product> subquery = query.subquery(Product.class);
                    Root<Product> subroot = subquery.from(Product.class);
                    Predicate subpredicate1 = cb.equal(subroot.get("isDeleted"),false);
                    Predicate subpredicate2 = cb.in(subroot.get("status")).value("NEEDS_REVIEW").value("READY_FOR_PROD").value("IS_PROD_AVAIL").value("IN_PROD_NEEDS_REVIEW");

subquery.select(subroot.get("serial")).where(subpredicate1,subpredicate2).groupBy(subroot.get("serial")).having(cb.greaterThan(cb.count(subroot.get("serial")),1L));
                    predicates.add(cb.in(root.get("serial")).value(subquery));
                   query.groupBy(root.get("key"));
                   return cb.and(predicates.toArray(new Predicate[predicates.size()]));
                 }catch(Exception e) {
                   throw e;
                 }
               };
            }

Thanks in Advance

Updating I changed the OneToOne Mapping to

@OneToOne(fetch=FetchType.LAZY)
@JoinColumn(name="manufacturerID")
private Manufacturer manufacturer;

Then there is an exception occured on run time.

ERROR 28185 --- [nio-8090-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.http.converter.HttpMessageConversionException: Type definition error: [simple type, class org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer]; nested exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: com.dmp.servicecontroller.response.Response["data"]->java.util.ArrayList[0]->com.product.entities.Product["manufacturer"]->com.manufacturer.entities.Manufacturer_$$_jvst266_1c["handler"])] with root cause

In the exception, it is recommended for to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS So I added @JsonIgnore, then

@JsonIgnore
@OneToOne(fetch=FetchType.LAZY)
@JoinColumn(name="manufacturerID")
private Manufacturer manufacturer;

Is it a correct method?

My Controller

    @RestController
    @RequestMapping(RestApiUrls.PRODUCT_BASE_URL)
    public class ProductController {
    @Autowired
    SearchSpecification searchSpecification;
    @Autowired
    ProductRepository productRepository;
        @PostMapping(RestApiUrls.SEARCH)
        public List<Product> productSearch(@Valid @RequestBody ProductSearchRequest productSearchRequest) {
            List<Product> productList = null;
            try {
                productList = productRepository.findAll(searchSpecification.findByCriteria(new ArrayList<>()));
            } catch (Exception e) {
                e.printStackTrace();
            }
            return productList;
        }
    }

Can you update your question with code which is triggered after reading entity from DB (e.g. Controller)? — Adam, Mar 29 '20 at 18:50

Adam · Answer 1 · 2020-03-29T20:06:12.547

0

@OneToOne mapping by default is eager which means Hibernate will execute the additional query to fetch those data automatically.

Just change fetch type if you do not need those details:

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name="manufacturerID")
private Manufacturer manufacturer;

However, you have to keep in mind that when you want to return from your controller the Product entity then Jackson will try to get all your public properties or properties with getter (if not annotated with @JsonIgnore). It means Jackson will call on your Product object e.g. the method getManufacturer(). Because of lazy loading, this will be a proxy - not a real object. Hibernate will try to fetch this object from the database. If there is no transaction then you will get an exception org.hibernate.LazyInitializationException.

What I suggest is either create custom ProductDto from Product entity which will contain only the data you want to return or use @JsonIgnore (as you did).

edited Mar 29 '20 at 20:06

answered Mar 29 '20 at 15:13

Adam

884
7
29

Thanks for the answer. But the multiple query is running for `@OneToMany` mapping also? – GameCoder007 Mar 29 '20 at 17:32
And I changed the Fetch type to Lazy .. But run time error occurs `` Type definition error: [simple type, class org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer]; nested exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) `` – GameCoder007 Mar 29 '20 at 17:35
For which OneToMany mappings it happens? If you add only FetchType.LAZY to OneToOne do you see the additional query for Manufacturer? – Adam Mar 29 '20 at 17:47
I changed the fetch to Lazy and added `@JsonIgnore` in every mapping. Now I am getting fine.. Can you plz advise me, adding @JsonIgnore is a correct method or not?? – GameCoder007 Mar 29 '20 at 17:47
I added `@OneToOne(fetch=FetchType.LAZY) @JoinColumn(name="manufacturerID") private Manufacturer manufacturer;` but the above exception occured So I added @JsonIgnore. – GameCoder007 Mar 29 '20 at 17:49
answer for your Qn: For which OneToMany mappings it happens? For all the OneToMany mapping there is seperate query running for each row. If you need I can add that query in the description. – GameCoder007 Mar 29 '20 at 17:50
IMO it happens because you are accessing fields (with OneToMany mappings) after you read them from DB. This is a well know n+1 problem (https://stackoverflow.com/questions/32453989/what-is-the-solution-for-the-n1-issue-in-jpa-and-hibernate). – Adam Mar 29 '20 at 18:02
I added OneToOne fetch type to lazy but the above mentioned exception occured and it is recommended to disable the SerializationFeature.FAIL_ON_EMPTY_BEANS. So I used the @Jsonignored annotations. Is it a correct method?? – GameCoder007 Mar 29 '20 at 18:09
Can you update your question so we can see what are you doing after with entities retrieved from db? – Adam Mar 29 '20 at 18:12
Updated the question with updation – GameCoder007 Mar 29 '20 at 18:35

Java : Repeatly running the child mapping query individually in Spring Boot Application

1 Answers1