poolmanvelocityservlet.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.velocity;

import com.codestudio.sql.PoolMan;
import com.codestudio.util.JDBCPool;
import com.codestudio.util.SQLManager;
import org.apache.velocity.Template;
import org.apache.velocity.app.Velocity;
import org.apache.velocity.context.Context;
import org.apache.velocity.exception.ParseErrorException;
import org.apache.velocity.exception.ResourceNotFoundException;
import org.apache.velocity.servlet.VelocityServlet;

import javax.servlet.ServletConfig;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Properties;

/**
 * A simple VelocityServlet extension for creating a web application using the
 * poolman-velocity.vm XML template.
 *
 * @author PS Neville
 */
public class PoolManVelocityServlet
        extends VelocityServlet {

    /* CONSTANTS USED TO IDENTIFY ATTRIBUTES IN THE VELOCITY CONTEXT */

    public static final String SQL_QUERY = "sql";
    public static final String CURRENT_DATABASE = "currentDatabaseName";
    public static final String ALL_DB_NAMES = "allDatabaseNames";
    public static final String HEADER_COLS = "headerColumns";
    public static final String RESULTS_ROWS = "resultsRows";
    public static final String ERRORS = "errors";
    public static final String QUERY_COUNT = "queryCount";
    public static final String QUERY_DURATION = "lastQueryDuration";
    public static final String USING_CACHE = "usingCache";
    public static final String DB_URL = "url";
    public static final String DB_DRIVER = "driver";
    public static final String DB_USER = "user";
    public static final String TOTAL_CONS = "totalConnections";
    public static final String AVAIL_CONS = "availableConnections";
    public static final String UNAVAIL_CONS = "unavailableConnections";

    /**
     * A simplistic load method used to prepare Velocity.
     */
    protected Properties loadConfiguration(ServletConfig config)
            throws IOException, FileNotFoundException {

        Properties p = new Properties();
        String path = config.getServletContext().getRealPath("/");

        if (path == null) {
            System.out.println(" com.codestudio.bean.velocity.PoolManVelocityServlet.loadConfiguration():" +
                                " unable to get the current webapp root.  Using '/'. Please fix.");

            path = "/";
        }

        p.setProperty(Velocity.FILE_RESOURCE_LOADER_PATH, path);
        p.setProperty("runtime.log", path + "velocity.log");

        return p;
    }


    /**
     * The servlet entry point. It passes control off to a method that will
     * introspect the Context and the request for the sake of transforming the Context
     * and template.
     */
    public Template handleRequest(HttpServletRequest req,
                                  HttpServletResponse res,
                                  Context ctx) {

        // get the request params and possibly execute some sql
        if (inspectRequest(ctx, req)) {
            try {
                executeSQL(ctx);
            } catch (SQLException sqle) {
                // record any errors
                ctx.put(ERRORS, sqle.toString());
            }
        }

        // set metrics
        try {
            String currentDB = (String) ctx.get(CURRENT_DATABASE);
            JDBCPool pool = (JDBCPool) SQLManager.getInstance().getPool(currentDB);
            ctx.put(TOTAL_CONS, "" + pool.numTotalObjects());
            ctx.put(AVAIL_CONS, "" + pool.numCheckedInObjects());
            ctx.put(UNAVAIL_CONS, "" + pool.numCheckedOutObjects());
        } catch (Exception e) {
                // ok, ignorable
        }

        Template outty = null;

        try {
            outty = getTemplate("poolman-velocity.vm");
        } catch (ParseErrorException pee) {
            System.out.println("com.codestudio.bean.velocity.PoolManVelocityServlet : parse error for template " + pee);
        } catch (ResourceNotFoundException rnfe) {
            System.out.println("com.codestudio.bean.velocity.PoolManVelocityServlet : template not found " + rnfe);
        } catch (Exception e) {
            System.out.println("Error " + e);
        }
        return outty;
    }

    /**
     * Handle request parameters and make a decision (boolean) about whether
     * a SQL invocation should occur.
     */
    protected boolean inspectRequest(Context ctx, HttpServletRequest req) {

        boolean executeSQL = false;

        // get all database names
        ArrayList allDatabaseNames = null;
        try {
            allDatabaseNames = (ArrayList) ctx.get(ALL_DB_NAMES);
        } catch (Exception e) {
        }
        if ((allDatabaseNames == null) || (allDatabaseNames.size() < 1)) {
            allDatabaseNames = new ArrayList();
            Enumeration enum = SQLManager.getInstance().getAllPoolnames();
            while (enum.hasMoreElements()) {
                allDatabaseNames.add(enum.nextElement());
            }
            if (allDatabaseNames.size() < 1)
                allDatabaseNames = null;
        }
        ctx.put(ALL_DB_NAMES, allDatabaseNames);

        // get the current selected database, if there is one
        String currentDB = null;
        try {
            currentDB = (String) req.getParameter(CURRENT_DATABASE);
            ctx.put(CURRENT_DATABASE, currentDB);
        } catch (Exception e) {
        }

        // get the information about this database
        if (currentDB != null) {
            JDBCPool pool = (JDBCPool) SQLManager.getInstance().getPool(currentDB);
            if (pool.usingCache())
                ctx.put(USING_CACHE, "true");
            else
                ctx.put(USING_CACHE, "false");
            ctx.put(DB_URL, pool.getURL());
            ctx.put(DB_DRIVER, pool.getDriver());
            ctx.put(DB_USER, pool.getUserName());
        }


        // get the sql string, if there is one
        String sql = null;
        try {
            sql = (String) req.getParameter(SQL_QUERY);
        } catch (Exception e) {
        }
        if ((sql != null) && (sql.length() > 0)) {
            ctx.put(SQL_QUERY, sql);
        }
        else {
            ctx.put(SQL_QUERY, "");
        }

        // if we have valid data, we need to execute the SQL
        if ((currentDB != null) && (sql != null) && (sql.length() > 0)) {
            executeSQL = true;
        }
        else {
            executeSQL = false;
        }

        return executeSQL;
    }

    /**
     * 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(Context ctx)
            throws SQLException {

        // clear previous data
        ctx.put(ERRORS, null);
        ctx.put(HEADER_COLS, null);
        ctx.put(RESULTS_ROWS, null);

        // retrieve the dbname and sql
        String currentDB = (String) ctx.get(CURRENT_DATABASE);
        String sqlQuery = (String) ctx.get(SQL_QUERY);

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

        // handle query count
        int queryCount = 0;
        try {
            int n = Integer.parseInt((String) ctx.get(QUERY_COUNT));
            if (n > queryCount) {
                queryCount = n;
            }
        } catch (Exception e) {}
        queryCount++;
        ctx.put(QUERY_COUNT, "" + queryCount);

        try {

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

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

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

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

            // prepare for the returned data
            ArrayList allRows = new ArrayList();

            if (st.execute(sqlQuery)) {

                // done executing query, set time
                ctx.put(QUERY_DURATION, "" + (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));
                }
                ctx.put(HEADER_COLS, header);

                // get the rows
                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);
                    }
                    allRows.add(row);
                }
            }

            else {

                // done executing query, set time
                ctx.put(QUERY_DURATION, "" + (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);
                allRows.add(row);
            }

            // put the returned rows in the context
            ctx.put(RESULTS_ROWS, allRows);

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