Travel Agency Demo

Retrieving Data with JDBC

Christina Lin

Environment Setup

Install H2 database
Copy travelagency.mv.db to ~/h2 folder in your machine.

Setup Fabric authentication
Go to ~/.m2/settings.xml
Unser servers, add the following id/pwd information
    <server>
      <id>fabric8.upload.repo</id>
      <username>admin</username>
      <password>admin</password>
    </server>

Setup JBoss Fuse

• Unzip you JBoss Fuse, (please use the one provided)
• Startup JBoss Fuse, by going to INSTALL_PATH/jboss-fuse-6.1.1.redhat-412/bin and start it
  • Linux
    • ./fuse
  • Windows
    • fuse.bat
• In console create Fuse Fabric by typing 
  • fabric:create --wait-for-provisioning
• Stop your fuse by typing exit in console

This application lists all the available hotels to the client with condition like the location of the hotels. After retrieving all the possible hotel data from database, it'll either randomly pick one of them, or create a default hotel. 

Create a Fuse project,

Choose the blueprint archetype, 
GroupID: org.blogdemo.travelagency
ArtifactID:promotionhotel

Open up /example/src/main/resources/OSGI-INF/blueprint/blueprint.xml and remove the route, so you are left with a blank canvas. 

Remove the hellobean registry in the xml file. And delete all the java files.

<bean id="helloBean" class="com.mycompany.camel.blueprint.HelloBean">         
<property name="say" value="Hi from Camel"/> 
</bean>

Remove everything under

