This discussion is archived
6 Replies Latest reply: Sep 29, 2012 2:09 AM by 964881 RSS

Why it is dangerous to use Connection as an instance variable in a project?

964881 Newbie
Currently Being Moderated
Why it is dangerous to use Connection as an instance variable in a project?
  • 1. Re: Why it is dangerous to use Connection as an instance variable in a project?
    gimbal2 Guru
    Currently Being Moderated
    It is not dangerous by default, it depends on the situation in which you use the connection. Anywhere where the connection might be used concurrently by different threads is a risk.
  • 2. Re: Why it is dangerous to use Connection as an instance variable in a project?
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    Why it is dangerous to use Connection as an instance variable in a project?
    >
    Why do you say it is dangerous?
  • 3. Re: Why it is dangerous to use Connection as an instance variable in a project?
    Joe Weinstein Expert
    Currently Being Moderated
    Gimbal2 got it. In a simple single-threaded app, there is no problem, but with multiple threads running the
    same class, such as a servlet, and/or with connection pooling, the instance variable is vulnerable to sharing,
    or worse, overwriting, so everyone shares the connection established by the last writer and any previous
    ones may have been overwritten before being closed, causing pool problems.
    The ideal coding style for JDBC in a multithreaded/pooling environment is:

    /* This is how you should make any of your top-level methods
    * that will do JDBC work for any of your user invokes.
    */
    public void myTopLevelJDBCMethod()
    {
    Connection c = null; // All JDBC objects should be method
    // level objects to ensure thread-safety
    // and prevent connection leaking.
    // Define the connection object before
    // the JDBC 'try' block.

    try {
    // This is the JDBC try block for this method. Do
    // all the JDBC for this method in this block.

    // Get the connection directly from a DataSource
    // in the try block. Do not get it from any method/object
    // that has reserved a connection and is keeping it for repeated
    // (or shared!) use.

    c = myDataSource.getConnection();
    ....; // do all the JDBC needed with this connection

    // When all the JDBC is finished in the try-block, close the con:
    c.close(); // always close connection ASAP in the same method
    // and block that created/obtained it.
    c = null; // set the con to null so the finally block below
    // knows it's been taken care of.

    // If you have more non-JDBC work to do in this routine, OK, do
    // it now, but be sure to close the connection as above, as soon
    // as possible before doing anything else that may take any time.
    // I have seen customer applications triple their throughput simply
    // by getting the JDBC connection as late as possible, and closing
    // the JDBC connection as soon as possible.
    ...
    }
    catch (Exception e ) {
    ... do whatever, according to your needs... you do not have to
    ... have a catch block if you don't want it...
    }
    finally {
    // Always have this finally block. A finally block is crucial to
    // ensure the connection is closed and returned to the pool,
    // (not leaked).
    // failsafe: Do every individual thing you want to do in the
    // finally block in it's own try block-catch-ignore so every
    // individual thing is attempted.

    try {if (c != null) c.close();} catch (Exception ignore){}
    try {if (you have something else to do) do_it();} catch (Exception ignore){}
    }
    }
  • 4. Re: Why it is dangerous to use Connection as an instance variable in a project?
    rp0428 Guru
    Currently Being Moderated
    >
    Gimbal2 got it.
    >
    No - in terms of my question Gimbal2 didn't 'get it'. What Gimbal2 did was provide an explanation for why it 'could be' dangerous.

    I ask OP
    >
    Why do you say it is dangerous?
    >
    No one but OP can tell us why OP is saying it is dangerous and without knowing the source of OPs comment we can't be sure if he is talking about the same thing. He could be merely repeating what he was ask in an interview.

    The comments and code in your reply are informative but should have been directed to OP, not to me.
  • 5. Re: Why it is dangerous to use Connection as an instance variable in a project?
    Joe Weinstein Expert
    Currently Being Moderated
    You are right, I intended to respond to the OP. What I said did not relate to your post.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points