You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user-java@ibatis.apache.org by gr...@freenet.de on 2009/05/04 16:03:38 UTC

Lazy loading when having dynamic table names

Hi!

In our DB schema (that cannot be changed) we got dynamic table names, depending
on the current company, i.e. there are tables ORDER_100, ORDER_200,... with
ORDER_100 being company 100's ORDER table, etc. 

Now everything is fine, for a single select, as I can pass the current company
as a parameter and configure query like this:

<select id="findOrders" parameterClass="org.test.OrderSearchCriteria"
resultMap="OrderDataResult">
 ORDER_$company$ where ORNO = $id$
</select>

But what if i want to get ORDER table's related CUSTOMER using lazy loading?

Then I will need something like:

<select id="getCustomerById" resultMap="CustomerDataResult">
  	 CUSTOMER_$company$ where CUNO =#id#
</select>

with the resultMap of orders looking like this:

<resultMap id="OrderDataResult"  class="OrderData">
    <result property="orderNumber" column="ORNO" />
    <result property="customer" column="CUNO" select="getCustomerById" />
</resultMap>

And I can see no possibility to pass the 'company' parameter to getCustomerById
select...
I think of generating sqlmap configuration dynamically, i.e. there would be a
get getCustomerById_100, getCustomerById_200, etc. then. But that's poor and
ugly...

What would be useful is some global variable that could be set on the current
session, or something, and one could use within any statement then.

Do you have a solution to this problem?
Any comments are greatly appreciated!





Gratis: Jeden Monat 3 SMS versenden-
Mit freenetMail - Ihrer kostenlose E-Mail-Adresse
http://email.freenet.de/dienste/emailoffice/produktuebersicht/basic/sms/index.html?pid=6830

RE: Lazy loading when having dynamic table names

Posted by meindert <me...@eduflex.com>.

I don't have the answer, because I haven't had the issue,
but would it be possible to use token replacement like clinton described
last week?
I have no idea of you can replace the tokens runtime for the session
though...

<select id="findOrders" parameterClass="org.test.OrderSearchCriteria"
resultMap="OrderDataResult">
 ORDER_${company} where ORNO = $id$
</select>


-----Original Message-----
From: gratler69@freenet.de [mailto:gratler69@freenet.de] 
Sent: 04 May 2009 04:04 PM
To: user-java@ibatis.apache.org
Subject: Lazy loading when having dynamic table names

Hi!

In our DB schema (that cannot be changed) we got dynamic table names,
depending
on the current company, i.e. there are tables ORDER_100, ORDER_200,... with
ORDER_100 being company 100's ORDER table, etc. 

Now everything is fine, for a single select, as I can pass the current
company
as a parameter and configure query like this:

<select id="findOrders" parameterClass="org.test.OrderSearchCriteria"
resultMap="OrderDataResult">
 ORDER_$company$ where ORNO = $id$
</select>

But what if i want to get ORDER table's related CUSTOMER using lazy loading?

Then I will need something like:

<select id="getCustomerById" resultMap="CustomerDataResult">
  	 CUSTOMER_$company$ where CUNO =#id#
</select>

with the resultMap of orders looking like this:

<resultMap id="OrderDataResult"  class="OrderData">
    <result property="orderNumber" column="ORNO" />
    <result property="customer" column="CUNO" select="getCustomerById" />
</resultMap>

And I can see no possibility to pass the 'company' parameter to
getCustomerById
select...
I think of generating sqlmap configuration dynamically, i.e. there would be
a
get getCustomerById_100, getCustomerById_200, etc. then. But that's poor and
ugly...

What would be useful is some global variable that could be set on the
current
session, or something, and one could use within any statement then.

Do you have a solution to this problem?
Any comments are greatly appreciated!





Gratis: Jeden Monat 3 SMS versenden-
Mit freenetMail - Ihrer kostenlose E-Mail-Adresse
http://email.freenet.de/dienste/emailoffice/produktuebersicht/basic/sms/inde
x.html?pid=6830


No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.287 / Virus Database: 270.12.16/2094 - Release Date: 05/03/09
16:51:00

Re: Lazy loading when having dynamic table names

Posted by Erwan QUEFFELEC <er...@gmail.com>.

Neither would I throw stones at anyone, but remember that dynamic table
identifiers can be an issue on some RDMS  (for instance; Oracle table names
cannot exceed 30 characters).

This problem can easily be avoided with validation code, but in your case, I
would strongly advocate using the DBMS schema functionality as well.

On Mon, May 4, 2009 at 18:55, <Ch...@sybase.com> wrote:

>
> Not that I am throwing stones, but you really should raise an issue about
> this schema design. I perviously worked in a company that took this approach
> in designing their data model. As you have already seen, this is not an easy
> data model to work with for obvious reasons, but the greatest problem with
> this design is the fact that you are intermingling different companies data.
> How do you think General Motors would feel knowing that their data resided
> in tables right next to Ford's. I'm certain Ford would not like this as
> well. You should leverage the database's schema functionality to isolate the
> data properly.
>

Re: Lazy loading when having dynamic table names

Posted by Ch...@sybase.com.

Not that I am throwing stones, but you really should raise an issue about
this schema design. I perviously worked in a company that took this
approach in designing their data model. As you have already seen, this is
not an easy data model to work with for obvious reasons, but the greatest
problem with this design is the fact that you are intermingling different
companies data. How do you think General Motors would feel knowing that
their data resided in tables right next to Ford's. I'm certain Ford would
not like this as well. You should leverage the database's schema
functionality to isolate the data properly.

gratler69@freenet.de
05/04/2009 07:03 AM
Please respond to
user-java@ibatis.apache.org

To
user-java@ibatis.apache.org
cc

Subject
Lazy loading when having dynamic table names

Hi!

In our DB schema (that cannot be changed) we got dynamic table names,
depending
on the current company, i.e. there are tables ORDER_100, ORDER_200,...
with
ORDER_100 being company 100's ORDER table, etc.

Now everything is fine, for a single select, as I can pass the current
company
as a parameter and configure query like this:

<select id="findOrders" parameterClass="org.test.OrderSearchCriteria"
resultMap="OrderDataResult">
ORDER_$company$ where ORNO = $id$
</select>

But what if i want to get ORDER table's related CUSTOMER using lazy
loading?

Then I will need something like:

<select id="getCustomerById" resultMap="CustomerDataResult">
CUSTOMER_$company$ where CUNO =#id#
</select>

with the resultMap of orders looking like this:

And I can see no possibility to pass the 'company' parameter to
getCustomerById
select...
I think of generating sqlmap configuration dynamically, i.e. there would
be a
get getCustomerById_100, getCustomerById_200, etc. then. But that's poor
and
ugly...

What would be useful is some global variable that could be set on the
current
session, or something, and one could use within any statement then.

Do you have a solution to this problem?
Any comments are greatly appreciated!

Gratis: Jeden Monat 3 SMS versenden-
Mit freenetMail - Ihrer kostenlose E-Mail-Adresse
http://email.freenet.de/dienste/emailoffice/produktuebersicht/basic/sms/index.html?pid=6830