sqlutils.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: SqlUtils.java,v 1.16 2005/07/13 20:53:39 jmettraux Exp $
 */

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

package openwfe.org.sql;

import openwfe.org.ServiceException;
import openwfe.org.ApplicationContext;
import openwfe.org.sql.ds.OwfeDataSource;


/**
 * SQL related help methods
 *
 * <p><font size=2>CVS Info :
 * <br>$Author: jmettraux $
 * <br>$Id: SqlUtils.java,v 1.16 2005/07/13 20:53:39 jmettraux Exp $ </font>
 *
 * @author [e-mail]
 */
public abstract class SqlUtils
{

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

    //
    // CONSTANTS

    //
    // METHODS

    public static String prepareString (final String in)
    {
        if (in == null) return "NULL";

        String result;

        //result = in.replaceAll("'", "\\\\'");
        result = in.replaceAll("'", "''");

        result = "'" + result + "'";

        return result;
    }

    public static void closeStatement 
        (final java.sql.Statement st, final java.sql.ResultSet rs)
    {
        try
        {
            if (rs != null) rs.close();
            if (st != null) st.close();
        }
        catch (final Throwable t)
        {
            log.debug
                ("closeStatement() problem when disposing of sql resources", t);
        }
    }

    public static void closeStatement 
        (final java.sql.Statement st)
    {
        closeStatement(st, null);
    }

    public static long getNewInsertId
        (final java.sql.Connection con,
         final String tableName,
         final String idColName)
    throws
        java.sql.SQLException
    {
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            StringBuffer sb = new StringBuffer();

            sb.append("SELECT MAX(");
            sb.append(idColName);
            sb.append(") FROM ");
            sb.append(tableName);

            String query = sb.toString();

            st = con.createStatement();

            rs = st.executeQuery(query);

            if (rs.next())
                return rs.getLong(1)+1;

            return 0;
        }
        finally
        {
            rs.close();
            st.close();
        }
    }

    /**
     * Takes care of producing an INSERT string, also takes care of wrapping
     * string values inside quotes and to protect special chars.
     */
    public static String buildInsertString
        (final String tableName,
         final String[] colNames,
         final java.util.List values)
    {
        if (colNames.length != values.size())
        {
            throw new IllegalArgumentException
                ("number of columns doesn't match number of values for table '"+
                 tableName+"'");
        }

        final StringBuffer sb = new StringBuffer();

        sb.append("INSERT INTO ");
        sb.append(tableName);
        sb.append(" (");
        for (int i=0; i<colNames.length; i++)
        {
            sb.append(colNames[i]);
            if (i < colNames.length-1) sb.append(", ");
        }
        sb.append(") VALUES (");
        java.util.Iterator it = values.iterator();
        while (it.hasNext())
        {
            Object oVal = it.next();

            if (oVal == null)
                sb.append("NULL");
            else if (oVal instanceof String)
                sb.append(prepareString(oVal.toString()));
            else
                sb.append(oVal.toString());

            if (it.hasNext()) sb.append(", ");
        }
        sb.append(")");

        final String result =  sb.toString();

        //log.debug("buildInsertString() :\n"+result);

        return result;
    }

    public static String buildQueryString
        (final String tableName,
         final String[] colNames,
         final String whereClause)
    {
        return buildQueryString
            (tableName, colNames, whereClause, null);
    }

    public static String buildQueryString
        (final String tableName,
         final String[] colNames,
         final String whereClause,
         final String orderClause)
    {
        StringBuffer sb = new StringBuffer();

        sb.append("SELECT ");

        for (int i=0; i<colNames.length; i++)
        {
            sb.append(colNames[i]);
            if (i < colNames.length-1) sb.append(", ");
        }

        sb.append(" FROM "); sb.append(tableName);

        sb.append(" WHERE "); sb.append(whereClause);

        if (orderClause != null)
        {
            sb.append(" ORDER BY "); sb.append(orderClause);
        }

        return sb.toString();
    }

    public static OwfeDataSource lookupDataSource
        (final ApplicationContext context,
         final java.util.Map serviceParams)
    throws
        ServiceException
    {
        String dsKey = (String)serviceParams
            .get(OwfeDataSource.P_DATA_SOURCE);

        log.debug
            ("lookupDataSource() looking for data source named '"+dsKey+"'");

        /*
        if (dsKey == null)
        {
            throw new ServiceException
                ("No dataSource indicated, parameter '"+
                 OwfeDataSource.DATA_SOURCE+"' is not set");
        }
        */

        if (dsKey == null) dsKey = OwfeDataSource.P_DATA_SOURCE;

        final OwfeDataSource ds = (OwfeDataSource)context.lookup(dsKey);

        if (ds == null)
        {
            throw new ServiceException
                ("No OwfeDataSource stored as service named '"+dsKey+"'");
        }

        return ds;
    }

}