sqlutil.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.util;

// The Codestudio PoolMan Library

import com.codestudio.PoolManConstants;
import com.codestudio.sql.PoolManResultSetMetaData;
import com.codestudio.sql.PoolManStatement;
import com.codestudio.sql.ResultSetAdapter;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Properties;

/**
 * A utility class that is capable of performing any sql operation.
 * All results from queries are returned as Hashtable arrays, where each
 * Hashtable represents one row (record) of data (key=column_name,
 * value=data).
 *<p>
 * If the sql is an update, insert, or delete, then the
 * Hashtable array contains only one slice, and the value of that first
 * Hashtable contains a description of the number of rows affected.
 *<p>
 * Results are cached by query that refreshes itself perdiocially, at a
 * configurable interval that defaults to five minutes.
 * <p>
 * If you wish to force the cache to refresh before this interval, you should
 * either update the cacheRefresh parameter in the poolman.props file or
 * invoke the <code>executeSql(String sql, boolean usecache)</code>
 *  method with the second argument set to 'false'. If the second argument
 * is set to true or ommitted, the cache will be used. The cache updates are
 * handled internally by a separate thread of execution.
 *<p>
 * For specific usage examples, take a look at <code>Sample.java</code>
 * in the <code>samples</code> directory of the distribution.
 */
public class SQLUtil {

    private static SQLUtil myself;
    public static final int MAX_ATTEMPTS = 3;
    public String propsfilename;
    private Properties props;


    /* TESTING */
    public static void main(String[] args) {

        System.out.println("\n");
        if (args.length < 2) {
            System.out.println("SYNTAX:\njava com.codestudio.SQLUtil " + "\"[name of database as specified in poolman.props]\"" + "\"[SQL statement to be executed]\"\n");
            System.exit(0);
        }

        try {
            SQLResult res = SQLUtil.getInstance().execute(args[0], args[1]);
            while (res.hasNext()) {
                Hashtable row = res.next();
                for (Enumeration enum = row.keys(); enum.hasMoreElements();) {
                    String key = enum.nextElement().toString();
                    System.out.print(key + ": ");
                    System.out.print(row.get(key).toString() + "\t");
                }
                System.out.print("\n");
            }
            if (res.size() < 1)
                System.out.println("SQLUtil: No results were returned for that SQL statement.");
        } catch (Exception e) {
            e.printStackTrace();
        }

        System.out.println("\n");
        System.exit(1);

    }

    /**
     * The default method used to retrieve an instance of SQLUtil,
     * based on the properties file SQLManager.DEFAULT_PROPSFILE,
     * which must be in your CLASSPATH.
     */
    public static SQLUtil getInstance() {
        if (myself == null) {
            synchronized (SQLUtil.class) {
                myself = new SQLUtil();
            }
        }
        return myself;
    }

    /**
     * An alternative method to retrieve an instance of SQLUtil,
     * using a specified props file.
     */
    public static SQLUtil getInstance(String propsfilename) {
        if (myself == null) {
            synchronized (SQLUtil.class) {
                myself = new SQLUtil(propsfilename);
            }
        }
        return myself;
    }

    /**
     * An alternative method to retrieve an instance of SQLUtil,
     * using a Properties object.
     */
    public static SQLUtil getInstance(Properties p) {
        if (myself == null) {
            synchronized (SQLUtil.class) {
                myself = new SQLUtil(p);
            }
        }
        return myself;
    }

    private SQLUtil() {
        this(PoolManConstants.XML_CONFIG_FILE);
    }

    private SQLUtil(String propsfilename) {
        this.propsfilename = propsfilename;
    }

    private SQLUtil(Properties p) {
        this.props = p;
    }

    public SQLResult execute(String sql) throws SQLException {
        return execute(null, sql);
    }

    public SQLResult execute(String dbname, String sql) throws SQLException {
        return makeResult(executeSql(dbname, sql));
    }

    /** Simply executes executeSql(sql, true). */
    public Hashtable[] executeSql(String sql) throws SQLException {
        return executeSql(null, sql);
    }

    /** Wraps an array of Hashtables into a more
     * convenient data structure, called a SQLResult. */
    protected SQLResult makeResult(Hashtable[] h) throws SQLException {
        return new SQLResult(h);
    }

    /** Reference the SQLManager, which contains all available JDBCPools. */
    private SQLManager getSQLManager() {
        SQLManager datab = null;
        try {
            /*
            if (props != null)
            datab = SQLManager.getInstance(this.props);
            else
            */
            datab = SQLManager.getInstance();
        } catch (Exception pe) {
            throw new RuntimeException("Couldn't get a reference to the SQLManager: " + pe.toString());
        }
        return datab;
    }

    /**
     * Get a Vector containing all the names of the database
     * pools currently in use.
     */
    public Enumeration getAllPoolnames() {
        SQLManager datab = getSQLManager();
        if (datab == null)
            return null;
        return datab.getAllPoolnames();
    }

    /** @return JDBCPool The pool requested by name. */
    public JDBCPool getPool(String dbname) {
        SQLManager datab = getSQLManager();
        return (JDBCPool) datab.getPool(dbname);
    }

