sqlwrapperimpl.java

CRM源码This file describes some issues that should be implemented in future and how it should be imple

/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package com.queplix.core.utils.sql.generic;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.jxb.entity.types.SqlSType;
import com.queplix.core.jxb.sqlwrapper.Property;
import com.queplix.core.utils.StringHelper;
import com.queplix.core.utils.SystemHelper;
import com.queplix.core.utils.log.AbstractLogger;
import com.queplix.core.utils.sql.SqlWrapper;
import com.queplix.core.utils.sql.error.SQLDeleteConflictException;
import com.queplix.core.utils.sql.error.SQLDuplicateKeyException;
import com.queplix.core.utils.sql.error.SQLIndexConflictException;
import com.queplix.core.utils.sql.parser.SqlTypeParser;
import org.apache.regexp.RE;
import org.apache.regexp.RESyntaxException;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

/**
 * Generic SQL operations wrapper implementation.
 * !!! Don't share the instance between EJBs.
 *
 * @author [ALB] Baranov Andrey
 * @author [ONZ] Oleg N. Zhovtanyuk
 * @version $Revision: 1.2 $ $Date: 2006/01/20 15:19:10 $
 */
public abstract class SqlWrapperImpl
    extends AbstractLogger implements SqlWrapper {

    // ================================================================== Constants

    /** Property - DataSource JNDI name. */
    protected static final String DATASOURCE_PROPERTY = "datasource";

    /** SQL stored procedure to get next key. */
    protected static final String NEXT_KEY_PROC = "QX_NEXT_KEY";

    // ================================================================== Fields

    // Temporary data.
    protected String dsJNDI;

    // ================================================ Interface implementation

    /* (non-Javadoc)
     * @see SqlWrapper#init
     */
    public void init( Property[] properties ) {

        if( properties == null ) {
            WARN( "No properties found" );
            return;
        }

        // Read property and initialize datasource JNDI names
        for( int i = 0; i < properties.length; i++ ) {
            Property p = properties[i];
            String name = p.getName();
            String value = p.getValue();

            if( name.equalsIgnoreCase( DATASOURCE_PROPERTY ) ) {
                this.dsJNDI = value;
            }
        }
    }

    /* (non-Javadoc)
     * @see SqlWrapper#doConnection()
     */
    public Connection doConnection() {

        // >>> debug
        // System.err.println(">>>>>>>>>>>>>>> doConnection()");
        // Thread.dumpStack();
        // <<<

        Connection con = null;
        try {
//            INFO( "### doDataSource(): " + doDataSource().getClass().getName() );
            con = doDataSource().getConnection();
        } catch( SQLException ex ) {
            ERROR( ex );
            throw new GenericSystemException( "Can't get connection: " + ex.getMessage(), ex );
        }
        return con;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#doConnection(boolean)
     */
    public Connection doConnection( boolean autocommit ) {
        Connection con = null;
        try {
            con = doDataSource().getConnection();
            con.setAutoCommit( autocommit );
        } catch( SQLException ex ) {
            ERROR( ex );
            throw new GenericSystemException( "Can't get connection: " + ex.getMessage(), ex );
        }
        return con;
    }

    /* (non-Javadoc)
     * @see SqlWrapper#doStatement(Connection)
     */
    public Statement doStatement( Connection con ) {
        Statement stat = null;
        try {
            stat = con.createStatement();
        } catch( SQLException ex ) {
            throw new GenericSystemException( "Can`t create statement: " + ex.getMessage(), ex );
        }
        return stat;
    }

    /* (non-Javadoc)
     * @see SqlWrapper#doPreparedStatement(Connection, String)
     */
    public PreparedStatement doPreparedStatement( Connection con, String sql ) {

        PreparedStatement stat = null;
        try {
            stat = con.prepareStatement( sql );
        } catch( SQLException ex ) {
            ERROR( ex );
            throw new GenericSystemException( "Can`t create statement for SQL '" + sql + "': " + ex.getMessage(), ex );
        }

        if( !SystemHelper.isProductionMode() ) {
            printSQL( sql, -1 );
        }
        return stat;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#closeConnection(Connection)
     */
    public void closeConnection( Connection con ) {
        try {
            if( con != null ) {
                con.close();
            }
        } catch( Exception ex ) {}
    }

    /* (non-Javadoc)
     * @see SqlWrapper#closeConnection(Statement)
     */
    public void closeConnection( Statement stat ) {
        try {
            if( stat != null ) {
                stat.close();
            }
        } catch( Exception ex ) {}
    }

    /* (non-Javadoc)
     * @see SqlWrapper#closeConnection(Connection, Statement)
     */
    public void closeConnection( Connection con, Statement stat ) {
        try {
            if( stat != null ) {
                stat.close();
            }
        } catch( Exception ex ) {}
        try {
            if( con != null ) {
                con.close();
            }
        } catch( Exception ex ) {}
    }

    /* (non-Javadoc)
     * @see SqlWrapper#closeResultSet(ResultSet)
     */
    public void closeResultSet( ResultSet rs ) {
        try {
            rs.close();
        } catch( Exception ex ) {}
    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeQuery(Statement, String)
     */
    public ResultSet executeQuery( Statement stat, String sql )
        throws SQLException {

        long time = 0;
        if( !SystemHelper.isProductionMode() ) {
            time = System.currentTimeMillis();
        }

        ResultSet rs = stat.executeQuery( sql );

        if( !SystemHelper.isProductionMode() ) {
            printSQL( sql, time );
        }

        return rs;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeQuery(PreparedStatement)
     */
    public ResultSet executeQuery( PreparedStatement stat )
        throws SQLException {
        return stat.executeQuery();
    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeUpdate(Statement, String)
     */
    public int executeUpdate( Statement stat, String sql )
        throws SQLDuplicateKeyException, SQLIndexConflictException, SQLDeleteConflictException, SQLException {

        // Initialization.
        long time = 0;
        boolean isProduction = SystemHelper.isProductionMode();
        if( !isProduction ) {
            time = System.currentTimeMillis();
        }

        // SQL job.
        int rows = 0;
        try {
            rows = stat.executeUpdate( sql );
            if( !isProduction ) {
                printSQL( sql, time );
            }
        } catch( SQLException ex ) {
            throwSQLException( ex );
        }

        // Ok.
        return rows;
    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeUpdate(PreparedStatement)
     */
    public int executeUpdate( PreparedStatement stat )
        throws SQLDuplicateKeyException, SQLIndexConflictException, SQLDeleteConflictException, SQLException {
        int rows = 0;
        try {
            return stat.executeUpdate();
        } catch( SQLException ex ) {
            throwSQLException( ex );
        }
        return rows;
    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeSql(Connection, String, int)
     */
    public final String executeSql( Connection con, String sql, int sqltype )
        throws SQLException {

        String value = null;
        Statement stat = null;

        try {
            stat = doStatement( con );
            ResultSet rs = executeQuery( stat, sql );
            if( rs.next() ) {
                Object obj = getParser( sqltype ).getObject( rs, 1 );
                if( !rs.wasNull() ) {
                    value = obj.toString();
                }
            }
        } finally {
            closeConnection( stat );
        }

        return value;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeSql(Connection, String, String, String, long)
     */
    public final String executeSql( Connection con, String tableName, String fieldName, String pkeyName, long pkey )
        throws SQLException {

        String value = null;

        // Make SQL query.
        StringBuffer sql = new StringBuffer();
        sql.append( "SELECT " ).append( fieldName ).append( " FROM " );
        sql.append( tableName ).append( " WHERE " + pkeyName + " = " );
        sql.append( pkey );