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 Karsten Silz <ks...@seldonsystems.com> on 2005/05/05 16:02:20 UTC
How to support multiple SQL dialects?
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)
Re: DAO Exception
Posted by Clinton Begin <cl...@gmail.com>.
You need to dig the real exception out of the Apache logs. This is a
secondary error.
Clinton
On 5/9/05, Folashade Adeyosoye <sh...@gmail.com> wrote:
>
> My code still produces these errors from this line in my daoConfig.java,
> mind you am just trying to get the DAO aspect to work, no sqlMap
>
> I have also test this with a standalone app (No Struts) and it worked
> fine…
>
> I am also using the jars that came with the JPetStore Application
>
> Any help would be appreciated.
>
> Thanks
>
> > daoManager = DaoManagerBuilder.buildDaoManager(reader);
>
> StandardContext[/fgcsys]: Servlet /fgcsys threw load() exception:
> javax.servlet.ServletException: Servlet.init() for servlet fgcsys threw
> exception
>
> javax.servlet.ServletException: Servlet.init() for servlet fgcsys threw
> exception
>
> at org.apache.catalina.core.StandardWrapper.loadServlet(
> StandardWrapper.java:963)
>
> at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:823)
>
> at org.apache.catalina.core.StandardContext.loadOnStartup(
> StandardContext.java:3420)
>
> at org.apache.catalina.core.StandardContext.start(StandardContext.java
> :3608)
>
> at org.apache.catalina.core.ContainerBase.addChildInternal(
> ContainerBase.java:821)
>
> at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:807)
>
> at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:579)
>
> at org.apache.catalina.core.StandardHostDeployer.addChild(
> StandardHostDeployer.java:700)
>
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>
> at sun.reflect.NativeMethodAccessorImpl.invoke(
> NativeMethodAccessorImpl.java:39)
>
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(
> DelegatingMethodAccessorImpl.java:25)
>
> at java.lang.reflect.Method.invoke(Method.java:324)
>
> at org.apache.commons.beanutils.MethodUtils.invokeMethod(MethodUtils.java
> :252)
>
> at org.apache.commons.digester.SetNextRule.end(SetNextRule.java:260)
>
> at org.apache.commons.digester.Rule.end(Rule.java:276)
>
> at org.apache.commons.digester.Digester.endElement(Digester.java:1064)
>
> at org.apache.crimson.parser.Parser2.maybeElement(Parser2.java:1720)
>
> at org.apache.crimson.parser.Parser2.parseInternal(Parser2.java:667)
>
> at org.apache.crimson.parser.Parser2.parse(Parser2.java:337)
>
> at org.apache.crimson.parser.XMLReaderImpl.parse(XMLReaderImpl.java:448)
>
> at org.apache.commons.digester.Digester.parse(Digester.java:1562)
>
> at org.apache.catalina.core.StandardHostDeployer.install(
> StandardHostDeployer.java:385)
>
> at org.apache.catalina.core.StandardHost.install(StandardHost.java:803)
>
> at org.apache.catalina.startup.HostConfig.deployDescriptors(
> HostConfig.java:442)
>
> at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:399)
>
> at org.apache.catalina.startup.HostConfig.start(HostConfig.java:718)
>
> at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java
> :358)
>
> at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(
> LifecycleSupport.java:166)
>
> at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1196)
>
> at org.apache.catalina.core.StandardHost.start(StandardHost.java:738)
>
> at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1188)
>
> at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:347)
>
> at org.apache.catalina.core.StandardService.start(StandardService.java
> :497)
>
> at org.apache.catalina.core.StandardServer.start(StandardServer.java:2190)
>
> at org.apache.catalina.startup.Catalina.start(Catalina.java:512)
>
> at org.apache.catalina.startup.Catalina.execute(Catalina.java:400)
>
> at org.apache.catalina.startup.Catalina.process(Catalina.java:180)
>
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>
> at sun.reflect.NativeMethodAccessorImpl.invoke(
> NativeMethodAccessorImpl.java:39)
>
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(
> DelegatingMethodAccessorImpl.java:25)
>
> at java.lang.reflect.Method.invoke(Method.java:324)
>
> at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:203)
>
>
DAO Exception
Posted by Folashade Adeyosoye <sh...@gmail.com>.
My code still produces these errors from this line in my daoConfig.java,
mind you am just trying to get the DAO aspect to work, no sqlMap
I have also test this with a standalone app (No Struts) and it worked fine.
I am also using the jars that came with the JPetStore Application
Any help would be appreciated.
Thanks
> daoManager = DaoManagerBuilder.buildDaoManager(reader);
StandardContext[/fgcsys]: Servlet /fgcsys threw load() exception:
javax.servlet.ServletException: Servlet.init() for servlet fgcsys threw
exception
javax.servlet.ServletException: Servlet.init() for servlet fgcsys threw
exception
at
org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:96
3)
at
org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:823)
at
org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:
3420)
at
org.apache.catalina.core.StandardContext.start(StandardContext.java:3608)
at
org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:8
21)
at
org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:807)
at
org.apache.catalina.core.StandardHost.addChild(StandardHost.java:579)
at
org.apache.catalina.core.StandardHostDeployer.addChild(StandardHostDeployer.
java:700)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
org.apache.commons.beanutils.MethodUtils.invokeMethod(MethodUtils.java:252)
at
org.apache.commons.digester.SetNextRule.end(SetNextRule.java:260)
at org.apache.commons.digester.Rule.end(Rule.java:276)
at
org.apache.commons.digester.Digester.endElement(Digester.java:1064)
at
org.apache.crimson.parser.Parser2.maybeElement(Parser2.java:1720)
at
org.apache.crimson.parser.Parser2.parseInternal(Parser2.java:667)
at org.apache.crimson.parser.Parser2.parse(Parser2.java:337)
at
org.apache.crimson.parser.XMLReaderImpl.parse(XMLReaderImpl.java:448)
at
org.apache.commons.digester.Digester.parse(Digester.java:1562)
at
org.apache.catalina.core.StandardHostDeployer.install(StandardHostDeployer.j
ava:385)
at
org.apache.catalina.core.StandardHost.install(StandardHost.java:803)
at
org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.java:442
)
at
org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:399)
at
org.apache.catalina.startup.HostConfig.start(HostConfig.java:718)
at
org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:358)
at
org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSuppor
t.java:166)
at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1196)
at
org.apache.catalina.core.StandardHost.start(StandardHost.java:738)
at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1188)
at
org.apache.catalina.core.StandardEngine.start(StandardEngine.java:347)
at
org.apache.catalina.core.StandardService.start(StandardService.java:497)
at
org.apache.catalina.core.StandardServer.start(StandardServer.java:2190)
at org.apache.catalina.startup.Catalina.start(Catalina.java:512)
at
org.apache.catalina.startup.Catalina.execute(Catalina.java:400)
at
org.apache.catalina.startup.Catalina.process(Catalina.java:180)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:203)
Re: How to support multiple SQL dialects?
Posted by Larry Meadors <la...@gmail.com>.
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!
<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 <br...@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 <ks...@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)
> >
>
Re: How to support multiple SQL dialects?
Posted by Brandon Goodin <br...@gmail.com>.
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 <ks...@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)
>