sqlprocessor.java

Sequoia ERP是一个真正的企业级开源ERP解决方案。它提供的模块包括：电子商务应用(e-commerce), POS系统(point of sales),知识管理,存货与仓库管理

/*
 * $Id: SQLProcessor.java 5993 2005-10-21 06:32:11Z jonesde $
 *
 * Copyright (c) 2001-2005 The Open For Business Project - www.ofbiz.org
 *
 * Permission is hereby granted, free of charge, to any person obtaining a
 * copy of this software and associated documentation files (the "Software"),
 * to deal in the Software without restriction, including without limitation
 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
 * and/or sell copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included
 * in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
 * IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
 * CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT
 * OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR
 * THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */
package org.ofbiz.entity.jdbc;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectOutputStream;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.ofbiz.base.util.Debug;
import org.ofbiz.entity.GenericDataSourceException;
import org.ofbiz.entity.GenericEntityException;
import org.ofbiz.entity.config.DatasourceInfo;
import org.ofbiz.entity.config.EntityConfigUtil;
import org.ofbiz.entity.transaction.GenericTransactionException;
import org.ofbiz.entity.transaction.TransactionUtil;

/**
 * SQLProcessor - provides utitlity functions to ease database access
 * 
 * @author     <a href="mailto:[e-mail]">David E. Jones</a>
 * @author     <a href="mailto:[e-mail]">Andy Zeneski</a>
 * @version    $Rev: 5993 $
 * @since      2.0
 */
public class SQLProcessor {

    /** Module Name Used for debugging */
    public static final String module = SQLProcessor.class.getName();
    
    /** Used for testing connections when test is enabled */
    public static List CONNECTION_TEST_LIST = new ArrayList();
    public static int MAX_CONNECTIONS = 1000;
    public static boolean ENABLE_TEST = false;

    /** The datasource helper (see entityengine.xml <datasource name="..">) */
    private String helperName;

    // / The database resources to be used
    private Connection _connection = null;

    // / The database resources to be used
    private PreparedStatement _ps = null;

    // / The database resources to be used
    private Statement _stmt = null;

    // / The database resources to be used
    private ResultSet _rs = null;
    
    private ResultSetMetaData _rsmd = null;

    // / The SQL String used. Use for debugging only
    private String _sql;

    // / Index to be used with preparedStatement.setValue(_ind, ...)
    private int _ind;

    // / true in case of manual transactions
    private boolean _manualTX;

    // / true in case the connection shall be closed.
    private boolean _bDeleteConnection = false;
    
    private Map _needClobWorkAroundWrite = null;
    private Map _needBlobWorkAroundWrite = null;
    
    /**
     * Construct an object based on the helper/datasource
     *
     * @param helperName  The datasource helper (see entityengine.xml &lt;datasource name=".."&gt;)
     */
    public SQLProcessor(String helperName) {
        this.helperName = helperName;
        this._manualTX = true;
    }

    /**
     * Construct an object with an connection given. The connection will not
     * be closed by this SQLProcessor, but may be by some other.
     *
     * @param helperName  The datasource helper (see entityengine.xml &lt;datasource name=".."&gt;)
     * @param connection  The connection to be used
     */
    public SQLProcessor(String helperName, Connection connection) {
        this.helperName = helperName;
        this._connection = connection;

        // Do not commit while closing
        if (_connection != null) {
            _manualTX = false;
        }
    }
    
    ResultSetMetaData getResultSetMetaData() {
        if (_rsmd == null) {
            // try the ResultSet, if not null, or try the PreparedStatement, also if not null
            try {
                if (_rs != null) {
                    _rsmd = _rs.getMetaData();
                } else if (_ps != null) {
                    _rsmd = _ps.getMetaData();
                }
            } catch (SQLException sqle2) {
                Debug.logWarning("[SQLProcessor.rollback]: SQL Exception while rolling back insert. Error was:" + sqle2, module);
                Debug.logWarning(sqle2, module);
            }
        }
        return _rsmd;
    }

    /**
     * Commit all modifications
     *
     * @throws GenericDataSourceException
     */
    public void commit() throws GenericDataSourceException {
        if (_connection == null) {
            return;
        }
        
        if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:commit() _manualTX=" + _manualTX, module);

        if (_manualTX) {
            try {
                _connection.commit();
                if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:commit() : called commit on connection", module);
            } catch (SQLException sqle) {
                Debug.logError(sqle, "Error committing transaction: " + sqle.toString());
                try {
                    rollback();
                } catch (GenericDataSourceException rbsqle) {
                    Debug.logError(rbsqle, "Got another error when trying to rollback after error committing transaction: " + sqle.toString());
                } 
                throw new GenericDataSourceException("SQL Exception occurred on commit", sqle);
            }
        }
    }

