Jboss SQL Deployer

I whipped up a fairly simple SQL deployer for Jboss this past week. It is currently fairly limited, but also very straight forward and should be easy to maintain.

It consists of a class implementing java.lang.Runnable (a Thread) that wakes up every 20 seconds or so and scans a directory for any new SQL files. Of course there is also a class implementing java.io.FileFilter to look for just SQL scripts. To turn this into a JMX service, I wrote a simple BeanShell wrapper to drop into the deploy directory.

Once a SQL file is found, the code assumes the first line will contain the DataSource JNDI name, so it knows what database to connect to. Then it currently assumes the rest of the file is one query. The query results are formatted and dumped to STDOUT.

To make this truly useful, I will have to make it accept updates as well as queries, and parse the file looking for multiple SQL statements instead of assuming it is just one. Other than those limitations, it works great with the few tests I have done so far. I have been testing under Jboss 4.0.3, but this should work with any version of Jboss with the BSHDeployer.

SqlFileScanner.java


SqlFileFilter.java



sqldeployer.bsh



Why would you use this? That's always a good question to ask, and this time I have an answer besides "because you can".

• Running ad hoc queries right from your app server, using the same Jboss DataSource bindings your applications are using, and from the same server your apps are running, can be very helpful in troubleshooting issues.
  
• Let's say you use Realms instead of username/password in the DataSource XML descriptor, for security reasons. Now you can troubleshoot database issues as that user even without knowing the DB username and password.
  
• Set up simple reporting jobs without needing an Oracle client installed on the machine -- run them right from Jboss.

Conslusion
I think I may create a new open source project to house this as well as all the other Java "odds and ends" I have written over the years, like some of the code generators, MQ tools, servlet filters, JSP tags, etc. The hardest part will probably be coming up with a meaningful project name -- who wants to download and install "RobbsRandomJavaCrap.jar"?