sqlparameters.java

一个工作流设计及定义的系统,可以直接与数据库结合进行系统工作流程的定义及应用.

/*
 * Copyright (c) 2005, John Mettraux, OpenWFE.org
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without 
 * modification, are permitted provided that the following conditions are met:
 * 
 * . Redistributions of source code must retain the above copyright notice, this
 *   list of conditions and the following disclaimer.  
 * 
 * . Redistributions in binary form must reproduce the above copyright notice, 
 *   this list of conditions and the following disclaimer in the documentation 
 *   and/or other materials provided with the distribution.
 * 
 * . Neither the name of the "OpenWFE" nor the names of its contributors may be
 *   used to endorse or promote products derived from this software without
 *   specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE 
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS 
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
 * POSSIBILITY OF SUCH DAMAGE.
 *
 * $Id: SqlParameters.java,v 1.9 2005/06/14 13:17:26 jmettraux Exp $
 */

//
// SqlParameters.java
//
// [e-mail]
//
// generated with 
// jtmpl 1.1.01 2004/05/19 ([e-mail])
//

package openwfe.org.sql;

import java.util.Collections;


/**
 * A class for handling params in a SQL db
 *
 * <p><font size=2>CVS Info :
 * <br>$Author: jmettraux $
 * <br>$Id: SqlParameters.java,v 1.9 2005/06/14 13:17:26 jmettraux Exp $ </font>
 *
 * @author [e-mail]
 */
public class SqlParameters
{

    private final static org.apache.log4j.Logger log = org.apache.log4j.Logger
        .getLogger(SqlParameters.class.getName());

    //
    // CONSTANTS & co

    //
    // FIELDS

    private final String paramTableName;
    private final String idColName;
    private final String groupColName;
    private final String keyColName;
    private final String valueColName;

    //
    // CONSTRUCTORS

    public SqlParameters
        (final String paramTableName,
         final String idColName,
         final String groupColName,
         final String keyColName,
         final String valueColName)
    {
        this.paramTableName = paramTableName;
        this.idColName = idColName;
        this.groupColName = groupColName;
        this.keyColName = keyColName;
        this.valueColName = valueColName;
    }

    //
    // METHODS

    /**
     * Extracts a list of parameter maps for a given group or key
     */
    public java.util.List extractParameters 
        (final java.sql.Connection con, 
         final String group,
         final String key)
    {
        java.util.List result = new java.util.ArrayList();
        
        int id = -1;

        String query = null;
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            //
            // extract id
            
            query = buildQuery(group, key, null);

            st = con.createStatement();
            rs = st.executeQuery(query);


            while (rs.next())
            {
                id = rs.getInt(1);

                //
                // extract parameters

                result.add(extractParameters(con, id));
            }
        }
        catch (java.sql.SQLException se)
        {
            log.warn
                ("Failed to extract parameters from the database with query\n"+
                 query, se);
            return result;
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
        }

