i am using connection pooling with Apache tomcat server, with the underlying database as MySQL.
and there is a possibility of hundreds of parameters being passed to the query.
You have a SERIOUS design flaw if you have a procedure that uses 'hundreds of parameters'. That is not scaleable, performant or easy to write, test or maintain.
Code should be written in small, simple modules. In 25+ years of working with some of the largest Oracle, DB2 and SQL Server DBs around I have never come across any code that required more than a two or three dozen parameters. I can't even imagine a scenario where 'hundreds' of parameters would be appropriate.
I suggest that you address that design problem.
> Is there any limit ...
Computers always have limits.
Just some of the limits you might encounter.
- Statement length limit
- Parameters limit for 'in' clause
- Length limit on where clause
> and there is a possibility of hundreds of parameters being passed to the query.
Then some options would be
- Don't let them pass that many
- Do a loop with a subset of of the total set until you collect all the results.
- Find out how they are getting that list and see if you can get the same list from your database