2

I am looking for best solution for caching large amount of simple transactional pojo structure in memory. Transactions happen at oracle database on 3-4 tables by external application. Another application is kind of Business Intelligence type, which based on transactions in database evaluates updated pojos(mapped to table) and applies various business rules.

Hibernate solution relies on transactions on same server; where as in our case transactions happen some where else, and not sure cached objects can be queried.

Question:

  1. Is there oracle jdbc API that would trigger update event on java side?
  2. Which Caching solution would support #1,
  3. Is cached objects can be queried?
gpa
  • 2,411
  • 6
  • 38
  • 68

1 Answers1

8

Oracle databases support Java triggers, so in theory you could implement something like this yourself, see this guide. In theory, your Java trigger could invoke the client library of whichever distributed caching solution you are using, to update or evict stale entries.

Oracle also have a caching solution of their own, known as Coherence. It might have integration like this built in, or at least it might be worth checking it out. Search for "java distributed cache" for some alternatives.

As far as I know Hibernate does not support queries on objects stored in its cache.

However if you cache an entire collection of objects separately, then there are some libraries which will allow you to perform SQL-like queries on those collections:

  • LambdaJ - supports advanced queries, not as fast
  • CQEngine - supports typical queries, extremely fast

BTW I am the author of CQEngine. I like both of those libraries. But please excuse my slight bias for my own one :)

npgall
  • 2,979
  • 1
  • 24
  • 24
  • how do you create index on nullable column in CQEngine? – gpa Nov 16 '12 at 06:44
  • Use SimpleNullableAttribute, or MultiValueNullableAttribute instead of the normal ones. You can find JavaDocs [here](http://cqengine.googlecode.com/svn/cqengine/javadoc/apidocs/com/googlecode/cqengine/attribute/package-summary.html). – npgall Nov 16 '12 at 16:16
  • query works amazing fast... i's loaded 350K objects, and executed some simple queries using IN, CONTAINS clauses... it was less than 10-18 msecs!!! .... sorry to continue discussion on CQEngine, one final question how do keep cache in sync with DB? if i am using change notification from oracle database? do i have to manually detect and update records in cache? – gpa Nov 16 '12 at 17:18
  • As you know, if you add/remove objects from CQEngine's IndexedCollection, it will update its indexes automatically. But CQEngine doesn't know anything about caching. Most distributed caches however, allow you to register listeners, to be notified when objects are added to/removed from the cache. A solution therefore, is to simply store your objects in your cache as normal (and your trigger keeps them in sync with the DB), maintain a separate instance of IndexedCollection outside of the cache on each server, and program your listeners to add/remove from the IndexedCollection as appropriate. – npgall Nov 16 '12 at 22:06
  • Thank you for valuable input. I will give it shot with Oracle Change Notification, quartz and CQEngine. – gpa Nov 17 '12 at 07:01
  • oops! i am running into situation...(rather java); where my query is in String vs CQEngine Query is at compile time :(. Meaning i am expecting my query conditions are stored in list of strings. e.g. String query = "contains(MyPojo.NAME,\"Sam\")"; Does CQEngine support string based queries? – gpa Nov 18 '12 at 07:06
  • CQEngine supports building queries dynamically, but not from strings. String queries would reduce performance, and would require casts everywhere due to lack of type information. Instead you can construct a (type-safe) query dynamically at runtime, using nested And, Or, Not, Contains, LessThan objects (etc.), which you can find [here](http://cqengine.googlecode.com/svn/cqengine/javadoc/apidocs/com/googlecode/cqengine/query/simple/package-summary.html) and [here](http://cqengine.googlecode.com/svn/cqengine/javadoc/apidocs/com/googlecode/cqengine/query/logical/package-summary.html). – npgall Nov 18 '12 at 13:22
  • I can not have typed queries by requirements. I still like CQEngine's processing queries amazingly fast. I am looking into ASM framework to convert string based queries to typed CQEngine queries. Lets see how that goes. – gpa Nov 19 '12 at 21:20
  • If you really need string based queries, also take a look at [JoSQL](http://josql.sourceforge.net) – npgall Nov 20 '12 at 01:37
  • Yeah that looks good query interface, but performance is no where close to CQEngine. My minimum cache size is 350K records, with upper limit of 500K. – gpa Nov 20 '12 at 16:43
  • Yes afaik, CQEngine is the only one that does low latency – npgall Nov 20 '12 at 21:09
  • I have successfully implemented using CQEngine w/ dynamic SQL. It seems to be working as expected. One issue i see down the road about scale-out, in distributed environment...but for now it works like charm! thanks – gpa Nov 21 '12 at 19:49
  • Nice! Yes keeping cached data in sync can be challenging, but that's a different SO question! Congrats! (maybe you could open source your SQL->CQEngine query mapper, hint hint ;) – npgall Nov 21 '12 at 22:49
  • There is nothing fancy, though i am dynamically generating java classes on string conditions, which has common interface to return object Query.I am running into specific query condition where i would like to get records where attribute value is null. I am using SimpleNullableAttribute, and method equals(Pojo.START_DATE,null)? It throws null pointer. – gpa Nov 21 '12 at 22:57
  • The easiest way is to define a Boolean attribute START_DATE_IS_NULL. There are other ways to do it too, but they typically lead to "not" queries, which basically means "give me everything that's not in the index", which isn't fast. If you add a HashIndex on START_DATE_IS_NULL it should be fast. – npgall Nov 21 '12 at 23:09
  • That worked, will have to add additional attribute XXX_IS_NULL with hash index for all null-able field of pojo :( ... btw, pojo has @50+ fields... it is flattened object. – gpa Nov 22 '12 at 06:53
  • Oh... btw... oracle change notification sucks! i have another post [<<>>](http://stackoverflow.com/questions/13478273/oracle-change-notification-issue), i am relying on AQ (Advanced Queue jms architecture) for refreshing cache – gpa Nov 22 '12 at 06:56
  • I'm adding a new type of query to CQEngine - Has - like "has(Pojo.START_DATE)" (equivalent to SQL 'IS NOT NULL'). The opposite would be "not(has(Pojo.START_DATE))" (equivalent to SQL 'IS NULL'). You could then add a StandingQueryIndex on it, and wherever in other queries it sees this query fragment, it would use the index. I'll probably include in a release at the weekend. It would avoid the need to define XXX_IS_NULL attributes (although performance would be the same). – npgall Nov 22 '12 at 10:36
  • that would be great, i will look forward new version. Thanks! – gpa Nov 22 '12 at 19:28
  • how do i create query like START_DATE < PLAN_DATE ? – gpa Nov 26 '12 at 22:22
  • Implement that as a function in an attribute: SimpleAttribute() { public Boolean getValue(Pojo pojo) { return pojo.getStartDate() < pojo.getPlanDate(); } } – npgall Nov 27 '12 at 00:24
  • @npgall: Sorry to add to the long trail of comments but since you the author of CQEngine :) I thought you could contribute to this question I'm having: http://stackoverflow.com/questions/23119394. Thanks a lot! – JohnDoDo Apr 16 '14 at 20:39