geronimo-scm mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From conflue...@apache.org
Subject [CONF] Apache Geronimo v3.0 > Executing SQL statements at deployment time
Date Fri, 01 Jul 2011 11:33:02 GMT
<html>
<head>
    <base href="https://cwiki.apache.org/confluence">
            <link rel="stylesheet" href="/confluence/s/2042/9/4/_/styles/combined.css?spaceKey=GMOxDOC30&amp;forWysiwyg=true"
type="text/css">
    </head>
<body style="background: white;" bgcolor="white" class="email-body">
<div id="pageContent">
<div id="notificationFormat">
<div class="wiki-content">
<div class="email">
    <h2><a href="https://cwiki.apache.org/confluence/display/GMOxDOC30/Executing+SQL+statements+at+deployment+time">Executing
SQL statements at deployment time</a></h2>
    <h4>Page <b>edited</b> by             <a href="https://cwiki.apache.org/confluence/display/~chirunhua@gmail.com">Runhua
Chi</a>
    </h4>
        <br/>
                         <h4>Changes (2)</h4>
                                 
    
<div id="page-diffs">
                    <table class="diff" cellpadding="0" cellspacing="0">
    
            <tr><td class="diff-snipped" >...<br></td></tr>
            <tr><td class="diff-unchanged" >h1. How to execute SQL statements
during deployment <br> <br></td></tr>
            <tr><td class="diff-changed-lines" ><span class="diff-deleted-words"style="color:#999;background-color:#fdd;text-decoration:line-through;">There
may be times where you will want</span> <span class="diff-added-words"style="background-color:
#dfd;">{excerpt}The article is about how</span> to execute some cleanup or initialization
SQL statements during the deployment time of, for example, a <span class="diff-changed-words">data<span
class="diff-added-chars"style="background-color: #dfd;"> </span>source.<span class="diff-added-chars"style="background-color:
#dfd;">{excerpt}</span></span> Below is an example of how this can be done.
<br></td></tr>
            <tr><td class="diff-unchanged" > <br>The following are the dependencies
used in this example: <br></td></tr>
            <tr><td class="diff-snipped" >...<br></td></tr>
            <tr><td class="diff-unchanged" > <br>     &lt;!-- These
two GBeans will create the tables for the database automatically --&gt; <br></td></tr>
            <tr><td class="diff-changed-lines" >&lt;gbean name=&quot;MyDSGBean&quot;
<span class="diff-changed-words">class=&quot;org.apache.geronimo.connector.<span
class="diff-added-chars"style="background-color: #dfd;">wrapper.</span>DatabaseInitializationGBean&quot;&gt;</span>
<br></td></tr>
            <tr><td class="diff-unchanged" >	 &lt;!-- Execute the createTable.sql
SQL statements if the following query does NOT return any records --&gt; <br>  
      &lt;attribute name=&quot;testSQL&quot;&gt;SELECT t.tablename FROM SYS.SYSTABLES
t WHERE lower(t.tablename)=&#39;myTable&#39;&lt;/attribute&gt; <br></td></tr>
            <tr><td class="diff-snipped" >...<br></td></tr>
    
            </table>
    </div>                            <h4>Full Content</h4>
                    <div class="notificationGreySide">
        <style type='text/css'>/*<![CDATA[*/
