Sharing Connection Object

[jbig zala]

HI friends,
i have created servlet that uses Connection object for mysql database.
any reqeust for the servlet will create connection object individually.
i.e. 5 request will create 5 instance of servlet with connection object.
Is it possible to create database connection once that can be used by all request for same servlet ???

[Santosh Jagtap]

Hi dev,
Yeah its possible.u can use connection pooling take the connection from jndi.

[Neeraj Vij]

hi,

create the connection object in init() method.

cheers,
neeraj.

[Adeel Ansari]

Originally posted by dev smart:
HI friends,
i.e. 5 request will create 5 instance of servlet with connection object.

who said to you that 5 request will create 5 instance of servlet. No it is not like that really.

I would like to refer you to this thread

[Adeel Ansari]

Originally posted by Neeraj Vij:
hi,

create the connection object in init() method.

cheers,
neeraj.

yeah we can do that. but then wht you say about connection pooling. extension and shrinking of pool.

It could be a bottle neck. if one connection for all. and if you want to change the connection properties like,

conn.setAutoCommit(false);

then it will affect all transactions.

[Neeraj Vij]

Hi,


even though it is possible to create only one connection object..

just for my help..is it possible to use an existing opened connection to serve different request..which i don't think is possible..

If it is possible,I think we should never use this approach.. since any of the request can close the connection..

any inputs would be a great help...

Regards,
Neeraj.

[Adeel Ansari]

yeah we can use. and yeah you are right. But if we have three different transaction in the same method we usually use same conn object because in that method we takecare of those type of terrible things.

And the connection creation in init method, i said before "yeah we can do that".

[Santosh Jagtap]

see basically wat is connection pooling
server maintains the pool of connection object as per configuration
(i.e. minimum instances and maximum instances)
so any request comes for connection then server gives one object from the pool to tht request.
so there is no question of one object throughout the transaction..

[Neeraj Vij]

Hi,

I agree with the concept u told about connection pooling..however for a single transaction the same connection object is used.

Rgds,
Neeraj.

[Adeel Ansari]

Originally posted by Santosh Jagtap:
see basically wat is connection pooling
server maintains the pool of connection object as per configuration
(i.e. minimum instances and maximum instances)
so any request comes for connection then server gives one object from the pool to tht request.
so there is no question of one object throughout the transaction..

if you request one connection from the pool in your method and you need to perform say 3 transactions in the same method, then why you want to put back the connection to the pool and then request the connection again in the same method? and if you really want to do this then it would not work in some cases. think of a case.

[Santosh Jagtap]

agreed ,but in tht case i wont write three transactions in one method i'll seperate those three trans.
then it will work right?

[Adeel Ansari]

Originally posted by Santosh Jagtap:
agreed ,but in tht case i wont write three transactions in one method i'll seperate those three trans.
then it will work right?

i was not really meant three transaction in one method. i said something like,

doTransaction1(){
getConnFromPool();
....
..
putBackToPool(conn)
doTransaction2();
...
...
}

doTransaction2(){
getConnFromPool();
....
..
putBackToPool(conn)
}

now it is not that wrong if we say we are doing 2 transactions in method doTransaction1().

my point is why putting back the connection. just use the same.

there are two valid reason.

1 - if we put back the connection before calling doTransaction2(). we cannot rollback the first transaction if second fails.

2 - if we put back the connection after calling doTransaction2(). then why carry two connection objects. just use the same.

just want to add up. change the code like,

doTransaction1(){
getConnFromPool();
....
..
doTransaction2(conn);
...
...
putBackToPool(conn)
}

doTransaction2(){
....
..
}
[ September 21, 2004: Message edited by: adeel ansari ]

[Santosh Jagtap]

so it is as good as one transaction only right u r just spliting one transaction into two parts transactoin2 is a part of transaction1.so its better to use one connection object only...

[Adeel Ansari]

right buddy.

[Jeroen Wenting]

Originally posted by Neeraj Vij:
hi,

create the connection object in init() method.

cheers,
neeraj.

very, very bad practice for a variety of reasons.
1) when you close the connection it's gone
2) multiple requests will use the same connection at the same time leading to all kinds of trouble (thread deadlock, database trouble, etc. etc.)

and more.

Create a connection when you need it and destroy it as soon as that need is gone (or retrieve it from a connection pool and return it as soon as possible).

[Neeraj Vij]

Thanks Jeroen

It was just for creating a connection object once and using it for multiple request... I did not say..we should use it.

rgds,
Neeraj.

[Mark Spritzler]

"Dev Smart"-
Welcome to the JavaRanch! Please adjust your displayed name to meet the

JavaRanch Naming Policy.

You can change it

here.

Thanks! and welcome to the JavaRanch!

Mark