2 Replies Latest reply on Nov 6, 2013 6:35 AM by MH C

    How to handle org.postgresql.util.PSQLException

    MH C Newbie

      Using Jboss 7.1.1 Final with a datasource to a Postgresql database, I'm not able to get database status when the db service is stopped. I want to be able to check DB status to display a message in GUI(Using JSF/Primefaces). Here's my datasource in standalone.xml Jboss configuration file:

       

       

      <datasource jndi-name="java:jboss/datasources/ProjectDS" pool-name="postgresDS" enabled="true" use-java-context="true">

          <connection-url>jdbc:postgresql://127.0.0.1:5432/ProjectDB</connection-url>

          <driver>postgresql</driver>

          <security>

            <user-name>postgres</user-name>

            <password>*******</password>

          </security>

          <validation>

             <check-valid-connection-sql>select 1</check-valid-connection-sql>

          </validation>

      </datasource>

      I've found that the <check-valid-connection-sql> tag allows checking the availability of connection.

       

       

      public boolean isDbConnectionOperational() {

          boolean result = false;

          Connection conn = null;

          DataSource ds = null;

          try {

              Context ctx = new InitialContext();

              ds = (DataSource) ctx.lookup("java:jboss/datasources/ProjectDS");

              conn = ds.getConnection();

              if (conn != null) {

                  result = true;

              }

          } catch (NamingException e) {

              LOGGER.error(e.getMessage());

          } catch (SQLException e) {

              LOGGER.error(e.getMessage());

          } finally {

              if (conn != null) {

                  try {

                      conn.close();

                  } catch (SQLException e) {

                      LOGGER.error(e.getMessage());

                  }

              }

          }

       

       

       

       

          return result;

      }

      In my Bean, i call this method:

       

       

      dbStatus = myService.isDbConnectionOperational();

      This boolean is assigned to an output text in the xhtml page to display either OK or KO.but it never displays the KO status because it crashes giving the following errors:

       

       

          Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/ProjectDS

      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:390)

      at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:368)

      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464)

      at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:129)

      ... 220 more

       

       

       

       

      Caused by: javax.resource.ResourceException: IJ000658: Unexpected throwable while trying to create a connection: null

      at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:371)

      at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getTransactionNewConnection(AbstractPool.java:495)

      at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:374)

      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329)

      ... 223 more

      Caused by: javax.resource.ResourceException: Could not create connection

      at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:277)

      at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:235)

      at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.createConnectionEventListener(SemaphoreArrayListManagedConnectionPool.java:761)

      at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:343)

      ... 226 more

      Caused by: org.postgresql.util.PSQLException: Connexion refusée. Vérifiez que le nom de machine et le port sont corrects et que postmaster accepte les connexions TCP/IP.

      Caused by: java.net.ConnectException: Connection refused: connect