Get Json message from REST Openexchangerates service

JSON is a widely used messaging format in REST/SOAP web services. The internet offers numerous public API RESTful services that provide data in JSON format. In this exercise, we will explore how to retrieve and parse JSON messages.

Task: Openexchangerates is an open RESTful web service that provides currency quotes against the US dollar. We will fetch the data, parse it, and store it in a database. Our goal is to determine which country had the most stable exchange rate against the US dollar over the past 18 years. Additionally, we will examine how much the Russian currency has fluctuated in relation to the US dollar.


Jackson Json API RESTFull

To accomplish the task, the following steps need to be performed:

  1. Create a database: Set up a database where you will store the retrieved data. Ensure that the necessary tables and columns are created to store the relevant information.
  2. Create a REST client: Implement a REST client using a programming language of your choice. This client will be responsible for making requests to the Openexchangerates API and fetching the currency data in JSON format.
  3. Prepare test data: Identify the specific data points you want to collect from the API. Define the structure of the data you will be storing in the database and prepare the necessary variables or objects to hold the retrieved data.
  4. Loop through the URL links: Read the URL links from a text file or any other source where they are stored. Use a loop to iterate through the links one by one.
  5. Retrieve and parse JSON data: For each URL link, make a request to the Openexchangerates API using the REST client. Receive the JSON response and parse it to extract the required data.
  6. Save data into the database: Once you have parsed the JSON data, save the relevant information into the database. Ensure that you map the data to the appropriate columns in the database tables.

Link to API Introduction REST web service.

Get request: /api/historical/2001-02-16.json?app_id=YOUR_APP_ID
We want to get data for each month for the last 18 years.
Need to loop through the parameter: date.
In response, the service returns a message in Json format. Create POJO class.

  "disclaimer": "Usage subject to terms:",
  "license": "",
  "timestamp": 982342800,
  "base": "USD",
  "rates": {
    "AED": 3.67246,
    "ALL": 144.529793,
    "ANG": 1.79,
    "ARS": 1.000567

Full script data on GitHub.

CREATE TABLE exchangerates(
id serial PRIMARY KEY,

I use libraries to describe POJO lombok and jackson 2

public class ClassRates {
    private String disclaimer;
    private String license;
    private int timestamp;
    private String base;
    private Rates rates;
public class Rates{
    private double AED;
    private double ALL;

For saving data use Spring Jdbc and NamedParameterJdbcTemplate.


Call REST service with Spring RestTemplate.

RestTemplate restTemplate = new RestTemplate();
String fooResourceUrl
		= ""+line+".json?app_id=" +APIKEY;
ClassRates forObject = restTemplate.getForObject(fooResourceUrl, ClassRates.class);

Unmarshalling response and create Map, insert into db.

// Creating map with all required params
Map<string, object=""> paramMap = new HashMap<string, object="">();
paramMap.put("id", i);
paramMap.put("data_cur", line);
nqu.update(INSERT_QUERY, paramMap);

We have a good table with 226 records for further analytics.

Create graph MS Excel.

Link to full project on GitHub:
Github USDHistoricalRateProject

Related Posts