Re:Re: Join with subquery in 1.9 or 2.0

[贺波]

Hi,
    Your demo is different from my.Mine is an example of recursive,using "with 
as"function.My test demo is in the attachment.
package userlist;

import java.util.List;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.CacheAtomicityMode;
import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.cache.query.annotations.QuerySqlField;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.configuration.IgniteConfiguration;
import org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi;
import org.apache.ignite.spi.discovery.tcp.ipfinder.TcpDiscoveryIpFinder;
import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder;
import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest;

/**
 *
 */
public class JoinTest extends GridCommonAbstractTest {
    /** */
    private static final TcpDiscoveryIpFinder IP_FINDER = new 
TcpDiscoveryVmIpFinder(true);

    /** */
    private static final String DEFAULT_CACHE_NAME = "MYCACHE";

    /** */
    private static final int NODES_COUNT = 2;

    /** */
    private static final int CUSTOMER_COUNT = 23;

    /** */
    private static final int DETAILS_PER_CUSTOMER = 37;

    /** */
    private static String SQL = "with RECURSIVE children(typeId, pTypeId) AS ( 
"+
                                " SELECT typeId, pTypeId FROM ProcessDefTypePo 
WHERE pTypeId = ? "+
                                " UNION ALL "+
                                " SELECT ProcessDefTypePo.typeId, 
ProcessDefTypePo.pTypeId "+
                                " FROM children INNER JOIN ProcessDefTypePo ON 
children.typeId = ProcessDefTypePo.pTypeId "+
                                ") "+
        "select t1.typeId,t1.pTypeId,t1.typeName,t1.description, t2.typeName as 
pTypeName from ProcessDefTypePo t1 left join ProcessDefTypePo t2 on 
t1.pTypeId=t2.typeId where t1.typeId not in ( select typeId from children )";
    
    /** {@inheritDoc} */
    @Override protected IgniteConfiguration getConfiguration(String gridName) 
throws Exception {
        IgniteConfiguration cfg = super.getConfiguration(gridName);

        cfg.setPeerClassLoadingEnabled(false);

        TcpDiscoverySpi disco = new TcpDiscoverySpi();

        disco.setIpFinder(IP_FINDER);

        cfg.setDiscoverySpi(disco);

        return cfg;
    }

    /** {@inheritDoc} */
    @Override protected void beforeTestsStarted() throws Exception {
        startGridsMultiThreaded(NODES_COUNT, true);
    }

    /** {@inheritDoc} */
    @Override protected void afterTestsStopped() throws Exception {
        stopAllGrids();
    }

    /**
     * @param name Cache name.
     * @param idxTypes Indexed types.
     * @return Cache configuration.
     */
    private <K, V> CacheConfiguration<K, V> cacheConfig(String name, 
Class<?>... idxTypes) {
        return new CacheConfiguration<K, V>(DEFAULT_CACHE_NAME)
            .setName(name)
            .setCacheMode(CacheMode.PARTITIONED)
            .setAtomicityMode(CacheAtomicityMode.ATOMIC)
            .setBackups(1)
            .setIndexedTypes(idxTypes);
    }

    /** */
    public void testJoinQuery() throws Exception {
        CacheConfiguration<String, ProcessDefTypePo> ccfg1 = 
cacheConfig("processDefTypeCache", String.class, ProcessDefTypePo.class);

        final IgniteCache<String, ProcessDefTypePo> c1 = 
ignite(0).getOrCreateCache(ccfg1);

        try {
            populateDataIntoCaches(c1);

            final SqlFieldsQuery qry = new SqlFieldsQuery(SQL);

            qry.setDistributedJoins(true);

            assertEquals(CUSTOMER_COUNT,c1.query(qry).getAll().size());

            //
            Ignite client = startGrid("client", 
getConfiguration("client").setClientMode(true));
            IgniteCache<Object, Object> c = client.cache("processDefTypeCache");

            assertEquals(CUSTOMER_COUNT,c.query(qry).getAll().size());

        }
        finally {
            c1.destroy();
        }
    }

    /**
     * @param c1 Cache1.
     */
    private void populateDataIntoCaches(IgniteCache<String, ProcessDefTypePo> 
c1) {
        
        ProcessDefTypePo processDefTypePo=new ProcessDefTypePo();
        processDefTypePo.setTypeId("0");
        processDefTypePo.setPTypeId("-1");
        processDefTypePo.setTypeName(0);
        c1.put("0", processDefTypePo);
        
        for (int j = 1; j < 10; j++) {
            ProcessDefTypePo dtls = new ProcessDefTypePo();
            dtls.setTypeId(j);
            dtls.setPTypeId(j-1);
            dtls.setTypeName("" + j);

            c1.put(j, dtls);

        }
        
        
    }

    /**
     *
     */
    private static class ProcessDefTypePo {
        /** */
        @QuerySqlField(index = true)
        private String typeId;

        /** */
        @QuerySqlField(index = true)
        private String pTypeId;

                                @QuerySqlField
                                private String typeName;
        
                                public void setTypeId(String typeId) {
                                        this.typeId = typeId;
                                }
                                public String getTypeId(){
                                        return typeId;
                                }
                                public void setPTypeId(String pTypeId) {
                                        this.pTypeId = pTypeId;
                                }
                                public String getPTypeId(){
                                        return pTypeId;
                                }
                                public void setTypeName(String typeName) {
                                        this.typeName = typeName;
                                }
                                public String getTypeName(){
                                        return typeName;
                                }
    }    
}