groovy mysql 示例

最新推荐文章于 2024-04-30 20:39:11 发布

_bliu

最新推荐文章于 2024-04-30 20:39:11 发布

阅读量164

点赞数

CC 4.0 BY-SA版权

分类专栏： groovy code examle 文章标签： mysql

本文链接：https://blog.csdn.net/lsk_shah/article/details/117297586

groovy code examle 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何使用Groovy和Java SQL进行数据库操作，重点展示了一个名为SqlHelper的类，用于查找表中列值超过特定阈值的记录。通过实例代码，展示了如何查询`relationtable`表中port为53455的表名，并针对每个表进行列值检查。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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}"