Hi,

I have 2 tables A and B. There are triggers on tables A and B, which populate Table C.

My application searches entities in Table C. Therefore, I need to implement hibernate search against the data in Table C. Since the entities mapping to Table C are not persisted using hibernate, can I still take advantage of automatic indexing? I created a dummy Table C entity that was not yet persisted in the DB and tried indexing it when i received this exception - "org.hibernate.TransientObjectException: The instance was not associated with this session".

Table C has 15 million records and a couple of CLOB columns. Hibernate search seems to be a perfect fit for this, but if i am unable to index the entities in table C somehow, I am stuck :((. Has anyone experienced this situation or has any suggestions?

I know I can index data periodically, but this is a real-time application, so auto-indexing is really the preferred approach.

Thanks.

Hi,
this should be possible.
Basically you have to create a listener "after persist", "after update", "before delete"
for the entities A and B and use them like a "hibernate trigger".
If you use JPA (entitymanager) you define one annotating a method on the entity
itself, when using plain hibernate you implement a listener like
http://fisheye.jboss.org/browse/Hibernate/search/trunk/src/java/org/hibernate/search/event/FullTextIndexEventListener.java?r=11631

To avoid to exception you're getting, you'll have to load the C entity from database;
You'll need to be able to find out which primary key you're going to need to load C from the A or B entity. After you've loaded C use index(object) to update it's state.
For deletions, you'll need to use purge() before you delete it.

Please keep us updated about how this works out, I'm unsure about the loading of an entity after the commit phase.

Hi,
Thanks for the response. I think I can load C using the primary key. I may have to do this when I commit to A as well as to B. C is the union of A & B, and between the inserts to A & B, there is no order of precedence. So I can load from C and index it every time I save entity A, and also when I save entity B. I still have to try if this works though !

For purging, we have table C partitioned by date, and have a daily job that drops partitions older than 30 days. So I guess in this case, I have no choice, but to have a daily job the deletes the indexes of the dropped data? Or does session.purgeAll(C.class) take care of this automatically by removing only obsolete data?

no, purgeAll removes all instances from the index only, it doesn't look into the db.
You'll have to purge() one-by-one loading the entities; you don't need to load the the actual entities as long as you know the type and primary key.