Day 7, Java Holiday Calendar 2016, Access Databases with Streams

7. Access Databases with Streams

Today's tips is about querying databases using Java 8 streams. By adding both a runtime and plugin dependency to open-source Speedment in you applications 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();
      }
   }

Read more in the original DZone article at https://dzone.com/articles/use-smart-streams-with-your-database-in-2-minutes

Follow the Java Holiday Calendar 2016 with small tips and tricks all the way through the winter holiday season.