Groovy + MySQL (insert if not exist)

Groovy+MySQL.md

With User Input

import groovy.sql.GroovyResultSet
import groovy.sql.Sql

import javax.swing.JOptionPane

class Test {

    static void main(String[] arg) {

        // create table vicky (id int auto_increment, pid varchar(10), eid varchar(10), aid varchar(10), Primary key (id));
        
        def dialog = JOptionPane.&showInputDialog

        String pid = dialog 'Enter pid'
        String eid = dialog 'Enter eid'
        String aid = dialog 'Enter aid'

        Map dbConnParams = [
                url: 'jdbc:mysql://localhost:3306/vicky',
                user: 'root',
                password: 'root',]

        def sql = Sql.newInstance(dbConnParams)

        def ids = sql.executeInsert """
            INSERT INTO vicky (pid, eid, aid) 
            SELECT * FROM (SELECT ${pid}, ${eid}, ${aid}) AS tmp 
            WHERE NOT EXISTS 
            (SELECT id FROM vicky WHERE pid = ${pid} and eid = ${eid} and aid = ${aid}) 
            LIMIT 1;
        """

        println ids

        sql.eachRow("SELECT * FROM vicky") { GroovyResultSet rs ->
            println(rs)
        }

        sql.close()
    }
}

Batch Insert with Map

import groovy.sql.Sql

class Test {

    static void main(String[] arg) {

        Map dbConnParams = [
                url: 'jdbc:mysql://localhost:3306/vicky',
                user: 'root',
                password: 'root',]

        def sql = Sql.newInstance(dbConnParams)

        Map map = [1 : [pid: 'p1', eid: 'e1', aid: 'a1'],
                   2 : [pid: 'p1', eid: 'e1', aid: 'a2'],
                   3 : [pid: 'p1', eid: 'e1', aid: 'a3'],
                   4 : [pid: 'p2', eid: 'e1', aid: 'a1'],
                   5 : [pid: 'p2', eid: 'e1', aid: 'a2'],
                   6 : [pid: 'p3', eid: 'e1', aid: 'a3'],
                   7 : [pid: 'p3', eid: 'e2', aid: 'a4'],
                   8 : [pid: 'p4', eid: 'e2', aid: 'a4'],
                   9 : [pid: 'p5', eid: 'e5', aid: 'a5'],
                   10 : [pid: 'p6', eid: 'e2', aid: 'a6'],
                   11 : [pid: 'p10', eid: 'e10', aid: 'a10'],
                   12 : [pid: 'p11', eid: 'e1', aid: 'a11'],
                   13 : [pid: 'p12', eid: 'e1', aid: 'a11'],
                   14 : [pid: 'p13', eid: 'e1', aid: 'a11'],
                   15 : [pid: 'p14', eid: 'e1', aid: 'a11'],
                   16 : [pid: 'p15', eid: 'e1', aid: 'a11'],
                   17 : [pid: 'p11', eid: 'e1', aid: 'a12'],
                   18 : [pid: 'p12', eid: 'e1', aid: 'a12'],
                   19 : [pid: 'p13', eid: 'e1', aid: 'a12'],
                   20 : [pid: 'p14', eid: 'e1', aid: 'a12'],
                   21 : [pid: 'p15', eid: 'e1', aid: 'a12'],
                   22 : [pid: 'p16', eid: 'e1', aid: 'a13'],
                   23 : [pid: 'p17', eid: 'e1', aid: 'a13'],
                   24 : [pid: 'p18', eid: 'e1', aid: 'a13'],
                   25 : [pid: 'p18', eid: 'e1', aid: 'a13']]

        sql.withBatch(10, """
            INSERT INTO vicky (pid, eid, aid) 
            SELECT * FROM (SELECT :pid, :eid, :aid) AS tmp 
            WHERE NOT EXISTS 
            (SELECT id FROM vicky WHERE pid = :pid and eid = :eid and aid = :aid) 
            LIMIT 1;
        """) { ps ->
            map.each { k,v ->
                println v
                ps.addBatch(pid:v.pid, eid:v.eid, aid: v.aid)
            }
        }

        sql.close()
    }
}

<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy-all</artifactId>
    <version>2.4.13</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.22</version>
</dependency>