StackOverflowError when adding many BooleanExpressions

[timowest]

I first tried to fire a query like

query.from(Table).where(Table.name.in(names))

where names consists of about 3000 items. This failed due to a JDBC restriction:

Prepared or callable statement has more than 2000 parameter markers

Next, I tried something like this:

       protected BooleanExpression prepareNamesExpression(StringPath stringPath, Collection<String> names) {
                BooleanExpression nameExp = null;
                Iterator<String> namesIt = names.iterator();
                for (int i = 0; namesIt.hasNext(); i++) {
                        BooleanExpression nameExp0 = stringPath.eq(namesIt.next());
                        if (i == 0) {
                                nameExp = nameExp0;
                        }
                        else {
                                nameameExp = nameExp.or(nameExp0);
                        }
                }
                return nameExp;
        }

Unfortunately, in this case I am getting a StackOverflowError:

Caused by: java.lang.StackOverflowError
        at com.mysema.query.types.OperatorImpl.hashCode(OperatorImpl.java:61)
        at java.util.HashMap.get(HashMap.java:300)
        at com.mysema.query.types.Templates.getTemplate(Templates.java:228)
        at com.mysema.query.support.SerializerBase.visitOperation(SerializerBase.java:209)
        at com.mysema.query.sql.SQLSerializer.visitOperation(SQLSerializer.java:533)
        at com.mysema.query.support.SerializerBase.visit(SerializerBase.java:190)
        at com.mysema.query.support.SerializerBase.visit(SerializerBase.java:25)
        at com.mysema.query.types.expr.BooleanOperation.accept(BooleanOperation.java:44)
        at com.mysema.query.support.SerializerBase.handle(SerializerBase.java:75)
        at com.mysema.query.support.SerializerBase.visitOperation(SerializerBase.java:235)
        at com.mysema.query.sql.SQLSerializer.visitOperation(SQLSerializer.java:533)
...

I am using JRE 1.6 64-bit server VM and did not modify the maximum stack size.

I have already come across such problems in earlier Hibernate versions which used recursion instead of loops at many places.

What should I do?

timowest

Here is a discussion of available options : http://stackoverflow.com/questions/178479/pre...nt-in-clause-alternatives

This is a JDBC problem, and unfortunately Querydsl doesn't have a dedicated solution for it.

I'd defintely stick with a solution that uses PreparedStatement style binding.

nyc2

That means even if Querydsl were able to not run in a StackOverflowError this would not work since the number of bind parameters is just the same. I see...

The only feasible solution of the stackoverflow thread for me would be having a subselect as the in-clause as mentioned here: http://stackoverflow.com/questions/178479/pre...ernatives/5104833#5104833

I definetely have to use PreparedStatements to avoid SQL injections...

I tested the maximum number of parameters via TSQL:

There are too many parameters in this EXECUTE statement. The maximum number is 2100.

This shows that not only JDBC but even the RDBMS is limiting the maximum number of parameters.

timowest

Using a subselect is probably the right approach here, and it can be expressed with Querydsl.

nyc2

Could you please give me a hint how to produce something like

where col in (select * from split('val1,val2,val3'))

I know the example from the reference manual but I am not able to adopt it for the in-clause. Should I use

in(CollectionExpression)

with

new ListSubQuery<String>

I really don't know, sorry.

@timowest: I think you're on well-earned holiday, aren't you? :-)
At the moment the question mentioned above is blocking my work. I'd like you to take a quick look at it when you are back again.

Any other help would also be appreciated. :-)

vema

@nyc2 The reason why things have slowed down a bit is that Timo got his firstborn a few days ago. But I am guessing he will be back in action soon.

nyc2

@timowest: Congratulations on the birth of your baby!

timowest

where col in (select * from split('val1,val2,val3'))

becomes


TemplateExpression<String> split = TemplateExpressionImpl.create(String.class, "split({0}", new ConstantImpl<String>("val1,val2,val3"));
TemplateExpression<String> all = TemplateExpressionImpl.create(String.class, "*");
// alternatively Wildcard.all, but a String typed expression works better in this case

col.in(new SQLSubQuery().from(split).list(all));