How to close the embedded Derby that is correctly defined as a DataSource on WildFly

Question

When I usually defined Derby DataSource in WildFly, the db.lck file remains idle and indicates that the database was not disconnected properly every time WildFly shut down. because embedded Derby requires a special shutdown procedure that acquires a new connection with a JDBC URL that ends with the string "; shutdown = true".

So, I need something like a shutdown that performs a shutdown procedure. I found that old JBoss has a way to achieve this:

https://developer.jboss.org/wiki/SetUpADerbyDatasource http://grepcode.com/file/repository.jboss.org/nexus/content/repositories/releases/org.jboss.jbossas/jboss-as-varia/6.0 .0.Final / org / jboss / jdbc / DerbyDatabase.java

But I don’t know how to apply this in recent WildFly, because it looks like an “mbean” and “dependent” element, which is no longer allowed in the data source definition, and I can’t find its equivalent in recent WildFly.

I think the variable "connection-listener-class" of the data source definition is significant, and this may be a way to achieve it. I haven't tried it yet, but it looks a bit complicated, and I'm not sure if it works as expected.

So, is there a way to determine the shutdown that performs the Derby shutdown procedure with the recent version of WildFly?

EDIT:

I posted instructions for installing Apache Derby in WildFly, which includes my solution. http://www.nailedtothex.org/roller/kyle/entry/installing-apache-derby-to-wildfly

Answer 1

I found the best solution with MBean. it just performs a shutdown procedure every time WildFly shuts down.

Simply clone the repository and create the jar, put it on $WILDFLY_HOME/standalone/deployments . There are different files in the project, so you need to insert all the files here.

Dependence on org.jboss.ironjacamar.jdbcadapters, connection-listener-class and connection-listener-property is not required, so now $WILDFLY_HOME/modules/org/apache/derby/main/module.xml can be simplified as follows:

module.xml

 <?xml version="1.0" encoding="UTF-8"?> <module xmlns="urn:jboss:module:1.0" name="org.apache.derby"> <resources> <resource-root path="derby.jar"/> </resources> <dependencies> <module name="javax.api"/> <module name="javax.transaction.api"/> </dependencies> </module> 

Answer 2

The connection-listener-class worked as I expected. I registered a listener class, after which Derby starts shutdown correctly every time WildFly shuts down.

But I have to remember one thing: test-connection-in-pool will not start the listener-class. if I just ran WildFly, then exec test-connection-in-pool in the datasource Derby, and then turn off WildFly, then the listener will never be called, and db.lck will remain unchanged. so this may not be the perfect solution. something like turning off the hook more easily would be better.

I have done the following:

• Put the .jar derby, the jar that contains the ConnectionListener and module.xml implementation in $WILDFLY_HOME/modules/org/apache/derby/main
• Register JDBC Derby driver (following commands for jboss-cli)
  /subsystem=datasources/jdbc-driver=derby:add(driver-name=derby, driver-module-name=org.apache.derby, driver-class-name=org.apache.derby.jdbc.EmbeddedDriver)
• Register a data source
  data-source add --name=DerbyDS --driver-name=derby --connection-url=jdbc:derby:/Users/kyle/tmp/derbytest --jndi-name=java:jboss/jdbc/DerbyDS --user-name=sa --password=sa
• Configure a listener connection class
  /subsystem=datasources/data-source=DerbyDS:write-attribute(name=connection-listener-class, value=org.nailedtothex.derby.DerbyShutdownConnectionListener)
• Setting connection-listener-property
  /subsystem=datasources/data-source=DerbyDS:write-attribute(name=connection-listener-property,value={"url"=>"jdbc:derby:/Users/kyle/tmp/derbytest"}

module.xml

 <?xml version="1.0" encoding="UTF-8"?> <module xmlns="urn:jboss:module:1.0" name="org.apache.derby"> <resources> <resource-root path="derby.jar"/> <resource-root path="derby-shutdown-hook-1.0-SNAPSHOT.jar"/> </resources> <dependencies> <module name="javax.api"/> <module name="javax.transaction.api"/> <module name="org.jboss.ironjacamar.jdbcadapters"/> </dependencies> </module> 

DerbyShutdownConnectionListener.java

Note that GitHub for the whole project contains pom.xml

 import org.jboss.jca.adapters.jdbc.spi.listener.ConnectionListener; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; public class DerbyShutdownConnectionListener implements ConnectionListener { private static final Logger log = Logger.getLogger(DerbyShutdownConnectionListener.class.getName()); private static final String DEFAULT_URL = "jdbc:derby:"; private static final String SHUTDOWN_SUFFIX = ";shutdown=true"; private String url; private String urlForShutdown; public String getUrl() { return url; } public void setUrl(String url) { this.url = url; } @Override synchronized public void initialize(final ClassLoader classLoader) throws SQLException { urlForShutdown = createUrlForShutdown(); Runtime.getRuntime().addShutdownHook(new Thread() { @Override public void run() { log.log(Level.INFO, "Shutdown derby. URL to use: {0}", urlForShutdown); shutdown(urlForShutdown); } }); log.log(Level.INFO, "Derby shutdown hook added. URL to use: {0}", urlForShutdown); } private String createUrlForShutdown() { return (url == null ? DEFAULT_URL : url) + SHUTDOWN_SUFFIX; } private void shutdown(String url) { Connection cn = null; try { cn = DriverManager.getConnection(url); } catch (SQLException e) { if ("08006".equals(e.getSQLState()) || "XJ015".equals(e.getSQLState())) { log.log(Level.INFO, "Derby shutdown succeeded. SQLState={0}", e.getSQLState()); return; } log.log(Level.SEVERE, "Derby shutdown failed", e); } finally { if (cn != null) { try { cn.close(); } catch (Exception e) { } } } } @Override public void activated(Connection connection) throws SQLException { } @Override public void passivated(Connection connection) throws SQLException { } }