You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 23 Current »

{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:

  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="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> <!-- 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>
  • No labels