groovy-200px

When we migrate data at SRMvision, we use Groovy to focus on the migration logic without loosing time with heavy syntax and POJO mapping. Groovy is a great tool to do this, and its out of the box Sql handling is really very good. I found myself having the need to insert data to multiple existing table filling all columns.

While we can do it easily, it can become a mess rapidly when there is a lot of columns to handle. With this little snippet, you can leverage Groovy's maps to get a solid insertIntoTable

static def insertIntoTable(String tableName, 
                           Map paramMap, 
                           final Sql sql) {
        sql.executeInsert("""
             INSERT INTO ${tableName} 
                    (${paramMap.keySet().join(',')})
                    VALUES 
                    (${paramMap.keySet().collect { key -> ":" + key }.join(',')})
        """, paramMap)
}

To use it, one can simply call it this way (with the sql object correctly bound to a connection)

def paramMap = [ 
    id : 1, 
    myFirstColumn : "My first value", 
    mySecondColumn : "Second" 
]
insertIntoTable("MyTable", paramMap, sql)
comments powered by Disqus