Mostly about Software Development and Data Science

I have been using AppFuse 2.0 to create some applications that read from legacy databases. Under AppFuse’s standard configuration, you can populate the development application with sample data by adding data to the sample-data.xml file located in the src\test\resources directory. In my case, I have several data tables that contain appropriate sample data, so my real chore has how to easily get a subset of that data into the sample-data.xml file.

Groovy to the rescue.  Groovy has an XML MarkupBuilder and supports metaprogramming such that I was able to create a relatively generic script that would create an XML file in the proper format, the contents of which I could paste into sample-data.xml.

Here is how to use it:

1. Install Groovy if you have not already. Easy to do, the download and all the instructions you need are at http://groovy.codehaus.org/.
2. Copy your database jar file to the lib directory of your groovy installation. In my case, I use Oracle and I have groovy installed in C:\sdks\groovy-1.5.5\, so I copied ojdbc14.jar to C:\sdks\groovy-1.5.5\lib.
3. Create a groovy script file, let’s call it create-sample-data.groovy. Copy and paste the code below to your script file:

   import groovy.sql.Sql
   import groovy.xml.MarkupBuilder
   
   def tableName = "customers"
   def sqlString = "SELECT * FROM customers"
   def fileName = "customers-sample-data.xml"
   
   def fw = new FileWriter(fileName)
   
   def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE", "username", "password", "oracle.jdbc.OracleDriver")
   
   def getColumns(sql, sqlString) {
     columnList = new ArrayList()
     sql.query(sqlString){ rs ->
       def meta = rs.metaData
       if (meta.columnCount <= 0) return
         for (i in 0..< meta.columnCount) {
           columnList += "${meta.getColumnLabel(i+1)}"
         }
       }
       columnList
   }
   
   columnList = getColumns(sql,sqlString)
   
   def xml = new groovy.xml.MarkupBuilder(fw)
   
   xml.table (name:"${tableName}") {
     for(int i = 0; i < columnList.size(); i++)
     {
       column(columnList[i])
     }  
   
     sql.eachRow(sqlString) { myRowData ->
       xml.row {
         for(int i = 0; i < columnList.size(); i++)
         {
           value(description:columnList[i], myRowData[i] )
         }
       }
     }
   }
   

4. Edit the line def tableName = "customers", replacing "customers" with the actual table name for which you want to populate sample data in AppFuse.
5. Edit the line def sqlString = "SELECT * FROM customers", replacing it with your own SQL statement. Note that we're grabbing sample data, not referring to our actual development table. The sql select statement can be anything that you want, perhaps joining several legacy tables to provide the data you want.
6. Edit the line def fileName = "customers-sample-data.xml", replacing it with whatever destination file name you want.
7. One last edit, change the line:
   def sql = sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE", "username", "password", oracle.jdbc.OracleDriver")
   to match your appropriate database connection string.
8. That's it. From a command prompt, type "groovy create-sample-data.groovy" and you should have your sample data in a file from which you can paste the contents into AppFuse's sample-data.xml file. Nuthin' to it!

I won't claim my code is the prettiest, but it works. I haven't tested it extensively, but it appears to work for all my needs, and all I have to do is change a few lines to create data for a new table. If the formatting of dates or numbers is not suitable, this can readily be fixed by performing the formatting within the SQL string itself. Hope you enjoy it