sqlselectbuildergenericimpl.java

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

            default:
                throw new GenericSystemException( "Unsupported order operation '" + op + "'" );
        }

        return orderClause.toString();
    }

    /**
     * EQL to SQL where clause transformation
     * @param reqWhere EQLReqWhere object
     * @return sql substring
     * @throws EQLException
     */
    protected String getSQLWhere( EQLReqWhere reqWhere )
        throws EQLException {

        StringBuffer whereClause = new StringBuffer();

        int size = reqWhere.size();

        for( int i = 0; i < size; i++ ) {
            EQLReqSubWhere reqSubWhere = reqWhere.getSubWhere( i );

            int op = reqSubWhere.getOperation();
            switch( op ) {
                case EQLReqSubWhere.NONE_OP:
                    // default where conditions could be before...
                    if( i > 0 ) {
                        whereClause.append( " AND\n" );
                    }
                    break;

                case EQLReqSubWhere.AND_OP:
                    whereClause.append( " AND\n" );
                    break;

                case EQLReqSubWhere.OR_OP:
                    whereClause.append( " OR\n" );
                    break;

                default:
                    throw new GenericSystemException( "Unsupported where operation '" + op + "'" );
            }

            java.io.Serializable o = reqSubWhere.getCond();
            whereClause.append( _getSQLCond( o ) );
        }

        return whereClause.toString();
    }

    //
    // Operand
    //

    protected String getSQLOperand( EQLReqOp reqOp )
        throws EQLException {

        StringBuffer sb = new StringBuffer();

        int size = reqOp.size();
        for( int i = 0; i < size; i++ ) {
            EQLReqSubOp subOp = reqOp.getSubOp( i );
            sb.append( _getSQLSubOperand( subOp ) );
        }

        return sb.toString();
    }

    //
    // Conditions
    //

    protected String getSQLCond( EQLReqWhere reqWhere )
        throws EQLException {

        return "(\n" + getSQLWhere( reqWhere ) + "\n)";
    }

    protected String getSQLCond( EqCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " = " + rightOp;
    }

    protected String getSQLCond( GtCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " > " + rightOp;
    }

    protected String getSQLCond( LtCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " < " + rightOp;
    }

    protected String getSQLCond( GtEqCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " >= " + rightOp;
    }

    protected String getSQLCond( LtEqCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " <= " + rightOp;
    }

    protected String getSQLCond( NotEqCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " != " + rightOp;
    }

    protected String getSQLCond( IsNullCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        return leftOp + " IS NULL";
    }

    protected String getSQLCond( IsNotNullCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        return leftOp + " IS NOT NULL";
    }

    protected String getSQLCond( InCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " IN " + rightOp;
    }

    protected String getSQLCond( NotInCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " NOT IN " + rightOp;
    }

    protected String getSQLCond( LikeCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " LIKE " + rightOp + " ESCAPE '" + EQL_ESCAPE_CHARACTER + "'";
    }

    protected String getSQLCond( NotLikeCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        String rightOp = getSQLOperand( cond.getRightMember() );
        return leftOp + " NOT LIKE " + rightOp + " ESCAPE '" + EQL_ESCAPE_CHARACTER + "'";
    }

    protected String getSQLCond( NoneCond cond )
        throws EQLException {

        String leftOp = getSQLOperand( cond.getLeftMember() );
        return leftOp;
    }

    //
    // Sub Operands
    //

    protected String getSQLSubOperand( NoneOp subOp )
        throws EQLException {

        return _getSQLOperandMember( subOp.getMember() );
    }

    protected String getSQLSubOperand( PlusOp subOp )
        throws EQLException {

        return "+" + _getSQLOperandMember( subOp.getMember() );
    }

    protected String getSQLSubOperand( MinusOp subOp )
        throws EQLException {

        return "-" + _getSQLOperandMember( subOp.getMember() );
    }

    protected String getSQLSubOperand( MultOp subOp )
        throws EQLException {

        return "*" + _getSQLOperandMember( subOp.getMember() );
    }

    protected String getSQLSubOperand( DivOp subOp )
        throws EQLException {

        return "/" + _getSQLOperandMember( subOp.getMember() );
    }

    //
    // Operand Members
    //

    protected String getSQLOperandMember( EQLReqOp memberField )
        throws EQLException {

        return "(" + getSQLOperand( memberField ) + ")";
    }

    protected String getSQLOperandMember( EQLReqSubOpMemberField memberField )
        throws EQLException {

        if( memberField.getReqField() == null ) {
            return "NULL";
        } else {
            return getSQLColumnName( memberField.getReqField() );
        }
    }

    protected String getSQLOperandMember( EQLReqSubOpMemberUnknown memberField ) {
        return "?";
    }

    protected String getSQLOperandMember( EQLReqSubOpMemberEnum memberField )
        throws EQLException {

        StringBuffer ret = new StringBuffer( "(" );
        int size = memberField.getSize();
        for( int i = 0; i < size; i++ ) {
            if( i > 0 ) {
                ret.append( "," );
            }
            ret.append( _getSQLOperandMember( memberField.getParameter( i ) ) );
        }
        ret.append( ")" );

        return ret.toString();
    }

    protected String getSQLOperandMember( EQLReq memberField )
        throws EQLException {

        SQLSelectBuilder child = ( SQLSelectBuilder ) createChildBuilder();
        String childSQL = child.buildSelectSql( memberField ).getMainSql();
        if( childSQL == null ) {
            /** @todo is it right to insert NULL here? */
            return "( NULL )";
        } else {
            return "(" + childSQL + ")";
        }
    }

    protected String getSQLOperandMember( SoundexFunc memberField )
        throws EQLException {

        return "soundex(" + getSQLOperand( memberField.getParameter( 0 ) ) + ")";
    }

    protected String getSQLOperandMember( LowerFunc memberField )
        throws EQLException {

        return "lower(" + getSQLOperand( memberField.getParameter( 0 ) ) + ")";
    }

    protected String getSQLOperandMember( UpperFunc memberField )
        throws EQLException {

        return "upper(" + getSQLOperand( memberField.getParameter( 0 ) ) + ")";
    }

    protected String getSQLOperandMember( DateDiffFunc memberField )
        throws EQLException {

        return "datediff(" + getSQLOperand( memberField.getParameter( 0 ) ) + "," + getSQLOperand( memberField.getParameter( 1 ) ) + ")";
    }

    protected String getSQLOperandMember( IsNullFunc memberField )
        throws EQLException {

        return "isnull(" + getSQLOperand( memberField.getParameter( 0 ) ) + "," + getSQLOperand( memberField.getParameter( 1 ) ) + ")";
    }

    protected String getSQLOperandMember( CountAggFunc memberField )
        throws EQLException {

        return "count(*)";
    }

    protected String getSQLOperandMember( MinAggFunc memberField )
        throws EQLException {

        return "min(" + getSQLOperand( memberField.getParameter( 0 ) ) + ")";
    }

    protected String getSQLOperandMember( MaxAggFunc memberField )
        throws EQLException {

        return "max(" + getSQLOperand( memberField.getParameter( 0 ) ) + ")";
    }

    //
    // Values
    //

    protected String getSQLValue( EQLStringObject o )
        throws EQLException {
        return StringHelper.java2sql( o.getValue() );
    }

    protected String getSQLValue( EQLNumberObject o )
        throws EQLException {
        return o.getValue().toString();
    }

    protected String getSQLValue( EQLDateObject o )
        throws EQLException {
        return sqlWrapper.getTimestampParser().sqlDateFunction( o.getValue() );
    }

    protected String getSQLValue( EQLTimeObject o )
        throws EQLException {
        return sqlWrapper.getTimeParser().sqlTimeFunction( o.getValue() );
    }

    protected String getSQLValue( EQLNullObject o )
        throws EQLException {
        return "NULL";
    }

// ---------------- PRIVATE METHODS ------------------------

    //
    // Build JOIN clause
    //
    private void getJoinClause( EQLReq req,
                                EQLReqEntity reqLeftEntity,
                                StringBuffer sql )
        throws EQLException {

        EQLReqFrom reqFrom = req.getFrom();
        int joinSize = reqFrom.joinSize();

        // add all join entities for left entity
        for( int j = 0; j < joinSize; j++ ) {
            EQLReqJoin reqJoin = reqFrom.getJoinEntity( j );

            if( reqLeftEntity.equals( reqJoin.getLeftEntity() ) ) {
                sql.append( "\n" );
                sql.append( getSQLJoin( reqJoin ) );
                getJoinClause( req, reqJoin.getRightEntity(), sql );
            }
        }
    }

}