Learn sql and java stream together in half an hour code covers the basic usage of sql selects syntax and their stream api parallel.
package derby;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DerbyDb {
/*
* Download and unzip latest Derby http://db.apache.org/derby/derby_downloads.html
* Set classpath to point to derby.jar
* javac -cp "/Users/homenetwork/Downloads/db-derby-10.13.1.1-lib/lib/derby.jar:." DerbyDb.java
* java -cp "/Users/homenetwork/Downloads/db-derby-10.13.1.1-lib/lib/derby.jar:." DerbyDb
*/
static final String URL = "jdbc:derby:social;create=true;";
public static void main(String...args) throws SQLException {
try(Connection conn = DriverManager.getConnection(URL);
Statement stmt = conn.createStatement();){
//stmt.executeUpdate("Drop table people");
sql(stmt, "create table people ("
+ "name VARCHAR(255),"
+ "age INTEGER,"
+ "emailAddress VARCHAR(255),"
+ "gender varchar(255))");
sql(stmt, "insert into people values ('Ted', 16, 'ted@email.com', 'MALE')");
sql(stmt, "insert into people values ('Teder', 16, 'teder@email.com', 'MALE')");
sql(stmt, "insert into people values ('Tedy', 26, 'tedy@email.com', 'MALE')");
sql(stmt, "insert into people values ('Min', 36, 'min@email.com', 'MALE')");
sqlVsStream(stmt);
sql(stmt, "Drop table people");
}
}
static void sqlVsStream(Statement stmt) throws SQLException {
List<People> roster = toCollection(stmt);
sql(stmt, "select * from people");
System.out.println("====stream=====");
roster.stream().forEach(System.out::println);
sql(stmt, "select * from people where name = 'Ted'");
System.out.println("====stream=====");
roster.stream().filter(i -> i.getName().equals("Ted")).forEach(System.out::println);
sql(stmt, "select * from people where name like 'Te%'");
System.out.println("====stream=====");
roster.stream().filter(i -> i.getName().startsWith("Te")).forEach(System.out::println);
sql(stmt, "select * from people where age > 20 and name like 'Te%'");
System.out.println("====stream=====");
roster.stream().filter(i -> i.getAge() > 20)
.filter(i -> i.getName().startsWith("Te")).forEach(System.out::println);
sql(stmt, "select * from people where age < 20 and name like '%d'");
System.out.println("====stream=====");
roster.stream().filter(i -> i.getAge() < 20)
.filter(i -> i.getName().endsWith("d")).forEach(System.out::println);
sql(stmt, "select * from people where name like 'Te%' order by name asc");
System.out.println("====stream=====");
roster.stream().filter(i -> i.getName().startsWith("Te"))
.sorted((a, b) -> a.getName().compareTo(b.getName())).forEach(System.out::println);
sql(stmt, "select * from people where name like 'Te%' order by name desc");
System.out.println("====stream=====");
roster.stream().filter(i -> i.getName().startsWith("Te"))
.sorted((a, b) -> b.getName().compareTo(a.getName())).forEach(System.out::println);
sql(stmt, "select * from people where name like 'Te%' order by age asc");
System.out.println("====stream=====");
roster.stream().filter(i -> i.getName().startsWith("Te"))
.sorted((a, b) -> a.getAge() - b.getAge()).forEach(System.out::println);
sql(stmt, "select * from people where name like 'Te%' order by age desc");
System.out.println("====stream=====");
roster.stream().filter(i -> i.getName().startsWith("Te"))
.sorted((a, b) -> b.getAge() - a.getAge()).forEach(System.out::println);
sql(stmt, "select count(*) from people where name like 'Te%'", 1);
System.out.println("====stream=====");
System.out.println(roster.stream().filter(i -> i.getName().startsWith("Te")).count());
sql(stmt, "select count(age) from people where name like 'Te%'", 1);
System.out.println("====stream=====");
System.out.println(roster.stream().filter(i -> i.getName().startsWith("Te"))
.mapToLong(People::getAge).count());
sql(stmt, "select count(name) from people where name like 'Te%'", 1);
System.out.println("====stream=====");
System.out.println(roster.stream().filter(i -> i.getName().startsWith("Te"))
.map(People::getName).count());
sql(stmt, "select sum(age) from people where name like 'Te%'", 1);
System.out.println("====stream=====");
System.out.println(roster.stream().filter(i -> i.getName().startsWith("Te"))
.mapToLong(People::getAge).sum());
sql(stmt, "select sum(age)/count(*) from people where name like 'Te%'", 1);
System.out.println("====stream=====");
System.out.println(roster.stream().filter(i -> i.getName().startsWith("Te"))
.mapToLong(People::getAge).average().getAsDouble());
sql(stmt, "select max(age) from people where name like 'Te%'", 1);
System.out.println("====stream=====");
System.out.println(roster.stream().filter(i -> i.getName().startsWith("Te"))
.mapToLong(People::getAge).max().getAsLong());
sql(stmt, "select min(age) from people where name like 'Te%'", 1);
System.out.println("====stream=====");
System.out.println(roster.stream().filter(i -> i.getName().startsWith("Te"))
.mapToLong(People::getAge).min().getAsLong());
sql(stmt, "select distinct age from people where name like 'Te%'", 1);
System.out.println("====stream=====");
roster.stream().filter(i -> i.getName().startsWith("Te"))
.mapToLong(People::getAge).distinct().forEach(System.out::println);
sql(stmt, "select age from people where name like 'Te%'", 1);
System.out.println("====stream=====");
roster.stream().filter(i -> i.getName().startsWith("Te"))
.mapToLong(People::getAge).forEach(System.out::println);
}
static List<People> toCollection(Statement stmt) throws SQLException {
List<People> roster = new ArrayList<>();
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 sql(Statement stmt, String sql, int... num) throws SQLException {
System.out.println(sql);
int num0 = num.length == 0 ? 4 : num[0];
boolean isResultSet = stmt.execute(sql);
if(isResultSet) {
ResultSet rs = stmt.getResultSet();
while(rs.next()) {
for(int i = 1; i <= num0; i++) {
System.out.println("" + rs.getObject(i));
}
System.out.println();
}
rs.close(); //closing Statement also closes ResultSet
} else {
int r = stmt.getUpdateCount();
System.out.println(r);
}
}
}
The program output.
The detail of derby jdbc driver set up are in this post.
