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, say, 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)
| Code Block | ||||
|---|---|---|---|---|
| ||||
<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>
|