This discussion is archived
9 Replies Latest reply: Jan 14, 2013 8:53 AM by user738616 RSS

How to load data in to cache from Database Table

praveen435 Newbie
Currently Being Moderated
HI,
I had a requirement on coherence.How to load the cache from the database table.My database contains almost 16000 records and 12 coloumns.Is it require to have a primary key for the table to be loaded in to the cache.

Regards,
Praveen
  • 1. Re: How to load data in to cache from Database Table
    user738616 Pro
    Currently Being Moderated
    Hi,

    There are quite a few ways in which you may do the bulk loading within Coherence:

    Option1 (not recommended): Get all the rows from DB, convert into javaObject and put it into the cache
    Option2 (for small set of data): Get all the cache keys from DB and use EP as invokeAll(keys, PreloadRequest.INSTANCE);
    Option3 (for large set of data): Get all the cache keys from DB, create buckets for keys based on key ownership and then pass this bucket to each of the key owners for loading it.

    HTH

    Cheers,
    _NJ                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  • 2. Re: How to load data in to cache from Database Table
    praveen435 Newbie
    Currently Being Moderated
    Hi NJ,
    Thanks for the reply.But how to go with option2 & option3.can you please post somelinks on the same. IS it mandatory to use cachestore approach while getting data from the database and putting it in to the coherence.

    Regards,
    Praveen
  • 3. Re: How to load data in to cache from Database Table
    user738616 Pro
    Currently Being Moderated
    Praveen,

    The option1 does not need you to implement cacheStore approach while option 2 & 3 do. You may go thru this link: http://docs.oracle.com/cd/E18686_01/coh.37/e18677/api_preloadcache.htm

    HTH

    Cheers,
    NJ
  • 4. Re: How to load data in to cache from Database Table
    praveen435 Newbie
    Currently Being Moderated
    HI NJ,
    Thanks for the reply.So do i need to use spring in option 2 & option 3 for loading data in to the cache from the database dynamically.

    Regards,
    Praveen
  • 5. Re: How to load data in to cache from Database Table
    user738616 Pro
    Currently Being Moderated
    Hi,

    You don't have to use but you may use it. Let me explain how option 2 will work:

    - Execute a query against the DB "select key from xxx";
    - Put the above results in a java Set "keys"
    - Execute myCache.invokeAll(keys, PreloadRequest.INSTANCE);

    On executing step 3, coherence will check if the key exists in the Cluster and if not available then it will invoke the load/loadAll method of the CacheStore interface. You need to implement RW backing map for your cache scheme.

    HTH

    Cheers,
    NJ
  • 6. Re: How to load data in to cache from Database Table
    praveen435 Newbie
    Currently Being Moderated
    HI NJ,
    Thanks for the reply.I used option1 to get the data from the DB and put it in the cache.
    Now all my data is available in the cache.I am querying the cache as below by using the filter.
    Object s[]={"Loc2","ab","bc",null,null,null};
    String s2="getLocName = ?1 and getQ1 = ?2 and getQ2 = ?3 and getQ3 = ?4 and getQ4 = ?5 and getQ5 = ?6";
              
    Filter f3=QueryHelper.createFilter(s2,s);
    Set setKeys = cache.keySet(f3);
    Iterator itr = setKeys.iterator();
              while(itr.hasNext())
         {
         
         Location l1=(Location)cache.get(itr.next());
         
         System.out.print("SEQ_NO:"+l1.getSeqNo());
         System.out.print("DVM_NAME:"+l1.getDvmName());
         System.out.print(" Q1:"+l1.getQ1());
         System.out.print(" Q2:"+l1.getQ2());
         System.out.print(" Q3:"+l1.getQ3());
         System.out.print(" Q4:"+l1.getQ4());
         System.out.print(" Q5:"+l1.getQ5());
         System.out.print(" COLUMN1:"+l1.getColumn1());
         System.out.print(" COLUMN2:"+l1.getColumn2());
         System.out.print(" COLUMN3:"+l1.getColumn3());
         System.out.print(" COLUMN4:"+l1.getColumn4());
         System.out.print(" COLUMN5:"+l1.getColumn5());
         }

    But here i am getting two or more rows from the DB which is matching with the filter. Here my requirement is to select the best match(Like DVM in SOA).So how can i perform it on cache.So how can i use "order by" in coherence.

    Regards,
    Praveen
  • 7. Re: How to load data in to cache from Database Table
    user738616 Pro
    Currently Being Moderated
    Hi Praveen,

    For "orderBy", you may want to use the Comparator class while fetching the records from the cache:

    cache.entrySet(Filter filter,Comparator comparator)

    HTH

    Cheers,
    NJ
  • 8. Re: How to load data in to cache from Database Table
    praveen435 Newbie
    Currently Being Moderated
    HI NJ,
    Thanks for the reply.Here we want the "orderby" by Q5,Q4,Q3,Q2,Q1(But not in ascending order or descending order). Here Q5,Q4,Q3,Q2,Q1 are columns from DB.
    I saw some examples on using comparator.They all are arranging the list with ascending or descending order based on some filed(like age element).
    So,here in my case how to proceed with the comparator?

    Regards,
    Praveen
  • 9. Re: How to load data in to cache from Database Table
    user738616 Pro
    Currently Being Moderated
    Hi Praveen,

    The data from these DB columns will be populated into JavaBeans and your comparator needs to have the logic to sort (assending/descending) these Beans based on these attributes.

    HTH

    Cheers,
    _NJ                                                                                                                                                                                                                                                                                                                                                                                                                                               

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points