connection pooling with servlets

[J.]

I am a novice, but thought it would make more sense to post this here. I'm trying to write a basic webstore app for a school project and I'm having trouble getting more than one servlet to access the database connection. I tried the method described on the PostgreSQL site - as I understood it. That entailed opening a connection in one servlet and then having code like this in other servlets that need connections:

Connection grabConnection(HttpSession userSession){
ds = (Jdbc3PoolingDataSource) userSession.getAttribute("dSource");
Connection con = null;

try {
con = ds.getConnection();
// use connection
} catch(SQLException e) {
// log error
} finally {
if(con != null) {
try {con.close();}catch(SQLException e) {}
}
}
return con;
}

The trouble is (I'm sure you see it already) that passing a dataSource object in a session doesn't seem to work. If I don't have a dataSource, then I can't make a connection and if I try to create a new connection, I get the error that is something like "Connection with that name already exists". Any help and I'd be gratefull. Thanks.


---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.

[Dave Cramer]

Use a static method to get the connection instead of the session.

Dave

Post by J.
I am a novice, but thought it would make more sense to post this
here. I'm trying to write a basic webstore app for a school
project and I'm having trouble getting more than one servlet to
access the database connection. I tried the method described on
the PostgreSQL site - as I understood it. That entailed opening a
connection in one servlet and then having code like this in other
Connection grabConnection(HttpSession userSession){
ds = (Jdbc3PoolingDataSource) userSession.getAttribute
("dSource");
Connection con = null;
try {
con = ds.getConnection();
// use connection
} catch(SQLException e) {
// log error
} finally {
if(con != null) {
try {con.close();}catch(SQLException e) {}
}
}
return con;
}
The trouble is (I'm sure you see it already) that passing a
dataSource object in a session doesn't seem to work. If I don't
have a dataSource, then I can't make a connection and if I try to
create a new connection, I get the error that is something like
"Connection with that name already exists". Any help and I'd be
gratefull. Thanks.
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.
Great rates starting at 1¢/min.

[David Durham]

Thanks for replying. I'm not sure if that would help, but maybe if I
understand the suggestion better it will.
Right now I've got the index.jsp calling Login servlet via POST. Then
Login creates the connection, puts it into a session with some other
attributes and forward(req,res) to welcome.jsp.

Not much point in storing a datasource as a session attribute (1 datasource per application user?). More appropriate to make it application-wide by putting a datasource in a servlet context or, as someone else suggested, a static attribute/property/member/variable ... request.getSession().getServletContext().setAttribute(...). JNDI is another good place to put this sort of thing. An easy way to make sure your datasource is always available is to use a servlet context listener to create it when the application is initialized. Google for servlet context lifecycle should tell you how to do setup a listener.

Now, as for a null pointer on your connection object, maybe you have something misconfigured in your datasource. Username, password, host, and driver class are usual suspects.

Welcome.jsp has a form
that uses GET to call a Search servlet and this is where I get a null
pointer on the connection object. I'm trying to get the session out of
the request object, but it seems like the request loses state by the
time I'm getting to it (in Search servlet).

Not sure. You can post your code and maybe get some help. Wait, is this homework?


-Dave


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[J.]

Thanks. I posted some of the relevant code here;

http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=7&t=016798

But I'm still baffled as to how to get access to a session in a servlet that was called by a jsp. Should servlets have access to sessions easily? Everyone is making it seem like this shouldn't be a problem, but then I've read in other places that a jsp calling a servlet clears whatever attributes were part of the request object.

Thanks for replying. I'm not sure if that would help, but maybe if I
understand the suggestion better it will.
Right now I've got the index.jsp calling Login servlet via POST. Then
Login creates the connection, puts it into a session with some other
attributes and forward(req,res) to welcome.jsp.

Not much point in storing a datasource as a session attribute (1 datasource per application user?). More appropriate to make it application-wide by putting a datasource in a servlet context or, as someone else suggested, a static attribute/property/member/variable ... request.getSession().getServletContext().setAttribute(...). JNDI is another good place to put this sort of thing. An easy way to make sure your datasource is always available is to use a servlet context listener to create it when the application is initialized. Google for servlet context lifecycle should tell you how to do setup a listener.

Now, as for a null pointer on your connection object, maybe you have something misconfigured in your datasource. Username, password, host, and driver class are usual suspects.

Welcome.jsp has a form
that uses GET to call a Search servlet and this is where I get a null
pointer on the connection object. I'm trying to get the session out of
the request object, but it seems like the request loses state by the
time I'm getting to it (in Search servlet).

Not sure. You can post your code and maybe get some help. Wait, is this homework?


-Dave




---------------------------------
Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.

[David Durham]

Post by J.
Thanks. I posted some of the relevant code here;
http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=7&t=016798
But I'm still baffled as to how to get access to a session in a servlet
that was called by a jsp. Should servlets have access to sessions
easily?

Just look at the servlet api that matches the specification in the deployment descriptor, which is a DTD or schema in WEB-INF/web.xml.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[J.]

I've got postgresql installed with cygwin under xp. I need to alter my password but I can't find anything that works. Everything I've tried so far hasn't made the change and gives no feedback as to what might be wrong. Here is the command I use to run the psql tool:

psql -U username -d template1

Here are some things I've tried that didn't work:

alter user username with password 'userpass'

net user username userpass

This is the command I ran before I created my username, but now when I run it, it asks for a password which I either never knew or forgot:

psql -d template1

I could even delete the user and re-create it as long as I wouldn't lose access to the system. Thanks.


---------------------------------
Yahoo! Mail goes everywhere you do. Get it on your phone.

[Dave Cramer]

Please ask this on the correct list, this has nothing to do with JDBC

Post by J.
I've got postgresql installed with cygwin under xp. I need to
alter my password but I can't find anything that works. Everything
I've tried so far hasn't made the change and gives no feedback as
to what might be wrong. Here is the command I use to run the psql
psql -U username -d template1
alter user username with password 'userpass'
net user username userpass
This is the command I ran before I created my username, but now
when I run it, it asks for a password which I either never knew or
psql -d template1
I could even delete the user and re-create it as long as I wouldn't
lose access to the system. Thanks.
Yahoo! Mail goes everywhere you do. Get it on your phone.

[Guy Rouillier]

Post by J.
I am a novice, but thought it would make more sense to post this
here. I'm trying to write a basic webstore app for a school project
and I'm having trouble getting more than one servlet to access the
database connection. I tried the method described on the PostgreSQL
site - as I understood it. That entailed opening a connection in one
servlet and then having code like this in other servlets that need

Don't attempt to put your datasource in the session. Use a database
pool instead, and then just do a JNDI lookup to obtain a connection
wherever you need it. If you are using Tomcat as your servlet
container, it has built-in support for datasources. Post back if you
need help in configuring that.

[Jason Porter]

I've fixed the problem. Looked like there was a conflict between the
MyApp.war/META-INF/context.xml and the
Tomcat/Catalina/localhost/MyApp/context.xml

Thanks for the reply.

-----Original Message-----
Sent: Friday, April 28, 2006 13:33
Subject: Re: [JDBC] connection pooling with servlets

Post by J.
I am a novice, but thought it would make more sense to post this
here. I'm trying to write a basic webstore app for a school project
and I'm having trouble getting more than one servlet to access the
database connection. I tried the method described on the PostgreSQL
site - as I understood it. That entailed opening a connection in

one

Post by J.
servlet and then having code like this in other servlets that need

Don't attempt to put your datasource in the session. Use a database
pool instead, and then just do a JNDI lookup to obtain a connection
wherever you need it. If you are using Tomcat as your servlet
container, it has built-in support for datasources. Post back if you
need help in configuring that.
--
Guy Rouillier
---------------------------(end of

broadcast)---------------------------

TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster