Vanilla Sky

The Streams API introduced in Java SE 8 is a set of operations which can be applied on collections to express complex queries to process data.

We come across collections in programs often. Such as an array of products for an e-commerce website or a set of transactions for a customer of a bank or a file containing many lines of text.

Consider the example: A user of an online bookstore wants to see a list of all second hand ‘Harry Potter’ books sorted by prices from cheapest onwards available at the store.

To answer the query, following Stream operations are required:

1. Filter all available books by the title ‘Harry Potter’
2. Sort the resulting stream from point 1 above by prices in ascending order.
3. Collect the resulting filtered and sorted stream into a collection.

We can answer the above business requirement with the following SQL query:

select title, price
from Books
where title='Harry Potter'
order by price ASC;

Now consider the ‘older’ method of answering the above query without the Streams API explicitly using loops:

List harryPotterBooks = new ArrayList<>();

for (Book book:
     listOfBooks) {
    if (book.getTitle().equals("Harry Potter")){
        harryPotterBooks.add(book);
    }
}

harryPotterBooks.sort(new Comparator() {
    @Override
    public int compare(Book o1, Book o2) {
        if (o1.getPrice() > o2.getPrice()){
            return 1;
        } else if(o1.getPrice() < o2.getPrice()){
            return -1;
        }else return 0;
    }
});

System.out.println("Printing sorted Harry Potter books by price asc");

for (Book book:
     harryPotterBooks) {
    System.out.println(book.toString());
}

Now consider the same business requirement implemented using the Streams API:

List harryPotterBooks = new ArrayList<>();
harryPotterBooks = listOfBooks.stream()
        .filter(b -> b.getTitle() == "Harry Potter")
        .sorted(comparing(Book::getPrice))
        .collect(Collectors.toList());

harryPotterBooks.forEach(System.out::println);

Over here, the stream(), filter(), sorted(), collect() and forEach() methods are Stream API operations.

Just to revisit, the business requirement implemented using the SQL solution looked like this:

select title, price
from Books
where title='Harry Potter'
order by price ASC;

As can be seen, both the SQL solution and the solution using the Streams API solve the business requirement by declaratively expressing what needs to be done (by using lambda expressions), rather than imperatively expression how we should filter the input, sort it, collect and print the output using for loops.

Hope this small introduction into the Streams API is useful in clarifying the difference between the use of Streams API and explicitly using loops to perform operations on collections.

The complete code example can be found here: https://github.com/zeesh49/Java_Streams_API

In the next post, we will discuss the Streams API in more detail.

In this article we will discuss how we can use CROSS JOINS in SQL to transpose data from columns into rows.

Consider the following Budget table:

As can be seen, the table displays the budget of departments by years. For example: The budget of IT department for the 2017 was US$125,000/-.

Now suppose we wanted to transpose the above table and display it like this:

The above requirement to transpose the budget table could have many reasons including answering following query:

• What is the total allocated budget of each department for the past 3 years?

In order to solve this problem, we need to create another table listing the column names we want to transpose like below:

Now we need to cross join the Budget table with the Years table and apply case statement as follows to transpose the ‘Year’ columns into rows:

select B.id, B.department,
Y."Year",
CASE WHEN Y."Year" = '2016' THEN B."2016"
WHEN Y."Year" = '2017' THEN B."2017"
WHEN Y."Year" = '2018' THEN B."2018" END as Amount
from Budget B
CROSS JOIN
"Years" Y;

The output of the above query is the following table:

Now if we wanted to answer the business question:

• What is the total allocated budget of each department for the past 3 years?

We could easily write the following query to answer:

select id, department,sum(Amount) as "Total Budget" from (
select B.id as id, B.department as department,
Y."Year" as year,
CASE WHEN Y."Year" = '2016' THEN B."2016"
WHEN Y."Year" = '2017' THEN B."2017"
WHEN Y."Year" = '2018' THEN B."2018" END as Amount
from Budget B
CROSS JOIN
"Years" Y) AA group by AA.id,AA.department;

As can be seen from the above example, CROSS JOINS can be used to transpose columns into rows. This is also known as ‘unpivoting’ the table.

Hope you will find this article useful for learning about cross joins and their 1 of many uses.

SQL joins are operations to ‘join’ 2 or more tables related to each other with primary and foreign keys to get corresponding columns of the tables based on the relationship. There are 4 types of SQL joins:

1. Inner Join
2. Left Outer Join
3. Right Outer Join
4. Full Outer Join

In this post, we will be discussing inner joins.

Before starting, lets consider the database below with the following tables:

Ofcourse the 2 tables have the following relationship:

– 1 Department can have many employees

– 1 Employee can belong to only 1 department

Further, the primary key of the Employee table is a composite key made from First_Name and Last_Name columns, while the primary key of Department table is only 1 column: Department_Name.

Inner joins between the columns of any 2 tables only join rows with join column values that are present in both tables. In case, there is a row with join column values not present in any 1 of the tables, that row is not part of the result of the join.

Let me explain:

For example:

Consider the following business question:

How many employees work in John Doe’s department?

Following is the query to answer the above question:

Select First_Name,
Last_Name,
Department_Name,
Employee_Count
FROM Employee E
INNER JOIN
Department D
ON E.Department_Name=D.Department_name
WHERE First_Name = ‘John’ and Last_Name = ‘Doe’;

Following will be the result of the above query with inner join:

Please note that we are inner-joining the Employee and Department tables on the column Department_Name, which is the primary key of the Department table and a foreign key of the Employee table.

Now consider the following business question:

Get me all the employee names belonging to the HR department.

To answer this question, consider the query below:

Select First_Name,
Last_Name,
Department_Name
FROM Department D
INNER JOIN
Employee E
ON E.Department_Name=D.Department_name
Where D.Department_Name = ‘HR’;

The above query returns no results. To explain this, please have a look at the tables again:

The Department_Name (s) in green are present in both the tables, hence, whenever we apply an inner join between Employee and Department tables, the green cells will be part of the join.

However, the Department_Name (s) in red only belong to the Department table, hence, they will be filtered out when the inner join is applied as shown in the example of ‘HR’ department above.

I hope after this post, you will be able to apply inner joins correctly to answer your business questions.