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 Gianluca Gaiotto <gi...@gmail.com> on 2006/03/11 08:56:58 UTC
declarative - db schema
Ciao,
can I specify the database schema in sqlMap, or elsewhere, but in a
declarative way (without hard-coding it)?
The problem is that we have several environments stages (dev,
integration, test, production) , and therefore I have to configure the
right schema for each environment... And I don't want to add the schema
name prefix in front of each table name, but configuring a default db
schema somewhere in an iBatis config file (and to have several config
files, one for each environments).
Thanks
-Gianluca
Re: declarative - db schema
Posted by Gianluca Gaiotto <gi...@gmail.com>.
Yes, the point is that - from a design point of view - it is not so
'sound' to put the schema as a property into the beans (parameter maps
passed to the SqlMap statements)...
Thank you all for your answers.
-Gianluca
Re: declarative - db schema
Posted by John Hurst <jo...@gmail.com>.
I've seen this discussed a couple of times here.
We have a scenario, or usage pattern, with Oracle that would benefit from
iBATIS somehow being able to specify a schema name as a parameter/property.
If we have an application XXX, we commonly use several schemas/users:
XXXOWNER creates and owns the objects
XXXUSER is used by application servers to query and update objects
XXXREADER is used for read-only access, for example by users running
reports
The idea is that only XXXOWNER has DDL privileges; so XXXUSER capabilities
are limited. Perhaps this is not terribly useful as a security feature
per-se.
But, we do find it kind of nice for separation of duties -- the DBA team are
responsible for all DDL in production, and they have the XXXOWNER
schema/password. The development/support team deploy applications on to
application servers, including configuration of DataSources, and they have
the XXXUSER schema/password.
We use a bit of Hibernate and a bit of iBATIS. Hibernate does allow you to
configure the schema as a property. iBATIS doesn't. Our current solution
with iBATIS is to define PUBLIC SYNONYMs for XXXOWNER objects, so they can
be accessed from other schemas without qualification. I would prefer to
qualify them as we do with Hibernate.
But, I'm not sure how this would work with iBATIS. I certainly don't want to
change or break the way parameters are currently provided to statements --
and I don't want to put the schema as a property into my beans!
Food for thought I guess ...
Regards
John Hurst
Wellington, New Zealand
On 3/12/06, Larry Meadors <lm...@apache.org> wrote:
>
> No, there really isn't anything like this in iBATIS, because we try
> (as much as possible) to not mess with the SQL that you write.
>
> What database are you using?
>
> Many allow you to specify a default schema on the connection string,
> or connect as the schema owner.
>
> Larry
>
>
> On 3/11/06, Gianluca Gaiotto <gi...@gmail.com> wrote:
> > Ciao,
> > can I specify the database schema in sqlMap, or elsewhere, but in a
> > declarative way (without hard-coding it)?
> > The problem is that we have several environments stages (dev,
> > integration, test, production) , and therefore I have to configure the
> > right schema for each environment... And I don't want to add the schema
> > name prefix in front of each table name, but configuring a default db
> > schema somewhere in an iBatis config file (and to have several config
> > files, one for each environments).
> >
> > Thanks
> > -Gianluca
> >
> >
> >
>
Re: declarative - db schema
Posted by Larry Meadors <lm...@apache.org>.
No, there really isn't anything like this in iBATIS, because we try
(as much as possible) to not mess with the SQL that you write.
What database are you using?
Many allow you to specify a default schema on the connection string,
or connect as the schema owner.
Larry
On 3/11/06, Gianluca Gaiotto <gi...@gmail.com> wrote:
> Ciao,
> can I specify the database schema in sqlMap, or elsewhere, but in a
> declarative way (without hard-coding it)?
> The problem is that we have several environments stages (dev,
> integration, test, production) , and therefore I have to configure the
> right schema for each environment... And I don't want to add the schema
> name prefix in front of each table name, but configuring a default db
> schema somewhere in an iBatis config file (and to have several config
> files, one for each environments).
>
> Thanks
> -Gianluca
>
>
>
Re: declarative - db schema
Posted by Beemsterboer Software <ha...@beemsterboer-software.nl>.
This has been described in the DevGuide, refer to: "Simple Dynamic SQL
Elements".
In my previous project, I read the schema name from a properties file and
passed it to the SqlMap statement via a parameter map.
Greetings,
Hans.
Sven Boden wrote:
> There's no "defaultSchema=name" in iBatis. So that's not possible, I
> also don't think it's in the JDBC spec. The closest you can get for
> the moment is putting the default schema in a properties file:
>
> schema=yourschema
>
> And then in the SqlMap use schema's explicitly as:
>
> select *
> from ${schema}.table
>
> In your build procedure you can retrieve the properties for the
> specific environment you're building for.
>
> Regards,
> Sven
>
> Gianluca Gaiotto wrote:
>
>> Thanks for your immediate reply.
>> But what I am trying to discover is which is (if exists!) the
>> <properties /> tag (in the sql map config xml file) for expressing
>> the default schema.
>> And I don't want let the connection parameters determine which schema
>> to use, just because in our database environment we could have
>> different schemas per single connection.
>>
>> -Gianluca
>>
>>
>> Sven Boden wrote:
>>
>>> Use property files... look at e.g.
>>> http://www.mail-archive.com/user-java@ibatis.apache.org/msg02598.html
>>>
>>> Regards,
>>> Sven
>>
>>
>>
>>
>
>
>
Re: declarative - db schema
Posted by Sven Boden <li...@pandora.be>.
There's no "defaultSchema=name" in iBatis. So that's not possible, I
also don't think it's in the JDBC spec. The closest you can get for the
moment is putting the default schema in a properties file:
schema=yourschema
And then in the SqlMap use schema's explicitly as:
select *
from ${schema}.table
In your build procedure you can retrieve the properties for the specific
environment you're building for.
Regards,
Sven
Gianluca Gaiotto wrote:
> Thanks for your immediate reply.
> But what I am trying to discover is which is (if exists!) the
> <properties /> tag (in the sql map config xml file) for expressing the
> default schema.
> And I don't want let the connection parameters determine which schema
> to use, just because in our database environment we could have
> different schemas per single connection.
>
> -Gianluca
>
>
> Sven Boden wrote:
>
>> Use property files... look at e.g.
>> http://www.mail-archive.com/user-java@ibatis.apache.org/msg02598.html
>>
>> Regards,
>> Sven
>
>
>
>
Re: declarative - db schema
Posted by Gianluca Gaiotto <gi...@gmail.com>.
Thanks for your immediate reply.
But what I am trying to discover is which is (if exists!) the
<properties /> tag (in the sql map config xml file) for expressing the
default schema.
And I don't want let the connection parameters determine which schema to
use, just because in our database environment we could have different
schemas per single connection.
-Gianluca
Sven Boden wrote:
> Use property files... look at e.g.
> http://www.mail-archive.com/user-java@ibatis.apache.org/msg02598.html
>
> Regards,
> Sven
Re: declarative - db schema
Posted by Sven Boden <li...@pandora.be>.
Use property files... look at e.g.
http://www.mail-archive.com/user-java@ibatis.apache.org/msg02598.html
Regards,
Sven
Gianluca Gaiotto wrote:
> Ciao,
> can I specify the database schema in sqlMap, or elsewhere, but in a
> declarative way (without hard-coding it)?
> The problem is that we have several environments stages (dev,
> integration, test, production) , and therefore I have to configure the
> right schema for each environment... And I don't want to add the
> schema name prefix in front of each table name, but configuring a
> default db schema somewhere in an iBatis config file (and to have
> several config files, one for each environments).
>
> Thanks
> -Gianluca
>
>
>
>