Jun 19 2009

Get column names from a dabase table using Groovy

Published by at 12:24 am under Software Development

Groovy has some great tools to simplify database queries. In this example I show how to fetch column names from a database table. This can be handy if you want to write a result set to a comma separated values (csv) file or to an Excel file using Apache POI and want the column names in the first line.

For this example, I am using MySQL but any database should work if you change the query accordingly. The code creates a connection to the INFORMATION_SCHEMA database and lists all the columns in the Tables table. Be sure to change db_user and db.user to a user name and password in your database.

def sql = groovy.sql.Sql.newInstance("jdbc:mysql://localhost:3306/INFORMATION_SCHEMA", "db_user","db.user", "com.mysql.jdbc.Driver")
println sql.rows("select * from TABLES limit 1")[0].keySet()

The column output from the Tables table should look something like this:

TABLE_SCHEMA=information_schema
TABLE_NAME=CHARACTER_SETS
TABLE_TYPE=SYSTEM VIEW
ENGINE=MEMORY
...

Note, in order to make a database connecation, a JDBC driver needs to be in your library path. The MySQL database driver can be downloaded at http://www.mysql.com/products/connector/.

One response so far

One Response to “Get column names from a dabase table using Groovy”

  1. John Doe says:

    Alternatively, firstRow() allow you to get the table columns without changing the query to accomodate for vendor specific syntax:

    println sql.firstRow(‘SELECT * FROM table”).keySet()

Leave a Reply