Обсуждение: connection pooling with servlets

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.

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

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.  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).

Dave Cramer <pg@fastcrypt.com> wrote:

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

Dave

On 27-Apr-06, at 8:02 PM, J. wrote:

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.

---

New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.

J. wrote:
> 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
tomake 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
goodplace to put this sort of thing.  An easy way to make sure your datasource is always available is to use a servlet
contextlistener to create it when the application is initialized.  Google for servlet context lifecycle should tell you
howto 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

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.

David Durham <ddurham@vailsys.com> wrote:

J. wrote:
> 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.

J. wrote:
> 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.

J. wrote:
> 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:

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

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-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Guy Rouillier
> Sent: Friday, April 28, 2006 13:33
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] connection pooling with servlets
>
> J. wrote:
> > 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:
>
> 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

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.

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