Java tutorial
/** * Licensed to JumpMind Inc under one or more contributor * license agreements. See the NOTICE file distributed * with this work for additional information regarding * copyright ownership. JumpMind Inc licenses this file * to you under the GNU General Public License, version 3.0 (GPLv3) * (the "License"); you may not use this file except in compliance * with the License. * * You should have received a copy of the GNU General Public License, * version 3.0 (GPLv3) along with this library; if not, see * <http://www.gnu.org/licenses/>. * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. */ package org.jumpmind.vaadin.ui.sqlexplorer; import static org.jumpmind.vaadin.ui.sqlexplorer.Settings.SQL_EXPLORER_AUTO_COMMIT; import static org.jumpmind.vaadin.ui.sqlexplorer.Settings.SQL_EXPLORER_DELIMITER; import static org.jumpmind.vaadin.ui.sqlexplorer.Settings.SQL_EXPLORER_IGNORE_ERRORS_WHEN_RUNNING_SCRIPTS; import static org.jumpmind.vaadin.ui.sqlexplorer.Settings.SQL_EXPLORER_MAX_RESULTS; import static org.jumpmind.vaadin.ui.sqlexplorer.Settings.SQL_EXPLORER_RESULT_AS_TEXT; import java.io.Serializable; import java.io.StringReader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.sql.DataSource; import org.apache.commons.io.IOUtils; import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.exception.ExceptionUtils; import org.jumpmind.db.platform.DatabaseNamesConstants; import org.jumpmind.db.sql.JdbcSqlTemplate; import org.jumpmind.db.sql.SqlScriptReader; import org.jumpmind.properties.TypedProperties; import org.jumpmind.vaadin.ui.common.CommonUiUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.vaadin.server.FontAwesome; import com.vaadin.shared.ui.label.ContentMode; import com.vaadin.ui.Component; import com.vaadin.ui.Label; import com.vaadin.ui.Panel; import com.vaadin.ui.VerticalLayout; public class SqlRunner extends Thread { protected static final Logger log = LoggerFactory.getLogger(SqlRunner.class); private SqlExplorer explorer; private static List<SqlRunner> sqlRunners = new ArrayList<SqlRunner>(); private ISqlRunnerListener listener; private QueryPanel queryPanel; private boolean isInQueryGeneralResults; private boolean runAsScript; private String sqlText; private Connection connection; private Date startTime = new Date(); private Date endTime = null; private boolean rowsUpdated = false; private boolean createdConnection = true; private boolean showSqlOnResults = true; private IDb db; private String user; private boolean autoCommit; private boolean logAtDebug; private static final String COMMIT_COMMAND = "commit"; private Settings settings; private boolean isCanceled = false; private PreparedStatement stmt; public static List<SqlRunner> getSqlRunners() { return sqlRunners; } public SqlRunner(String sqlText, boolean runAsScript, String user, IDb db, Settings settings) { this(sqlText, runAsScript, user, db, settings, null, null); } public SqlRunner(String sqlText, boolean runAsScript, String user, IDb db, Settings settings, ISqlRunnerListener listener) { this(sqlText, runAsScript, user, db, settings, null, listener); } public SqlRunner(String sqlText, boolean runAsScript, String user, IDb db, Settings settings, SqlExplorer explorer) { this(sqlText, runAsScript, user, db, settings, explorer, null); } public SqlRunner(String sqlText, boolean runAsScript, String user, IDb db, Settings settings, SqlExplorer explorer, ISqlRunnerListener listener) { this(sqlText, runAsScript, user, db, settings, explorer, listener, null, false); } public SqlRunner(String sqlText, boolean runAsScript, String user, IDb db, Settings settings, QueryPanel queryPanel, boolean isInQueryGeneralResults) { this(sqlText, runAsScript, user, db, settings, null, null, queryPanel, isInQueryGeneralResults); } public SqlRunner(String sqlText, boolean runAsScript, String user, IDb db, Settings settings, SqlExplorer explorer, ISqlRunnerListener listener, QueryPanel queryPanel, boolean isInQueryGeneralResults) { this.setName("sql-runner-" + getId()); this.sqlText = sqlText; this.runAsScript = runAsScript; this.db = db; this.listener = listener; this.settings = settings; this.autoCommit = settings.getProperties().is(SQL_EXPLORER_AUTO_COMMIT); this.user = user; this.explorer = explorer; this.queryPanel = queryPanel; this.isInQueryGeneralResults = isInQueryGeneralResults; sqlRunners.add(0, this); } public void setLogAtDebug(boolean logAtDebug) { this.logAtDebug = logAtDebug; } public void setShowSqlOnResults(boolean showSqlOnResults) { this.showSqlOnResults = showSqlOnResults; } public static void commit(Connection connection) throws SQLException { if (connection != null) { try { connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { } throw e; } finally { try { connection.setAutoCommit(true); } catch (SQLException e) { } JdbcSqlTemplate.close(connection); } } } public static void rollback(Connection connection) { if (connection != null) { try { connection.rollback(); } catch (SQLException e) { // do nothing } finally { try { connection.setAutoCommit(true); } catch (SQLException e) { } JdbcSqlTemplate.close(connection); } } } public Date getStartTime() { return startTime; } public Date getEndTime() { return endTime; } @Override public void run() { TypedProperties properties = settings.getProperties(); boolean resultsAsText = properties.is(SQL_EXPLORER_RESULT_AS_TEXT); int maxResultsSize = properties.getInt(SQL_EXPLORER_MAX_RESULTS); String delimiter = properties.get(SQL_EXPLORER_DELIMITER); boolean ignoreWhenRunAsScript = properties.is(SQL_EXPLORER_IGNORE_ERRORS_WHEN_RUNNING_SCRIPTS); List<Component> resultComponents = new ArrayList<Component>(); FontAwesome icon = FontAwesome.CHECK_CIRCLE; rowsUpdated = false; boolean committed = false; boolean autoCommitBefore = true; try { DataSource dataSource = db.getPlatform().getDataSource(); JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) db.getPlatform().getSqlTemplate(); stmt = null; StringBuilder results = new StringBuilder(); try { if (connection == null) { connection = dataSource.getConnection(); connection.setAutoCommit(autoCommit); } autoCommitBefore = connection.getAutoCommit(); if (connection.getTransactionIsolation() != sqlTemplate.getIsolationLevel()) { connection.setTransactionIsolation(sqlTemplate.getIsolationLevel()); } if (sqlTemplate.isRequiresAutoCommitFalseToSetFetchSize()) { connection.setAutoCommit(false); } SqlScriptReader sqlReader = null; try { sqlReader = new SqlScriptReader(new StringReader(sqlText)); sqlReader.setDelimiter(delimiter); String sql = sqlReader.readSqlStatement(); while (sql != null) { JdbcSqlTemplate.close(stmt); if (db.getPlatform().getName().equals("voltdb")) { stmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); } else { stmt = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); } String lowercaseSql = sql.trim().toLowerCase(); if (!lowercaseSql.startsWith("delete") && !lowercaseSql.startsWith("update") && !lowercaseSql.startsWith("insert")) { if (db.getPlatform().getName().equals(DatabaseNamesConstants.MYSQL)) { stmt.setFetchSize(Integer.MIN_VALUE); } else { stmt.setFetchSize(maxResultsSize < 100 ? maxResultsSize : 100); } } if (logAtDebug) { log.debug("[" + db.getName() + "] Executing: {}", sql.trim()); } else { log.info("[" + db.getName() + "] Executing: {}", sql.trim()); } if (sql.replaceAll("\\s", "").equalsIgnoreCase(COMMIT_COMMAND)) { committed = true; } else { committed = false; } boolean hasResults = false; try { hasResults = stmt.execute(); } catch (SQLException e) { if (runAsScript && ignoreWhenRunAsScript) { results.append(sql); results.append("\n"); results.append(buildErrorMessage(e)); results.append("\n"); results.append("\n"); } else { throw e; } } int updateCount = stmt.getUpdateCount(); while (hasResults || updateCount != -1) { ResultSet rs = null; try { if (hasResults) { rs = stmt.getResultSet(); if (!runAsScript) { if (!resultsAsText) { resultComponents.add(new TabularResultLayout(explorer, db, sql, rs, listener, user, settings, queryPanel, showSqlOnResults, isInQueryGeneralResults)); } else { resultComponents.add(putResultsInArea(stmt, maxResultsSize)); } } else { int rowsRetrieved = 0; while (rs.next()) { rowsRetrieved++; } results.append(sql); results.append("\n"); results.append("Rows Retrieved: "); results.append(rowsRetrieved); results.append("\n"); results.append("\n"); } } else { rowsUpdated = updateCount > 0 ? true : false; if (!runAsScript) { resultComponents.add(wrapTextInComponent( String.format("%d rows affected", updateCount))); } else { results.append(sql); results.append("\n"); results.append("Rows Affected: "); results.append(updateCount); results.append("\n"); results.append("\n"); } } hasResults = stmt.getMoreResults(); updateCount = stmt.getUpdateCount(); } finally { JdbcSqlTemplate.close(rs); } } sql = sqlReader.readSqlStatement(); } } finally { IOUtils.closeQuietly(sqlReader); } } catch (Throwable ex) { if (isCanceled) { String canceledMessage = "Canceled successfully.\n\n" + sqlText; resultComponents.add(wrapTextInComponent(canceledMessage)); } else { icon = FontAwesome.BAN; resultComponents.add(wrapTextInComponent(buildErrorMessage(ex), "marked")); } } finally { if (autoCommitBefore) { try { connection.commit(); connection.setAutoCommit(autoCommitBefore); } catch (SQLException e) { } } JdbcSqlTemplate.close(stmt); if (autoCommit || (!autoCommit && !rowsUpdated && createdConnection)) { JdbcSqlTemplate.close(connection); connection = null; } } if (resultComponents.size() == 0 && StringUtils.isNotBlank(results.toString())) { resultComponents .add(wrapTextInComponent(results.toString(), icon == FontAwesome.BAN ? "marked" : null)); } } finally { endTime = new Date(); if (listener != null) { listener.finished(icon, resultComponents, endTime.getTime() - startTime.getTime(), !autoCommit && rowsUpdated, committed); } else if (!autoCommit) { rollback(connection); } } } protected String buildErrorMessage(Throwable ex) { StringBuilder errorMessage = new StringBuilder("<span style='color: red'>"); if (ex instanceof SQLException) { SQLException sqlException = (SQLException) ex; errorMessage.append("SQL Message: ").append(ex.getMessage()); errorMessage.append("\nSQL State: "); errorMessage.append(sqlException.getSQLState()); errorMessage.append("\nError Code: "); errorMessage.append(sqlException.getErrorCode()); } else { errorMessage.append(ex.getMessage()); errorMessage.append(ExceptionUtils.getStackTrace(ex)); } errorMessage.append("</span>"); return errorMessage.toString(); } protected Class<?> getClass(ResultSetMetaData meta, int i) throws SQLException { try { return Class.forName(meta.getColumnClassName(i)); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } } protected Component wrapTextInComponent(String text) { return wrapTextInComponent(text, null); } protected Component wrapTextInComponent(String text, String style) { Panel panel = new Panel(); VerticalLayout content = new VerticalLayout(); content.setMargin(true); panel.setContent(content); Label label = new Label("<pre>" + text.toString() + "</pre>", ContentMode.HTML); if (StringUtils.isNotBlank(style)) { label.setStyleName(style); } content.addComponent(label); return panel; } protected Component putResultsInArea(Statement stmt, int maxResultSize) throws SQLException { return wrapTextInComponent(resultsAsText(stmt, maxResultSize)); } protected String resultsAsText(Statement stmt, int maxResultSize) throws SQLException { ResultSet rs = null; try { rs = stmt.getResultSet(); ResultSetMetaData meta = rs.getMetaData(); int columns = meta.getColumnCount(); int[] maxColumnSizes = new int[columns]; for (int i = 1; i <= columns; i++) { String columnName = meta.getColumnName(i); maxColumnSizes[i - 1] = columnName.length(); } int rowNumber = 1; List<Object[]> rows = new ArrayList<Object[]>(); while (rs.next() && rowNumber <= maxResultSize) { Object[] row = new Object[columns]; for (int i = 1; i <= columns; i++) { Object obj = CommonUiUtils.getObject(rs, i); row[i - 1] = obj; if (obj != null) { int size = obj.toString().length(); if (maxColumnSizes[i - 1] < size) { maxColumnSizes[i - 1] = size; } } } rows.add(row); rowNumber++; } StringBuilder text = new StringBuilder(); for (int i = 1; i <= columns; i++) { String columnName = meta.getColumnName(i); text.append(StringUtils.rightPad(columnName, maxColumnSizes[i - 1])); text.append(" "); } text.append("\n"); for (int i = 1; i <= columns; i++) { text.append(StringUtils.rightPad("", maxColumnSizes[i - 1], "-")); text.append(" "); } text.append("\n"); for (Object[] objects : rows) { for (int i = 0; i < objects.length; i++) { text.append(StringUtils.rightPad(objects[i] != null ? objects[i].toString() : "<null>", maxColumnSizes[i])); text.append(" "); } text.append("\n"); } return text.toString(); } finally { JdbcSqlTemplate.close(rs); } } public void setListener(ISqlRunnerListener listener) { this.listener = listener; } public Connection getConnection() { return connection; } public void setConnection(Connection connection) { if (connection != null) { this.createdConnection = false; } this.connection = connection; } public String getUser() { return user; } public String getSqlText() { return sqlText; } public boolean isAutoCommit() { return autoCommit; } public boolean isRowsUpdated() { return rowsUpdated; } public boolean isRunAsScript() { return runAsScript; } public void cancel() { try { stmt.cancel(); isCanceled = true; } catch (SQLException e) { log.error("Failed to cancel", e); } } interface ISqlRunnerListener extends Serializable { public void writeSql(String sql); public void reExecute(String sql); public void finished(FontAwesome icon, List<Component> results, long executionTimeInMs, boolean transactionStarted, boolean transactionEnded); } }