Hibernate Books

All times are UTC - 5 hours [ DST ]

Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: Dynamic/Runtime Schema Change
PostPosted: Fri Dec 07, 2007 11:16 am 

Joined: Tue Apr 24, 2007 12:53 pm
Posts: 28
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:

<?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" >
            <key-property name="id" column="ID" type="java.lang.String" >
            <generator class="assigned" />
        <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" />

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.


 Post subject: Re: Dynamic/Runtime Schema Change
PostPosted: Fri May 18, 2012 7:24 am 

Joined: Wed Apr 18, 2012 4:37 am
Posts: 7
Would have been good, to have the link to the original thread.

I also ran into the same problem and am looking for a solution.
Can I somehow change the schema of a session during runtime?
I also don't fancy writing 200+ SessionFactories for the same reason. Has anyone got an idear?

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.