groovy получение данных из БД + парсинг значений

groovy-Получение данных из БД + парсинг значений

[code:]groovy[:code]
package ru.ezhov

import groovy.sql.Sql
import java.util.regex.*

class ParserQueryUser {
    
    private static def PATTERN_STRING_BASIC = '([Ff][Rr][Oo][Mm] {1,}(\\[?\\w+\\]?\\.?){0,2}\\[?[А-яа-я\\w]+\\]?)|([Jj][Oo][Ii][Nn] {1,}(\\[?\\w+\\]?\\.?){0,2}\\[?[А-яа-я\\w]+\\]?)'
    private static def PATTERN_STRING_REMOVE = '[Ff][Rr][Oo][Mm] +|[Jj][Oo][Ii][Nn] +'
    
    private static def PATTERN_CLASS = Pattern.compile(PATTERN_STRING_BASIC)
    
    public static void main(String[] args)
    {
        //подключаем dll
        ActiveLibraryPath.setLibraryPath('''E:/java_library/jdbc_drivers_sql/mssql/auth/x64''')
        def parser = new ParserQueryUser()
        parser.execute()
    }
    
    void execute(){
        //настраиваем подключения
        def url = 'jdbc:sqlserver://;servername=prod1;integratedSecurity=true'
        def driver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
        def sql = Sql.newInstance(url,driver)

        def lastUser = null
        
        def List<UserQuery> usersHashSet = new ArrayList()
        //получаем данные
        sql.query('''
            select 
                    * 
            from OTZ.dbo.T_E_activeUserSessionsLog 
            where 
                    sqlscript is not null 
                    and 
                    login !='' 
                    and 
                    local_net_address is not null 
            order by login
            '''){ resultSet ->
            
            def Set setUserLoginTables;
            while (resultSet.next()) {
                def login = resultSet.getString('login')
                def sqlscript = resultSet.getString('sqlscript')
                
                if (lastUser != login){

                    if (lastUser != null){
                        usersHashSet << new UserQuery(lastUser, setUserLoginTables)
                    }
                    setUserLoginTables = new HashSet() 
                    lastUser = login
                }
                
                def mapScriptForUser = parseSqlScript(sqlscript)
                setUserLoginTables = setUserLoginTables + mapScriptForUser
            }
        }

        def c = {
            it.printObject()
        }
        
        usersHashSet.each(c)        
        
        def deleteQuery = 'delete from OTZ.dbo.T_E_userTablesUse'
        sql.execute deleteQuery
        
        
        def insertQuery = 'INSERT INTO OTZ.dbo.T_E_userTablesUse (login, tableName) VALUES (?,?)'
        
        def insertClosure = {
            def String username = it.user
            def tables =  it.mapQuery
            
            sql.withBatch(10, insertQuery)  {
                ps ->
                for(String table : tables){
                    println("вносим строку: [пользователь - ${username}] [таблица - ${table}]")
                    def params = [username, table]
                    ps.addBatch(params)
                }
            }
            
        }
        usersHashSet.each(insertClosure)   
        
        sql.close()
    }
    
    Set parseSqlScript(def sqlScript){
        def Matcher matches = PATTERN_CLASS.matcher(sqlScript);
  
        def Set mySet = new HashSet()
        
        def String findString
        while (matches.find()){
            findString = matches.group()
            findString = findString.replaceAll(PATTERN_STRING_REMOVE, "")
            mySet << findString.trim()
        }
        return mySet
    }
}

class UserQuery{
    private String user                      
    private Set mapQuery
    
    UserQuery(String user, Set mapQuery){
        this.user = user;
        this.mapQuery = mapQuery;
    }
    
    void printObject(){
        println(user + ' -> ')
        mapQuery.each({
                print('\t')
                println("${it}")
            })
    }
}


[/code]

[code:]groovy[:code]

package ru.ezhov

import java.lang.reflect.Field;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ActiveLibraryPath
{
    private static final Logger LOG = Logger.getLogger(ActiveLibraryPath.class.getName());

    static final synchronized void setLibraryPath(String path)
    {
        try
        {
            System.setProperty("java.library.path", path);
            Field fieldSysPath = ClassLoader.class.getDeclaredField("sys_paths");
            fieldSysPath.setAccessible(true);
            fieldSysPath.set(null, null);
        } catch (Exception ex)
        {
            LOG.log(Level.SEVERE, "error load library path", ex);
        }
    }
}

[/code]