Child pages
  • Executing SQL statements at deployment time
Skip to end of metadata
Go to start of metadata

How to execute SQL statements during deployment


The article is about how to execute some cleanup or initialization SQL statements during the deployment time of, for example, a data source.

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.wrapper.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>
  • No labels