XYZ CODE: Learn sql syntax and Stream API in one program

Back>

Say you have a collection of data (stored in a database table) to handle. You want to select, filter, order, map, group them. Sql have a very concise syntax to achieve your goal. Before java 7, if you want to handle the collection of data, you have to iterate, store temp result in Map<>, List<> etc, which is distracting. Now we have Stream API in java 7, you can write high level query style code like sql commands.

If you already know sql, learning Stream API became simpler.

Understand sql and java stream api in half an hour

Even though you already know sql syntax well, the following program will give you better understanding of sql keywords such as where, group by, having, order by combinations.

The program uses derby db, the driver setup is covered in this post.

import java.sql.*;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashSet;
import java.util.IntSummaryStatistics;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.TreeMap;
import java.util.stream.Collectors;

public class DerbyTest {

static final String URL = "jdbc:derby:social;create=true";

static void runSql(String sql, Integer... colNum) {
Integer b1 = colNum.length > 0 ? colNum[0] : 4;
List<People> roster = new ArrayList<>();
System.out.println("SQL: "+sql);
try (Connection conn = DriverManager.getConnection(URL);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while(rs.next()) {
for(int i = 1; i<= b1; i++) {
System.out.println(rs.getObject(i));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
roster.stream().forEach(i -> System.out.println(i));
System.out.println("----------Stream-------------");
}

static void cleanUp() {
try (Connection conn = DriverManager.getConnection(URL);
Statement stmt = conn.createStatement()) {
stmt.executeUpdate("Drop Table people");
} catch (SQLException e ) {
e.printStackTrace();
}
}

public static void main(String...args) throws SQLException {
String url = URL;
try (Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement()) {
stmt.executeUpdate("Create Table people ("
+ "name VARCHAR(255), "
+ "age INTEGER, "
+ "emailAddress VARCHAR(255), "
+ "gender VARCHAR(255)"
+ ")");
stmt.executeUpdate("Insert into people values ('Fred', 16, 'fred@example.com', 'MALE')");
stmt.executeUpdate("Insert into people values ('Ted', 16, 'ted@example.com', 'MALE')");
stmt.executeUpdate("Insert into people values ('Susan', 26, 'susan@example.com', 'FEMALE')");
stmt.executeUpdate("Insert into people values ('Steven', 26, 'steven@example.com', 'MALE')");
stmt.executeUpdate("Insert into people values ('Yan', 56, 'yan@example.com', 'MALE')");
stmt.executeUpdate("Insert into people values ('Peter', 31, 'peter@example.com', 'MALE')");
}

sqlVsStream();
cleanUp();
}

static List<People> getRoster() throws SQLException {
String url = URL;
List<People> roster = new ArrayList<>();

try (Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from people")) {
while(rs.next()) {
People p = new People();
p.setName(rs.getString(1));
p.setAge(rs.getInt(2));
p.setEmailAddress(rs.getString(3));
p.setGender(rs.getString(4));
roster.add(p);
}

}
return roster;
}

static void sqlVsStream() throws SQLException {
runSql("select * from people");
List<People> roster = getRoster();
roster.stream().forEach(System.out::println);

//where vs. filter
System.out.println("----where vs. filter");
runSql("select * from people where age > 30");
roster = getRoster();
roster.stream().filter(i -> i.getAge() > 30).forEach(System.out::println);

System.out.println("-------1");
runSql("select * from people where gender = 'FEMALE'");
roster = getRoster();
roster.stream().filter(i -> i.getGender().equalsIgnoreCase("FEMALE")).forEach(System.out::println);

System.out.println("--------2");
runSql("select * from people where name like '%red'");
roster = getRoster();
roster.stream().filter(i -> i.getName().endsWith("red")).forEach(System.out::println);

//limit, derby don't support limit

//map?
System.out.println("------map");
runSql("select name from people", 1);
roster = getRoster();
roster.stream().map(People::getName).forEach(System.out::println);

//count
System.out.println("-----count");
runSql("select count(*) from people", 1);
roster = getRoster();
System.out.println(roster.stream().count());
System.out.println(roster.stream().collect(Collectors.counting()));
System.out.println(roster.stream().mapToLong(a -> 1L).sum());

//sum
System.out.println("----sum");
runSql("select sum(age) from people", 1);
roster = getRoster();
System.out.println(roster.stream().mapToInt(People::getAge).sum());
System.out.println(roster.stream()
.collect(Collectors.reducing(0, People::getAge, (a, b) -> a + b)));
System.out.println(roster.stream()
.collect(Collectors.reducing(0, People::getAge, Integer::sum)));
System.out.println(roster.stream().collect(Collectors.summingInt(People::getAge)));
System.out.println(roster.stream().map(People::getAge).reduce(Integer::sum).get());
System.out.println(roster.stream().map(People::getAge).reduce(0, Integer::sum));

//average
System.out.println("-----average");
runSql("select sum(age)/count(age) from people", 1);
roster = getRoster();
System.out.println(roster.stream().mapToInt(People::getAge).average().getAsDouble());
System.out.println(roster.stream().collect(Collectors.averagingInt(People::getAge)));

//max
System.out.println("------max");
runSql("select max(age) from people", 1);
roster = getRoster();
System.out.println(roster.stream().mapToInt(People::getAge).max().getAsInt());
System.out.println(roster.stream().collect(
Collectors.maxBy((a, b) -> (a.getAge() - b.getAge())))
.get().getAge());
System.out.println(roster.stream().collect(
Collectors.reducing((a, b) -> a.getAge() > b.getAge() ? a : b))
.get().getAge());
System.out.println(roster.stream().map(People::getAge).reduce(Integer::max).get());
System.out.println(roster.stream().map(People::getAge).reduce(Integer.MIN_VALUE, Integer::max));

//min
System.out.println("------min");
runSql("select min(age) from people", 1);
roster = getRoster();
System.out.println(roster.stream().mapToInt(People::getAge).min().getAsInt());
System.out.println(roster.stream().collect(
Collectors.minBy((a,b) -> (a.getAge() - b.getAge()))).get().getAge());
System.out.println(roster.stream().collect(
Collectors.reducing((a, b) -> a.getAge() < b.getAge() ? a : b))
.get().getAge());
System.out.println(roster.stream().map(People::getAge).reduce(Integer::min).get());
System.out.println(roster.stream().map(People::getAge).reduce(Integer.MAX_VALUE, Integer::min));

//summarize
System.out.println("------summarize");
runSql("select count(*), sum(age), min(age), sum(age)/count(*), max(age) from people", 5);
roster = getRoster();
IntSummaryStatistics insum = roster.stream().mapToInt(People::getAge).summaryStatistics();
System.out.println(insum.getCount());
System.out.println(insum.getSum());
System.out.println(insum.getMin());
System.out.println(insum.getAverage());
System.out.println(insum.getMax());

System.out.println();
insum = roster.stream().collect(Collectors.summarizingInt(People::getAge));
System.out.println(insum.getCount());
System.out.println(insum.getSum());
System.out.println(insum.getMin());
System.out.println(insum.getAverage());
System.out.println(insum.getMax());

//distinct
System.out.println("------distict");

runSql("select gender from people", 1);
roster = getRoster();
roster.stream().map(People::getGender).forEach(System.out::println);
System.out.println(roster.stream().map(People::getGender).collect(Collectors.toList()));
System.out.println(roster.stream().map(People::getGender).collect(Collectors.toCollection(ArrayList::new)));
System.out.println();
runSql("select distinct gender from people", 1);
roster = getRoster();
roster.stream().map(People::getGender).distinct().forEach(System.out::println);
System.out.println(roster.stream().map(People::getGender).collect(Collectors.toSet()));
System.out.println(roster.stream().map(People::getGender).collect(Collectors.toCollection(HashSet::new)));

//order
System.out.println("------order by");
runSql("select * from people order by age");
roster = getRoster();
roster.stream().sorted((a,b) -> (a.getAge() - b.getAge())).forEach(System.out::println);

//group by
System.out.println("------group by");
runSql("select age, count(*) from people group by age", 2);
roster = getRoster();
Map<Integer, List<People>> map = roster.stream().collect(Collectors.groupingBy(People::getAge));
map.keySet().stream().forEach(
key -> {System.out.println(key); System.out.println(map.get(key).size());});

System.out.println();
Map<Integer, List<String>> map0 = roster.stream().collect(
Collectors.groupingBy(People::getAge,
Collectors.mapping(People::getName, Collectors.toList())));
map0.keySet().stream().forEach(
key -> {System.out.println(key); System.out.println(map0.get(key).size());});

System.out.println();
Map<Integer, Long> map1 = roster.stream().collect(
Collectors.groupingBy(People::getAge, Collectors.counting()));
System.out.println(map1);

System.out.println();
map1 = roster.stream().collect(
Collectors.groupingBy(People::getAge,
Collectors.mapping(a -> 1L, Collectors.counting())));
System.out.println(map1);
System.out.println("------2");
runSql("select age, sum(age) from people group by age", 2);
Map<Integer, Integer> map2 = roster.stream().collect(
Collectors.groupingBy(People::getAge, Collectors.summingInt(People::getAge)));
System.out.println(map2);
System.out.println("------3");
runSql("select gender, max(age) from people group by gender", 2);
Map<String, Optional<People>> map3 = roster.stream().collect(
Collectors.groupingBy(People::getGender, Collectors.maxBy(
Comparator.comparingInt(People::getAge))
));
map3.keySet().stream().forEach(key ->
{System.out.println(key); System.out.println(map3.get(key).get().getAge());});
System.out.println("------4");
runSql("select gender, min(age) from people group by gender", 2);
Map<String, Optional<People>> map4 = roster.stream().collect(
Collectors.groupingBy(People::getGender, Collectors.minBy(
Comparator.comparingInt(People::getAge))
));
map4.keySet().stream().forEach(key ->
{System.out.println(key); System.out.println(map4.get(key).get().getAge());});

System.out.println("------5");
runSql("select gender, min(age) from people group by gender having gender in ('FEMALE')", 2);
Map<String, Optional<People>> map5 = roster.stream().collect(
Collectors.groupingBy(People::getGender, Collectors.minBy(
Comparator.comparingInt(People::getAge))
));
map5.keySet().stream().filter(a -> a.equalsIgnoreCase("FEMALE")).forEach(key ->
{System.out.println(key); System.out.println(map5.get(key).get().getAge());});
System.out.println();
Map<String, Optional<People>> map50 = roster.stream()
.filter(a -> a.getGender().equalsIgnoreCase("FEMALE"))
.collect(
Collectors.groupingBy(People::getGender, Collectors.minBy(
Comparator.comparingInt(People::getAge))
));
map50.keySet().stream().forEach(key ->
{System.out.println(key); System.out.println(map50.get(key).get().getAge());});

System.out.println("-----6");
runSql("select gender, age, count(*) from people group by gender, age "
+ "having age < 50 "
+ "order by gender, age asc", 3);
Map<String, Map<Integer, Long>> map6 = roster.stream().filter(a -> a.getAge() < 50).collect(
Collectors.groupingBy(People::getGender,
Collectors.groupingBy(People::getAge, Collectors.counting())
));

map6.keySet().stream().sorted().forEach(key -> {
Map<Integer, Long> tmpm = map6.get(key);
tmpm.keySet().stream().forEach(key1 -> {
System.out.println(key);
System.out.println(key1);
System.out.println(tmpm.get(key1));
});
});

System.out.println();
Map<String, Map<Integer, Long>> map60 = roster.stream().collect(
Collectors.groupingBy(People::getGender,
Collectors.groupingBy(People::getAge, Collectors.counting())
));

map60.keySet().stream().sorted().forEach(key -> {
Map<Integer, Long> tmpm = map60.get(key);
tmpm.keySet().stream().filter(a -> a < 50).forEach(key1 -> {
System.out.println(key);
System.out.println(key1);
System.out.println(tmpm.get(key1));
});
});

System.out.println();
Map<String, Map<Integer, List<People>>> map61 = roster.stream().collect(
Collectors.groupingBy(People::getGender,
Collectors.groupingBy(
People::getAge,
TreeMap::new,
Collectors.toCollection(ArrayList::new))
));

map61.keySet().stream().sorted(Comparator.naturalOrder()).forEach(key -> {
Map<Integer, List<People>> tmpm = map61.get(key);
tmpm.keySet().stream().sorted(Comparator.naturalOrder()).filter(a -> a < 50)
.forEach(key1 -> {
System.out.println(key);
System.out.println(key1);
System.out.println(tmpm.get(key1).size());
});
});

System.out.println("-----7");
runSql("select gender, age, count(*) from people group by gender, age "
+ "having age < 50 and gender = 'MALE' "
+ "order by gender, age asc", 3);
Map<String, Map<Integer, List<People>>> map7 = roster.stream()
.filter(a -> a.getGender().equalsIgnoreCase("MALE"))
.filter(a -> a.getAge() < 50)
.collect(
Collectors.groupingBy(People::getGender,
Collectors.groupingBy(
People::getAge,
TreeMap::new,
Collectors.toCollection(ArrayList::new))
));

map7.keySet().stream().sorted(Comparator.naturalOrder())
.forEach(key -> {
Map<Integer, List<People>> tmpm = map7.get(key);
tmpm.keySet().stream().sorted(Comparator.naturalOrder())
.forEach(key1 -> {
System.out.println(key);
System.out.println(key1);
System.out.println(tmpm.get(key1).size());
});
});

System.out.println();
Map<String, Map<Integer, List<People>>> map71 = roster.stream().collect(
Collectors.groupingBy(People::getGender,
Collectors.groupingBy(
People::getAge,
TreeMap::new,
Collectors.toCollection(ArrayList::new))
));

map71.keySet().stream().sorted(Comparator.naturalOrder())
.filter(a -> a.equalsIgnoreCase("MALE"))
.forEach(key -> {
Map<Integer, List<People>> tmpm = map71.get(key);
tmpm.keySet().stream().sorted(Comparator.naturalOrder())
.filter(a -> a < 50)
.forEach(key1 -> {
System.out.println(key);
System.out.println(key1);
System.out.println(tmpm.get(key1).size());
});
});

System.out.println("-----8");
runSql("select gender, age, count(*) from people "
+ "where name != 'Peter' "
+ "group by gender, age "
+ "having age < 50 and gender = 'MALE' and count(*) = 2"
+ "order by gender, age asc", 3);
Map<String, Map<Integer, List<People>>> map8 = roster.stream()
.filter(a -> a.getGender().equalsIgnoreCase("MALE"))
.filter(a -> a.getAge() < 50)
.filter(a -> !a.getName().equals("Peter"))
.collect(
Collectors.groupingBy(People::getGender,
Collectors.groupingBy(
People::getAge,
TreeMap::new,
Collectors.toCollection(ArrayList::new))
));

map8.keySet().stream().sorted(Comparator.naturalOrder())
.forEach(key -> {
Map<Integer, List<People>> tmpm = map8.get(key);
tmpm.keySet().stream().sorted(Comparator.naturalOrder())
.filter(k -> tmpm.get(k).size() == 2)
.forEach(key1 -> {
System.out.println(key);
System.out.println(key1);
System.out.println(tmpm.get(key1).size());
});
});

System.out.println("-----9");
runSql("select gender, age, sum(length(name)) from people "
+ "where name != 'Peter' "
+ "group by gender, age "
+ "having age < 50 and gender = 'MALE' and count(*) = 2 "
+ "order by gender, age asc", 3);
Map<String, Map<Integer, List<People>>> map9 = roster.stream()
.filter(a -> a.getGender().equalsIgnoreCase("MALE"))
.filter(a -> a.getAge() < 50)
.filter(a -> !a.getName().equals("Peter"))
.collect(
Collectors.groupingBy(People::getGender,
Collectors.groupingBy(
People::getAge,
TreeMap::new,
Collectors.toCollection(ArrayList::new))
));

map9.keySet().stream().sorted(Comparator.naturalOrder())
.forEach(key -> {
Map<Integer, List<People>> tmpm = map9.get(key);
tmpm.keySet().stream().sorted(Comparator.naturalOrder())
.filter(k -> tmpm.get(k).size() == 2)
.forEach(key1 -> {
System.out.println(key);
System.out.println(key1);
List<People> pl = tmpm.get(key1);
int s = pl.stream().collect(Collectors.summingInt(p -> p.getName().length()));
System.out.println(s);
});
});

System.out.println("-----10");
runSql("select gender, age, sum(length(name)) from people "
+ "where name != 'Peter' "
+ "group by gender, age "
+ "having age < 50 and gender = 'MALE' and count(*) = 2 "
+ "and sum(age) > 20 "
+ "order by gender desc, age asc", 3);
Map<String, Map<Integer, List<People>>> map10 = roster.stream()
.collect(
Collectors.groupingBy(People::getGender,
Collectors.groupingBy(
People::getAge,
TreeMap::new,
Collectors.toCollection(ArrayList::new))
));

map10.keySet().stream().sorted(Comparator.reverseOrder())
.filter(a -> a.equals("MALE"))
.forEach(key -> {
Map<Integer, List<People>> tmpm = map10.get(key);
tmpm.keySet().stream().sorted(Comparator.naturalOrder())
.filter(a -> a < 50)
.filter(k -> tmpm.get(k).size() == 2)
.filter(k -> tmpm.get(k).stream().collect(Collectors.summingInt(People::getAge)) > 20)
.forEach(key1 -> {
System.out.println(key);
System.out.println(key1);
List<People> pl = tmpm.get(key1);
int s = pl.stream()
.filter(a -> !a.getName().equals("Peter"))
.collect(Collectors.summingInt(p -> p.getName().length()));
System.out.println(s);
});
});
}
}

The output is:

SQL: select * from people

Fred

16

fred@example.com

MALE

Ted

16

ted@example.com

MALE

Susan

26

susan@example.com

FEMALE

Steven

26

steven@example.com

MALE

Yan

56

yan@example.com

MALE

Peter

31

peter@example.com

MALE

----------Stream-------------

Fred

16

fred@example.com

MALE

Ted

16

ted@example.com

MALE

Susan

26

susan@example.com

FEMALE

Steven

26

steven@example.com

MALE

Yan

56

yan@example.com

MALE

Peter

31

peter@example.com

MALE

----where vs. filter

SQL: select * from people where age > 30

Yan

56

yan@example.com

MALE

Peter

31

peter@example.com

MALE

----------Stream-------------

Yan

56

yan@example.com

MALE

Peter

31

peter@example.com

MALE

-------1

SQL: select * from people where gender = 'FEMALE'

Susan

26

susan@example.com

FEMALE

----------Stream-------------

Susan

26

susan@example.com

FEMALE

--------2

SQL: select * from people where name like '%red'

Fred

16

fred@example.com

MALE

----------Stream-------------

Fred

16

fred@example.com

MALE

------map

SQL: select name from people

Fred

Ted

Susan

Steven

Yan

Peter

----------Stream-------------

Fred

Ted

Susan

Steven

Yan

Peter

-----count

SQL: select count(*) from people

6

----------Stream-------------

6

6

6

----sum

SQL: select sum(age) from people

171

----------Stream-------------

171

171

171

171

171

171

-----average

SQL: select sum(age)/count(age) from people

28

----------Stream-------------

28.5

28.5

------max

SQL: select max(age) from people

56

----------Stream-------------

56

56

56

56

56

------min

SQL: select min(age) from people

16

----------Stream-------------

16

16

16

16

16

------summarize

SQL: select count(*), sum(age), min(age), sum(age)/count(*), max(age) from people

6

171

16

28

56

----------Stream-------------

6

171

16

28.5

56

6

171

16

28.5

56

------distict

SQL: select gender from people

MALE

MALE

FEMALE

MALE

MALE

MALE

----------Stream-------------

MALE

MALE

FEMALE

MALE

MALE

MALE

[MALE, MALE, FEMALE, MALE, MALE, MALE]

[MALE, MALE, FEMALE, MALE, MALE, MALE]

SQL: select distinct gender from people

MALE

FEMALE

----------Stream-------------

MALE

FEMALE

[MALE, FEMALE]

[MALE, FEMALE]

------order by

SQL: select * from people order by age

Ted

16

ted@example.com

MALE

Fred

16

fred@example.com

MALE

Steven

26

steven@example.com

MALE

Susan

26

susan@example.com

FEMALE

Peter

31

peter@example.com

MALE

Yan

56

yan@example.com

MALE

----------Stream-------------

Fred

16

fred@example.com

MALE

Ted

16

ted@example.com

MALE

Susan

26

susan@example.com

FEMALE

Steven

26

steven@example.com

MALE

Peter

31

peter@example.com

MALE

Yan

56

yan@example.com

MALE

------group by

SQL: select age, count(*) from people group by age

----------Stream-------------

{16=2, 56=1, 26=2, 31=1}

{16=2, 56=1, 26=2, 31=1}

------2

SQL: select age, sum(age) from people group by age

----------Stream-------------

{16=32, 56=56, 26=52, 31=31}

------3

SQL: select gender, max(age) from people group by gender

FEMALE

26

MALE

56

----------Stream-------------

MALE

56

FEMALE

26

------4

SQL: select gender, min(age) from people group by gender

FEMALE

26

MALE

16

----------Stream-------------

MALE

16

FEMALE

26

------5

SQL: select gender, min(age) from people group by gender having gender in ('FEMALE')

FEMALE

26

----------Stream-------------

FEMALE

26

FEMALE

26

-----6

SQL: select gender, age, count(*) from people group by gender, age having age < 50 order by gender, age asc

FEMALE

26

1

MALE

16

2

MALE

26

1

MALE

31

1

----------Stream-------------

FEMALE

26

1

MALE

16

2

MALE

26

1

MALE

31

1

FEMALE

26

1

MALE

16

2

MALE

26

1

MALE

31

1

FEMALE

26

1

MALE

16

2

MALE

26

1

MALE

31

1

-----7

SQL: select gender, age, count(*) from people group by gender, age having age < 50 and gender = 'MALE' order by gender, age asc

MALE

16

2

MALE

26

1

MALE

31

1

----------Stream-------------

MALE

16

2

MALE

26

1

MALE

31

1

MALE

16

2

MALE

26

1

MALE

31

1

-----8

SQL: select gender, age, count(*) from people where name != 'Peter' group by gender, age having age < 50 and gender = 'MALE' and count(*) = 2order by gender, age asc

MALE

16

2

----------Stream-------------

MALE

16

2

-----9

SQL: select gender, age, sum(length(name)) from people where name != 'Peter' group by gender, age having age < 50 and gender = 'MALE' and count(*) = 2 order by gender, age asc

MALE

16

7

----------Stream-------------

MALE

16

7

-----10

SQL: select gender, age, sum(length(name)) from people where name != 'Peter' group by gender, age having age < 50 and gender = 'MALE' and count(*) = 2 and sum(age) > 20 order by gender desc, age asc

MALE

16

7

----------Stream-------------

MALE

16

7

Hope you have learned both sql syntax and java Stream API from this program.