Java Database Connection Down Glassfish

Question

I have a requirement where I need to check if the database connection is down, I then send an e-mail. Below is the code:

public static void main(String[] args) {
     try {
         Connection connection = DriverManager.getConnection("url", "username", "password");
         Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery("SELECT * FROM Table");
    }
    catch(SQLException e) {
        if(e.getMessage().contains(new String("ORA-12543"))) {
           // send email
        }
    }
}

I used this website to determine when the Oracle database is down: http://www.dbmotive.com/ora-12543-tnsdestination-host-unreachable/

Now, I would like to setup the JDBC Resource and JDBC Connection pool in Glassfish and use the connection from there. I have never used Glassfish. I can't seem to find what I am looking for. Once, I have setup the resources and connection pool in Glassfish I would like to know if the database is down according to the above code will this code still work or there would be any changes. I would like to know when the database is down, then send an e-mail.

Please note the above code is sample, but in my main code this code exists.

Answer 1

IMHO, you will need to use the following codes once you are using connection retrieved through JNDI datasource. (found here:) I have also provided details on how to retrieve the JNDI datasource below.

■3113: "end-of-file on communication channel"

■3114: "not connected to ORACLE"

■1033: "ORACLE initialization or shutdown in progress"

■1034: "ORACLE not available"

■1089: "immediate shutdown in progress - no operations are permitted"

■1090: "shutdown in progress - connection is not permitted"

■17002: "I/O exception"

=========================== Code to retrieve the connection from datasource

InitialContext ic = new InitialContext();
     com.sun.appserv.DataSource ds = (com.sun.appserv.DataSOurce) ic.lookup("jdbc/PointBase"); 
     Connection con = ds.getConnection();
     Connection drivercon = ds.getConnection(con);

Glassfish datasource javadoc is here

If you dont want to deploy the application, but use the datasource of a remote glassfish server, get the initialcontext as below

Properties props=new Properties();    
            props.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.fscontext.RefFSContextFactory");    
            props.put(Context.PROVIDER_URL,"iiop://URL_OF_APP_SERVER:PORT");    
            Context ctx=new InitialContext(props);    
            DataSource datasource = (DataSource)ctx.lookup("jndi/mydatasource");