Compiled statement cache grows unbounded when not using prepared statements about sqlite4java HOT 3 CLOSED

Whoops.  I was just looking around your issues and noticed issue 32.  I promise 
I searched beforehand!

I actually didn't know about the boolean on prepare that allows me to specify 
that I don't want the statement cached.  That's useful.

Anyway, I'd still maintain that the cache is an unexpected feature, and at 
least needs a limit.

Brian, thanks for posting this issue.

I see nothing terrible in OutOfMemoryError and 100MB of used memory - it 
allowed you to discover caching in sqlite4java and the additional option to 
prepare method. :) Anyone can easily write code, using any library or not, that 
would consume all of the memory and terminate. 

I think we could have a bounded LRU cache for the statements, but I doubt its 
usefulness. Imagine it were there, and your application wouldn't terminate 
because out of memory - but because you didn't know about the caching, it would 
still consume, say, 50MB constantly. Not a good thing. Unbounded cache is more 
fail-fast.

What makes sense to me is that probably most users don't need this caching. 
There is benefit in caching the results of prepare(), but it is likely to be 
negligible in the context of most apps. The library was created with really 
strong performance requirements in mind -- the SQLParts class, for example, is 
intended to avoid concatenating Strings when creating generated SQL, to produce 
less garbage.

I think we can add a SQLite.setCacheStatementByDefault() method to govern the 
call to prepare() without explicit cache parameter, and have the corresponding 
flag set to false initially. This would not be backwards-compatible with the 
current version though - current users would have to call 
SQLite.setCacheStatementByDefault(true) to revert to the old version behavior.

Thoughts?

Cheers
Igor

At the moment, we have decided not to go ahead with any caching improvements, 
given the existing method parameter. Should there be sufficient demand for this 
feature, the issue may be reopened.

Thanks,
Igor