I posted this in the Hibernate Users forum, but I thought I'd post it here as well as I figured that those who also read this forum might have a better understanding of my situation and can provide assistance.
My problem isn't partitioned data across databases, as it is across a single database's schemas....
The real core problem I'm running into is the SQL generation.
Lets start by providing a better example than my previous one:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="my.companys.package">
<class name="Member" table="MEMBER" lazy="false" >
<id>
<key-property name="id" column="ID" type="java.lang.String" >
<generator class="assigned" />
</id>
<property name="customerId" column="CUSTOMER_ID" type="java.lang.Integer" />
<property name="firstName" column="FIRST_NAME" type="java.lang.String" not-null="true" />
<property name="lastName" column="LAST_NAME" type="java.lang.String" not-null="true" />
<property name="dateOfBirth" column="BIRTH_DATE" type="java.util.Date" not-null="false" />
<property name="addressLine1" column="ADDR_LINE_1" type="java.lang.String" not-null="true" />
<property name="addressLine2" column="ADDR_LINE_2" type="java.lang.String" not-null="false" />
<property name="city" column="CITY" type="java.lang.String" not-null="true" />
<property name="state" column="ST" type="java.lang.String" not-null="true" />
<property name="zip" column="ZIP_CDE" type="java.lang.String" not-null="true" />
</class>
</hibernate-mapping>
Our Member data is partitioned across multiple schemas. The schema in which a member resides is determined by using the Customer ID on the Member instance. Customer A's data cannot co-exist in the same table as Customer B's data.
When
SessionFactory instances are created, you define a default schema for all of the
Sessions that will be created. You don't necessarily have to define the default schema for the
SessionFactory, however if you don't, it will use whatever the default schema is for the connection.
When you go to use the
Session to create a
Criteria, or run a HQL query, at some point, it converts all of this into a native SQL string to be executed. If a default schema is defined, this information is used to qualify the table names for the mapped entities when generating the SQL string.
What I would like to do is provide a means to plug-in a class that knows how to differentiate which schema to use based on the instance of the mapped entity to be found. In essence: change the schema that will be used when the SQL generation is done, for each Entity in the query. Meaning: if it's a multi-tiered mapped structure, each mapped entity is used to resolve the schema for that entities SQL string.
This functionality would need to work for all of the operations supported for an Entity.
Obviously I would define the class that would make the determination. But from what I can see in the API, theres no real way to change the schema value once/if its defined.
I could use multiple connections/
SessionFactory instances, but this would require that I create 100+ connection pools. Not something I want to do. (And we use JNDI configured connection pools so c3p0 is out...)
Does anyone know if this is possible? Does it seem like its something that would be useful?
I tried to find out where exactly in the framework the SQL is generated, so i could find out if it'd be possible to wrap an interceptor around that part and update that value. Unfortunately I haven't been able to dig that deep into the framework (as it is way, way, way down there, necessarily so). As such I'm still at a loss for what to do.
Any help would really be great. Thanks in advance.
-B
Login
FAQ
Search
