sqlwrapperimpl.java

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

        // SQL job.
        Statement stat = null;
        try {
            stat = doStatement( con );
            ResultSet rs = executeQuery( stat, sql.toString() );
            if( rs.next() ) {
                value = rs.getString( 1 );
            }
        } finally {
            closeConnection( stat );
        }

        return value;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeSql(Connection, String, String, String, String)
     */
    public final String executeSql( Connection con, String tableName, String fieldName, String pkeyName, String 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 ).append( "'" );

        // SQL job.
        Statement stat = null;
        try {
            stat = doStatement( con );
            ResultSet rs = executeQuery( stat, sql.toString() );
            if( rs.next() ) {
                value = rs.getString( 1 );
            }
        } finally {
            closeConnection( stat );
        }

        return value;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#getParser(int)
     */
    public final SqlTypeParser getParser( int sqltype ) {

        // Get parser instance.
        SqlTypeParser parser = null;
        switch( sqltype ) {
        case SqlSType.INT_TYPE:
            parser = getIntParser();
            break;
        case SqlSType.LONG_TYPE:
            parser = getLongParser();
            break;
        case SqlSType.FLOAT_TYPE:
            parser = getFloatParser();
            break;
        case SqlSType.STRING_TYPE:
            parser = getStringParser();
            break;
        case SqlSType.TIMESTAMP_TYPE:
            parser = getTimestampParser();
            break;
        case SqlSType.DATE_TYPE:
            parser = getDateParser();
            break;
        case SqlSType.TIME_TYPE:
            parser = getTimeParser();
            break;
        case SqlSType.MEMO_TYPE:
            parser = getMemoParser();
            break;
        case SqlSType.MEMO_LONG_TYPE:
            parser = getLongParser();
            break;
        case SqlSType.BINARY_TYPE:
            parser = getBinaryParser();
            break;
        default:
            throw new GenericSystemException( "Can't get parser for sql type '" + sqltype + "'" );
        }

        // Ok.
        return parser;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#getNextKey(Connection, String)
     */
    public long getNextKey( Connection con, String table )
        throws SQLException {
        return getNextKey( con, table, 1 );
    }

    /* (non-Javadoc)
     * @see SqlWrapper#getNextKey(Connection, String, int)
     */
    public long getNextKey( Connection con, String table, int range )
        throws SQLException {

        CallableStatement cs = null;
        table = table.toLowerCase();
        long keyValue;

        // Make SQL query.
        String sql = "{call " + NEXT_KEY_PROC + "('" + table + "', " + range + ", ?)}";
        DEBUG( "next key sql: " + sql );

        try {
            cs = con.prepareCall( sql );
            cs.registerOutParameter( 1, Types.NUMERIC );
            cs.execute();

            keyValue = cs.getLong( 1 );
            if( cs.wasNull() ) {
                keyValue = StringHelper.EMPTY_NUMBER;
            }

        } finally {
            closeConnection( cs );
        }

        // Ok.
        return keyValue;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#typeMapping(int, int)
     */
    public SqlSType typeMapping( int sqlType, int sqlColumnSize ) {

        // Get type.
        SqlSType sqlSType = null;
        switch( sqlType ) {
            case Types.TINYINT:
            case Types.SMALLINT:
                sqlSType = SqlSType.INT;
                break;
            case Types.INTEGER:
            case Types.NUMERIC:
            case Types.DECIMAL:
                if( sqlColumnSize > 0 && sqlColumnSize < 32 ) {
                    sqlSType = SqlSType.INT;
                } else {
                    sqlSType = SqlSType.LONG;
                }
                break;
            case Types.BIGINT:
                sqlSType = SqlSType.LONG;
                break;
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                sqlSType = SqlSType.FLOAT;
                break;
            case Types.CHAR:
            case Types.VARCHAR:
                sqlSType = SqlSType.STRING;
                break;
            case Types.DATE:
                sqlSType = SqlSType.DATE;
                break;
            case Types.TIMESTAMP:
                sqlSType = SqlSType.TIMESTAMP;
                break;
            case Types.TIME:
                sqlSType = SqlSType.TIME;
                break;
            case Types.CLOB:
                sqlSType = SqlSType.MEMO;
                break;
            case Types.LONGVARCHAR:
                sqlSType = SqlSType.MEMO;//text type is mapped to MEMO sql type, because till 2.6 application version it is fixing by the handmade attributes.
                break;
        }

        // Ok.
        return sqlSType;

    }


    // ======================================================= Protected methods

    /**
     * @todo Describe it!
     *
     * @param ex SQLException to (re-)throw
     * @throws SQLException
     */
    protected abstract void throwSQLException( SQLException ex )
        throws SQLException;

    /**
     * Soundex function value getter. Eliminates the code duplication in
     * database-specific subclasses.
     *
     * @param con database connection
     * @param soundexSQL database-specific SQL query to get soundex value
     * @param str string to get soundex value of
     * @return soundex value
     * @throws SQLException
     */
    protected String getSoundexBySQL( Connection con, String soundexSQL, String str )
        throws SQLException {

        PreparedStatement stat = null;
        String soundex = null;

        try {
            stat = doPreparedStatement( con, soundexSQL );
            stat.setString( 1, str );
            ResultSet rs = executeQuery( stat );
            if( rs.next() ) {
                soundex = rs.getString( 1 );
            }

        } finally {
            closeConnection( stat );
        }

        return soundex;
    }

    /**
     * Current schema getter. Eliminates the code duplication in database-
     * specific subclasses.
     *
     * @param con database connection
     * @param schemaSQL database-specific SQL query to get current schema
     * @return current schema name
     * @throws SQLException
     */
    protected String getCurrentSchemaBySQL( Connection con, String schemaSQL )
        throws SQLException {

        Statement stat = null;
        String schema = null;

        try {
            stat = doStatement( con );
            ResultSet rs = executeQuery( stat, schemaSQL );
            if( rs.next() ) {
                schema = rs.getString( 1 );
            }

        } finally {
            closeConnection( stat );
        }

        return schema;
    }

    /**
     * Prints SQL info.
     *
     * @param sql SQL query
     * @param time from application start
     */
    protected void printSQL( String sql, long time ) {
        try {
            time = System.currentTimeMillis() - time;
            RE re = new RE( "(\\n|\\t)" );
            String newSql = re.subst( sql, " " );
            INFO( time + ";" + newSql );
        } catch( RESyntaxException ex ) {
            ERROR( ex );
        }
    }

    // ====================================================== DataSource getters

    //
    // DataSource object getter.
    // [ALB] switched from protected to public for testing
    public synchronized javax.sql.DataSource doDataSource() {

        // !!! Don't save reference on DataSource as SqlWrapper
        // instance can be used in different contexts.
        javax.sql.DataSource ds;

        if( dsJNDI == null ) {
            throw new NullPointerException( "Cannot load DataSource JNDI name" );
        }

        try {
            javax.naming.InitialContext context = new javax.naming.InitialContext();
            ds = ( javax.sql.DataSource ) context.lookup( dsJNDI );

            if( getLogger().isDebugEnabled() ) {
                DEBUG( "Data source '" + dsJNDI + "' loaded. Class: " +
                       ds.getClass().getName() );
            }

        } catch( Exception ex ) {
            String err = "Cannot lookup data source by '" + dsJNDI +
                "': " + ex.getMessage();

            ERROR( err, ex );
            throw new GenericSystemException( err, ex );
        }

        return ds;
    }
}