java 调用groovy脚本，实现多个sql按指定逻辑运行，可做报表预聚合

java 调用groovy脚本，实现多个sql按指定逻辑运行，可做报表预聚合

1、引入pom

		<dependency>
            <groupId>org.codehaus.groovy</groupId>
            <artifactId>groovy</artifactId>
            <version>2.1.6</version>
        </dependency>
        <dependency>
            <groupId>org.codehaus.groovy</groupId>
            <artifactId>groovy-jsr223</artifactId>
            <version>2.1.6</version>
        </dependency>

2、调用脚本

其中，getConnection()方法需自己实现，就是简单的根据用户名密码获取数据库连接

    public static void main(String[] args) throws Exception {
        ScriptEngineManager factory = new ScriptEngineManager();
        ScriptEngine engine = factory.getEngineByName("groovy");
        Bindings binding = engine.createBindings();
        binding.put("connection", getConnection());// 绑定jdbc链接
        String script = getScript();
        engine.eval(script, binding);// 绑定脚本和connection
        Object res = ((Invocable) engine).invokeFunction("execute", null);// 执行
        System.out.println(res);
        List<Map<String, Object>> list = getList((ResultSet)res); // 转换结果list
        System.out.println(list);
    }

	// 返回groovy脚本语言，这个只做示例，里面可以写各种逻辑，且不局限于mysql
    private static String getScript(){
        return "def execute(){return connection.prepareStatement('select * from business').executeQuery();}";
    }

	// executeQuery方法返回的对象转成list
    private static List<Map<String, Object>> getList(ResultSet resultSet) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        while (resultSet.next()) {
            Map<String, Object> map = new HashMap<>();
            String colName;
            for (int i = 0; i < cols_len; i++) {
                map.put(colName = metaData.getColumnLabel(i + 1), resultSet.getString(colName));
            }
            list.add(map);
        }
        return list;
    }