{scrollbar}

How to execute SQL statements during deployment

There may be times where you will want to execute some cleanup or initialization SQL statements during the deployment time of, for example, a datasource. Below is an example of how this can be done.

The following are the dependencies used in this example:

my-sql.jar containing the .sql files to be executed (this has nothing to do with MySQL, it's just a jar file containing .sql files, nothing more).

How it works

We must specify a GBean in the connector deployment descriptor. This gbean will execute during deployment time. But it needs the following information:

Name of a Datasource to execute on
Path to the .sql file
A test sql query that will act as a boolean. If the query returns zero number of records, the sql statements will be executed (e.g. you may want to create some tables)

xml

<connector xmlns="http://geronimo.apache.org/xml/ns/j2ee/connector-${geronimoSchemaVersion}"> <environment> <moduleId> <groupId>${pom.groupId}</groupId> <artifactId>${pom.artifactId}</artifactId> <version>${version}</version> <type>car</type> </moduleId> <dependencies> <dependency> <groupId>org.apache.geronimo.configs</groupId> <artifactId>system-database</artifactId> <type>car</type> </dependency>  <dependency> <groupId>org.apache.geronimo.sample</groupId> <artifactId>my-sql</artifactId> <version>${monitoringConsoleVersion}</version> <type>jar</type> </dependency> </dependencies> </environment> <resourceadapter> <outbound-resourceadapter>  <connection-definition> <connectionfactory-interface>javax.sql.DataSource</connectionfactory-interface> <connectiondefinition-instance> <name>jdbc/myDS</name> <config-property-setting name="CreateDatabase">true</config-property-setting> <config-property-setting name="DatabaseName">MyDB</config-property-setting> <connectionmanager> <local-transaction/> <single-pool> <max-size>10</max-size> <min-size>0</min-size> <match-one/> </single-pool> </connectionmanager> </connectiondefinition-instance> </connection-definition> </outbound-resourceadapter> </resourceadapter>  <gbean name="MyDSGBean" class="org.apache.geronimo.connector.DatabaseInitializationGBean">  <attribute name="testSQL">SELECT t.tablename FROM SYS.SYSTABLES t WHERE lower(t.tablename)='myTable'</attribute> !-- Path to the SQL file defined in the module: 'my-sql' where the dependency above is referencing --> <attribute name="path">META-INF/database/derby/createTables.sql</attribute> <reference name="DataSource"> <name>jdbc/MyDS</name> </reference> </gbean> </connector>