We will now run the Chapter6
sample application that demonstrates the usage of the SQL gateway:
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>
chapter6-ds.xml.bak
file to chapter6-ds.xml
. Also, rename the jbossesb-service.xml.bak
file to jbossesb-service.xml
.jboss-esb.xml
file in Source mode.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>
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>
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>
src
folder and expand it till the SendSQLMessage.java
file is displayed in the tree. Now click Run | Run As | Java Application.SQL Gateway says Hello!
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.