table.ScrollbarTable  {border: none;padding: 3px;width: 100%;padding: 3px;margin: 0px;background-color:
#f0f0f0}
table.ScrollbarTable td.ScrollbarPrevIcon {text-align: center;width: 16px;border: none;}
table.ScrollbarTable td.ScrollbarPrevName {text-align: left;border: none;}
table.ScrollbarTable td.ScrollbarParent {text-align: center;border: none;}
table.ScrollbarTable td.ScrollbarNextName {text-align: right;border: none;}
table.ScrollbarTable td.ScrollbarNextIcon {text-align: center;width: 16px;border: none;}

/*]]>*/</style><div class="Scrollbar"><table class='ScrollbarTable'><tr><td
class='ScrollbarPrevIcon'><a href="/confluence/display/GMOxDOC30/Configuring+a+MySQL+datasource"><img
border='0' align='middle' src='/confluence/images/icons/back_16.gif' width='16' height='16'></a></td><td
width='33%' class='ScrollbarPrevName'><a href="/confluence/display/GMOxDOC30/Configuring+a+MySQL+datasource">Configuring
a MySQL datasource</a>&nbsp;</td><td width='33%' class='ScrollbarParent'><sup><a
href="/confluence/display/GMOxDOC30/Configuring+datasources"><img border='0' align='middle'
src='/confluence/images/icons/up_16.gif' width='8' height='8'></a></sup><a
href="/confluence/display/GMOxDOC30/Configuring+datasources">Configuring datasources</a></td><td
width='33%' class='ScrollbarNextName'>&nbsp;<a href="/confluence/display/GMOxDOC30/Import+database+pools+from+JBoss+4">Import
database pools from JBoss 4</a></td><td class='ScrollbarNextIcon'><a
href="/confluence/display/GMOxDOC30/Import+database+pools+from+JBoss+4"><img border='0'
align='middle' src='/confluence/images/icons/forwd_16.gif' width='16' height='16'></a></td></tr></table></div>
<h1><a name="ExecutingSQLstatementsatdeploymenttime-HowtoexecuteSQLstatementsduringdeployment"></a>How
to execute SQL statements during deployment</h1>

<p>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. </p>

<p>The following are the dependencies used in this example:</p>
<ul>
	<li><tt>my-sql.jar</tt> 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).</li>
</ul>


<h2><a name="ExecutingSQLstatementsatdeploymenttime-Howitworks"></a>How
it works</h2>

<p>We must specify a GBean in the connector deployment descriptor. This gbean will execute
during deployment time. But it needs the following information:</p>
<ol>
	<li>Name of a Datasource to execute on</li>
	<li>Path to the .sql file</li>
	<li>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)</li>
</ol>


<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-xml">
<span class="code-tag">&lt;connector xmlns=<span class="code-quote">"http://geronimo.apache.org/xml/ns/j2ee/connector-${geronimoSchemaVersion}"</span>&gt;</span>
    <span class="code-tag">&lt;environment&gt;</span>
        <span class="code-tag">&lt;moduleId&gt;</span>
            <span class="code-tag">&lt;groupId&gt;</span>${pom.groupId}<span
class="code-tag">&lt;/groupId&gt;</span>
            <span class="code-tag">&lt;artifactId&gt;</span>${pom.artifactId}<span
class="code-tag">&lt;/artifactId&gt;</span>
            <span class="code-tag">&lt;version&gt;</span>${version}<span
class="code-tag">&lt;/version&gt;</span>
            <span class="code-tag">&lt;type&gt;</span>car<span class="code-tag">&lt;/type&gt;</span>
        <span class="code-tag">&lt;/moduleId&gt;</span>
        <span class="code-tag">&lt;dependencies&gt;</span>
            <span class="code-tag">&lt;dependency&gt;</span>
                <span class="code-tag">&lt;groupId&gt;</span>org.apache.geronimo.configs<span
class="code-tag">&lt;/groupId&gt;</span>
                <span class="code-tag">&lt;artifactId&gt;</span>system-database<span
class="code-tag">&lt;/artifactId&gt;</span>
                <span class="code-tag">&lt;type&gt;</span>car<span
class="code-tag">&lt;/type&gt;</span>
            <span class="code-tag">&lt;/dependency&gt;</span>
            <span class="code-tag"><span class="code-comment">&lt;!-- SQL
files --&gt;</span></span>
            <span class="code-tag">&lt;dependency&gt;</span>
                <span class="code-tag">&lt;groupId&gt;</span>org.apache.geronimo.sample<span
class="code-tag">&lt;/groupId&gt;</span>
                <span class="code-tag">&lt;artifactId&gt;</span>my-sql<span
class="code-tag">&lt;/artifactId&gt;</span>
                <span class="code-tag">&lt;version&gt;</span>${monitoringConsoleVersion}<span
class="code-tag">&lt;/version&gt;</span>
                <span class="code-tag">&lt;type&gt;</span>jar<span
class="code-tag">&lt;/type&gt;</span>
            <span class="code-tag">&lt;/dependency&gt;</span>
        <span class="code-tag">&lt;/dependencies&gt;</span>
    <span class="code-tag">&lt;/environment&gt;</span>

    <span class="code-tag">&lt;resourceadapter&gt;</span>
        <span class="code-tag">&lt;outbound-resourceadapter&gt;</span>
            <span class="code-tag"><span class="code-comment">&lt;!-- DB Pool
