Groovy - Batch queries with map or list

Question

I am new to Groovy (2.4.7) and this is what I am trying to do. Basically, the goal is to load data from Excel into SQL Server. There are two lists, one is the key and the other one is the value. How do I call these two lists to run a batch SQL statements?

Here is what I have so far:

def k = [Field1,Field2,Field3,Field4,Field5]

def Value1 = 1
def Value2 = 2
def Value3 = 3
def Value4 = 4
def Value5 = 5

def v = [Value1,Value2,Value3,Value4,Value5]

def query = """\
INSERT INTO TableA
    ($k)
VALUES
    ($v)
"""

sql.withTransaction {
    sql.withBatch(20) {stmt ->
        stmt.addBatch(query)
    }
}

By doing this way, it seems like Groovy will treat the whole list as a big string. I can output the list as string and remove the single quote but that doesn't seem cool. Can we parameterize the fields inside the list?
For the k list, do I need to have single quotes for all the fields like below because it gave me errors:

def k = ['Field1','Field2','Field3','Field4','Field5']

I know the field names should be strings but the way below worked to run one SQL query at a time. I feel like to modify the list is better than mess up with the query. But please stop me if I should not do this way.
```
def query = """\
    INSERT INTO TableA
        (Field1,Field2,Field3,Field4,Field5)
    VALUES 
        (${Value1},${Value2},${Value3},${Value4},${Value5})
"""
sql.execute(query)
```
I can even add these two lists into a map (See below). Will this be a better way to run batch queries? I just don't know how to utilize the map in this project.

def m = [k, v].transpose().collectEntries {it}

Of course, the method I came up with may not be ideal and I also want to prevent from any SQL Injection. I am open to any best practice if you can provide me a sample code to start with. Again, thanks for your time and help.

score 0 · Accepted Answer · answered Nov 18 '16 at 17:03

Okay, here is what I have figured out and it worked. This will also work for dynamic fields. From what I have learned so far, this may not be a good answer due to SQL Injection but this is only use internally with authroized person. But welcome to anyone's opinion to improve this method.

def sheet1 // Need to read in your Excel sheet
def sql = Sql.newInstance(// Your SQL connection config)

def tableName = "TableA"

def k = ['Field1','Field2','Field3','Field4','Field5']

// Code below is from http://stackoverflow.com/questions/20150880/insert-a-hashmap-into-any-database-table/20151521#20151521                
StringBuilder query = new StringBuilder("INSERT INTO ").append(tableName).append(" (")
StringBuilder placeholders = new StringBuilder()

for (Iterator<String> iter = k.iterator(); iter.hasNext();) {
    query.append(iter.next())
    placeholders.append("?")

    if (iter.hasNext()) {
        query.append(",")
        placeholders.append(",")
    }
}

query.append(") VALUES (").append(placeholders).append(")")

sql.withTransaction {

    sql.withBatch(20, query.toString()) {ps ->
        sheet1.each {data ->

            def Value1 = 1
            def Value2 = 2
            def Value3 = 3
            def Value4 = 4
            def Value5 = 5

            def v = [Value1,Value2,Value3,Value4,Value5]

            ps.addBatch(v)
        }
    }
}

sql.close()

Groovy - Batch queries with map or list

1 Answers1