Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

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:

  1. Name of a Datasource to execute on
  2. Path to the .sql file
  3. 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="${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> <!-- SQL files --> <dependency> <groupId>org.apache.geronimo.sample</groupId> <artifactId>my-sql</artifactId> <version>${monitoringConsoleVersion}</version> <type>jar</type> </dependency> </dependencies> </environment> <resourceadapter> <outbound-resourceadapter> <!-- DB Pool --> <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> <!-- These two GBeans will create the tables for the database automatically --> <gbean name="MyDSGBean" class="org.apache.geronimo.connector.DatabaseInitializationGBean"> <!-- Execute the createTable.sql SQL statements if the following query does NOT return any records --> <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>