Checking a connection and reconnecting if needed

[Roger F. Gay]

Can anybody see why this doesn't work? I haven't had any trouble with it except just now ... testing a program that asks for a connection when no connection at all has been made before. But I haven't really confirmed at this point that it reconnects automatically when the connection is lost.

I think I've included all of the relevant code. My apologies in advance if I just forgot to include something important from the actual program.

The process starts with an object requesting the connection, by calling getWSConnection().

  private static boolean webshop_status;
  private static Connection webshop_con = null;
  private static String errorMessage;

  private static String webshop_url = "jdbc:sqlserver: .... etc.";

  public static synchronized Connection getWSConnection() {
    if (WSIsConnected()) {
      return(webshop_con);
    } else {
        if(reConnectWS()) {
          return(webshop_con);
        } else {
          return(null);
        }
    }
  }
  public static synchronized boolean WSIsConnected () {
      try {
       return(webshop_con.isValid(0));
      } catch (SQLException e) {
       return(false);
      }
  }
  public static synchronized boolean reConnectWS () {
    if (webshop_status) {
      if (connectWebshop()) {
        webshop_status = true;
      } else {
        webshop_status = false;
      }
    }
    return(butiksystem_status);
  }
  public static synchronized boolean connectWebshop () {

    try {
      webshop_con = DriverManager.getConnection(webshop_url);
      webshop_status = true;
      return(webshop_status);
    } catch (Exception e) {
      webshop_status = false;
      errorMessage = "WebShopStorage.connectWebshop.Exception: " + e;
      return(webshop_status);
    }
  }

[Greg Charles]

You forgot to include what you meant by "this doesn't work". Is there an exception? Does the flow not work as expected? What did you expect, and what happened?

A couple things I see seem funny. In reConnectWS() you don't try to reconnect unless webshot_status is true, so one failure to connect would mean no reconnecting ever. You also protect reconnecting with WSIsConnected, and I don't see where that's set.

Now, if I could get pedantic for a moment: please learn Java naming conventions. Maybe it's just me getting old and inflexible, but this code makes my eyes hurt.

[Roger F. Gay]

Greg Charles wrote:You forgot to include what you meant by "this doesn't work". Is there an exception? Does the flow not work as expected? What did you expect, and what happened?

A couple things I see seem funny. In reConnectWS() you don't try to reconnect unless webshot_status is true, so one failure to connect would mean no reconnecting ever. You also protect reconnecting with WSIsConnected, and I don't see where that's set.

Now, if I could get pedantic for a moment: please learn Java naming conventions. Maybe it's just me getting old and inflexible, but this code makes my eyes hurt.

Yeah, sorry. I know I get loose with naming conventions at times. Never took a formal course, but WSIsConnected() is a method.
I was getting a 500 error in response to my http request and nothing at all in the responseText so I haven't seen an error message telling me what's happening in the code.

What I want is a data base connection.
So long as I've initialized the connection by starting the webshop, the functions I was testing today (A guest book.) work fine.
The circumstances of the failure were that the guest book was asking for a connection when one had never been created before.
So it was using the code above absolutely fresh ... and I'm starting to think that maybe I shouldn't be using the isValid(0) method on a Connection object (webshop_con) that's set to null.

[Greg Charles]

A 500 error usually means an uncaught exception. Do you see a stacktrace in your browser, or a log file somewhere? If not, you might have to attach a debugger and try to figure out what's going wrong and where.

Generally speaking, you want to hold on to database connections for as little time as possible. Open it, run your queries, then close it. When you need to do more queries, open a new connection. In JavaEE, you always get connections from a javax.sql.DataSource, so opening means taking a connection from a managed pool of open connections, and closing it means returning it to the pool.

[Roger F. Gay]

What I'm doing is a fake little connection pool with one connection for a low traffic website. I also have a question in to help me get started with Tomcat connection pooling so I can switch out as soon as I'm ready and have the time. Connection pools hold onto a set of connections indefinitely. When you request a connection, the whole idea is that it doesn't have to create a new one. You want to let go of connections from connection pools as fast as possible so they can be reused. I can understand that you also don't want a zillion connections building up ... which doesn't happen in my app because I'm sharing a single connection.

I think your question about giving up after one try is a good one too, given that I'm a newbie. It is based on an assumption so I might end up learning something by stating it. I assume that if it cannot reconnect, then the problem will need to be handled by a human. Somebody might have turned off the computer with the database running in it or something. I'm fetching data from a database that's part of their internal cash register system. I in fact, don't know yet whether the database continues running if all the computers running their cash register system have closed their registers etc. I've been in contact a bit with their support group and they know what's going on, but I have had some surprises. They're not actually helping with this project unless someone explicitly asks for help and pays them for it.

The fact that I'm tapping into someone else's database is another reason for this particular design. I was asked not to make any changes to their database. So, instead of using stored procedures, I'm using prepared statements. In an effort to get them to operate as close to the efficiency of stored procedures as possible, I initialize them and then try to hang onto the connection so they don't have to be reinitialized / recompiled. I'm not showing that code because the problem I'm posting doesn't involve connecting to their system. I'm just being consistent by doing something similar with the new db for this app ... i.e. hanging onto the connection etc.

[Roger F. Gay]

Oh wait .. what you said ... if it's never been initialized, webshop_status isn't set. There's no "else". lol