I have a problem. I want to read JSON from a URL. Naturally, like anybody else, I googled for some solutions. There were a lot of solutions shared in Stack Overflow and other sites. I tried some of them but I’d like to share the one where I used the Data Pipeline Java library.
1. Tools and Prerequisites
2. Project Setup
Download the Maven project example and place the Data Pipeline jar under /libs. Place the trial license file under /src/main/resources. That’s it. You should be able to run the examples driven by JUnit.
3. Read JSON From a URL
The code below is the simplest way of reading a JSON stream from a URL. Here, we are retrieving stock quotes. This API is undocumented and can change to stop working anytime without notice. Please don’t hit this API more than once a minute, or for 200 tickers, you will get your IP blocked. This is one of the methods of the ReadJsonFromUrl class.
readAJsonStream()
public void readAJsonStream() throws Exception {
String url = "http://www.google.com/finance/info?client=ig&q=msft,orcl,adbe";
BufferedReader input = new BufferedReader(new InputStreamReader(new URL(url).openStream(), "UTF-8"));
// remove preceding slashes from stream
input.readLine();
input.read();
input.read();
DataReader reader = new JsonReader(input)
.addField("symbol", "//array/object/t")
.addField("exchange", "//array/object/e")
.addField("price", "//array/object/l")
.addField("change", "//array/object/c")
.addRecordBreak("//array/object");
reader = new TransformingReader(reader)
.add(new BasicFieldTransformer("price").stringToDouble())
.add(new BasicFieldTransformer("change").stringToDouble());
DataWriter writer = new StreamWriter(System.out);
Job.run(reader, writer);
}
We open the stream using InputStreamReader and pass it to BufferedReader. Somehow Google precedes the stream with a couple of slashes, so we remove them (highlighted). The JsonReader is the muscle behind reading the JSON stream which looks something like this:
JSON Stream
[
{
"id":"358464",
"t":"MSFT",
"e":"NASDAQ",
"l":"31.67",
"l_cur":"31.67",
"s":"2",
"ltt":"4:00PM EDT",
"lt":"Aug 1, 4:00PM EDT",
"c":"-0.17",
"cp":"-0.53",
"ccol":"chr",
"el":"31.69",
"el_cur":"31.69",
"elt":"Aug 1, 7:54PM EDT",
"ec":"+0.02",
"ecp":"0.06",
"eccol":"chg",
"div":"0.23",
"yld":"2.90"
},
{
"id":"419344",
"t":"ORCL",
"e":"NYSE",
"l":"32.75",
"l_cur":"32.75",
"s":"2",
"ltt":"4:00PM EDT",
"lt":"Aug 1, 4:00PM EDT",
"c":"+0.40",
"cp":"1.24",
"ccol":"chg",
"el":"32.70",
"el_cur":"32.70",
"elt":"Aug 1, 7:15PM EDT",
"ec":"-0.05",
"ecp":"-0.15",
"eccol":"chr",
"div":"",
"yld":"1.47"
},
{
"id":"4112",
"t":"ADBE",
"e":"NASDAQ",
"l":"47.70",
"l_cur":"47.70",
"s":"0",
"ltt":"4:00PM EDT",
"lt":"Aug 1, 4:00PM EDT",
"c":"+0.42",
"cp":"0.89",
"ccol":"chg"
}
]
What does JsonReader do? It maps the 't' attribute and its value to 'symbol' which is the stock ticker symbol. It maps the 'e' attribute and its value to 'exchange' which indicates the name of the stock exchange the public company belongs to. It maps 'l' and its value to 'price' which is the last price traded. It maps 'c' and its value to 'change' which is the percentage of the price change since closing. The addRecordBreak breaks each object into records.
The TransformingReader transforms the field name 'price' (which is the data 'l') into a Double as well as the field name 'change'. We transformed it because it was sent as a string and we know it is a number. We will do some calculations on the number.
The StreamWriter outputs the contents read by JsonReader to the console. The last line in the methods executes the read and write. Your console output should be similar to the one below:
Read JSON From a URL Output
-----------------------------------------------
0 - Record (MODIFIED) {
0:[symbol]:STRING=[MSFT]:String
1:[exchange]:STRING=[NASDAQ]:String
2:[price]:DOUBLE=[72.29]:Double
3:[change]:DOUBLE=[-0.5]:Double
}
-----------------------------------------------
1 - Record (MODIFIED) {
0:[symbol]:STRING=[ORCL]:String
1:[exchange]:STRING=[NYSE]:String
2:[price]:DOUBLE=[49.02]:Double
3:[change]:DOUBLE=[-0.07]:Double
}
-----------------------------------------------
2 - Record (MODIFIED) {
0:[symbol]:STRING=[ADBE]:String
1:[exchange]:STRING=[NASDAQ]:String
2:[price]:DOUBLE=[147.8]:Double
3:[change]:DOUBLE=[-0.01]:Double
}
-----------------------------------------------
3 records
4. Read JSON From a URL and Write to CSV
We can also write the JSON stream straight away to a CSV file while we are reading the stream. Just reminding you again, please don’t hit this API more than once a minute, or for 200 tickers, you will get your IP blocked. This is one of the methods of the ReadJsonFromUrl class.
readAJsonStreamAndWriteToCsv()
public void readAJsonStreamAndWriteToCsv() throws Exception {
String url = "http://www.google.com/finance/info?client=ig&q=aapl,mcd";
BufferedReader input = new BufferedReader(new InputStreamReader(new URL(url).openStream(), "UTF-8"));
// remove preceding slashes from stream
input.readLine();
input.read();
input.read();
DataReader reader = new JsonReader(input)
.addField("symbol", "//array/object/t")
.addField("exchange", "//array/object/e")
.addField("price", "//array/object/l")
.addField("change", "//array/object/c")
.addRecordBreak("//array/object");
reader = new TransformingReader(reader).add(new BasicFieldTransformer("price").stringToDouble())
.add(new BasicFieldTransformer("change").stringToDouble());
DataWriter writer = new CSVWriter(new File("Apple-McDonalds.txt"));
Job.run(reader, writer);
}
We are now reading the Apple and McDonald’s ticker symbols. Everything is the same as the readAJsonStream() method except for line number 22 (highlighted). This is now the muscle behind writing our records in CSV format. We only need to provide a filename to the CSVWriter API. So now, instead of printing it out to console, we are writing it to file. The “Apple-McDonalds.txt” contents should look like this:
Apple-McDonalds.txt
symbol,exchange,price,change AAPL,NASDAQ,160.64,"0.56" MCD,NYSE,155.06,"0.14"
The first line in the file are the field names followed by the records.
5. Read JSON From a URL and Transform the Data
As mentioned above, we transformed the 'price' and 'change' in order to perform calculations. The code below does exactly that.
readAJsonStreamAndTransformTheData()
public void readAJsonStreamAndTransformTheData() throws Exception {
String url = "http://www.google.com/finance/info?client=ig&q=aapl,mcd";
BufferedReader input = new BufferedReader(new InputStreamReader(new URL(url).openStream(), "UTF-8"));
// remove preceding slashes from stream
input.readLine();
input.read();
input.read();
DataReader reader = new JsonReader(input).addField("symbol", "//array/object/t")
.addField("exchange", "//array/object/e")
.addField("price", "//array/object/l")
.addField("change", "//array/object/c")
.addRecordBreak("//array/object");
reader = new TransformingReader(reader)
.add(new BasicFieldTransformer("price").stringToDouble())
.add(new BasicFieldTransformer("change").stringToDouble());
reader = new TransformingReader(reader)
.setCondition(new FilterExpression("symbol == 'AAPL' && price < 165.00"))
.add(new SetField("recommendation", "BUY"));
DataWriter writer = new StreamWriter(System.out);
Job.run(reader, writer);
}
The code above is similar to the previous examples shown above. The difference is in line numbers 22 to 24 (highlighted). The highlighted code looks like a Java if statement. What does this do? If you can understand an if condition, I’m sure you’ll get it. If it’s an Apple stock (AAPL ticker symbol) and the price is less than 165.00 then a 'recommendation' field is added with a 'BUY' rating. If you are familiar with technical analysis in stock market trading, this could mean that the Apple stock has reached the support level which triggers a 'Buy' rating. The console output looks like this:
Console Ouput
-----------------------------------------------
0 - Record (MODIFIED) {
0:[symbol]:STRING=[AAPL]:String
1:[exchange]:STRING=[NASDAQ]:String
2:[price]:DOUBLE=[161.06]:Double
3:[change]:DOUBLE=[0.98]:Double
4:[recommendation]:STRING=[BUY]:String
}
-----------------------------------------------
1 - Record (MODIFIED) {
0:[symbol]:STRING=[MCD]:String
1:[exchange]:STRING=[NYSE]:String
2:[price]:DOUBLE=[154.92]:Double
3:[change]:DOUBLE=[0.02]:Double
}
-----------------------------------------------
2 records
Did you notice the difference? The Apple stock record now has a 'recommendation' field. The McDonald’s stock didn’t satisfy the FilterExpression, that’s why it doesn’t have a 'recommendation' field.
6. Read JSON From a URL and Write to Database
In my opinion, this is the best part. We are able to read JSON from a URL and write it to a database as it is being read. Please don’t use the Google API in a production application. It is undocumented and you will get your IP blocked.
The code below uses MySQL but it should work on any JDBC compliant database. Did you notice that some of the field names have changed? The 'exchange' field name is now 'bourse' and 'change' is now 'price_change'.
readAJsonStreamAndWriteToDatabase()
public void readAJsonStreamAndWriteToDatabase() throws Exception {
// connect to the database
String dbUrl = "jdbc:mysql://localhost/stocks?user=root&password=root";
Connection connection = DriverManager.getConnection(dbUrl);
String url = "http://www.google.com/finance/info?client=ig&q=mcd,aapl";
BufferedReader input = new BufferedReader(new InputStreamReader(new URL(url).openStream(), "UTF-8"));
// remove preceding slashes from stream
input.readLine();
input.read();
input.read();
DataReader reader = new JsonReader(input).addField("symbol", "//array/object/t")
.addField("bourse", "//array/object/e")
.addField("price", "//array/object/l")
.addField("price_change", "//array/object/c")
.addRecordBreak("//array/object");
reader = new TransformingReader(reader)
.add(new BasicFieldTransformer("price").stringToDouble())
.add(new BasicFieldTransformer("price_change").stringToDouble());
reader = new TransformingReader(reader)
.setCondition(new FilterExpression("symbol != 'AAPL'"))
.add(new SetField("recommendation", "WAIT"));
reader = new TransformingReader(reader)
.setCondition(new FilterExpression("symbol == 'AAPL' && price < 165.00"))
.add(new SetField("recommendation", "BUY"));
DataWriter writer = new JdbcWriter(connection, "usa")
.setAutoCloseConnection(true);
Job.run(reader, writer);
}
First off, we need to create the database schema and the table. Here’s the SQL statement for creating the table:
SQL
CREATE TABLE `stocks`.`usa` ( `symbol` VARCHAR(5) NOT NULL COMMENT '', `bourse` VARCHAR(10) NOT NULL COMMENT '', `price` DECIMAL(10,2) NOT NULL COMMENT '', `price_change` DECIMAL(10,2) NOT NULL COMMENT '', `recommendation` VARCHAR(5) NULL COMMENT '', PRIMARY KEY (`symbol`) COMMENT '');
What did we add to our code now? It’s highlighted. We need a connection to the database. We have a new recommendation which is 'WAIT'. The JdbcWriter API takes the connection and the table name "usa" as parameters. Lastly, we set it to automatically close after writing is finished. Here’s the output:
MySQL Output
mysql> select * from usa; +--------+--------+--------+--------------+----------------+ | symbol | bourse | price | price_change | recommendation | +--------+--------+--------+--------------+----------------+ | AAPL | NASDAQ | 161.06 | 0.98 | BUY | | MCD | NYSE | 154.92 | 0.02 | WAIT | +--------+--------+--------+--------------+----------------+ 2 rows in set (0.00 sec)
7. Summary
The Data Pipeline library does the heavy lifting for you. Reading a JSON stream is simplified and you can write the contents into many formats. It’s truly a nice tool to have in your arsenal.
8. Download the Source Code
This is an example of a Read JSON from a URL using Data Pipeline.
You can download the source code of this example here: read-json-from-url-data-pipeline.zip.