    /**
     * Rollback all modifications
     */
    public void rollback() throws GenericDataSourceException {
        if (_connection == null) {
            return;
        }
        
        if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:rollback() _manualTX=" + _manualTX, module);

        try {
            if (_manualTX) {
                _connection.rollback();
                if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:rollback() : _manualTX=" + _manualTX, module);
            } else {
                try {
                    TransactionUtil.setRollbackOnly("rollback called in Entity Engine SQLProcessor", new Exception("Current Location Stack"));
                    if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:rollback() : _manualTX=" + _manualTX, module);
                } catch (GenericTransactionException e) {
                    Debug.logError(e, "Error setting rollback only", module);
                    throw new GenericDataSourceException("Error setting rollback only", e);
                }
            }
        } catch (SQLException sqle2) {
            Debug.logWarning("[SQLProcessor.rollback]: SQL Exception while rolling back insert. Error was:" + sqle2, module);
            Debug.logWarning(sqle2, module);
        }
    }

    /**
     * Commit if required and remove all allocated resources
     *
     * @throws GenericDataSourceException
     */
    public void close() throws GenericDataSourceException {        
        if (_manualTX) {
            if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:close() calling commit : _manualTX=" + _manualTX, module);
            commit();
        }

        _sql = null;

        if (_rs != null) {
            try {
                _rs.close();
                if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:close() result close : _manualTX=" + _manualTX, module);
            } catch (SQLException sqle) {
                Debug.logWarning(sqle.getMessage(), module);
            }

            _rs = null;
        }

        if (_ps != null) {
            try {
                _ps.close();
                if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:close() preparedStatement close : _manualTX=" + _manualTX, module);
            } catch (SQLException sqle) {
                Debug.logWarning(sqle.getMessage(), module);
            }

            _ps = null;
        }

        if (_stmt != null) {
            try {
                _stmt.close();
                if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:close() statement close : _manualTX=" + _manualTX, module);
            } catch (SQLException sqle) {
                Debug.logWarning(sqle.getMessage(), module);
            }

            _stmt = null;
        }

        if ((_connection != null) && _bDeleteConnection) {
            try {
                _connection.close();
                if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:close() connection close : _manualTX=" + _manualTX, module);
            } catch (SQLException sqle) {
                Debug.logWarning(sqle.getMessage(), module);
            }

            _connection = null;
        }               
    }

    /**
     * Get a connection from the ConnectionFactory
     *
     * @return  The connection created
     *
     * @throws GenericDataSourceException
     * @throws GenericEntityException
     */
    public Connection getConnection() throws GenericDataSourceException, GenericEntityException {
        if (_connection != null)
            return _connection;

        _manualTX = true;

        try {
            _connection = ConnectionFactory.getConnection(helperName);
            if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:connection() : manualTx=" + _manualTX, module);
        } catch (SQLException sqle) {
            throw new GenericDataSourceException("Unable to esablish a connection with the database.", sqle);
        }

        // make sure we actually did get a connection
        if (_connection == null) {
            throw new GenericDataSourceException("Unable to esablish a connection with the database. Connection was null!");
        }

        // test the connection
        testConnection(_connection);
        
        /* causes problems w/ postgres ??
        if (Debug.verboseOn()) {
            int isoLevel = -999;
            try {
                isoLevel = _connection.getTransactionIsolation();
            } catch (SQLException e) {
                Debug.logError(e, "Problems getting the connection's isolation level", module);
            }
            if (isoLevel == Connection.TRANSACTION_NONE) {
                Debug.logVerbose("Transaction isolation level set to 'None'.", module);
            } else if (isoLevel == Connection.TRANSACTION_READ_COMMITTED) {
                Debug.logVerbose("Transaction isolation level set to 'ReadCommited'.", module);
            } else if (isoLevel == Connection.TRANSACTION_READ_UNCOMMITTED) {
                Debug.logVerbose("Transaction isolation level set to 'ReadUncommitted'.", module);
            } else if (isoLevel == Connection.TRANSACTION_REPEATABLE_READ) {
                Debug.logVerbose("Transaction isolation level set to 'RepeatableRead'.", module);
            } else if (isoLevel == Connection.TRANSACTION_SERIALIZABLE) {
                Debug.logVerbose("Transaction isolation level set to 'Serializable'.", module);
            }
        }
        */
                            
        // always try to set auto commit to false, but if we can't then later on we won't commit
        try {
            if (_connection.getAutoCommit()) {
                try {
                    _connection.setAutoCommit(false);
                    if (Debug.verboseOn()) Debug.logVerbose("SQLProcessor:setAutoCommit(false) : manualTx=" + _manualTX, module);
                } catch (SQLException sqle) {
                    _manualTX = false;
                }
            }
        } catch (SQLException e) {
            throw new GenericDataSourceException("Cannot get autoCommit status from connection", e);            
        }

        try {
            if (TransactionUtil.getStatus() == TransactionUtil.STATUS_ACTIVE) {
                if (Debug.verboseOn()) Debug.logVerbose("[SQLProcessor.getConnection] : active transaction", module);
                _manualTX = false;
            }
        } catch (GenericTransactionException e) {
            // nevermind, don't worry about it, but print the exc anyway
            Debug.logWarning("[SQLProcessor.getConnection]: Exception was thrown trying to check " +
                "transaction status: " + e.toString(), module);
        }

        if (Debug.verboseOn()) Debug.logVerbose("[SQLProcessor.getConnection] : con=" + _connection, module);
        
        _bDeleteConnection = true;        
        return _connection;
    }

