Time for action – using the SQL gateway

We will now run the Chapter6 sample application that demonstrates the usage of the SQL gateway:

  1. In JBoss Developer Studio, stop the runtime if it is already running.
  2. Open the deployment.xml file and uncomment the following highlighted section:
    <jbossesb-deployment>
  <depends>
    jboss.esb.book.samples.destination:service=Queue,
    name=chapter6_Request_esb
  </depends>
  <depends>
    jboss.esb.book.samples.destination:service=Queue,
    name=chapter6_Request_esb_reply
  </depends>
  <depends>
    jboss.esb.book.samples.destination:service=Queue,
    name=chapter6_Request_gw
  </depends>
  <depends>
    jboss.esb.book.samples.destination:service=Queue,
    name=chapter6_Request_gw_reply
  </depends>
  <depends>
    jboss.esb.book.samples.database:
    service=Chapter6SqlDatabaseInitializer
  </depends>
</jbossesb-deployment>
  3. Rename thechapter6-ds.xml.bak file to chapter6-ds.xml. Also, rename the jbossesb-service.xml.bak file to jbossesb-service.xml.
  4. Start the server runtime.
  5. Open the jboss-esb.xml file in Source mode.
  6. Append the following listener definition to the <listeners> tag:
    <listeners>
  <jms-listener busidref="chapter6ESBChannel"
                name="Chapter6ESBListener"/>
  <jms-listener busidref="chapter6GwChannel" is-gateway="true"
                name="Chapter6GwListener"/>
  <sql-listener busidref="chapter6SQLChannel" is-gateway="true"
                name="Chapter6SQLGwListener"/>
</listeners>
  7. Add the following provider to the providers list:
    <sql-provider name="SQLprovider" 
              url="jdbc:hsqldb:hsql://localhost:1704"
              driver="org.hsqldb.jdbcDriver"
              username="sa" password="">
  <sql-bus busid="chapter6SQLChannel">
    <sql-message-filter tablename="GATEWAY_TABLE"
                        order-by="GWDATA"
                        message-column="message"
                        message-id-column="UNIQUE_ID"
                        status-column="GWSTATUS"/>
  </sql-bus>
</sql-provider>
  8. Add the following notifier action:
    <action name="notificationAction"
        class="org.jboss.soa.esb.actions.Notifier">
  <property name="okMethod" value="notifyOK"/>
  <property name="notification-details"> 
    <NotificationList type="ok">
      <target class="NotifySqlTable"
              driver-class="org.hsqldb.jdbcDriver"
              connection-url="jdbc:hsqldb:hsql://localhost:1704"
              user-name="sa"
              password=""
              table="gateway_table"
              dataColumn="gwdata">
        <column name="gwstatus" value="R"/>
      </target>
    </NotificationList>
  </property>
</action>
  9. Click the Save button and the modified application should now be deployed in the server.
  10. Select the src folder and expand it till the SendSQLMessage.java file is displayed in the tree. Now click Run | Run As | Java Application.
  11. The console will display the output as shown:
    SQL Gateway says Hello!

What just happened?

We sent a message via a database table using the SQL gateway listener and our response was received to the same table with the help of a notifier. Have a look at the client code. Here is part of the listing for your understanding:

public static void main(String[] args) throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:1704","sa", "");
    Statement stmt = connection.createStatement();
    stmt.executeUpdate("insert into gateway_table(gwdata, gwstatus) values('Hello SQL Gateway!','P')");
    stmt.close();
    connection.commit();
    connection.close();
    Thread.sleep(3000);
    connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:1704", "sa", "");
    stmt = connection.createStatement();
    ResultSet results = stmt.executeQuery("select gwdata from gateway_table where gwstatus like 'R%'");
    while (results.next()) {
        System.out.println(results.getString("gwdata"));
    }
    stmt.executeUpdate("delete from gateway_table where gwstatus like 'R%'");
    connection.commit();
    connection.close();
}

There are a few more files that were used in this example, such as jbossesb-service.xml, create.sql, and chapter6-ds.xml. The service in jbossesb-service.xml creates the SQL table during deployment while the chapter6-ds.xml creates the datasource used to reference the database where the table will reside.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.154.208