Question on DB design approaches in Java

I have data in a DB that have hierarchical relationship (could be represented as a tree).

Right now, whenever a request comes in the server, a recursive query is done in the DB to create an in-memory list (which is a branch of the tree, from leaf to root that actually exists) and do processing using that list.

This is not very efficient, but at least it works for now, and has the advantage that if the DB is changed, the changes gets immediately noticed.

Anyway, I want to improve it and looking for a pattern to:

1)Not to have to access the DB all the time

2)If a change is done in the DB, it gets immediatelly reflected

3)It can be used in all places of the server.

Is there a standard design pattern commonly used for cases like this? Or ju开发者_如何学编程st have a data structure maintained by a backend thread and on any update in the DB, reload everything in the data structure?

Thank you

Instead of a thread you could use a caching solution. The result of the query would be cached and returned on every request, if any of the tables in the query are modified you invalidate the cache, wait until the next request to retrieve the new values and load them on the cache. This solution meets requisites 1 and 2. What do you mean by "It can be used in all places of the server"?

If you are using Hibernate, this can be easily done with the second level cache, the caveat is that you must guarantee that only your application changes the database (no external processes).

If you are not using Hibernate, you can roll your own solution with Ehcache. You would need to change the method that returns the structure to check the cache before querying the DB. Methods that make changes to the tables involved, would have to modified to invalidate the cache.

Ehcache also has integration with Spring, but I've never tried it.

You don't give much details which ORM tool you use, but in Hibernate, this is doable by declaring a list of children. Below is taken from the production code:

<hibernate-mapping>
   <class name="YourTreeClass" table="tree_table" dynamic-insert="true" dynamic-update="true">
   ...other properties...

      <list name="children" cascade="save-update" inverse="false" >
         <cache usage="nonstrict-read-write"/>
         <key column="parent_id"/>
         <one-to-many class="YourTreeClass" />
      </list>
    </class>
</hibernate-mapping>

No need for hacky background threads - on node update, Hibernate takes care and invalidates relevant cached entries.