groovy mysql 示例

package com.bliu.demo
@GrabConfig(systemClassLoader = true)
@Grab(group = 'mysql', module = 'mysql-connector-java', version = '5.1.6')
import com.beust.jcommander.ParameterException
import groovy.sql.Sql
import java.sql.SQLException
import java.util.stream.Collectors
//db settting
def url = 'jdbc:mysql://localhost:3306/xxx'
def user = 'xxx'
def password = 'xxxl'
def driver = 'com.mysql.jdbc.Driver'
def sql = Sql.newInstance(url, user, password, driver)
class SqlHelper {
    Sql sql
    def retMap = [:]
    // 查找表中列值超过某个值的列
    void findUnValidates(String tableName, int max) throws ParameterException {
        if (!tableName?.trim()) {
            throw new ParameterException("table can't be null or ''")
        }
        retMap[tableName] = []
        def col = (0..15 as List).stream().map(it -> "F${it}").collect Collectors.toList()
        String sqlStr = """
            select ${col.join(',')} ,
                cur_time from ${tableName}
            where cur_time > :cur_time
        """
        sql.eachRow(sqlStr, [cur_time: '2021-05-01 09:04:23']) { row ->
            //each row
            for (int i in 0..15) {
                //each col
                if (row[i].getClass() == Integer.class
                        && row[i] > max) {
                    //may add same col
                    retMap[tableName].add "F${i}"
                }
            }
        }
    }
}
def sqlHelper = new SqlHelper(sql: sql)
def TableName, t1, t2
t1 = System.currentTimeMillis()
try {
    sql.query('SELECT `table_name` FROM relationtable WHERE port = 53455') { resultSet ->
        while (resultSet.next()) {
            TableName= resultSet.getString(1)
            sqlHelper.findUnValidates(TableName, 10000)
        }
    }
} catch (SQLException e) {
    e.printStackTrace()
} catch (Exception e1) {
    e1.printStackTrace()
}
sqlHelper.retMap.eachWithIndex { k, v, i ->
    println "${i}:${k} ${v.unique()}"
}
t2 = System.currentTimeMillis()
println "spend time ${t2 -t1}"