9 Replies Latest reply: Jan 14, 2013 10:53 AM by user738616 RSS

    How to load data in to cache from Database Table

    praveen435
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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