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!
<sqlMap resource="org/apache/ibatis/jgamestore/dao/sqlmap/sql/${dialect}/Account.xml"/>
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 namespace.
If you are interested in tweaking the framework, here is how you could add this:
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 behavior).
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.");
}
ms.setBaseCacheKey(hashCode());
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. :-)
Larry
On 5/5/05, Brandon Goodin <[EMAIL PROTECTED]> 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 < [EMAIL PROTECTED]> 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)
>
