SQLEntityFinder.java

/*
 * Copyright (c) 2010, Stanislav Muhametsin. All Rights Reserved.
 * Copyright (c) 2012, Paul Merlin. All Rights Reserved.
 *
 * Licensed under the Apache License, Version 2.0 (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.apache.org/licenses/LICENSE-2.0
 * 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 org.qi4j.index.sql.internal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.qi4j.api.common.Optional;
import org.qi4j.api.composite.Composite;
import org.qi4j.api.entity.EntityReference;
import org.qi4j.api.injection.scope.Service;
import org.qi4j.api.query.grammar.OrderBy;
import org.qi4j.functional.Specification;
import org.qi4j.index.sql.support.api.SQLQuerying;
import org.qi4j.library.sql.common.SQLUtil;
import org.qi4j.spi.query.EntityFinder;
import org.qi4j.spi.query.EntityFinderException;

public class SQLEntityFinder
    implements EntityFinder
{
    @Service
    private SQLQuerying parser;

    @Service
    private DataSource _dataSource;

    /**
     * Helper interface to perform some SQL query. Using this simplifies the structure of some of the methods.
     *
     * @param <ReturnType> The return type of something to be done.
     */
    private interface DoQuery<ReturnType>
    {
        ReturnType doIt( Connection connection )
            throws SQLException;
    }

    @Override
    public long countEntities( Class<?> resultType, @Optional Specification<Composite> whereClause, Map<String, Object> variables ) throws EntityFinderException
    {
        final List<Object> values = new ArrayList<Object>();
        final List<Integer> valueSQLTypes = new ArrayList<Integer>();
        final String query = this.parser.constructQuery( resultType, whereClause, null, null, null, variables, values,
            valueSQLTypes, true );

        return this.performQuery( new DoQuery<Long>()
        {

            @Override
            public Long doIt( Connection connection )
                throws SQLException
            {
                PreparedStatement ps = null;
                ResultSet rs = null;
                try
                {
                    ps = createPS( connection, query, values, valueSQLTypes );
                    rs = ps.executeQuery();
                    rs.next();
                    return rs.getLong( 1 );
                }
                finally
                {
                    SQLUtil.closeQuietly( rs );
                    SQLUtil.closeQuietly( ps );
                }
            }
        } );
    }

    @Override
    public Iterable<EntityReference> findEntities( Class<?> resultType, @Optional Specification<Composite> whereClause, @Optional OrderBy[] orderBySegments, @Optional final Integer firstResult, @Optional final Integer maxResults, Map<String, Object> variables ) throws EntityFinderException
    {
        // TODO what is Qi4j's policy on negative firstResult and/or maxResults? JDBC has its own way of interpreting
        // these values - does it match with Qi4j's way?
        Iterable<EntityReference> result;
        if( maxResults == null || maxResults > 0 )
        {
            final List<Object> values = new ArrayList<Object>();
            final List<Integer> valueSQLTypes = new ArrayList<Integer>();
            final String query = this.parser.constructQuery( resultType, whereClause, orderBySegments, firstResult,
                maxResults, variables, values, valueSQLTypes, false );

            result = this.performQuery( new DoQuery<Iterable<EntityReference>>()
            {
                @Override
                public Iterable<EntityReference> doIt( Connection connection )
                    throws SQLException
                {
                    PreparedStatement ps = null;
                    ResultSet rs = null;
                    List<EntityReference> resultList = new ArrayList<EntityReference>( maxResults == null ? 100
                        : maxResults );
                    try
                    {
                        // TODO possibility to further optimize by setting fetch size (not too small not too little).
                        Integer rsType = parser.getResultSetType( firstResult, maxResults );
                        ps = createPS( connection, query, values, valueSQLTypes, rsType, ResultSet.CLOSE_CURSORS_AT_COMMIT );
                        rs = ps.executeQuery();
                        if( firstResult != null && !parser.isFirstResultSettingSupported()
                            && rsType != ResultSet.TYPE_FORWARD_ONLY )
                        {
                            rs.absolute( firstResult );
                        }
                        Integer i = 0;
                        while( rs.next() && (maxResults == null || i < maxResults) )
                        {
                            resultList.add( new EntityReference( rs.getString( 1 ) ) );
                            ++i;
                        }
                    }
                    finally
                    {
                        SQLUtil.closeQuietly( rs );
                        SQLUtil.closeQuietly( ps );
                    }

                    return resultList;
                }

            } );

        }
        else
        {
            result = new ArrayList<EntityReference>( 0 );
        }

        return result;
    }


    @Override
    public EntityReference findEntity( Class<?> resultType, @Optional Specification<Composite> whereClause, Map<String, Object> variables ) throws EntityFinderException
    {
        final List<Object> values = new ArrayList<Object>();
        final List<Integer> valueSQLTypes = new ArrayList<Integer>();
        final String query = this.parser.constructQuery( resultType, whereClause, null, null, null, variables, values,
            valueSQLTypes, false );

        return this.performQuery( new DoQuery<EntityReference>()
        {
            @Override
            public EntityReference doIt( Connection connection )
                throws SQLException
            {
                PreparedStatement ps = null;
                ResultSet rs = null;
                EntityReference result = null;
                try
                {
                    ps = createPS( connection, query, values, valueSQLTypes );
                    ps.setFetchSize( 1 );
                    ps.setMaxRows( 1 );
                    rs = ps.executeQuery();
                    if( rs.next() )
                    {
                        result = new EntityReference( rs.getString( 1 ) );
                    }
                }
                finally
                {
                    SQLUtil.closeQuietly( rs );
                    SQLUtil.closeQuietly( ps );
                }

                return result;
            }
        } );
    }

    private PreparedStatement createPS( Connection connection, String query, List<Object> values, List<Integer> valueSQLTypes )
        throws SQLException
    {
        return this.createPS( connection, query, values, valueSQLTypes, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CLOSE_CURSORS_AT_COMMIT );
    }

    private PreparedStatement createPS( Connection connection,  String query, List<Object> values, List<Integer> valueSQLTypes,
        Integer resultSetType, Integer resultSetHoldability )
        throws SQLException
    {
        PreparedStatement ps = connection
            .prepareStatement( query, resultSetType, ResultSet.CONCUR_READ_ONLY, resultSetHoldability );
        if( values.size() != valueSQLTypes.size() )
        {
            throw new InternalError( "There was either too little or too much sql types for values [values="
                + values.size() + ", types=" + valueSQLTypes.size() + "]." );
        }

        for( Integer x = 0; x < values.size(); ++x )
        {
            ps.setObject( x + 1, values.get( x ), valueSQLTypes.get( x ) );
        }

        return ps;
    }

    // Helper method to perform SQL queries and handle things if/when something happens

    private <ReturnType> ReturnType performQuery( DoQuery<ReturnType> doQuery )
        throws EntityFinderException
    {
        ReturnType result = null;
        Connection connection = null;
        try
        {
            connection = this._dataSource.getConnection();
            connection.setReadOnly( true );

            result = doQuery.doIt( connection );

        }
        catch( SQLException sqle )
        {
            throw new EntityFinderException( sqle );
        }
        finally
        {
            SQLUtil.closeQuietly( connection );
        }

        return result;
    }

}