threadlocal vs connection pool

[guruuu prasadd]

OK, this might not be a beginning Java question. I am new to the forum and i apologize if this is not the correct place for this question.

Application gets a message from sender application.

opens a oracle db connection and stores the connection in threadlocal varaible

calls a whole bunch of classes passing the threadlocal variable to do some things to the data and store it in a oracle array

then continues to get more messages one at a time and does that same thing until it gets a commit messages from sender

when the whole set of array does a bunch insert to db

and releases the thread. and resets the threadlocal variable.


Now i saw multiple online didcussions on how threadlocal variables are not a recommended way to keep the db connection
and instead we should be using jdbc connection pool.

what i dont understand is, the jdbc connection pool is available and the threadlocal variable is merely getting a connection from the pool
and returning it back to the pool when it is done doing what it does.

i dont know what people mean when they say use jdbc connection pool instead

Also, do you guys think the above approach to handle db connections is a good way to go about this?

Any other better ways this could be done. I saw a forum where they recommend using Hibernate.

Please share your thoughts.

thanks.

[guruuu prasadd]

I guess I am trying to find out what is
The most efficient way to get oracle connection and keep it alive to form orracle arrays of data and finally do a batch insert.

[Steve Luke]

In my mind, no, I don't like this way of doing things. What I see happening is that you are holding on to the connection for too long. The connection to the database is a precious resource and you should hold on to it for as short a period of time as possible.

If you need to store it in a variable (thread local or not), that isn't method local than it is taken out of the pool for too long, in my opinion. Your thread should collect all the data it needs to put into the database first. Then get the connection from the pool, commit the transaction, and release the connection.

If you have to publish intermediately to the database in order to get data required for the next steps, then you need to ask if the entire process must be handled as a unit from a db perspective (i.e. does it need to be committed and or rolled back as one big unit). If not, then you should get and release connections for each subset of data, and only hold the connection across multiple accesses when all of them need to be handled in the same transaction.

And in all cases I would rather see the worker task be a new instance for the entire transaction, rather than a single instance shared between multiple transactions/calls (so ThreadLocal isn't needed and there is no chance of data cross talk).

[guruuu prasadd]

Thanks that makes sense

[Jayesh A Lalwani]

Actually, what you proposed is exactly what happens in most applications that use Hibernate. In Hibernate, you have this notion of session. When you open an Hibernate Session, Hibernate borrows a connection from the pool and holds on to it. WHen you close the session, Hibernate returns it back to the pool. Usually, what most people do is start a transaction, do business logic + db operations, commit the transaction. In this case, it will exactly work like you are proposing. ANd actually if you use Spring, the current transaction is stored in a TL variable, which holds on to the Session, which holds on to the COnnection. So, in essence, the connection is in a TL variable

Having said that, I agree with Steve's point too. You shouldn't be holding on to the connection for longer than you ave to. It's just that in most cases the business logic is so light that it doesn't make sense to build the complexity of doing the business logic outside of the transaction. If your business logic is intensive, then I agree with Steve 100%: you should definitely do it outside the transaction/session boundary

I would reccoemdn using some sort of ORM layer too. It looks like you are kind o reinventing the wheel here. If you have the option of picking which technology to use, I would suggest that you build this to run in some sort of container that provides transaction, and use an ORM library like JPA/Hibernate. Personally, I would do this in SPring + JPA. Actually, I would just use Spring Data. It makes programming the DataAccess layer a cinch.