How to handle org.postgresql.util.PSQLException
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