--&gt;</span></span>
            <span class="code-tag">&lt;connection-definition&gt;</span>
                <span class="code-tag">&lt;connectionfactory-interface&gt;</span>javax.sql.DataSource<span
class="code-tag">&lt;/connectionfactory-interface&gt;</span>
                <span class="code-tag">&lt;connectiondefinition-instance&gt;</span>
                    <span class="code-tag">&lt;name&gt;</span>jdbc/myDS<span
class="code-tag">&lt;/name&gt;</span>
                    <span class="code-tag">&lt;config-property-setting name=<span
class="code-quote">"CreateDatabase"</span>&gt;</span>true<span class="code-tag">&lt;/config-property-setting&gt;</span>
                    <span class="code-tag">&lt;config-property-setting name=<span
class="code-quote">"DatabaseName"</span>&gt;</span>MyDB<span class="code-tag">&lt;/config-property-setting&gt;</span>
                    <span class="code-tag">&lt;connectionmanager&gt;</span>
                        <span class="code-tag">&lt;local-transaction/&gt;</span>
                        <span class="code-tag">&lt;single-pool&gt;</span>
                            <span class="code-tag">&lt;max-size&gt;</span>10<span
class="code-tag">&lt;/max-size&gt;</span>
                            <span class="code-tag">&lt;min-size&gt;</span>0<span
class="code-tag">&lt;/min-size&gt;</span>
                            <span class="code-tag">&lt;match-one/&gt;</span>
                        <span class="code-tag">&lt;/single-pool&gt;</span>
                    <span class="code-tag">&lt;/connectionmanager&gt;</span>
                <span class="code-tag">&lt;/connectiondefinition-instance&gt;</span>
            <span class="code-tag">&lt;/connection-definition&gt;</span>
         <span class="code-tag">&lt;/outbound-resourceadapter&gt;</span>
     <span class="code-tag">&lt;/resourceadapter&gt;</span>

     <span class="code-tag"><span class="code-comment">&lt;!-- These two GBeans
will create the tables for the database automatically --&gt;</span></span>
     <span class="code-tag">&lt;gbean name=<span class="code-quote">"MyDSGBean"</span>
class=<span class="code-quote">"org.apache.geronimo.connector.wrapper.DatabaseInitializationGBean"</span>&gt;</span>
	 <span class="code-tag"><span class="code-comment">&lt;!-- Execute the createTable.sql
SQL statements if the following query does NOT return any records --&gt;</span></span>
         <span class="code-tag">&lt;attribute name=<span class="code-quote">"testSQL"</span>&gt;</span>SELECT
t.tablename FROM SYS.SYSTABLES t WHERE lower(t.tablename)='myTable'<span class="code-tag">&lt;/attribute&gt;</span>
	 !-- Path to the SQL file defined in the module: 'my-sql' where the dependency above is referencing
--&gt;
         <span class="code-tag">&lt;attribute name=<span class="code-quote">"path"</span>&gt;</span>META-INF/database/derby/createTables.sql<span
class="code-tag">&lt;/attribute&gt;</span>
         <span class="code-tag">&lt;reference name=<span class="code-quote">"DataSource"</span>&gt;</span>
             <span class="code-tag">&lt;name&gt;</span>jdbc/MyDS<span
class="code-tag">&lt;/name&gt;</span>
         <span class="code-tag">&lt;/reference&gt;</span>
     <span class="code-tag">&lt;/gbean&gt;</span>
 <span class="code-tag">&lt;/connector&gt;</span>
</pre>
</div></div>
    </div>
        <div id="commentsSection" class="wiki-content pageSection">
        <div style="float: right;">
            <a href="https://cwiki.apache.org/confluence/users/viewnotifications.action"
class="grey">Change Notification Preferences</a>
        </div>
        <a href="https://cwiki.apache.org/confluence/display/GMOxDOC30/Executing+SQL+statements+at+deployment+time">View
Online</a>
        |
        <a href="https://cwiki.apache.org/confluence/pages/diffpagesbyversion.action?pageId=20645390&revisedVersion=2&originalVersion=1">View
Changes</a>
                |
        <a href="https://cwiki.apache.org/confluence/display/GMOxDOC30/Executing+SQL+statements+at+deployment+time?showComments=true&amp;showCommentArea=true#addcomment">Add
Comment</a>
            </div>
</div>
</div>
</div>
</div>
</body>
</html>

Mime
View raw message