    /**
     * Begins the actual database operation by preparing resources.
     * It calls doJDBC() to perform the actual operation.
     */
    public Hashtable[] executeSql(String dbname, String sql) throws SQLException {

        Hashtable[] hashResults = null;
        Connection con = null;
        ResultSet res = null;
        Statement s = null;

        SQLManager datab = getSQLManager();

        // try to get the ResultSet from the cache
        JDBCPool jp = getPool(dbname);
        if ((jp.usingCache()) && (sql.toLowerCase().startsWith("select"))) {
            SQLCache jc = jp.getCache();
            try {
                hashResults = ResultSetAdapter.convert(jc.getResult(sql));
            } catch (Exception cee) {
                jc.removeResult(sql);
            }
            if (hashResults != null)
                return hashResults;
        }

        try {
            if ((dbname == null) || (dbname.equals("")))
                con = datab.requestConnection();
            else
                con = datab.requestConnection(dbname);
            hashResults = doJDBC(dbname, sql, con);
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException(e.getMessage());
        } finally {
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return hashResults;

    }

    /** This method is called by the cache thread in SQLCache. */
    public Hashtable[] doJDBC(String dbname, String sql, boolean goNative) throws SQLException {

        SQLManager datab = getSQLManager();
        if (datab == null)
            throw new SQLException("Unable to initialize PoolMan's SQLManager");

        Connection con = null;
        if ((dbname == null) || (dbname.equals("")))
            con = datab.requestConnection();
        else
            con = datab.requestConnection(dbname);

        return doJDBC(dbname, sql, con, goNative);
    }

    /** Executes a statement and returns results in the form of a Hashtable array. */
    protected Hashtable[] doJDBC(String dbname, String sql, Connection con) throws SQLException {
        return doJDBC(dbname, sql, con, false);
    }

    /** Executes a statement and returns results in the form of a Hashtable array. */
    protected Hashtable[] doJDBC(String dbname, String sql, Connection con, boolean goNative) throws SQLException {

        Hashtable[] results = null;
        ResultSet res = null;
        Statement s = null;

        // build the query
        s = con.createStatement();

        // make sure we have a native Statement if it is required
        if ((goNative) && (s instanceof com.codestudio.sql.PoolManStatement)) {
            PoolManStatement ps = (PoolManStatement) s;
            s = ps.getNativeStatement();
        }

        // See if this was a select statement
        try {
            if (s.execute(sql)) {
                results = new Hashtable[10];
                res = s.getResultSet();
                ResultSetMetaData meta = PoolManResultSetMetaData.getCopy(res.getMetaData());
                int cols = meta.getColumnCount();
                int rowcount = 0;

                while (res.next()) {

                    if (rowcount == results.length) {
                        Hashtable[] temp = new Hashtable[results.length + 10];
                        for (int t = 0; t < results.length; t++) {
                            temp[t] = results[t];
                        }
                        results = temp;
                    }

                    Hashtable record = new Hashtable(1);
                    for (int i = 1; i <= cols; i++) {
                        Object value = null;
                        try {
                            switch (meta.getColumnType(i)) {
                                //case Types.LONGVARBINARY:
                                case Types.CHAR:
                                    try {
                                        // not sure about this fix, so be overly cautious
                                        value = new String(res.getBytes(i));
                                    } catch (Exception _e) {
                                        value = res.getObject(i);
                                    }
                                    break;
                                default:
                                    value = res.getObject(i);
                                    break;
                            }
                        } catch (Exception ee) {
                        }
                        if (value == null)
                            value = new String("");

                        record.put(meta.getColumnLabel(i), value);
                    }

                    results[rowcount] = record;
                    rowcount++;

                }

                // not necessary to cache it, the statement did that if it was necessary

                meta = null;

                if (results[0] == null) {
                    return results;
                    /*
                      JDBCPool.closeResources(s,res);
                    throw new SQLException("Query Valid, But Empty ResultSet Returned, and " +
                               "SQLUtil treats this as an Exception (NOTE: DataSource " +
                               "and Driver usage are standard, SQLUtil is deprecated).");
                    */
                }

                Hashtable[] temp = null;
                for (int i = 0; i < results.length; i++) {
                    if (results[i] == null) {
                        temp = new Hashtable[i];
                        break;
                    }
                    else {
                        temp = new Hashtable[results.length];
                    }
                }
                for (int i = 0; i < temp.length; i++) {
                    temp[i] = results[i];
                }
                results = temp;
                temp = null;

            }

            else {
                results = new Hashtable[1];
                results[0] = new Hashtable(1);
                int num;
                String str = "Rows Affected";
                switch (num = s.getUpdateCount()) {
                    case 0:
                        results[0].put(str, "No rows affected");
                        break;
                    case 1:
                        results[0].put(str, "1 row affected");
                        break;
                    default:
                        results[0].put(str, num + " rows affected");
                }
            }

        } catch (SQLException sqle) {
            throw sqle;
        } finally {
            JDBCPool.closeResources(s, res);
            con.close();
            s = null;
            res = null;
        }

        return results;

    }


}