ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Larry Meadors <larry.mead...@gmail.com>
Subject Re: How to support multiple SQL dialects?
Date Thu, 05 May 2005 15:30:02 GMT
This is something I have been thinking alot about lately.

ANSI SQL is great if you can meet all of the requirements easily, but in my 
experience you can't always depend on that, especially if you are thinking 
of supporting both MySQL and anything else (that supports stored 
procedures). ;-)

Brandon's suggestion is an easier way, but I would reccomend using the 
dialect as a directory instead of a file name. In the system i currently 
work on there are 59 SQL map files..multiply that by 3-4 dialects, and you 
get a heck of a mess!


Two options that I have been pondering are:
- add an extends attribute to the sqlMap element in the sql map files
- add a "last in wins" attribute to the settings element in the sql map 
config file

The extends route is probably better in the long run, but both give you 
essentially the same thing: two (or more) sql maps that define the same 

If you are interested in tweaking the framework, here is how you could add 

Look in the com.ibatis.sqlmap.engine.builder.xml package at the 
SqlMapConfigParser class. 

In there, the addSettingsNodelets method would have to be changed to get the 
"lastInWins" attribute and (or whatever it is called) and set (a new) 
property on its instance of the SqlMapExecutorDelegate class named "
vars.delegate". The default for this would be false (to match the current 

Next, in the com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate class, the 
addMappedStatement method would need to look like this:

public void addMappedStatement(MappedStatement ms) {
if (!lastInWins && mappedStatements.containsKey(ms.getId())) {
throw new SqlMapException("There is already a statement named " + ms.getId() 
+ " in this SqlMap.");
mappedStatements.put(ms.getId(), ms);

I think that would pretty much do it...and if I could dig up a couple hours 
that I was not supposed to be doing something else, I would write the darn 
code myself and test it. :-)


On 5/5/05, Brandon Goodin <brandon.goodin@gmail.com> wrote:
> 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)
> >

View raw message