src/java/* and src/test/*

Now we have a brand new project to work with. First we want to make sure we have all the dependency needed in pom.xml.

Add the following dependency in.

<!-- Database -->
<dependency>
  <groupId>org.apache.camel</groupId>
  <artifactId>camel-jdbc</artifactId>
  <version>2.12.0.redhat-610379</version>
</dependency>
<dependency>
  <groupId>commons-dbcp</groupId>
  <artifactId>commons-dbcp</artifactId>
  <version>1.4</version>
</dependency>    

<!--Messaging from and to AMQ -->
<dependency>
  <groupId>org.apache.activemq</groupId>
  <artifactId>activemq-camel</artifactId>
  <version>5.9.0.redhat-610379</version>
</dependency>

<!-- Message type conversion -->
<dependency>
  <groupId>org.apache.camel</groupId>
  <artifactId>camel-jackson</artifactId>
  <version>2.12.0.redhat-610379</version>

</dependency>

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.4.181</version>
</dependency>

Go back Camel route file, blueprint.xml, add datasource setting

<bean id="dataSourceTravelAgency" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="org.h2.Driver"/>
  <property name="url" value="jdbc:h2:file:~/h2/travelagency;AUTO_SERVER=TRUE" />
  <property name="username" value="sa"/>
  <property name="password" value=""/>
</bean> 

Add activemq setting to connect to our messaging queue.

<bean id="activemq" class="org.apache.activemq.camel.component.ActiveMQComponent">
  <property name="brokerURL" value="tcp://localhost:61616"/>    
  <property name="userName" value="admin"/>
  <property name="password" value="admin"/>
</bean>

Create route to

1. Read request data from queue
2. Convert data from Json to a Map object
3. Set data to header later will use these in JDBC query

Add SQL query and call JDBC endpoint to retrieve data

(When you save the route, it'll become a straight line)

• AMQ Endpoint
  • Uri: activemq:queue:requestHotel
• Log Endpoint
  • Uri: ${body}
• Unmarshal
  • Tab: json
  • Library: Jackson
  • Unmarshal Type Name: java.util.HashMap
• setHeader
  • HeaderName: requestCity
  • Language: simple
  • Expression: ${body[targetCity]}
• setHeader
  • HeaderName: requestStartDate
  • Language: simple
  • Expression ${body[startDate]}

• setHeader

  • HeaderName: requestEndDate

  • Language: simple

  • Expression: ${body[endDate]}

• setBody

  • Language: simple

  • Expression: SELECT * from avaliablehotels where hotelcity='${header.requestCity}

• Log Endpoint

  • Expression: requestCity ${headers.requestCity} requestStartDate ${headers.requestStartDate}

• JDBC Endpoint

  • Uri: jdbc:dataSourceTravelAgency

We have the data returned in a Hashmap format, but it’s better to map them into a POJO, so create a custom bean to do this.

Create the POJO java class
Package: org.blogdemo.travelagency.promotionhotel
Class name: ResortInfo

package org.blogdemo.travelagency.promotionhotel;

import java.io.Serializable;
import java.math.BigDecimal;

public class ResortInfo implements Serializable{

  private static final long serialVersionUID =  
   6313854994010205L;
  int hotelId;
  String hotelName;
  BigDecimal ratePerPerson;
  String hotelCity;
  String availableFrom;
  String availableTo;
  public int getHotelId() {return hotelId; }
  public void setHotelId(int hotelId) {
    this.hotelId = hotelId; 
  }

  public String getHotelName() {return hotelName;}
  public void setHotelName(String hotelName) { 

    this.hotelName = hotelName;}
  public BigDecimal getRatePerPerson() {return   ratePerPerson;}
  public void setRatePerPerson(BigDecimal ratePerPerson){
    this.ratePerPerson = ratePerPerson; }
  public String getHotelCity() {return hotelCity;}
  public void setHotelCity(String hotelCity) {
    this.hotelCity = hotelCity;}
  public String getAvailableFrom() {return availableFrom;}
  public void setAvailableFrom(String availableFrom){
    this.availableFrom = availableFrom;}
  public String getAvailableTo() {return availableTo;}
  public void setAvailableTo(String availableTo){ 
    this.availableTo = availableTo;}
}

Create a java class
Package: org.blogdemo.travelagency.promotionhotel
Class name: ResortDataConvertor

package org.blogdemo.travelagency.promotionhotel;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
public class ResortDataConvertor {
  public List<ResortInfo> processResultSet(List<Map<String, Object>> resultset){
    List<ResortInfo> avaliableResorts = new ArrayList<ResortInfo>();
    for(Map<String, Object> obj:resultset){
      ResortInfo resortInfo = new ResortInfo();
      resortInfo.setHotelId((Integer)obj.get("HOTELID"));
      resortInfo.setHotelName((String)obj.get("HOTELNAME"));
      resortInfo.setHotelCity((String)obj.get("HOTELCITY"));
      resortInfo.setAvailableFrom(new SimpleDateFormat("MM-dd-yyyy HH:mm:ss").format(obj.get("AVAILABLEFROM")));
      resortInfo.setAvailableTo(new SimpleDateFormat("MM-dd-yyyy HH:mm:ss").format(obj.get("AVAILABLETO")));
      resortInfo.setRatePerPerson((BigDecimal)obj.get("RATEPERPERSON"));
      avaliableResorts.add(resortInfo);}
   return avaliableResorts;
  }   }

package org.blogdemo.travelagency.promotionhotel;

import java.math.BigDecimal;
import java.util.*;
import org.apache.camel.Exchange;
import org.apache.camel.Processor;

public class RandomHotelBean implements Processor{
   @Override
  public void process(Exchange exchange) throws Exception {
    List<ResortInfo> resorts = (List<ResortInfo>)exchange.getIn().getBody();
    ResortInfo resort;    

Because of the restriction of BPM Suite Process, we can only return one hotel at a time, create the java class that randomly choose one hotel, and also create a default one if no criteria is matched in database.
 

Package: org.blogdemo.travelagency.promotionhotel 
Class name: ResortDataConvertor 

 
    if(resorts == null || resorts.size() == 0){
      resort = new ResortInfo();
      resort.setHotelId(201);
      resort.setHotelName("The Grand Default Hotel");
      resort.setHotelCity((String)exchange.getIn().getHeader("requestCity"));
      resort.setAvailableFrom((String)exchange.getIn().getHeader("requestStartDate"));
      resort.setAvailableTo((String)exchange.getIn().getHeader("requestEndDate"));
      resort.setRatePerPerson(new BigDecimal("109.99"));
    }else{
      Random rand = new Random();
      int  n = rand.nextInt(resorts.size());
      resort = resorts.get(n);
    }
    exchange.getOut().setBody(resort);
  }
  
}

Registry the two beans we created in camel context (in your blueprint file)

<bean id="dataProcessor" class="org.blogdemo.travelagency.promotionhotel.ResortDataConvertor" />
<bean id="randomHotel" class="org.blogdemo.travelagency.promotionhotel.RandomHotelBean" />

We can start adding the processes that deal with the JDBC result returned.
Add the last three steps in the end of your route. 

• Bean
  • Bean Name: dataProcessor
  • Method: processResultSet
• Bean:
  • Bean Name: randomHotel
• Marshal
  • Tab: json
  • Library: Jackson
• Log Endpoint
  • Message: ${body}

Creating Test code

Under src/test/java create 2 classes, one is the helper class to setup messaging queue and the other is the actual testing code.

package org.blogdemo.travelagency.promtionhotel;

import java.util.concurrent.atomic.AtomicInteger;

import javax.jms.ConnectionFactory;

import org.apache.activemq.ActiveMQConnectionFactory;
import org.apache.activemq.pool.PooledConnectionFactory;
public final class CamelJmsTestHelper {

    private static AtomicInteger counter = new AtomicInteger(0);

    private CamelJmsTestHelper() {
    }

    public static ConnectionFactory createConnectionFactory() {
        return createConnectionFactory(null);
    }

Package: org.blogdemo.travelagency.promotionhotel
Class name: CamelJmsTestHelper

public static ConnectionFactory createConnectionFactory(String options) {
        int id = counter.incrementAndGet();
        String url = "vm://test-broker-" + id + "?broker.persistent=false&broker.useJmx=false";
        if (options != null) {
            url = url + "&" + options;
        }
        ActiveMQConnectionFactory connectionFactory = new ActiveMQConnectionFactory(url);
        connectionFactory.setCopyMessageOnSend(false);
        connectionFactory.setOptimizeAcknowledge(true);
        connectionFactory.setOptimizedMessageDispatch(true);
        connectionFactory.setUseAsyncSend(false);

        connectionFactory.setAlwaysSessionAsync(false);
        PooledConnectionFactory pooled = new PooledConnectionFactory(connectionFactory);
        pooled.setMaxConnections(8);
        return pooled;
    }   

 }

Package: org.blogdemo.travelagency.promotionhotel
Class name: RouteTest

package org.blogdemo.travelagency.promtionhotel;

import javax.jms.ConnectionFactory;
import static org.apache.camel.component.jms.JmsComponent.jmsComponentAutoAcknowledge;

import org.apache.camel.CamelContext;
import org.apache.camel.test.blueprint.CamelBlueprintTestSupport;
import org.junit.Test;

public class RouteTest extends CamelBlueprintTestSupport {
    
     protected String componentName = "activemq";
    
    @Override
    protected String getBlueprintDescriptor() {
        return "/OSGI-INF/blueprint/blueprint.xml";
    }

protected CamelContext createCamelContext() throws Exception {
        CamelContext camelContext = super.createCamelContext();

        ConnectionFactory connectionFactory = CamelJmsTestHelper.createConnectionFactory();
        camelContext.addComponent(componentName, jmsComponentAutoAcknowledge(connectionFactory));

        return camelContext;
    }

    @Test
    public void testRoute() throws Exception {
        String out = template.requestBody("activemq:queue:requestHotel", "{\"startCity\":\"London\",\"startDate\":\"2015-01-01\",\"endDate\":\"2015-03-01\"}", String.class);
        
        assertEquals("{\"hotelId\":201,\"hotelName\":\"The Grand Default Hotel\",\"ratePerPerson\":109.99,\"hotelCity\":null,\"availableFrom\":\"2015-01-01\",\"availableTo\":\"2015-03-01\"}", out);
        
    }

}

Right click on the RouteTest.java and select JUnit Test

You should see the test has passed in the Junit Test view

Lab Complete!