Running SQL queries from liferay's groovy console

DXP70-runSQL.groovy

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import com.liferay.portal.kernel.dao.jdbc.DataAccess;

Connection con = null;
Statement st = null;

def query = "show tables"
def printColumns = true

try {
    con = DataAccess.getUpgradeOptimizedConnection();
    st = con.createStatement();
    ResultSet rs = st.executeQuery(query);
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();

    def columnNames = []
    for (int i = 1; i <= columnCount; i++ ) {
        columnNames << rsmd.getColumnName(i);
    }

    if (printColumns) {
        println(columnNames.join(" | "));
    }

	while (rs.next()) {
        if (printColumns) {
            vals = []
            for (int i = 1; i <= columnCount; i++ ) {
                vals << rs.getString(i)
            }
            println(vals.join(" | "));
        } else {
            for (int i = 1; i <= columnCount; i++ ) {
                println(columnNames[i - 1] + ": " + rs.getString(i));
            }
            println('==================================================');
        }
    }
}
finally {
	DataAccess.cleanUp(con, st);
}