ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: How to support multiple SQL dialects?
Date Thu, 05 May 2005 14:31:20 GMT
That's a pretty large question. IBatis can deal with this situation.
But, you have to strategize for it up front. If you are supporting
multiple dialets then you will need to determine which are common
queries and which are not. The queries that have dialect specific
syntax you can move to a dialect specific sqlmap and map it in your
sqlmapconfig using a ${databaseName} property placeholder. Then you
can configure the sqlmaps you want to load accordingly.

For example:

Account.xml
MySQLAccount.xml
OracleAccount.xml

---- database.properties ---
...
databaseName=MySQL
...

--- sql-map-config.xml ---

...
  <sqlMap resource="org/apache/ibatis/jgamestore/dao/sqlmap/sql/Account.xml"/>
  <sqlMap resource="org/apache/ibatis/jgamestore/dao/sqlmap/sql/${databaseName}Account.xml"/>
...

So, in this scenario you would load your common sql in Account.xml and
then you would have your uncommon sql statements located in the
MySQLAccount.xml. Something to note her is that if only one database
has uncommon SQL for a query then you will need place all your
database's queries for that particular mapped statement into their own
dialect specific uncommon SQL xml file. But, that is hard to avoid.
So, in this example if you wanted to switch to using your Oracle
uncommon SQL you would change your database.properties file to contain
databaseName=Oracle

We are planning to add extendable sql maps soon and that may provide
some relief here. Using the same combination of techniques we may be
able to override only certain sqlmaps for a dialect and reduce
redundancy.

Brandon

On 5/5/05, Karsten Silz <ksilz@seldonsystems.com> wrote:
> Hi!
> 
> I've used SqlMaps on a couple of projects and I'm quite happy with it.
> However, I don't know how to handle one requirement that has shown up
> recently: supporting multiple SQL dialects (i.e., different JOIN syntax
> etc.) when you interacting with different databases.
> 
> If you assume that the query just look a little different, I could see
> how you have multiple sets of query files and just use / ship a
> different one for each installation.  Now could you have a "base set"
> where most queries are in (because they are the same across databases)
> and then sets with queries that are specific for each database (similar
> to accessing resource bundles)?  I would really like to avoid
> duplicating each and every query for each supported database.  Or could
> you use the "namespace" feature for that?
> 
> Now this breaks down when there are queries that are one query for one
> database and multiple queries / queries plus Java code for another
> database.  Would you have to build a little framework around SqlMaps
> then?  I guess SqlMaps isn't really build to handle this kind
> situation.  Or is it?
> 
> Any help would be appreciated.
> 
> --
> Karsten Silz
> 
> "It is dangerous to be sincere unless you are also stupid."
> George Bernard Shaw (quote found on http://www.theinquirer.net)
>

Mime
View raw message