I'm starting work on a small java web app sitting on Oracle 10g. The expected user base will be up to 100 users. I don't expect to see more than 50 users at a time using the app.
Now, I'm considering DBCP, C3P0 or Tomcat JDBC Connection Pool, but will any of this add real value for such a small user base? I've never used any of the solutions, so there is a risk of introducing complexity(and I've seen other threads where people faced some crazy issues with connection pooling) and I'm not sure that the benefits will out-weight the risk. Alternatively are there any other ways of doing it? Maybe Oracle has it's own solution?
Please help.
Thank you.
Creating a DB connection on every request is expensive and will cause your app and DB server to spend a lot of time creating and tearing down connections instead of serving your users with useful data.
Even with a comparatively small user base, it is much better to have a pool of connections ready to be used to service requests as they arrive.
My experience is in exact contrast to the other threads you may seen, connection pooling is a well proven technology and I have never had an issue with it on any platform I've used.
Oracle does have its own connection pooling called Oracle Universal Connection Pooling ("UCP"). You can either use their data source that provides this or you can use the vanilla data source and layer one of the technologies listed in your question on top.