sql query not working on CacheTypeMetadata

classic Classic list List threaded Threaded
3 messages Options
gopika nair gopika nair
Reply | Threaded
Open this post in threaded view
|

sql query not working on CacheTypeMetadata

This post has NOT been accepted by the mailing list yet.
Hi,

I have created a cache using Automatic Persistence, connecting to Mysql database.
1 million rows are populated on startup into that node. Node is in PARTITIONED mode

When I try retrieving data from that Cache using SQL queries, it always returns empty array.
I have indexed the cache using "CacheTypeMetadata".

Please could anyone point out what I have missed out, or done incorrectly. I have been following the tutorials, but I cannot figure out why my query is not working fine.

Thanks in advance!

1. Cache :
CacheConfiguration<Dataloadermd5Key, DataLoaderMd5> cfg =
                    CacheConfigMd5.cache("DataMd5Cache", new JDBCFactory<Dataloadermd5Key, DataLoaderMd5>());

2. DataLoaderMd5Key::
public class Dataloadermd5Key implements Serializable {
    /** */
    private static final long serialVersionUID = 0L;

    /** Value for idClient. */
    private int idClient;

    /** Value for clientPropId. */
    private String clientPropId;

...}

3. DataLoaderMd5  ::
public class DataLoaderMd5 implements Serializable {
    /** */
    private static final long serialVersionUID = 0L;

    /** Value for idClient. */
    private int idClient;

    /** Value for clientPropId. */
    private String clientPropId;

    /** Value for md5. */
    private String md5;
..}

4. CacheConfigMd5 ::

public static <K, V> CacheConfiguration<K, V> cache(String name, Factory<CacheStore<K, V>> storeFactory) {
        if (storeFactory == null)
             throw new IllegalArgumentException("Cache store factory cannot be null.");

        CacheConfiguration<K, V> ccfg = new CacheConfiguration<>(name);

        ccfg.setCacheStoreFactory(storeFactory);
        ccfg.setReadThrough(true);
        ccfg.setWriteThrough(true);

        // Configure cache types.
        Collection<CacheTypeMetadata> meta = new ArrayList<>();

        // DataLoaderMd5.
        CacheTypeMetadata type = new CacheTypeMetadata();

        meta.add(type);

        type.setDatabaseSchema("abc");
        type.setDatabaseTable("dfg");
        type.setKeyType(Dataloadermd5Key.class.getName());
        type.setValueType(DataLoaderMd5.class.getName());

        // Key fields for DataLoaderMd5.
        Collection<CacheTypeFieldMetadata> keys = new ArrayList<>();
        keys.add(new CacheTypeFieldMetadata("id_client", Types.INTEGER, "idclient", int.class));
        keys.add(new CacheTypeFieldMetadata("client_prop_id", Types.VARCHAR, "clientPropId", String.class));
        type.setKeyFields(keys);

        // Value fields for DataLoaderMd5.
        Collection<CacheTypeFieldMetadata> vals = new ArrayList<>();
        vals.add(new CacheTypeFieldMetadata("id_client", Types.INTEGER, "idclient", int.class));
        vals.add(new CacheTypeFieldMetadata("client_prop_id", Types.VARCHAR, "clientPropId", String.class));
        vals.add(new CacheTypeFieldMetadata("Md5", Types.VARCHAR, "md5", String.class));
        type.setValueFields(vals);

        // Query fields for DataLoaderMd5.
        Map<String, Class<?>> qryFlds = new LinkedHashMap<>();

        qryFlds.put("idclient", int.class);
        qryFlds.put("clientPropId", String.class);
        qryFlds.put("md5", String.class);

        type.setQueryFields(qryFlds);

        // Groups for DataLoaderMd5.
        Map<String, LinkedHashMap<String, IgniteBiTuple<Class<?>, Boolean>>>  grps = new LinkedHashMap<>();

        LinkedHashMap<String, IgniteBiTuple<Class<?>, Boolean>> grpItems = new LinkedHashMap<>();

        grpItems.put("idclient", new IgniteBiTuple<Class<?>, Boolean>(int.class, false));
        grpItems.put("clientPropId", new IgniteBiTuple<Class<?>, Boolean>(String.class, false));

        grps.put("PRIMARY", grpItems);

        type.setGroups(grps);

        ccfg.setTypeMetadata(meta);

        return ccfg;
    }


5. Query ::

         Ignite ignite = Ignition.start("examples/config/example-cache.xml");
                 IgniteCache<Dataloadermd5Key, DataLoaderMd5> cache = ignite.cache(CACHE_NAME);
                  Dataloadermd5Key key = new Dataloadermd5Key();
            key.setIdClient(98255);
            key.setClientPropId("1000008");
                       
                 SqlQuery<Dataloadermd5Key, DataLoaderMd5> qry =
                new SqlQuery<Dataloadermd5Key, DataLoaderMd5>(DataLoaderMd5.class, "idClient = ? and clientPropId = ?");
             


        //Excecute query
        System.out.println("sqlQuery Lookup result :: " +
                cache.query(qry).getAll());

        System.out.println("sqlQuery Lookup result :: " +
                cache.query(qry.setArgs(key.getIdClient(), key.getClientPropId())).getAll());


dsetrakyan dsetrakyan
Reply | Threaded
Open this post in threaded view
|

Re: sql query not working on CacheTypeMetadata

We are trying to reproduce it and will reply here with our findings.

In the mean time, can you please properly subscribe to the user list, so everyone in the community will get email notifications?

Thanks!
dsetrakyan dsetrakyan
Reply | Threaded
Open this post in threaded view
|

Re: sql query not working on CacheTypeMetadata

In reply to this post by gopika nair
Looks like the user has found a solution for this on stack overflow:
http://stackoverflow.com/questions/30970577/apache-ignite-query

The issue was with wrong Ignite version in the POM file.