poolmanaction.java

Java Database connection pool

/*
 *  PoolMan Java Object Pooling and Caching Library
 *  Copyright (C) 1999-2001 The Code Studio
 *
 *  This library is free software; you can redistribute it and/or
 *  modify it under the terms of the GNU Lesser General Public
 *  License as published by the Free Software Foundation; either
 *  version 2 of the License, or (at your option) any later version.
 *
 *  This library is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *  Lesser General Public License for more details.
 *
 *  The full license is located at the root of this distribution
 *  in the LICENSE file.
 */
package com.codestudio.bean.struts;

import com.codestudio.sql.PoolMan;
import com.codestudio.util.JDBCPool;
import com.codestudio.util.SQLManager;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;


/**
 * A struts Action for handling the poolman JSP form.
 *
 * @author PS Neville
 */
public final class PoolManAction
        extends Action {

    /**
     *  Perform the Struts action associated with the poolman struts form.
     */
    public ActionForward perform(ActionMapping mapping,
                                 ActionForm form,
                                 HttpServletRequest request,
                                 HttpServletResponse response)
            throws IOException, ServletException {

        // retrieve the request data necessary to perform the action
        String sqlQuery = ((PoolManActionFormBean) form).getSql();
        String dbName = ((PoolManActionFormBean) form).getCurrentDatabaseName();

        // create or retrieve the results and metrics beans
        HttpSession session = request.getSession();
        PoolManResultsBean resultsBean = getResultsBean(session);
        PoolManMetricsBean metricsBean = getMetricsBean(session);

        // execute the query
        if ((sqlQuery != null) && (sqlQuery.length() > 0)) {
            try {
                executeSQL(dbName, sqlQuery, resultsBean, metricsBean);
            } catch (Exception sqle) {
                resultsBean.setError(sqle.toString());
            }
        }

        // prepare the Metrics Bean using the DataSource's pool
         metricsBean.setJDBCPool((JDBCPool) SQLManager.getInstance().getPool(dbName));

        // set the beans
        session.setAttribute("poolmanResults", resultsBean);
        session.setAttribute("poolmanMetrics", metricsBean);

        // forward control to the specified success URI
        return (mapping.findForward("poolman"));

    }

    /**
     * Execute a generic sql statement without knowing much about it or its destination.
     * A real-world query would be less generic, making the code simpler than this example.
     */
    protected void executeSQL(String dbName, String sqlQuery,
                              PoolManResultsBean resultsBean,
                              PoolManMetricsBean metricsBean)
            throws SQLException {

        // clear previous data
        resultsBean.setError(null);
        resultsBean.setHeaderColumns(null);
        resultsBean.setRows(null);

        // prepare the variables outside the loop to ensure closing
        DataSource ds = null;
        Connection con = null;
        Statement st = null;
        ResultSet res = null;

        metricsBean.incrementQueryCount();

        try {

            // start the method timer
            long start = System.currentTimeMillis();

            // get the DataSource
            ds = PoolMan.findDataSource(dbName);

            // get the Connection
            con = ds.getConnection();

            // create the Statement
            st = con.createStatement();

            if (st.execute(sqlQuery)) {

                // done executing query, set time
                metricsBean.setLastQueryDuration(System.currentTimeMillis() - start);

                // there was a ResultSet
                res = st.getResultSet();

                //get the header
                ResultSetMetaData meta = res.getMetaData();
                ArrayList header = new ArrayList();
                for (int n = 1; n <= meta.getColumnCount(); n++) {
                    header.add(meta.getColumnLabel(n));
                }
                resultsBean.setHeaderColumns(header);

                while (res.next()) {
                    ArrayList row = new ArrayList();
                    for (int n = 1; n <= meta.getColumnCount(); n++) {
                        Object value = null;
                        try {
                            switch (meta.getColumnType(n)) {
                                case Types.CHAR:
                                    try {
                                        value = new String(res.getBytes(n));
                                    } catch (Exception _e) {
                                        value = res.getObject(n);
                                    }
                                    break;
                                default:
                                    value = res.getObject(n);
                                    break;
                            }
                        } catch (Exception ee) {
                        }
                        if (value == null)
                            value = new String("NULL");
                        row.add(value);
                    }
                    resultsBean.addRow(row);
                }
            }

            else {

                // done executing query, set time
                metricsBean.setLastQueryDuration(System.currentTimeMillis() - start);

                // no ResultSet
                String resultsMessage = null;
                int num = st.getUpdateCount();
                switch (num) {
                    case 0:
                        resultsMessage = "No rows affected";
                        break;
                    case 1:
                        resultsMessage = "1 row affected";
                        break;
                    default:
                        resultsMessage = num + " rows affected";
                }
                ArrayList row = new ArrayList();
                row.add(resultsMessage);
                resultsBean.addRow(row);
            }

        } catch (SQLException e) {
            throw e;
        }
        finally {
            JDBCPool.closeResources(con, st, res);
        }
    }

    protected PoolManMetricsBean getMetricsBean(HttpSession session) {
        PoolManMetricsBean metricsBean = null;
        try {
            metricsBean = (PoolManMetricsBean) session.getAttribute("poolmanMetrics");
        } catch (Exception e) {
        }
        if (metricsBean == null)
            metricsBean = new PoolManMetricsBean();
        return metricsBean;
    }

    protected PoolManResultsBean getResultsBean(HttpSession session) {
        PoolManResultsBean resultsBean = null;
        try {
            resultsBean = (PoolManResultsBean) session.getAttribute("poolmanResults");
        } catch (Exception e) {
        }
        if (resultsBean == null) {
            resultsBean = new PoolManResultsBean();
        }
        return resultsBean;
    }
}