        return result;
    }

    /**
     * Given one parameter (key/value), retrieves the other parameters
     * with the same id
     */
    public java.util.Map extractParameters 
        (final java.sql.Connection con, 
         final String group,
         final String key,
         final String value)
    {
        //
        // extract id
        
        int id = -1;

        String query = null;
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            query = buildQuery(group, key, value);

            st = con.createStatement();
            rs = st.executeQuery(query);

            if ( ! rs.next())
            {
                log.debug("extracParameters() no id found");
                return null;
            }

            id = rs.getInt(1);
        }
        catch (java.sql.SQLException se)
        {
            log.warn
                ("Failed to extract parameters from the database with query\n"+
                 query, se);
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
        }

        //
        // extract parameters

        return extractParameters(con, id);
    }

    /**
     * Returns the parameter map behind a given id
     */
    public java.util.Map extractParameters 
        (final java.sql.Connection con, 
         final int id)
    {
        String query = null;
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            query = buildQuery(id);

            st = con.createStatement();
            rs = st.executeQuery(query);

            java.util.Map result = new java.util.HashMap();

            while (rs.next())
            {
                final String key = rs.getString(1);
                final String value = rs.getString(2);

                result.put(key, value);
            }

            return Collections.unmodifiableMap(result);
        }
        catch (java.sql.SQLException se)
        {
            log.warn
                ("Failed to extract parameters from the database with query\n"+
                 query, se);
            return null;
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
        }
    }

    /* *
     * removes every parameter from the db table. Use with Caution !!
     *
    public void cleanParamTable ()
    {
        java.sql.Statement st = null;
        try
        {
            st.execute("DELETE FROM "+this.paramTableName);
        }
        catch (java.sql.SQLException se)
        {
            log.warn
                ("Failed to clean param table", se);
        }
        finally
        {
            SqlUtils.closeStatement(st);
        }
    }
     *
     */

    public long insertParams
        (final java.sql.Connection con,
         final String group, 
         final java.util.Map params)
    throws
        java.sql.SQLException
    {
        final long id = SqlUtils.getNewInsertId
            (con, this.paramTableName, this.idColName);

        java.util.Iterator it = params.keySet().iterator();
        while (it.hasNext())
        {
            String key = (String)it.next();
            String val = (String)params.get(key);

            insertParam(con, id, group, key, val);
        }

        return id;
    }

    public void insertParam 
        (final java.sql.Connection con,
         final long id, 
         final String group, 
         final String key, 
         final String value)
    throws
        java.sql.SQLException
    {
        String sInsert = null;
        java.sql.Statement st = null;
        try
        {
            StringBuffer sb = new StringBuffer();

            sb.append("INSERT INTO ");
            sb.append(this.paramTableName);
            sb.append(" ( ");
            sb.append(this.idColName); sb.append(", ");
            sb.append(this.groupColName); sb.append(", ");
            sb.append(this.keyColName); sb.append(", ");
            sb.append(this.valueColName);
            sb.append(" ) VALUES ( ");
            sb.append(id); sb.append(", ");
            sb.append(SqlUtils.prepareString(group)); sb.append(", ");
            sb.append(SqlUtils.prepareString(key)); sb.append(", ");
            sb.append(SqlUtils.prepareString(value)); 
            sb.append(");");

            sInsert = sb.toString();

            st = con.createStatement();

            st.execute(sInsert);
        }
        //catch (java.sql.SQLException se)
        //{
        //    log.warn
        //      ("Failed to insert param with \n"+sInsert, se);
        //}
        finally
        {
            SqlUtils.closeStatement(st);
        }
    }

    //
    // PRIVATE METHODS

    /*
     * builds a query for determining the id, given
     * one group name and one key name
     */
    private String buildQuery
        (final String group,
         final String key,
         final String value)
    {
        final StringBuffer sb = new StringBuffer();

        sb.append("SELECT ");
        sb.append(this.idColName);

        sb.append(" FROM ");
        sb.append(this.paramTableName);

        sb.append(" WHERE ");
        sb.append(this.groupColName);
        sb.append(" = ");
        sb.append(SqlUtils.prepareString(group));
        sb.append(" AND ");
        sb.append(this.keyColName);
        sb.append(" = ");
        sb.append(SqlUtils.prepareString(key));

        if (value != null)
        {
            sb.append(" AND ");
            sb.append(this.valueColName);
            sb.append(" = ");
            sb.append(SqlUtils.prepareString(value));
        }

        sb.append(";");

        return sb.toString();
    }

    /*
     * builds a query for finding all the records with the same id
     */
    private String buildQuery 
        (final int id) 
    {
        final StringBuffer sb = new StringBuffer();

        sb.append("SELECT ");
        sb.append(this.keyColName); sb.append(", ");
        sb.append(this.valueColName);

        sb.append(" FROM ");
        sb.append(this.paramTableName);

        sb.append(" WHERE ");
        sb.append(this.idColName);
        sb.append(" = ");
        sb.append(id);
        sb.append(";");

        return sb.toString();
    }

    //
    // STATIC METHODS

}