DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Getting Started With JPA/Hibernate
  • How to Delay Database Connection Acquisition in Hibernate (Spring Data)
  • How Spring and Hibernate Simplify Web and Database Management
  • Enhanced Query Caching Mechanism in Hibernate 6.3.0

Trending

  • Scalability 101: How to Build, Measure, and Improve It
  • Setting Up Data Pipelines With Snowflake Dynamic Tables
  • How to Convert Between PDF and TIFF in Java
  • Why I Started Using Dependency Injection in Python
  1. DZone
  2. Data Engineering
  3. Data
  4. Hibernate - Dynamic Table Routing

Hibernate - Dynamic Table Routing

I have been searching for a method to dynamically route objects to databases at runtime using Hibernate and recently I found a solution which fit the bill.

By 
alvin sd user avatar
alvin sd
·
Jun. 13, 08 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
59.6K Views

Join the DZone community and get the full member experience.

Join For Free

I have been searching for a method to dynamically route objects to databases at runtime using Hibernate and recently I found a solution which fit the bill. This post explores the problem and identifies one possible solution.

From http://www.alvinsingh.org/blog

The Problem

I have a web-application (using Spring+Hibernate for ORM) which makes use of two database schemas (in the same physical database). I specifiy a datasource accessible via JNDI (using tomcat achieved via context xml) so across my different deployment environments I can use the same war file. This is particularly important as I do not want to use different binaries for different environments. My ethos is to keep any dynamic information pertinent to the deployment environment strictly outside of the war file exactly for the above reason - same war file across deployments.

I specify one DS (datasource) since the spring hibernate session-factory expects a single DS. But this only gets my half way there - I have only specified one database connection string - what about the other database? You can have multiple session factories but then have to deal with cross session transactions.

We can solve this problem (not the main problem this post addresses) by using the ’schema’ and ‘catalog’ configuration items on our entities which are in the DB #2. Below is an example which I will use for reference involving 3 objects spanning 2 schemas. It involves a common practice where identity management information is kept in a separate database to the application(s) database. I have omitted most of the information from the objects for brevity.

@Table
@Entity
public class ApplicationUser {

private IdentityUser identityUser;

}
@Table (schema="secure", catalog="identitymanagement")
@Entity
public class IdentityUser {

@OneToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "IdentityUserIdentityRole",
       schema="security",
       catalog="identitymanagement",
       joinColumns = {@JoinColumn(name = "IdentityUserID")},
               inverseJoinColumns = {@JoinColumn(name = "IdentityRoleID")})
    private Set<IdentityRole> roles;

}
@Table (schema="secure", catalog="identitymanagement")
@Entity
public class IdentityRole {

}

ApplicationUser is in DB #1 and is configured via my JNDI datasouce. Its schema and catalog can be configured via default hibernate properties. As can be seen, I have hard-coded the schema and catalog values for the identity management objects IdentityUser and IdentityUserRole. I cannot put dynamic elements in these annotations and also note that using hbm.xml files does not get around this (while also keeping a single binary across deployment environments). This is the core of the problem.

What this means is that I can have multiple instances of DB #1 and only a single instance of DB #2 (which must be named secure.identitymanagement.*) in any single deployment environment. Not very tenable.

The Solution

The way hibernate builds its queries is that it prepends the schema and catalog names when referring to the tables - i.e. in my example we would end up with a queries to the effect of - “select * from secure.identitymanagement.IdentityUser”. What we need is a way of manipulating this query building.

Enter the hibernate interceptor.

public class HibernateInterceptor extends EmptyInterceptor {

@Override
    public String onPrepareStatement(String sql) {
          String prepedStatement = super.onPrepareStatement(sql);
          prepedStatement = prepedStatement.replaceAll("secure.identitymanagement", "my_dynamic_goodness");
          return prepedStatement;
    }

}

This interface is probably one of the most powerful hibernate features in the framework. It allows you to get at the core of Hibernate and manipulate properties at execution time - in this instance to do dynamic table routing. The ‘onPrepareStatement’ is obviously called when Hibernate is preparing the sql statements just before it sends it to the DB. What the code is doing (if it isn’t obvious) is a simple string replacement of an exact string. This exact string is what we hard-code (can be anything but should be an identifier which will not occur otherwise in sql statements) and is to be replaced by the dynamic catalog and schema name. Where the actual catalog and schema name values come from is up-to the implementer. In the case of a web-application, I code this in the context as a String property and look it up via JNDI similiar to the DS.

I have deliberately made the above code very simple to show the underlying concept. One could put some quite complicated routing logic in there to do other forms of dynamic routing (e.g. based on logged in customer, based on users region). There are other solutions to the dynamic routing problem which address slightly different use-cases - check Hibernate Shards and dynamic models.

I am not sure the interceptor was meant for what I am ‘hijacking’ it to do but the first line of the api does give some insight…

Allows user code to inspect and/or change property values.

 

Database connection Hibernate

Opinions expressed by DZone contributors are their own.

Related

  • Getting Started With JPA/Hibernate
  • How to Delay Database Connection Acquisition in Hibernate (Spring Data)
  • How Spring and Hibernate Simplify Web and Database Management
  • Enhanced Query Caching Mechanism in Hibernate 6.3.0

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!