You can download this info as a csv - see the Free Gazetteer Data link- and create your own database using -for example- MySQL.
Note: As an alternative you can also use the "ZipcodeInfo" web service powered by FlightAware:
In addition, make sure you also download and install the SoapUI by SmartBear and "Don't be fooled by our name, we are the world's most advanced REST & SOAP testing tool in the world!".
SoapUI is the world's most widely-used open source API testing tool for SOAP and REST APIs.
The MySQL table structure is defined below:
CREATE TABLE `hr`.`zip_us` ( `COUNTRY_CODE` VARCHAR(2) NULL, `ZIP` VARCHAR(20) NOT NULL, `PLACE_NAME` VARCHAR(180) NULL, `ADMIN_NAME1` VARCHAR(100) NULL, `ADMIN_CODE1` VARCHAR(20) NULL, `ADMIN_NAME2` VARCHAR(100) NULL, `ADMIN_CODE2` VARCHAR(20) NULL, `ADMIN_NAME3` VARCHAR(100) NULL, `ADMIN_CODE3` VARCHAR(20) NULL, `LATITUDE` VARCHAR(100) NULL, `LONGITUDE` VARCHAR(100) NULL, `ACCURACY` INT(3) NULL, PRIMARY KEY (`ZIP`));
Once the new "zip_us" table is defined you can later import the data from GeoNames.
Note: The same US specific info is attached here as "export.csv" file [ click for download]. Use this "export.csv" file.
ZIP Client Schema files to be used:
Note: Use the below "mysql-connector-java-5.1.44-bin.jar" only for the SQL Developer tool; no real need to save it into the SOA domain lib folder; it looks we don't need this jar for the below SOA 12c project.
Download the "mysql-connector-java-5.1.44-bin.jar" and place it into the SOA DOMAIN_HOME/lib location:
Note: Use the "Platform Independent (Architecture Independent), ZIP Archive" option and download the mysql-connector-java-5.1.44.zip file.
You can actually use the same jar file into the Oracle SQL Developer to connect to your MySQL database: "Tools" -> "Preferences" -> "Database" -> "Third Party JDBC Drivers" -> "Add Entry...":
We are almost there with the MySQL configuration, we will now define the data source and the DBAdapter Connection Pool settings on the SOA domain. Once this step is done, we will go and create the composite, deploy and test it.
The below steps will provide enough details so in the future you can use other tables, or even a different Database instance. For example, you can always create a web service that will expose the known Oracle Database HR schema.
1. MySQL data source
We will define here the connection details between the WebLogic server and the actual MySQL DB instance.
Go to Domain Structure tree -> leo_domain [domain name] -> Services -> Data Sources:
From here click on New -> Generic Data Source and follow the below steps:
- Name: HRMySQL
- JNDI Name: jdbc/HRMySQL
- Database Type: MySQL
Now click on "Test Configuration" button and check that "Connection test succeeded":
Click on "Next" and select the only server available as target :
Note: Since this is a test/dev environment we have one single server; for more complex environments there are more servers available and each data source can be targeted to individual servers.
All done here - you can now see the new data source available:
2. DBAdapter Connection Pool
Now that we have the MySQL data source and the specific JNDI Name [jdbc/HRMySQL] we can link the new data source with the DBAdapter application.
Go to Domain Structure tree -> leo_domain [domain name] -> Deployments:
First of all, click on "Customize this table" and select "100" rows displayed and enable "Exclude libraries when displaying deployments". Apply:
From here click on "DbAdapter" link and follow the below steps:
Click on the "+" icon to see the current list of Connection Pools:
Click on "New" button.
Select the only available option and "Next".
Below you will add the new "JNDI Name" details and....Hold your horses!
Make sure you do not add here the same "JNDI Name" as you defined above in the data source configuration! This is a new "JNDI Name" and will be linked with future details you add in the JDeveloper when actually working with the specific DBAdapter component.
This is very important. This current configuration is about the DBAdapter itself and you have 2 places where you use it: here in the WLS Admin Console [as the run-time environment] and later in the JDeveloper [as the design -time environment].
However, if you still think there should be a link between this DBAdapter and the new data sources then you are not wrong - we will see this soon 😉
We will use here the below JNDI:
- eis/DB/HRMySQL
Finish and ....Hold your horses since we just did an update on the DBAdapter application and we need to save this in the "Plan.xml" file - you can use a specific folder from a custom location/path like below [use the "Current Location" to navigate to the specific folder]:
The best is not to touch this folder so, in the future, once you need to go with other DBAdapter updates you can use the same "Plan.xml" file and such you will no longer see this step since from now on the DBAdapter will know where to save the new info.
All fine for now as we can see from the below message:
What next ? First we can check the new Connection Pool and more important we can now link this new Connection Pool with the new MySQL data source [this time using the data source specific JNDI details].
Go back to the same location and click on the new Connection Pool link:
You will see different "Property Name"; focus now on the "PlatformClassName" and the "XADataSourceName" values since this is what we need to update:
We need to use the below details; click on the specific "Property Value" , "Paste" the new value and hit Enter:
PlatformClassName = org.eclipse.persistence.platform.database.MySQLPlatform
XADataSourceName = jdbc/HRMySQL
Save... and "Remember to update your deployment to reflect the new plan when you are finished with your changes" :
And this is exactly the next step: update the deployment. Where ?
Till now we did some updates on the DBAdapter application so this is the answer: we need to update the DBAdapter application.
So, now we go back to the first step: Domain Structure tree -> leo_domain [aka domain name] -> Deployments and here select the specific DBAdapter checkbox and "Update":
Make sure/check the new Plan.xml file is based on the new previous defined location [no need to do more if all fine]:
Next and Finish. All done:
Now that we are done with both the new MySQL Data Source and the DBAdapter we can go into the SOA and create the SOA project itself.
3. ZIP_ US SOA Composite Application
Open JDeveloper, "File- > New -> Application" and...
Note: Even if we have more option available, I always like to start with an "Empty Composite".
3.1 DBAdapter
Since we use a Database, then we need a DBAdapter...
We will use here the below JNDI:
- eis/DB/HRMySQL
Now back to the composite....
Note: We don't need all the columns.
Note: We actually implement a select like SELECT zip, place_name ,admin_name1, admin_name2,country_code FROM hr.zip_us WHERE zip = '10002'
So, for the specific client input value we need a custom variable/ parameter.
Note: All fine and clear till now; how about we now test the same above SQL in the SQL Developer ?
SELECT zip, place_name ,admin_name1, admin_name2,country_code
FROM zip_us WHERE zip = '10002'
SQL Error: No database selected.... 😒
We can test it again, now with the specific database name:
SELECT zip, place_name ,admin_name1, admin_name2,country_code
FROM hr.zip_us WHERE zip = '10002'
All fine; it works ! 😉
So, we need to add the same in the DBAdapter:
From here go "Next -> Next" and ....
3.2 BPEL
Note: For this simple scenario we can use both Bpel or Mediator. However, for now we will use the BPEL component.
We expose this Bpel as a SOAP web service; such we need an INPUT and an OUTPUT.
We will use the already defined XML Schemas for the INPUT and the OUTPUT... just import the 2 schemas from your local folder.
We will use the "Sync" option since we will "wait" for the database to response.
An alternative would be to use the "Base on a WSDL" option and even define the INPUT and the OUTPUT manually - this helps if we have no schemas already defined.
For now we only defined the Bpel component and the Bpel "service" - that is the service interface - but for now it does nothing. We need to go inside of this Bpel and implement the flow itself.
Double-click the Bpel component and...
Note: Even if we already created a link between the Bpel and the DB Adapter in the composite, we can now see there is no such link inside the Bpel itself. We only have a "Partner Link" that sounds like a link between the Bpel and the MySQL DB but this is nothing more than a "reference" or just a WSDL. You can double-click this MySQL"Partner Link" and see the details:
To actually invoke this MySQL"Partner Link" [aka WSDL] we will use the "Invoke" activity - as we can see in the previous picture:
For the below 2 steps just go with the default options and create a default variable- both for the INPUT and the OUTPUT:
Why such new variables ? Bpel is all about variables! Bpel will use such variables to keep the data itself.
We defined 2 variables when we created the Bpel component itself and now we defined 2 more variables to keep the data related with the MySQL"Partner Link" invoke step. After some minutes all will make more sense.
If we want to see the current variables we can use the below:
From now on we will focus on the "Assign" activity - both of them that is since we need 2 such "Assign" activity; each will handle different data.
Long story short: we need to define some data [based on a variable] that will be used when invoking the MySQL"Partner Link" and we will need a different data [based on a new variable] that will be used to store the data received from this MySQL"Partner Link".
We will now open each "Assign" activity and just drag and drop a link between what we need to map...
Now all should be clear: we will link the input from the client variable [Bpel component service interface] to the MySQL"Partner Link" input variable. Think about the "WHERE zip = '10002'" example or "WHERE (ZIP = #zipClient)". All done, save it.
We will link here the MySQL"Partner Link" output variable data with the output client variable [Bpel component service interface]. In addition we can now see the below info:
- PLACE_NAME = City
- ADMIN_NAME1 = State
- ADMIN_NAME2 = County
All done and we can now deploy this composite and actually use it.
3.3 Deploy
We will deploy from the PROJECT level:
Since we already have a SOA domain up and running....
Keep the above options even if for now there is no such composite already available on the SOA domain. If you want more "releases" of the same project then make sure you uncheck the "Overwrite" and just use a different "New Revision ID".
Since we already have a Standalone domain up and running....
Select the only available option ...
3.4 Test the composite using the EM Console 12C
Open the EM Console: http://localhost:7601/em
Before everything else, save the WSDL link since we will later use it with SoapUI:
Now back to the EM Console test.
Use a ZIP code from the unitedstateszipcodes.org:
3.5 Test the composite using the SoapUI
Open the SoapUI tool and click on the SOAP option:
Use the WSDL link from the EM Console and paste it to the "Initial WSDL":
No need for more, just click on the "Request":
Use a ZIP code for test and Run:
You can go with a new test and use a different ZIP code:
All done!
You now have a SOAP web service that will return the location main details based on US ZIP codes.
You can use it as it is, you can create a nice UI for it or you can later include it in a new SOA or BPM project - like any other web service.
