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

Compare with Current View Page History

« Previous Version 9 Next »

Configuring a MySQL datasource Configuring datasources Import database pools from JBoss 4

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)
<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.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