How to solve java.lang.StackOverflowError due to recursion while forming HQL query

Question

I am getting StackOverflowError whenever iterations in loop goes higher. i am writing my logic like this:

public List<Vehicle> seacrhCar(Integer from, SearchDto searchDto,List<String> coveredZipcodes) {
    String q = "from Vehicle where 1=1";
    int i=1;

    if(StringUtils.isNotBlank(searchDto.getFromYear()))
        q+=" and year>='"+StringUtils.replace(searchDto.getFromYear().trim(), "'", "''")+"'";

    if(StringUtils.isNotBlank(searchDto.getToYear()))
        q+=" and year<='"+StringUtils.replace(searchDto.getToYear().trim(), "'", "''")+"'";

    if(StringUtils.isNotBlank(searchDto.getManufacturer()))
        q+=" and make='"+StringUtils.replace(searchDto.getManufacturer().trim(), "'", "''")+"'";

    if(StringUtils.isNotBlank(searchDto.getCarModel()))
        q+=" and model='"+StringUtils.replace(searchDto.getCarModel(), "'", "''")+"'";

    if(StringUtils.isNotBlank(searchDto.getTrim()))
        q+=" and trim='"+StringUtils.replace(searchDto.getTrim().trim(), "'", "''")+"'";

    if(StringUtils.isNotBlank(searchDto.getMinPrice()))
        q+=" and priceBaseMsrp>="+searchDto.getMinPrice().trim();

    if(StringUtils.isNotBlank(searchDto.getMaxPrice()))
        q+=" and priceBaseMsrp<="+searchDto.getMaxPrice().trim();

    if(coveredZipcodes.size()>0) {
        q+=" and (";
        for(String zip : coveredZipcodes) {
            q+="zipcode LIKE '%"+zip+"'";
            q+=(i==coveredZipcodes.size())?")":" or ";
            i++;
        }
    }
    q+=" group by vehicle";


    List<Vehicle> vehicles = sessionFactory.getCurrentSession().createQuery(q).setFirstResult(from).setMaxResults(10).setFlushMode(FlushMode.ALWAYS).list();
    return vehicles;

This is the part in above code which is causing problem:

if(coveredZipcodes.size()>0) {
        q+=" and (";
        for(String zip : coveredZipcodes) {
            q+="zipcode LIKE '%"+zip+"'";
            q+=(i==coveredZipcodes.size())?")":" or ";
            i++;
        }
    }

The above logic is for searching vehicles from the Vehicle table based on multiple parameter, where one of the parameter is zipcodes. The values of the zipcodes are coming in a list which needs to be iterated and added to the query.

The size of zipcodeList (coveredZipcodes) depends on radius(distance) selected by user from his current zipcodes, e.g. when a user selects a 10 mile radius from his current zipcode, the number of zipcode will be, say 200, and as miles increases so does the number of zipcodes. Up to 80-90 miles, I'm not getting any errors, but when i am selecting 100 miles or more, it throws StackOverflowError.

Here is the Stack Trace:

org.springframework.web.util.NestedServletException: Handler processing failed; nested exception is java.lang.StackOverflowError
org.springframework.web.servlet.DispatcherServlet.triggerAfterCompletionWithError(DispatcherServlet.java:1284)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:965)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)

root cause

java.lang.StackOverflowError org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2654) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanExpr(SqlGeneratorBase.java:840) org.hibernate.hql.internal.antlr.SqlGeneratorBase.booleanOp(SqlGeneratorBase.java:2685)

I understood the problem after some research, but how do i solve this problem?

Answer 1

Thanks everyone for ur good suggestions, specially @Pratik. Finally this is the piece of code which worked for me.

if(coveredZipcodes.size()>0) {
        q.append(" and substring(zipcode,5,9) in (:zipcodes)");
    }
    q.append(" group by vehicle");

    Query query = sessionFactory.getCurrentSession().createQuery(q.toString());
    if(coveredZipcodes.size()>0)
        query.setParameterList("zipcodes", coveredZipcodes);

    List<Vehicle> vehicles = query.setFirstResult(from).setMaxResults(10).list();
    return vehicles; 

However i am going to replace the 'substring(zipcode,5,9)' part with just 'zipcode' and will insert zipcode in DB in format xxxxx (e.g, 12345) and not keep it in USA-xxxxx format to make the query more simpler and increase the search performance.

Answer 2

If you can change the slow performing LIKE to IN, then you could switch to criteria's. In that case you can parse the list of zip codes without having to do any looping yourself.

List<Vehicle> vehicles = sessionFactory.getCurrentSession().createCriteria(Vehicle.class)
    .add(Restrictions.in("zipcode ", coveredZipcodes)).list();