Read from a database and write into an xml using Groovy

You may come across a use-case where you need to read some data from a database and generate an xml report or file that needs to be sent across to be processed by some other system.

Doing this using a java program could be relatively overwhelming; with Groovy you can do this in a matter of minutes!

Let's take a look at how you can convert data returned from a sql query into an xml file.




For this example, i am fetching some employee details from a MySQL database. The details of the database are read from a property file. The database i have used for this sample has two tables Employee and Department.

import groovy.sql.Sql
import groovy.xml.MarkupBuilder
import java.text.SimpleDateFormat
import java.util.Properties

class DatabaseToXML {
  
  static void main(args) {
  
  SimpleDateFormat runDateformat = new SimpleDateFormat("yyyyMMdd")
  String runDate = runDateformat.format(new Date())
          
  def props = new Properties()
     new File('application.properties').withInputStream {
        stream -> props.load(stream)
     }

  String outFolderName = (props["OUTPUT_FOLDER"]==null)?"."+File.separatorChar : props["OUTPUT_FOLDER"]
  File outputFolder = new File(outFolderName)
  if(!outputFolder.exists())
      outputFolder.mkdir()

  String fileDate = runDateformat.format(new Date())
  String empFileName = outFolderName + File.separatorChar + "employees_" + runDate + ".xml"

def sql = Sql.newInstance(props["DB_URL"],props["USERNAME"],props["PASSWORD"], props["DRIVERCLASS"])
 
  def empQuery = """
        SELECT e.id, e.first_name, e.last_name, e.email_address, e.designation,
    d.dept_name FROM employee e, department d
    where d.id = e.dept_id
        """

  def xml = new MarkupBuilder(new FileWriter(new File(empFileName)))

  xml.employees(){
      run_date(fileDate)
      sql.eachRow( empQuery as String  ) {
         row ->
            employee(){
            first_name(row.first_name)
            last_name(row.last_name)
            email_address(row.email_address)
            designation(row.designation)
            dept_name(row.dept_name)                     
          }
       }
    }
  }
}

That was easy! The 'Sql.newInstance ...' statement connects to the database using the parameters passed to it and stores it in the variable 'sql'. We then use the sql.eachRow to fetch the rows from the database. To create the xml file we are using the MarkupBuilder class that supports Groovy's builder pattern. Find more options here.


To make this script you would need to add the mysql connector lib to the classpath.

SET CLASSPATH=./lib/mysql-connector-java-5.1.12-bin.jar

Run the script using the command :

groovy DatabaseToXML.groovy

 The xml file in my case looks like this:

Download the source code for the above example here: db_to_xml.rar


Hope you find this useful. Happy coding!