    /**
     * Prepare a statement. In case no connection has been given, allocate a
     * new one.
     *
     * @param sql  The SQL statement to be executed
     *
     * @throws GenericDataSourceException
     * @throws GenericEntityException
     */
    public void prepareStatement(String sql) throws GenericDataSourceException, GenericEntityException {
        this.prepareStatement(sql, false, 0, 0, -1, -1);
    }

    /**
     * Prepare a statement. In case no connection has been given, allocate a
     * new one.
     *
     * @param sql  The SQL statement to be executed
     *
     * @throws GenericDataSourceException
     * @throws GenericEntityException
     */
    public void prepareStatement(String sql, boolean specifyTypeAndConcur, int resultSetType, int resultSetConcurrency) throws GenericDataSourceException, GenericEntityException {
        this.prepareStatement(sql, specifyTypeAndConcur, resultSetType, resultSetConcurrency, -1, -1);
    }

    /**
     * Prepare a statement. In case no connection has been given, allocate a
     * new one.
     *
     * @param sql  The SQL statement to be executed
     *
     * @throws GenericDataSourceException
     * @throws GenericEntityException
     */
    public void prepareStatement(String sql, boolean specifyTypeAndConcur, int resultSetType, int resultSetConcurrency, int fetchSize, int maxRows) throws GenericDataSourceException, GenericEntityException {
        if (Debug.verboseOn()) Debug.logVerbose("[SQLProcessor.prepareStatement] sql=" + sql, module);

        if (_connection == null) {
            getConnection();
        }

        try {
            _sql = sql;
            _ind = 1;
            if (specifyTypeAndConcur) {
                _ps = _connection.prepareStatement(sql, resultSetType, resultSetConcurrency);
                if (Debug.verboseOn()) Debug.logVerbose("[SQLProcessor.prepareStatement] _ps=" + _ps, module);
            } else {
                _ps = _connection.prepareStatement(sql);
                if (Debug.verboseOn()) Debug.logVerbose("[SQLProcessor.prepareStatement] (def) _ps=" + _ps, module);
            }
            if (maxRows > 0) {
                _ps.setMaxRows(maxRows);
                if (Debug.verboseOn()) Debug.logVerbose("[SQLProcessor.prepareStatement] max rows set : " + maxRows, module);
            }
            this.setFetchSize(_ps, fetchSize);
        } catch (SQLException sqle) {
            throw new GenericDataSourceException("SQL Exception while executing the following:" + sql, sqle);
        }
    }

    /**
     * Execute a query based on the prepared statement
     *
     * @return The result set of the query
     * @throws GenericDataSourceException
     */
    public ResultSet executeQuery() throws GenericDataSourceException {
        try {
            // if (Debug.verboseOn()) Debug.logVerbose("[SQLProcessor.executeQuery] ps=" + _ps.toString(), module);
            _rs = _ps.executeQuery();
        } catch (SQLException sqle) {
            throw new GenericDataSourceException("SQL Exception while executing the following:" + _sql, sqle);
        }

        return _rs;
    }

    /**
     * Execute a query baed ont SQL string given
     *
     * @param sql  The SQL string to be executed
     * @return  The result set of the query
     * @throws GenericEntityException
     * @throws GenericDataSourceException
     */