In this article we will see how easy it is to write scripting in Groovy, with a practical example that serves a common use case. As you would probably know, Groovy is a JVM Language which is defined to run on top of Java Virtual Machine. Having said that it borrows all Java’s capabilities alongside providing its own extra features to simplify the tasks of the developers to achieve things in a easy and efficient manner.
Pre-requisite : The readers are expected to have a good exposure on Java programming language and the basic SQL (like table creation, inserting and reading values from a table etc.,) alongside a beginner level introduction to the Groovy at least to start off with. However we will cover the required aspects of the elementary semantics of Groovy, wherever required during the flow. You can read a good set of introductory tutorials of Groovy here.
The following table shows an overview of the entire article:
Table of Contents
1. Environment
The examples shown below are executed in the Command Prompt / Shell and the GroovyConsole. But they are guaranteed to work with any of the IDEs (like Eclipse, IntelliJ IDEA, Netbeans) through the respective plugins. The examples are executed with the Groovy Version Groovy Version: 2.4.3.
For our example Order Processing System, we will use MySQL open source database. We use MySQL V 5.6 for Windows, with JDBC driver for MySQL - MySQL JDBC Connector 5.1.29.
2. Scripting Basics
Script is an executable program which is accomplished to automate the mundane tasks in a simple and easy manner that requires a minimal or no human intervention thereby resulting in time saving. However there are various scripting languges and tools with their owns pros and cons depending on the features they have to offer, the simplified syntax they have in store, the limitations they have by nature etc.,
In general, the scripting languages have one thing in common – their simplified syntax and ease of use as that is the main attracting point to the users (or developers) who aims to get the job done quickly and efficiently.
Groovy is a programming language which can be used as a Scripting language due to its very simplified syntax which is very easy to use. If you are an experienced Java professional for more than 5 years and have explored Groovy for sometime, you would undoubtedly agree with the above statement.
3. Example – Order Processing
We will see an example of an Order Processing System where we will get the details related to an order in a flat file (a pipe separated / delimited) file where each line contains the information pertaining to an order. We will parse (read and process) the line entries from the file and add an Order for each line into the Database to store it permanently.
For the sake of our example, imagine a system that dumps such a flat file in our file system at regular intervals. Our Groovy Script will continuously monitor the same directory in the file system at regular intervals for the new files. Upon finding the files, it will process them and move the files into a different directory, so as not to process them in the next iteration.
We will be using Groovy language basics for covering this example like String Concatenation, Collections – List and Map, File Handling, SQL Handling etc., You may please check the References section at the bottom of this article to have a quick glance on each of the topics to facilitate your understanding of scripts.
4. Planning and approach
For our order processing script, we will do the following in order as a step by step approach.
- Business Model (Domain Object) – We will create a Domain Object (Business Object) to carry all the information of an order as a single entity, otherwise it will be tedious and an overhead if we were to pass all the individual attributes of an order. It is a common practice to wrap all the relevant attribute into an Object of a corresponding class. In Java, it is called as POJO (Plain Old Java Object) where in Groovy it is called as POGO (Plain Old Groovy Object).
- Application Business Scripts – We will write a few simple Groovy scripts for creating random Order entries and write them into a delimited file (pipe separated) in a directory so that our Database scripts will be able to parse (read and process) them for inserting them into Database.
- Database Scripts – We will write a few simple Groovy Scripts to interact with the database for creating a Database Table, reading the records from the table, truncating the table (wiping off the records), dropping a table etc., with a help of a JDBC Driver (MySQL JDBC Connector).
5. Groovy Scripts for Business Objects
In this section, we will write a few simple Groovy Scripts pertaining to the business purpose.
5.1 Order POGO
This is a very essential script that carries the class structure for a POGO (Plain Old Groovy Object) to have the essential properties of an Order – like the Order Id, Date, Customer Name, Currency, Price, Country, Payment Mode etc.,
We need to carry all the properties of an Order in a single entity – which is a Wrapper Object. It is also called as BO (Business Object) or a DTO (Data Transfer Object) – due to its nature of transferring the data from one layer to another within an application.
We will need to create as many as objects based on the number of order entries. The ratio between the order entry in a flat file Vs the Order POGO instance will be 1:1.
The following Groovy scripts has different member variables for each of the Order attributes/properties plus few methods to be used for a common usage – like toString() to print the state of an object in a meaningful manner, initOrder() – a method to initialize an object with a line of text being read from the text file etc.,
Please remember the fact that Groovy does not really mandate the presence of a semicolon at the end of every executable statement as in Java. This is one of the many significant ease of uses of Groovy.
All the scripts in this article will be under a package
com.javacodegeeks.example.groovy.scriptingwhich helps us to organize all the related classes and scripts in a same folder/directory (which is called as a Package in Java’s term).
We will compile this Groovy class using groovyc Order.groovy so that the compiled .class file gets generated in a folder matching with com/javacodegeeks/example/groovy/scripting in the present directory. It will be referred by other scripts to load the Order class.
Order.groovy
// package statement should be the very first executable statement in any script
// packaging helps us to organize the classes into a related folder
package com.javacodegeeks.example.groovy.scripting
/**
* A POGO (Plain Old Groovy Object) used as a DTO (Data Transfer Object)
* or a BO (Business Object) which will carry the essential particulars
* of an Order
*/
class Order
{
/** Self explanining fields and the default values for the few */
//unique value from Database to denote a particular order
def id = 0
def customerName, country='India', price
def currency='INR', paymentMode='Cash'
def orderDate // will be the current time in DB during insert
/**
* A method to format the value with the specified pattern (format),
* useful in having a fixed width while displaying in Console
*/
def String format(format, value)
{
String.format(format, value)
}
/**
* A method to parse a line of text and initialize an Order instance
* with the parsed tokens
*/
def initOrder(strAsCSV)
{
// tokenize() will split the line of text into tokens, and trimmed to
// remove the extra spaces if any
// the tokens are assigned to the variables in order - as per
// the declaration on the left side
def(nameParsed, countryParsed, priceParsed, currencyParsed, modeParsed) = strAsCSV.tokenize("|")*.trim()
this.customerName=nameParsed
this.country=countryParsed
this.price=priceParsed
this.currency=currencyParsed
this.paymentMode=modeParsed
return this
}
/**
* An overridden toString() method of this Order class to have a
* meaningful display wherever it is invoked (which otherwise
* will be a typical hashcode and the classname)
*/
def String toString()
{
//preparing the output in a fixed width format via format() method defined above
def output = String.format("%-15s", customerName) + " | " +
String.format("%-10s", country) + " | " + String.format("%-8s", price) + " | " +
String.format("%-8s", currency) + " | " + String.format("%-12s", paymentMode) + " | "
// add the OrderDate in the output Data if is not null
if(orderDate!=null && orderDate.trim().length()>0)
output += String.format("%-20s", orderDate) + " | "
// add the Id if it is valid (not zero - meaning it was inserted in the database)
if(id!=0)
output = String.format("%-5s", id) + " | " + output
output
}
}
5.2 First Level Unit Test
Let us just quickly test the Order.groovy to ensure that this class behaves as expected. This will be very important to complete one module perfectly so that we can be comfortable in proceeding with the rest of the scripts. This is the best and recommended practice for the correctness of the script execution.
This script will have 3 different Order instances created and printed on the screen. The script has a self explaining comment at every other step.
TestOrder.groovy
package com.javacodegeeks.example.groovy.scripting
/**
* A straight forward way to instantiate and initialize an Order instance
* Field name and value is separated by a colon (:) and
* each name value pair is separated by a commma (,)
*/
def order = new Order(customerName: 'Raghavan', country : 'India', id : '1',
price : '2351', currency: 'INR', paymentMode : 'Card')
println order
/**
* Another approach to initialize the Order instance where
* one property or field is intialized during instantiation (invoking the constructor)
* and rest of the properties are initialized separately
*/
def order2 = new Order(customerName: 'Kannan')
order2.country='Hong Kong'
order2.id='2'
order2.price='1121'
order2.currency='HKD'
println order2
/**
* Yet another way to initialize the Order instance with one complete line of formatted line
* pipe separated values ("|")
*/
def lineAsCSV = "Ganesh | Hong Kong | 1542.99 | HKD | Cheque |"
def order3 = new Order().initOrder(lineAsCSV)
println order3
The script produces the following output.
1 | Raghavan | India | 2351 | INR | Card | 2 | Kannan | Hong Kong | 1121 | HKD | Cash | Ganesh | Hong Kong | 1542.99 | HKD | Cheque |
The output in each line consists of the different pieces of an Order each separated by a pipe character. The output being displayed is generated out of the toString() method of the Order.groovy class. The fields displayed are Order Id (if not null), Customer Name, Country, Amount, Currency, Payment Mode
The first two instances are having the order Id because it was given during the instantiation. However the 3rd order instance did NOT have the Order Id and hence the output is slightly different from the first 2 instances. The 3rd instance is very important because the actual lines in the flat file will resemble the same as we will not have the order Id at first. It will be created after inserting the record into database, as it should be typically an auto-generated Id.
5.3 Test Order – Random Entries Onetime
Now that we have made our Business Object – Order.groovy and we had also tested that successfully, we will proceed further with the next step. We will create some random Order instances and write them into a text file in a delimited – pipe separated format.
This class have all few set of names, country and currencies, amounts and payment modes in separate data structures (List and Map) – using which we will pickup some values at random to constitute an Order instance. We will generate a random number to be used as an index (pointer) to the corresponding data structure.
This script will generate a set of entries based on a max limit that is configured in the script itself – noOfRecords, which can be passed as a command line argument if needed. Otherwise this will have a default value of 5 – meaning 5 entries will be generated and stored in a text file.
TestOrderRandomBulk.groovy
package com.javacodegeeks.example.groovy.scripting
// a list of values for customer names
def namesList = ['Raghavan', 'Ganesh', 'Karthick', 'Sathish', 'Kanna', 'Raja',
'Karthiga', 'Manoj', 'Saravanan', 'Adi', 'Dharma', 'Jhanani',
'Ulags', 'Prashant', 'Anand']
// a Map to hold a set of countries and the currency for each
def countryCurrencyMap = ['India':'INR', 'Australia' : 'AUD', 'Bahrain' : 'BHD',
'Dubai' : 'AED', 'Saudi' : 'SAR', 'Belgium' : 'EUR',
'Canada' : 'CAD', 'Hong Kong' : 'HKD', 'USA' : 'USD',
'UK' : 'GBP', 'Singapore' : 'SGD']
// a list to hold random amount values
def amountList = ['1234.00', '1542.99', '111.2', '18920.11', '4567.00', '9877.12',
'2500.00', '50000.00', '6500.18', '7894.11', '1234.56', '1756.55', '8998.11']
// a list to hold different payment modes
def paymentModeList = ['Cash', 'Card', 'Cheque']
int noOfRecords = 5
println " "
// Take a default value of 5 for the total number of entries to be printed
// if nothing was specified in the command prompt while invoking the script
if(args.length>0) {
noOfRecords = Integer.parseInt(args[0])
} else {
println "Considering the default value for total # of entries"
}
println "No of entries to be printed : " + noOfRecords
def random = new Random()
def randomIntName, randomIntCountry, randomIntAmount, randomIntMode
def orderRandom, mapKey
def outputToWrite = ""
for(int i = 0; i
writer.writeLine outputToWrite
}
println ""
println "Contents are written to the file -> [$fileName] in the directory [$baseDir]"
println ""
// 'line' is an implicit variable provided by Groovy
new File(baseDir, fileName).eachLine {
line -> println line
}
println "----------------- "
println " == COMPLETED == "
println "----------------- "
The script produces the following output.
Considering the default value for total # of entries No of entries to be printed : 5 Contents are written to the file -> [outputTest.txt] in the directory [outputFiles] Manoj | USA | 9877.12 | USD | Cheque | Jhanani | Canada | 4567.00 | CAD | Cash | Kanna | Singapore | 9877.12 | SGD | Card | Karthiga | Saudi | 9877.12 | SAR | Cash | Saravanan | Australia | 8998.11 | AUD | Cheque | ----------------- == COMPLETED == -----------------
As you see, the script produced 5 different Order Instances with random values picked up from the respective data structure and stored each Order instance as a pipe separated values. Each order instance was accumulated and all the lines were stored in a text file – outputTest.txt in the directory outputFiles.
At the end of the script, we had read the same file outputTest.text and printed the contents of the file into screen – for our quick and easy verification.
5.4 Test Order – Random Entries at Intervals
We saw how to write a script to write a set of Order Instances into a text file. We will see how to generate a similar set of random entries at regular intervals by making the script sleep for a while in between. This is to simulate a real time environment where one system will be dumping the flat files at regular intervals in a shared location where the other system will be watching the files for processing further.
For the sake of brevity, we will make our script execute 2 iterations where each iteration will generate 5 random Order instances and store them in a separate text file whose names will have the timestamp appended for distinguishing with each other.
The script will generate the files in outputTest.txt_count_<yyyyMMdd_HHmmss> pattern. We have an utility method getNow() to give the present date and time in the prescribed format, which we will call everytime while writing to a new file. The output file will be written in the same directory, which will be processed by a next database script which, after processing will move the file into a different target directory to avoid these files being reprocessed in the subsequent iterations.
As usual the script below has a good documentation at every step that will be self explanatory for the readers to follow along.
TestOrderRandomInterval.groovy
package com.javacodegeeks.example.groovy.scripting
// a list of values for customer names
def namesList = ['Raghavan', 'Ganesh', 'Karthick', 'Sathish', 'Kanna', 'Raja',
'Karthiga', 'Manoj', 'Saravanan', 'Adi', 'Dharma', 'Jhanani',
'Ulags', 'Prashant', 'Anand']
// a Map to hold a set of countries and the currency for each
def countryCurrencyMap = ['India':'INR', 'Australia' : 'AUD', 'Bahrain' : 'BHD',
'Dubai' : 'AED', 'Saudi' : 'SAR', 'Belgium' : 'EUR',
'Canada' : 'CAD', 'Hong Kong' : 'HKD', 'USA' : 'USD',
'UK' : 'GBP', 'Singapore' : 'SGD']
// a list to hold random amount values
def amountList = ['1234.00', '1542.99', '111.2', '18920.11', '4567.00', '9877.12',
'2500.00', '50000.00', '6500.18', '7894.11', '1234.56', '1756.55', '8998.11']
// a list to hold different payment modes
def paymentModeList = ['Cash', 'Card', 'Cheque']
def getNow() {
new Date().format("yyyyMMdd_HHmmss")
}
// default value if nothing is specified during runtime at the command prompt
int noOfRecords = 5
println ""
// Take a default value of 5 for the total number of entries to be printed
// if nothing was specified in the command prompt while invoking the script
if(args.length>0) {
noOfRecords = Integer.parseInt(args[0])
} else {
println "Considering the default value for total # of entries : 10"
}
println "Total # of records to be printed : $noOfRecords"
def random = new Random()
def randomIntName, randomIntCountry, randomIntAmount, randomIntMode
def orderRandom, mapKey
// a variable to hold the output content to be written into a file
def outputToWrite = ""
//no of iterations this script will execute
def MAX_ITERATIONS = 2;
// interval to let this script sleep (1000ms=1s, here it will be for 1 min)
def SLEEP_INTERVAL = 1000*60*1;
// Directory to which the output files to be written
def baseDir = "."
// Prefix of the output file
def fileNamePrefix = 'outputTest.txt'
// Name of the output file which will vary in the loop below
def fileName = fileNamePrefix
println " "
println "[*] This script will write $noOfRecords records for $MAX_ITERATIONS iterations"
println " "
for(int iteration = 1; iteration <= MAX_ITERATIONS; iteration++)
{
/* Empty the buffer to avoid the previous stuff to be appended over and over */
outputToWrite = ""
for(int i = 0; i
writer.writeLine outputToWrite
}
println getNow() + " : Contents are written to the file -> [$fileName] in the directory [$baseDir]"
println ""
// 'line' is an implicit variable provided by Groovy
new File(baseDir, fileName).eachLine {
line -> println line
}
println getNow() + " ...... Script will sleep for ${SLEEP_INTERVAL} milliseconds [1000ms=1s]......"
println " "
sleep(SLEEP_INTERVAL)
println getNow() + " .............. Script awaken ......................"
println ""
}
println " "
println "----------------- "
println " == COMPLETED == "
println "----------------- "
The script produces the following output.
Considering the default value for total # of entries : 5 Total # of records to be printed : 5 [*] This script will write 5 records for 2 iterations 20160415_122040 : Contents are written to the file -> [outputTest.txt_1_20160415_122040] in the directory [.] Kanna | UK | 8998.11 | GBP | Card | Manoj | Australia | 9877.12 | AUD | Cash | Prashant | Saudi | 9877.12 | SAR | Card | Dharma | Australia | 1756.55 | AUD | Card | Raja | Belgium | 18920.11 | EUR | Cheque | 20160415_122040 ...... Script will sleep for 60000 milliseconds [1000ms=1s]...... 20160415_122140 .............. Script awaken ...................... 20160415_122140 : Contents are written to the file -> [outputTest.txt_2_20160415_122140] in the directory [.] Prashant | Canada | 111.2 | CAD | Cash | Dharma | UK | 50000.00 | GBP | Card | Kanna | Belgium | 50000.00 | EUR | Card | Saravanan | Hong Kong | 9877.12 | HKD | Cheque | Manoj | Singapore | 8998.11 | SGD | Cash | 20160415_122140 ...... Script will sleep for 60000 milliseconds [1000ms=1s]...... 20160415_122240 .............. Script awaken ...................... ----------------- == COMPLETED == -----------------
As explained above, the script had produced two different flat (text) files with the pattern outputTest.txt_ with a sequence number along with the date and time pattern to distinguish the files from each other. We had also retrieved the file contents and displayed in the console for our quick verification.
We will see in the next subsection how to process these order entries for storing them into the database.
6. Groovy Scripts for Database
We have completed our scripts for producing the flat files to cater to the business needs. Now we need a place to store the data values into a permanent storage – a Database. Like how we created a main POGO Order.groovy to use it as a Business Object for our business oriented scripts, we need a database table to store the specific attributes of an Order, which were carried in an instance of Order class.
We will see in the subsequent sub-sections how we will write the simple groovy scripts to create a database table, insert or store values into the table, retrieve the values from the table, truncate (or wipe off the entries) from the table for us to clean and start over again, drop the database etc.,
Please ensure you have the MySQL database installed in your machine and you have the MySQL JDBC Connector Jar file before you proceed with the rest of the scripts in this section.
You may read a suitable tutorial or the MySQL website for installing the database in to your machine.
6.1 Configuration file to hold the database properties
A database is a different entity and it needs to be connected to before we could actually do any operation with it. After finising our operation we should close the connection as a best practice so that the database can support further clients and connections in a better manner.
We need certain important properties about the database like the Server IP Address or hostname where the database is running, the port number to which the database service is listening for client requests, the database type – like Oracle, DB2, MySQL etc., , the actual name of the database we need to connect to, the JDBC (Java Database Connectivity) driver url using which the underlying Java based Programming Language will talk to the database.
As we will have a few different scripts dealing with the database and each of them will need the same properties, it is better to capture them in a separate file and be it referred in all the relevant scripts whichever needs those properties. The advantage of doing this practice is if at all we need to make a change in any of the properties, we can do so in a single place rather than changing in all the files where it was referred individually.
Before we could write the separate database related scripts, we should see the configuration file (or a properties file in Java terminology) as to how we can store the values. The below db.properties stores the properties in a key,value pair where each pair is entered in a separate line and each key value pair is stored in the format key=value, where key is the actual key we will be referring to pick up the value from the file and the value is the actual value of the property.
The configuration file can be of any extension but it is a standard practice to have it stored in the meaningful
.propertiesextension.
The lines starting with a # will be treated as a comment and will be ignored. It is more like a // in a Java like Programming language to indicate a meaningful description or a comment.
db.properties
# Properties needed to construct a JDBC URL programmatically inside a script db.type=mysql db.host=localhost db.port=3306 db.name=test # Username and password for authenticating the client everytime a database connection is obtained db.user=root db.pwd=root # The JDBC Driver class that needs to be loaded from the classpath (mysql-connector-x.y.z.jar) # which does the actual talking to the database from within the Java based Programming language db.driver=com.mysql.jdbc.Driver # Database table name db.tableName=GroovyScriptTest
6.2 Parsing the configuration file
We will write a script to parse the database configuration file so that we can read and process the values and get them stored in to respective variables of a class. This way we can use these variables from the class any number of times we need, otherwise we may need to read it from the configuration file which is typically be slow and a time consuming operation when compared to reading it from the Groovy object variable.
The below Groovy script will read the configuration file from the file system and parse the values and store them into respective variables. To make it efficient reading, we read them through static initializer blocks which will be executed only once during the class loading time. The values read will be retained until the script completes its execution or the JVM shuts down. Just to verify the properties being read, we will print them in the console via a helper method called printValues().
We will compile this Groovy class using groovyc DBProps.groovy so that the compiled .class file gets generated in a folder matching with com/javacodegeeks/example/groovy/scripting in the present directory. It will be referred by other scripts to load the Order class.
dbProperties.groovy
package com.javacodegeeks.example.groovy.scripting
class DBProps
{
// meaningful class level variables for each of the db properties
static String dbType, dbHost, dbPort, dbName, dbUser, dbPwd, dbUrl, dbDriver, dbTblName
// class level varaibles for the properties file
static String baseDir=".", propsFileName = 'db.properties'
// class level variables to hold the properties loaded
static def props = new Properties(), config
//static initializer block (gets invoked when the class is loaded and only once)
static
{
// Groovy's simple way to load a configuration file from a directory and
// create a Properties (java.util.Properties) instance out of it
new File(baseDir, propsFileName).withInputStream {
stream -> props.load(stream)
}
// ConfigSlurper supports accessing properties using GPath expressions (dot notation)
config = new ConfigSlurper().parse(props)
// Assign the value of each property through ConfigSlurper instance
dbType = config.db.type
dbName = config.db.name
dbHost = config.db.host
dbPort = config.db.port
dbUser = config.db.user
dbPwd = config.db.pwd
dbDriver = config.db.driver
dbTblName = config.db.tableName
dbUrl = 'jdbc:' + dbType + '://' + dbHost + ':' + dbPort + '/' + dbName
//assert dbUrl=='jdbc:mysql://localhost:3306/test'
}
static printValues()
{
println "Db Type : " + DBProps.dbType
println "Db Name : " + DBProps.dbName
println "Db Host : " + DBProps.dbHost
println "Db Port : " + DBProps.dbPort
println "Db User : " + DBProps.dbUser
println "Db Pwd : " + DBProps.dbPwd
println "Db URL : " + DBProps.dbUrl
println "JDBC Driver : " + DBProps.dbDriver
println "Table Name : " + DBProps.dbTblName
}
}
// To test the values through the same script
DBProps.printValues()
The script produces the following output.
Db Type : mysql Db Name : test Db Host : localhost Db Port : 3306 Db User : root Db Pwd : root Db URL : jdbc:mysql://localhost:3306/test JDBC Driver : com.mysql.jdbc.Driver Table Name : GroovyScriptTest
As you see, the values read from the configuration / properties file were stored into a respective variables of a class, which will be referred in different other scripts for the efficient and ease of use.
6.3 Create Table
We will now write a Groovy script to create a database table GroovyScriptTest to capture the Order related information for our example. This table will have a placeholder to store every bit of information about our Order – as we discussed in Business Object in the Order.groovy. However the data types and declaration in the database may be little different from the Programming language(s).
For executing the database scripts, you should specify the directory where the MySQL JDBC Jar file is present, as a classpath option while invoking the groovy script. For example, to invoke the below script the command should be
groovy -classpath C:\commonj2eelibs\mysql-connector-java-5.1.29-bin.jar dbCreateTable.groovy, where the mysql jar filemysql-connector-java-5.1.29-bin.jaris present in the directoryC:\commonj2eelibs\
dbCreateTable.groovy
package com.javacodegeeks.example.groovy.scripting
// Database related methods are present in Sql class
// we need to import the Sql class from groovy.sql package
import groovy.sql.Sql
// we read the database properties from DBProps class
// which had already read those values from the configuration file
def url = DBProps.dbUrl
def user = DBProps.dbUser
def password = DBProps.dbPwd
def driver = DBProps.dbDriver
def tableName = DBProps.dbTblName
// Groovy's way of executing SQL statement after obtaining a connection
// from database. 'withInstance' is a special construct that helps the
// connection closed automatically even in case of any errors
Sql.withInstance(url, user, password, driver) { sql ->
sql.execute '''
CREATE TABLE ''' + tableName + '''
(
Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(30),
COUNTRY VARCHAR(20),
PRICE FLOAT(8,2),
CURRENCY VARCHAR(3),
PAYMENT_MODE VARCHAR(10),
ORDER_DATE DATETIME DEFAULT CURRENT_TIMESTAMP
);
'''
println "Table [${tableName}] created successfully"
}
The script produces the following output.
Table [GroovyScriptTest] created successfully
The above script created a table named GroovyScriptTest in the MySQL database named ‘test’ with the columns Id being auto-generated primary key (unique value per row), Name of String datatype with maximum 30 characters limit, country of String datatype of maximum 20 characters limit, price of floating data type of 8 digits with 2 digits for precision, currency as a String datatype of 30 characters limit, Payment_Mode of String datatype of 10 characters limit and Order_date of datetime datatype with the default value of the current date and time in the System, if not specified.
6.4 Insert Rows from a Single File
Now that we had created a table in the MySQL database, let us write/ insert some data into the table from a flat file. We had already executed a script TestOrderRandomBulk.groovy where it had produced a flat file outputTest.txt in the directory outputFiles. The below script dbInsertValueSingleFile.groovyy will read the same flat file and insert into the GroovyScriptTest table.
dbInsertValueSingleFile.groovy
package com.javacodegeeks.example.groovy.scripting
import groovy.sql.Sql
// Properties of DBProps class parsed out of the configuration file
def url = DBProps.dbUrl
def user = DBProps.dbUser
def password = DBProps.dbPwd
def driver = DBProps.dbDriver
def tableName = DBProps.dbTblName
def baseDir="./outputFiles"
def fileName = 'outputTest.txt'
// a list to hold the list of valid (non-empty) lines in the flat file
def list = new File(baseDir, fileName).readLines()*.trim()
def orderList = []
list.each { it ->
// Consider ONLY the non-empty lines in the flat file
if(it.trim().length() > 0)
orderList.add(new Order().initOrder(it))
}
Sql.withInstance(url, user, password, driver) { sql ->
//process each line which is an Order Instance
orderList.each { it ->
sql.execute """
INSERT INTO ${Sql.expand(tableName)}
(NAME, COUNTRY, PRICE, CURRENCY, PAYMENT_MODE)
VALUES
(
${it.customerName}, ${it.country},
${it.price}, ${it.currency}, ${it.paymentMode}
);
"""
}
println "Total Rows Inserted to db : " + orderList.size()
}
println " "
println "----------------- "
println " == COMPLETED == "
println "----------------- "
The script produces the following output.
package com.javacodegeeks.example.groovy.scripting
The script produces the following output.
Total Rows Inserted to db : 5 ----------------- == COMPLETED == -----------------
The above script successfully read the flat file and inserted each line as a separate Order entry in the database table. The output confirmed that there were 5 Order instances successfully inserted.
Let us verify the values inserted in the database table with another script as discussed in the next subsection.
6.5 Select Total Count
Let us write a simple Groovy script to get the total number of rows present in the database table.
dbSelectTotal.groovy
package com.javacodegeeks.example.groovy.scripting
import groovy.sql.Sql
def url = DBProps.dbUrl
def user = DBProps.dbUser
def password = DBProps.dbPwd
def driver = DBProps.dbDriver
def tableName = DBProps.dbTblName
// We will have a explicit handle on sql instance through newInstance() method
// which will require us to manually close the sql instance after the work is done
def sql = Sql.newInstance(url, user, password, driver)
query="""Select COUNT(*) as TotalRows from """ + tableName
def totatlRows
sql.query(query) { resultSet ->
while(resultSet.next()) {
totalRows = resultSet.getString(1)
println "Total Rows in the table [${tableName}] : ${totalRows}"
}
}
// Sql.withInstance() automatically closes, else we need to manually close the sql connection)
sql.close()
The script produces the following output.
Total Rows in the table [GroovyScriptTest] :: 5
6.6 Select All Rows
Let us write yet another Groovy script to retrieve all the rows available in the GroovyScriptTest database table.
dbSelectRows.groovy
package com.javacodegeeks.example.groovy.scripting
import groovy.sql.Sql
def url = DBProps.dbUrl
def user = DBProps.dbUser
def password = DBProps.dbPwd
def driver = DBProps.dbDriver
def tableName = DBProps.dbTblName
def sql = Sql.newInstance(url, user, password, driver)
def query= '''select count(*) as total_rows from ''' + tableName
def totalRows
sql.query(query) { resultSet ->
while(resultSet.next()) {
totalRows = resultSet.getString(1)
println "Total Rows in the table [${tableName}] : ${totalRows}"
}
}
def fieldsList = "Id, Name, Country, Price, Currency, Payment_Mode, Order_Date"
query='''
Select
Id, Name, Country, Price, Currency, Payment_Mode, Order_Date
from
''' + tableName
// declare the individual variables to hold the respective values from database
def id, name, country, orderNo, price, currency, mode, orderDate
// a list to hold a set of Order Instances
def orderList = []
// a variable to hold an Order Instance prepared out of the values extracted from database
def order
sql.query(query) { rs ->
while(rs.next())
{
// extract each field from the resultset(rs) and
// store them in separate variables
id = rs.getString(1)
name = rs.getString(2)
country = rs.getString(3)
price = rs.getString(4)
currency = rs.getString(5)
mode = rs.getString(6)
orderDate = rs.getString(7)
// create an Order Instance from the values extracted
order = new Order('id' : id, 'customerName': name, 'country': country, 'price': price,
'currency' : currency, 'paymentMode' : mode, 'orderDate' : orderDate)
// add the order instances into a collection (List)
orderList.add(order)
}
}
// Prepare the column headers with a fixed width for each
def fieldListDisplay = String.format("%-5s", "Id") + " | " +
String.format("%-15s", "Customer Name") + " | " +
String.format("%-10s", "Country") + " | " +
String.format("%-8s", "Price") + " | " +
String.format("%-8s", "Currency") + " | " +
String.format("%-12s", "Payment Mode") + " | " +
String.format("%-21s", "Order Date") + " | "
// an utility method to display a dash ("-") for a visual aid
// to keep the data aligned during the display in the console
def printDashes(limit) {
for(int i = 0 ; i 0)
{
//print the Headers
println ""
def dashWidth = 102
printDashes(dashWidth)
println fieldListDisplay
printDashes(dashWidth)
// Print each of the order instances
// Here the toString() method will be invoked on the Order instance
orderList.each { it ->
println it
}
// To print a line with dashes to indicate the end of the display
printDashes(dashWidth)
}
// Dont' forget to close the database connection
sql.close()
The script produces the following output.
Total Rows in the table [GroovyScriptTest] :: 5 ------------------------------------------------------------------------------------------------------ Id | Customer Name | Country | Price | Currency | Payment Mode | Order Date | ------------------------------------------------------------------------------------------------------ 1 | Manoj | USA | 9877.12 | USD | Cheque | 2016-04-15 16:53:14.0 | 2 | Jhanani | Canada | 4567.00 | CAD | Cash | 2016-04-15 16:53:14.0 | 3 | Kanna | Singapore | 9877.12 | SGD | Card | 2016-04-15 16:53:14.0 | 4 | Karthiga | Saudi | 9877.12 | SAR | Cash | 2016-04-15 16:53:14.0 | 5 | Saravanan | Australia | 8998.11 | AUD | Cheque | 2016-04-15 16:53:14.0 | ------------------------------------------------------------------------------------------------------
As you see, the above script extracted all the 5 rows from the database table and created a separate Order Instance using the values, in an iteration. All those Order instances were printed with the help of toString() method that helps us to get the values of an Order Instance with a fixed width – to have a better visual aid while displaying. The values are displayed with the headers to make it meaningful to indicate which value belongs to which column.
Please observe the values for Id column where it is in sequence starting from 1 to 5. It was because we declared the syntax for the column while creating the database table as an auto generated, identity column.
6.7 Insert Rows from the matching files at interval
Now we had successfully inserted the records from a single flat file and also verified the total record count plus extracted all the rows available in a table. We will enhance the script further to make it process the files in iteration at regular intervals while letting the script sleep for a while. The duration for sleeping, number of iterations are configured in the script.
Note: This script will look for the files with the matching pattern outputTest.txt_count_<yyyyMMdd_HHmmss> which were generated out of the script TestOrderRandomInterval.groovy
For the brevity, the script will process two iterations and will exit. However, to make it execute indefinitely, you can make the while loop as follows.
while(true) { ... }
dbInsertValueInterval.groovy
package com.javacodegeeks.example.groovy.scripting
import groovy.sql.Sql
import groovy.io.FileType
def url = DBProps.dbUrl
def user = DBProps.dbUser
def password = DBProps.dbPwd
def driver = DBProps.dbDriver
def tableName = DBProps.dbTblName
def baseDir = "."
def targetDir="./outputFiles"
def fileNamePattern = 'outputTest.txt_'
def dir = new File(baseDir)
def list, fileName, fileNameMatches
def orderlist = []
// interval to let the script sleep (1000ms = 1s, here it indicates 1 minute)
def SLEEP_INTERVAL = 1000*60*1
def MAX_ITERATIONS = 2, i = 1
// an utility method to print the current date and time in a specified format
def getNow()
{
new Date().format("yyyyMMdd_HHmmss")
}
println " "
println "Script will try ${MAX_ITERATIONS} times for the matching input files while sleeping for ${SLEEP_INTERVAL/60000} minutes"
while(i++
fileName = file.name
fileNameMatches = false
if(fileName.startsWith(fileNamePattern))
{
println "Processing the file -> $fileName"
fileNameMatches = true
}
if(fileNameMatches)
{
// store all the lines from a file into a List, after trimming the spaces on each line
list = new File(baseDir, fileName).readLines()*.trim()
orderList = []
// Process only the valid (non-empty) lines and prepare an Order instance
// by calling the initOrder() method by passing the pipe delimited line of text
list.each { it ->
if(it.trim().length()>0)
orderList.add(new Order().initOrder(it))
}
Sql.withInstance(url, user, password, driver) { sql ->
orderList.each { it ->
sql.execute """
INSERT INTO ${Sql.expand(tableName)}
(NAME, COUNTRY, PRICE, CURRENCY, PAYMENT_MODE)
VALUES
(
${it.customerName}, ${it.country},
${it.price}, ${it.currency}, ${it.paymentMode}
);
"""
}
println "Total Rows Inserted to db : " + orderList.size()
}
// Move the processed file into a different directory
// so that the same file will NOT be processed during next run
"mv ${fileName} ${targetDir}".execute()
println "File ${fileName} moved to dir [${targetDir}] successfully."
}
}
println ""
println getNow() + " ... script will sleep for ${SLEEP_INTERVAL} milliseconds..."
println ""
sleep(SLEEP_INTERVAL)
println getNow() + " ......... script awaken ........ "
println ""
}
println " "
println "----------------- "
println " == COMPLETED == "
println "----------------- "
The script produces the following output.
Script will try 2 times for the matching input files while sleeping for 1 minutes ------------------------------------------------------------------------------------------------- 20160415_172226 =========== SCANNING for files matching with outputTest.txt_ =================== ------------------------------------------------------------------------------------------------- Processing the file -> outputTest.txt_1_20160415_122040 Total Rows Inserted to db : 5 File outputTest.txt_1_20160415_122040 moved to dir [./outputFiles] successfully. Processing the file -> outputTest.txt_2_20160415_122140 Total Rows Inserted to db : 5 File outputTest.txt_2_20160415_122140 moved to dir [./outputFiles] successfully. 20160415_172227 ... script will sleep for 60000 milliseconds... 20160415_172327 ......... script awaken ........ ------------------------------------------------------------------------------------------------- 20160415_172327 =========== SCANNING for files matching with outputTest.txt_ =================== ------------------------------------------------------------------------------------------------- 20160415_172427 ... script will sleep for 60000 milliseconds... 20160415_172427 ......... script awaken ........ ----------------- == COMPLETED == -----------------
You can verify the total values inserted by executing the dbSelectRows.groovy where it will display all the rows in the table with a fixed width column for each of the values as we saw in section 6.6.
6.8 Truncate Table
We will also see a Groovy Script to truncate the database table. Truncating helps to retain the table structure but wipe off (delete) all the rows in the table. It will be helpful whenever you want to start the operations afresh from the beginning.
Once the table is truncated successfully, you can insert the values again, for which the auto-generated Id value will start from 1.
dbTruncateTable.groovy
package com.javacodegeeks.example.groovy.scripting
import groovy.sql.Sql
def url = DBProps.dbUrl
def user = DBProps.dbUser
def password = DBProps.dbPwd
def driver = DBProps.dbDriver
def tableName = DBProps.dbTblName
Sql.withInstance(url, user, password, driver) { sql ->
sql.execute """TRUNCATE TABLE """ + tableName
println "Table [${tableName}] truncated successfully"
}
The script produces the following output.
Table [GroovyScriptTest] truncated successfully
6.9 Drop Table
At times we need to drop the table to start everything afresh. Let us write a simple Groovy script for dropping the table from the database.
Once the table is dropped successfully, you need to recreate the table by executing the script
dbCreateTable.groovybefore you can insert the values.
dbDropTable.groovy
package com.javacodegeeks.example.groovy.scripting
import groovy.sql.Sql
def url = DBProps.dbUrl
def user = DBProps.dbUser
def password = DBProps.dbPwd
def driver = DBProps.dbDriver
def tableName = DBProps.dbTblName
Sql.withInstance(url, user, password, driver) { sql ->
sql.execute '''DROP TABLE ''' + tableName
println "Table [${tableName}] dropped successfully"
}
The script produces the following output.
Table [GroovyScriptTest] dropped successfully
7. Conclusion
Hope you had enjoyed this article – Groovy Script Tutorial for Beginners. In this article we have seen how to write simple Groovy scripts to create a Business Object, generate random Order entries, write them into a text file, read the flat file and parse the order entries, read a property/configuration file for the database related values, create a database table, store values into a table, read the values from the table, truncate and drop the table.
Though the article aimed at a practical scenario, there may be few different conditions and best practices for a requirement if any different you have at hand. In such case, you are required to go through the documentation of the respective language constructs for a better scripting.
8. References
You may please refer the following URLs for further reading.
- Groovy Official Documentation
- MySQL Downloads
- MySQL JDBC Connector Jar Download
- Groovy String Example Java Code Geeks Example
- Groovy Array Example Java Code Geeks Example
- Groovy Closures Example Java Code Geeks Example
- Groovy List Example from Java Code Geeks Example
- Groovy Map Example from Java Code Geeks Example
- Groovy Working with IO
- Groovy Database Interaction
9. Download the Source Code
This is an example of how to write scripting in Groovy, tested with the Command Prompt / Shell and Groovy Console against Groovy Version 2.4.3.
You can download the full source code of this example here: Groovy Scripting Tutorial. Please read the ReadMe.txt file for how to execute the scripts.
