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?

use the StringBuilder class instead of concatenating strings with += — samgak, Mar 27 '15 at 06:06
Search for every 50 zipcodes and merge the results at the end and return... — Pokuri, Mar 27 '15 at 06:21
@samgak thanks for the reply, but using StringBuilder doesn't seem to solve my problem. still getting same error. — Narendra, Mar 27 '15 at 06:23
@Pokuri won't that cause performance issue? hitting the database multiple times? — Narendra, Mar 27 '15 at 06:28
@Steve i don't uderstand. what does that have to do with this issue? — Narendra, Mar 27 '15 at 06:43
Ok @Steve im using where 1=1(Always True). Got it! thanks for pointing out that. i'll see to that later. first i need to fix this issue :) — Narendra, Mar 27 '15 at 06:52
A `StackOverflowError` often occures when you have some kind of recusive algorithem (that does not terminate correct) but I do not see any recursive method invocation in this code, so please post the stack trace. — Ralph, Mar 27 '15 at 07:27
http://stackoverflow.com/questions/12652937/passing-list-to-in-clause-in-hql-or-sql — Pratik Shelar, Mar 27 '15 at 07:27
Also as i am not getting all the results in one take and getting only 10 results at a time, I think its not possible to search for every 50 zipcodes at a time and merge the results at the end and return.. @Pokuri — Narendra, Mar 27 '15 at 07:28
you can replace you for loop by just passing the entire list to the hql — Pratik Shelar, Mar 27 '15 at 07:31
@Ralph i have list of zipcode which sometimes has less zips, but sometimes more(based on distance selected by user from their current zipcode). and this list of zipcode is iterated to form complete query(as you can see in the code). this iteration while forming HQL query causes this problem. i have added the stack trace in the post. — Narendra, Mar 27 '15 at 07:35
Form the stacktrace you can see that that problem is not the building of the query (therefore the StringBuilder solution (that makes your code more efficeient) does not help because it does not "optimize" the problem), you problem is "simply" that the query seams to complex for Hibernate. .... Do you need the "Like" or is an simple equals (zipcode = X) enogth — Ralph, Mar 27 '15 at 07:46
@Ralph the zip code in database is in USA-xxxxx format, thats why i used LIKE but i may be able to fix this while inserting the record..will try that — Narendra, Mar 27 '15 at 08:27
After identify the cause, you should maybe rephrase the question, so that you ask for an "better" query. -- I think it is an interesting question but i have no good answer. — Ralph, Mar 27 '15 at 09:48
I also strongly recommend to use a parametrized query instead - to get rid of the sql-injection problem!!! (I think in your case, writing the query by CriteriaAPI (like Jonas Pedersen showed in his answer), is the most elegant way to write a parametrized query) — Ralph, Mar 27 '15 at 09:50

score 4 · Accepted Answer · answered Mar 27 '15 at 09:59

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.

score 2 · Answer 2 · answered Mar 27 '15 at 07:43

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();

answered Mar 27 '15 at 07:43

Jonas Pedersen

926
10
21

the zip code in database is in USA-xxxxx format, thats why i used LIKE but i may be able to fix this while inserting the record..will try this.. – Narendra Mar 27 '15 at 08:28

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

2 Answers2