psiprobe.controllers.sql.ExecuteSqlController.java Source code

Introduction

Here is the source code for psiprobe.controllers.sql.ExecuteSqlController.java

Source

/**
 * Licensed under the GPL License. You may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
 *
 * THIS PACKAGE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
 * WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTIBILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE.
 */
package psiprobe.controllers.sql;

import org.apache.catalina.Context;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.ServletRequestUtils;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.util.HtmlUtils;

import psiprobe.controllers.ContextHandlerController;
import psiprobe.model.sql.DataSourceTestInfo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.naming.NamingException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

/**
 * Executes an SQL query through a given datasource to test database connectivity. Displays results
 * returned by the query.
 * 
 * @author Andy Shapoval
 * @author Mark Lewis
 * @author jackdimm
 */
public class ExecuteSqlController extends ContextHandlerController {

    /** The Constant logger. */
    private static final Logger logger = LoggerFactory.getLogger(ExecuteSqlController.class);

    @Override
    protected ModelAndView handleContext(String contextName, Context context, HttpServletRequest request,
            HttpServletResponse response) throws Exception {

        String resourceName = ServletRequestUtils.getStringParameter(request, "resource");
        String sql = ServletRequestUtils.getStringParameter(request, "sql", null);

        if (sql == null || sql.equals("") || sql.trim().equals("")) {
            request.setAttribute("errorMessage",
                    getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.required"));

            return new ModelAndView(getViewName());
        }

        int maxRows = ServletRequestUtils.getIntParameter(request, "maxRows", 0);
        int rowsPerPage = ServletRequestUtils.getIntParameter(request, "rowsPerPage", 0);
        int historySize = ServletRequestUtils.getIntParameter(request, "historySize", 0);

        // store current option values and query history in a session attribute

        HttpSession sess = request.getSession(false);
        DataSourceTestInfo sessData = (DataSourceTestInfo) sess.getAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR);

        synchronized (sess) {
            if (sessData == null) {
                sessData = new DataSourceTestInfo();
                sess.setAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR, sessData);
            }

            sessData.setMaxRows(maxRows);
            sessData.setRowsPerPage(rowsPerPage);
            sessData.setHistorySize(historySize);
            sessData.addQueryToHistory(sql);
        }

        DataSource dataSource = null;

        try {
            dataSource = getContainerWrapper().getResourceResolver().lookupDataSource(context, resourceName,
                    getContainerWrapper());
        } catch (NamingException e) {
            request.setAttribute("errorMessage", getMessageSourceAccessor()
                    .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName }));
            logger.trace("", e);
        }

        if (dataSource == null) {
            request.setAttribute("errorMessage", getMessageSourceAccessor()
                    .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName }));
        } else {
            List<Map<String, String>> results = null;
            int rowsAffected = 0;

            try {
                // TODO: use Spring's jdbc template?
                try (Connection conn = dataSource.getConnection()) {
                    conn.setAutoCommit(true);

                    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                        boolean hasResultSet = stmt.execute();

                        if (!hasResultSet) {
                            rowsAffected = stmt.getUpdateCount();
                        } else {
                            results = new ArrayList<>();

                            try (ResultSet rs = stmt.getResultSet()) {
                                ResultSetMetaData metaData = rs.getMetaData();

                                while (rs.next() && (maxRows < 0 || results.size() < maxRows)) {
                                    Map<String, String> record = new LinkedHashMap<>();

                                    for (int i = 1; i <= metaData.getColumnCount(); i++) {
                                        String value = rs.getString(i);

                                        if (rs.wasNull()) {
                                            value = getMessageSourceAccessor()
                                                    .getMessage("probe.src.dataSourceTest.sql.null");
                                        } else {
                                            value = HtmlUtils.htmlEscape(value);
                                        }

                                        // a work around for IE browsers bug of not displaying
                                        // a border around an empty table column

                                        if (value.isEmpty()) {
                                            value = "&nbsp;";
                                        }

                                        // Pad the keys of columns with existing labels so they are distinct
                                        String key = metaData.getColumnLabel(i);
                                        while (record.containsKey(key)) {
                                            key += " ";
                                        }
                                        record.put(HtmlUtils.htmlEscape(key), value);
                                    }

                                    results.add(record);
                                }
                            }

                            rowsAffected = results.size();
                        }
                    }
                }

                // store the query results in the session attribute in order
                // to support a result set pagination feature without re-executing the query

                synchronized (sess) {
                    sessData.setResults(results);
                }

                ModelAndView mv = new ModelAndView(getViewName(), "results", results);
                mv.addObject("rowsAffected", String.valueOf(rowsAffected));
                mv.addObject("rowsPerPage", String.valueOf(rowsPerPage));

                return mv;
            } catch (SQLException e) {
                String message = getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.failure",
                        new Object[] { e.getMessage() });
                logger.error(message, e);
                request.setAttribute("errorMessage", message);
            }
        }

        return new ModelAndView(getViewName());
    }

    @Override
    protected boolean isContextOptional() {
        return true;
    }

}