I run into a "weird" use case in the context of some monitoring application: several computing centers deliver the same type of information into 1 central  center hosting Oracle 11g.

Every remote center can write into a specific DB schema, and the table structure of every schema is identical. Also, I have a schema available with SELECT privileges over all individual schemas. So I was setting up a small test in order to use JBoss 7 and hibernate for accessing those tables, and I started using the global READER schema to access one of the "remote-owned" schemas for automatic table/pojo generation via hibernate-tools. I prepared some DAOs and RESTful services using RESTEasy, allowing to gather information from this specific schema for which I prepared my data model. Then a basic problem came up : how can I switch from one schema to another ? Obviously I could, using Native SQL queries, add a parameter in every DAO method , fill this parameter directly from every RESTful URL, and create the ad hoc query for the given schema at the level of the DAO method itself. But I wanted to see if I could do something less intrusive. Browsing the web I found an interesting post on hibernate interceptors. Here some links.

On hibernate interceptors:

http://www.tutorialspoint.com/hibernate/hibernate_interceptors.htm

On passing context parameters between different layers : from web servlet to EJB3:

HOW TO PASS CONTEXT BETWEEN LAYERS WITH THREADLOCAL AND EJB 3.(1)

So I ended up with creating the interceptor that would allow me to "catch" the prepared statement:

{code}
public class SchemaChangeInterceptor extends EmptyInterceptor {
 
  /**
           * 
           */
          private static final long serialVersionUID = 2517085568705192529L;
 
 
  private static final String origschemaname = "RM";
 
          public final static Logger log = Logger.getLogger(SchemaChangeInterceptor.class.getName());
  /**
           * 
           */
          public SchemaChangeInterceptor() {
                    super();
          }
 
          protected SessionContext getContext() {
                    try {
            InitialContext ic = new InitialContext();
            SessionContext sctxLookup =
              (SessionContext) ic.lookup("java:comp/EJBContext");
            System.out.println("look up EJBContext by standard name: " + sctxLookup);
            return sctxLookup;
        } catch (NamingException ex) {
            throw new IllegalStateException(ex);
        }
          }
 
  /* (non-Javadoc)
           * @see org.hibernate.EmptyInterceptor#onPrepareStatement(java.lang.String)
           */
  @Override
          public String onPrepareStatement(String sql) {
  // TODO Auto-generated method stub
  log.log(Level.INFO, "Interceptor received prepared statement : "+sql);
  String centername = (String) WebRestContextHolder.get("CalibCenter");  // See later how this works !!
  log.info("Found calibcenter set to "+centername);
                    sql = sql.replace("_"+origschemaname+"_", "_"+centername+"_");
  log.log(Level.INFO,"Interceptor returning prepared statement : "+sql);
                    return super.onPrepareStatement(sql);
          }
{code}

Then I had somehow to transfer in a transparent way the parameter that a client would insert in the URL of a RESTful service, directly into my interceptor....For this I used

the second link above to create a context holder static class...

{code}
public class WebRestContextHolder {
 
          private static final ThreadLocal<Map<String, Object>> THREAD_WITH_CONTEXT = new ThreadLocal<Map<String, Object>>();
 
          private WebRestContextHolder() {
          }
 
          public static void put(String key, Object payload) {
 
    if (THREAD_WITH_CONTEXT.get() == null) {
      THREAD_WITH_CONTEXT.set(new HashMap<String, Object>());
    }
    THREAD_WITH_CONTEXT.get().put(key, payload);
          }
 
          public static Object get(String key) {
    return THREAD_WITH_CONTEXT.get().get(key);
          }
 
          public static void cleanupThread() {
    THREAD_WITH_CONTEXT.remove();
          }
}
{code}

the context holder is filled directly from the RESTful service. Here is the example of a method :

{code}
  @GET
  @Produces("application/xml")
  @Path("/mdtrt/{center}/run/{run}/get")
          public List<MdtRt> listMdtRtInRun(@PathParam("center") String center,@PathParam("run") BigDecimal run) {
 
  log.info("Calling listMdtRtInRun..."+run);
                    setCenter(center);
{code}

the setCenter method just does :

{code}
WebRestContextHolder.put("CalibCenter", centerName);
{code}