Groovy, Oracle example

Please refer to my previous posts on how to get Oracle XE installed and a demonstration of the HR Schema Sample Data.

The dynamic nature of Groovy with built-in support for Sql really shines on quick scripts like this example:

def printHeader(name, sql){
    println """\nThere are
             ${sql.firstRow("SELECT COUNT(*) TOTAL FROM ${name}".toString()).TOTAL}
             total ${name.toLowerCase()}."""
    println "\n${name}\n${"=".multiply(name.size())}"
}

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
                "hr", "oracle", "oracle.jdbc.pool.OracleDataSource")


printHeader("EMPLOYEES", sql)
sql.eachRow("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES") {
            println "${it.FIRST_NAME} ${it.LAST_NAME}"
        }

printHeader("COUNTRIES", sql)
sql.eachRow("SELECT COUNTRY_NAME FROM COUNTRIES") {
    println it.COUNTRY_NAME
}

printHeader("DEPARTMENTS", sql)
sql.eachRow("SELECT DEPARTMENT_NAME FROM DEPARTMENTS") {
    println it.DEPARTMENT_NAME
}

printHeader("REGIONS", sql)
sql.eachRow("SELECT REGION_NAME FROM REGIONS") {
    println it.REGION_NAME
}

Please note that the ojdbc jar is provided in the classpath.

Pay attention on the easiness by which we create the connection doing the needed import on the spot:

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
                "hr", "oracle", "oracle.jdbc.pool.OracleDataSource")

Also the easiness and compactness by which we iterate through the result set via the help of closures and the default it reference is unparalleled in the ecosystem of JVM languages:

sql.eachRow("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES") {
            println "${it.FIRST_NAME} ${it.LAST_NAME}"
        }

For the demo purposes I’ve created a function that is creating the printed header output. It is accepting Sql instance on the input parameters which is not advisable but I wanted to demonstrate the powerful GString injection within a String literal:

def printHeader(name, sql){
    println """\nThere are
             ${sql.firstRow("SELECT COUNT(*) TOTAL FROM ${name}".toString()).TOTAL}
             total ${name.toLowerCase()}."""
    println "\n${name}\n${"=".multiply(name.size())}"
}

Please note how we inject the table name on the SQL literal and also how we are populating the underlying of the table name header using the size of the table name string. Moreover by surrounding the literal with triple double-quotes we can expand across multiple lines without needing implicit/explicit concatenation.

The above code yields output:

There are 107 total employees.


EMPLOYEES
=========
Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda
Elizabeth Bates
Sarah Bell
David Bernstein
Laura Bissot
Harrison Bloom
Alexis Bull
Anthony Cabrio
Gerald Cambrault
...

There are 25 total countries.

COUNTRIES
=========
Argentina
Australia
Belgium
Brazil
Canada
Switzerland
China
Germany
Denmark
Egypt
France
HongKong
Israel
India
Italy
Japan
Kuwait
Mexico
Nigeria
Netherlands
Singapore
United Kingdom
United States of America
Zambia
Zimbabwe

There are 27 total departments.

DEPARTMENTS
===========
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting
Treasury
Corporate Tax
Control And Credit
Shareholder Services
Benefits
Manufacturing
Construction
Contracting
Operations
IT Support
NOC
IT Helpdesk
Government Sales
Retail Sales
Recruiting
Payroll

There are 4 total regions.

REGIONS
=======
Europe
Americas
Asia
Middle East and Africa

Groovy, Scala closures demonstration

This is a quick demo of closures usage for Groovy and Scala side-by-side.

Let’s get an easy study case where we are trying to identify whether a string contains unique characters. Starting with Java we would have:

Java

public class StringAllUniqueChars {

    public static boolean hasStringAllUniqueChars(String str){

        //cache data structure
        final HashSet<Character> stackConfinedCache = new HashSet<Character>();
        //imperative iteration
        for(Character c : str.toCharArray()){
            if(stackConfinedCache.remove(c))
                //fail fast
                return false;
            else
                stackConfinedCache.add(c);
        }
        return true;
    }

    public static void main(String[] args) {
        System.out.println("hello\t" + StringAllUniqueChars.hasStringAllUniqueChars("hello"));
        System.out.println("helo\t" + StringAllUniqueChars.hasStringAllUniqueChars("helo"));
    }
}

Keypoints:

  • We create a data structure to store the findings along the way and fail fast in case of the first dup char found
  • We iterate over the chars of the string using a for loop

Groovy

def hasStringAllUniqueChars(str){

    str.collect {         //in a collection
            str.count(it)    //the occurrences of each char
        }
        .findAll {
            it>1    //filter those with more than one occurrences
        }
        .size()==0  //make sure they don't exist
}

def hasStringAllUniqueChars2(str){
    !   //if we don't
    str.any{       //find any
        str.count(it)>1    //character occurrence in string more than once
    }
}

println "hello\t" + hasStringAllUniqueChars2("hello")
println "hello\t" + hasStringAllUniqueChars("helo")

Keypoints:

  • We are running it in a form of Groovy script
  • We are making use of the default it reference while being on a closure
  • In the first implementation we are first transforming the string characters into a collection of corresponding character sizes. Then we are filtering only duplicate characters and finally we are taking the decision based on whether we have any dups across all chars.
  • In the second implementation we are taking a shortcut using Groovy’s any method hand-picking in its closure only dup characters

Scala

object StringAllUniqueChars {

  def hasStringAllUniqueChars(str: String) =

    !str.exists{  //if we don't find any case where
      c =>    //each character's
        str.count(_==c)>1       //count in the string is greater than 1
    }

  def main(args: Array[String]){
    println("hello\t"+hasStringAllUniqueChars("hello"))
    println("helo\t"+hasStringAllUniqueChars("helo"))
  }

}

Keypoints:

  • We are using an Object since we want to host our main method somewhere. It’s Scala’s way to address static and normally the so-called companion object is grouping all the static content of its corresponding class.
  • We are employing closures in a similar way as the second Groovy implementation using Groovy’s exists and count methods

For a matter of completeness this is how Scala, Groovy and Java are co-existing happily together during compile/run time under the Maven umbrella:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>ScalaGroovyClosures</groupId>
    <artifactId>ScalaGroovyClosures</artifactId>
    <version>1.0</version>

    <build>
        <plugins>
            <!--java compiler-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.3.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                </configuration>
            </plugin>

            <!--scala compiler-->
            <plugin>
                <groupId>org.scala-tools</groupId>
                <artifactId>maven-scala-plugin</artifactId>
                <version>2.15.2</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>compile</goal>
                            <goal>testCompile</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>

            <!--groovy compiler-->
            <plugin>
                <groupId>org.codehaus.groovy.maven</groupId>
                <artifactId>gmaven-plugin</artifactId>
                <version>1.0</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>compile</goal>
                            <goal>testCompile</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
    <dependencies>
        <dependency>
            <groupId>org.scala-lang</groupId>
            <artifactId>scala-library</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>org.codehaus.groovy.maven.runtime</groupId>
            <artifactId>gmaven-runtime-1.6</artifactId>
            <version>1.0</version>
        </dependency>
    </dependencies>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

</project>