I have found my self in a bit confusion while reading database connection pooling topic.
I have read about advantages of connection pooling, and how it Enhances performance etc.
But a question raises suddenly in my mind , that what if I put non pooled connection object reference in servlet context and access that context when ever required.
Means the connection will be made to database lets say while context initialization (only once) and same connection will be used by multiple requests. No need to create database connections/pool any more.
Will this be a valid approach in terms of designing the application?
I believe for the first problem , we could place a check in code that would test connection aliveliness before use and act as per the condition of it .Wouldn't singleton connection object work for this problem?
For second problem , I am confused , what will happen if same connection reference gets accessed by multiple threads? Are there any chances of any thing getting hampered ?
Are there any chances of any thing getting hampered ?
Yes. Not all jdbc drivers are thread safe. And you might hijack someone's transaction, thereby breaking isolation of transactions.
What if screen A uses the connection to create a transaction, and updates a row,
Screen B uses the same transaction, updates some other record and commits (while A still has to do some more work)
And A detects an error somewhere, and you want to roll back the transaction in A?
For load balancing, availability and high availability operations, your application server might require that you put objects in Session in stead of application scope, and it might require that all objects in session scope are serializable. Is your Connection object serializable?