Java Holiday Calendar 2016 (Day 7): Access Databases With Streams

Today's tip is about querying databases using Java 8 streams. By adding both a runtime and plugin dependency to open-source Speedment in your application's Maven POM file, you get access to standard stream implementations of all the database's tables. For MySQL, your POM file could look like this:

<properties>
    <speedment.version="">3.0.1</speedment.version>
    <db.groupid>mysql</db.groupid>
    <db.artifactid>mysql-connector-java</db.artifactid>
    <db.version>5.1.39</db.version>
</properties>

<build>
    <plugins>
        <plugin>
            <groupid>com.speedment</groupid>
            <artifactid>speedment-maven-plugin</artifactid>
            <version>${speedment.version}</version>
            <dependencies>
                <dependency>
                    <groupid>${db.groupId}</groupid>
                    <artifactid>${db.artifactId}</artifactid>
                    <version>${db.version}</version>
                </dependency>
            </dependencies> 
        </plugin>
    </plugins>
</build>

<dependencies>
    <dependency>
        <groupid>com.speedment</groupid>
        <artifactid>runtime</artifactid>
        <version>${speedment.version}</version>
        <type>pom</type>
    </dependency>
    <dependency>
        <groupid>${db.groupId}</groupid>
        <artifactid>${db.artifactId}</artifactid>
        <version>${db.version}</version>
    </dependency>
</dependencies>

Read more on how to configure your POM file for other database types here, which is also the place to be if you want to learn more on Speedment and how to write Speedment applications.

Do This

users.stream()
    .filter(EMAIL.endsWith(".com"))
    .forEach(System.out::println);

Don't Do This

Connection conn = null;
Statement stmt = null;
try {
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection
      conn = DriverManager.getConnection(DB_URL, USER, PASS);

      //STEP 4: Execute a query
      stmt = conn.createStatement();

      String sql = "SELECT id, first, last, age, email FROM user";
      ResultSet rs = stmt.executeQuery(sql);

      //STEP 5: Extract data from result set
      while(rs.next()){
         //Retrieve columns
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");
         String email = rs.getString("email");

         if (email.endsWith(".com")) {
             //Display values
             System.out.print("ID: " + id);
             System.out.print(", Age: " + age);
             System.out.print(", First: " + first);
             System.out.print(", Last: " + last);
             System.out.println(", E-mail: " + email);
         }
      }
      rs.close();
   } catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   } catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   } finally {
      //finally block used to close resources
      try{
         if(stmt!=null)
            conn.close();
      } catch(SQLException se){
      }// do nothing
      try{
         if(conn!=null)

            conn.close();

        }catch(SQLException se){

        se.printStackTrace();
    }
}