mybatis one DAO method map two or more Mapper XML element with different databaseid

zhou xiaohu

unread,

Apr 27, 2021, 8:54:55 AM4/27/21

to mybatis-user

i suppose to do a idea, example

I have two datasource Oracle & PostgreSQL, and put them in Mybatis multi-datasource, use Spring's AbstractRoutingDataSource to manage request

1st question:

But there are some SQL that PG not support, e, g 'select 1 from dual', what should i do ?

2nd question:

As i know, mybatis support 'databaseId' property in XML that meet different DB vendor, but i look into source, mybatis only load element that without 'databaseId' and with primary datasource's databaseId, that means i can choose only one database XML element

3rd question:

i suppose to code one DAO and map two or more different XML element with different databaseId, e, g:

----> UserMapper.xml ( id = 'queryOne' databaseId = 'oracle')

UserMapper.queryOne() +

-----> UserMapper.xml ( id = 'queryOne' databaseId = 'pg')

is there any way to release it ?

Guy Rouillier

unread,

Apr 27, 2021, 11:37:12 PM4/27/21

to mybatis-user

See inline comments.

--

Guy Rouillier

On 4/27/2021 8:54:55 AM, "zhou xiaohu" <4190...@qq.com> wrote:

i suppose to do a idea, example

I have two datasource Oracle & PostgreSQL, and put them in Mybatis multi-datasource, use Spring's AbstractRoutingDataSource to manage request

1st question:
But there are some SQL that PG not support, e, g 'select 1 from dual', what should i do ?

See https://www.dbconcepts.com/select-from-dual-in-postgres-englisch/. Note if you have a very large Oracle project you are trying to migrate to PG, look at Enterprise DB; they offer a modification of PostgreSQL that understands Oracle syntax. I participated in a large conversion, and we were able to support both databases with a single codebase.

2nd question:
As i know, mybatis support 'databaseId' property in XML that meet different DB vendor, but i look into source, mybatis only load element that without 'databaseId' and with primary datasource's databaseId, that means i can choose only one database XML element

Look in the MyBatis User's Guide, and search for databaseId. The guide has some discussion of databaseIdProvider. Sorry, I haven't done this in a couple years, so I'm a little rusty. Do you need to jump back and forth between Oracle and PG dynamically while your code is running? Or do you ever only run against one database at a time? If you need to talk to both during a single execution, then you are going to need a different SqlSessionFactory for each of them, since the connections are obviously different. In that case, you'll have both factories available, so you can just select the appropriate one to run the statement against.

3rd question:
i suppose to code one DAO and map two or more different XML element with different databaseId, e, g:
----> UserMapper.xml ( id = 'queryOne' databaseId = 'oracle')
UserMapper.queryOne() +
-----> UserMapper.xml ( id = 'queryOne' databaseId = 'pg')

is there any way to release it ?

Sorry, I don't know what you mean by releasing it. Please explain.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/e07d2b3a-f2e7-42b4-a706-8081ab890743n%40googlegroups.com.

zhou xiaohu

unread,

Apr 29, 2021, 11:14:35 AM4/29/21

to mybatis-user

Sry for my describe unclearly and thanks for your reply

My idea is that, when use multi datasources, is there any way to map one Mapper method to many Mapper XML tag with the same id

For example:

I got a mapper called UserMapper.java and its XML (UserMapper.xml)

There is a method called 'queryUserById' in this Mapper.java, and of course a '<select id="queryUserById">' in its Mapper.xml

Because I got two datasource in the same time, one is Oracle, another is PG, so the SQL syntax may be different

I need to control the request to those DB dynamically when program is in running time (use a config center or else)

e, g: first time, I got a 'queryUserById' request, it route to Oracle by default, then i changed the route switch, then it will route to PG

For the reason above, so I need a way to control this request to find a correct SQL syntax when route switch is changed

That is what I described in begin, I need to "map one Mapper method to many Mapper XML tag with the same id"

Those tags with the same id should be used dynamically when datasource changed

UserMapper.java:

public interface UserMapper {

UserInfo queryUserById();

}

----------------------------------------------------------------------------

UserMapper.xml

<select id="queryUserById" > SELECT user_name FROM user_info (some Oracle Syntax) </select>

<select id="queryUserById" > SELECT user_name FROM user_info (Some PG Syntax)</select>

-------------------------------------------------------------------------------

Guy Rouillier

unread,

Apr 30, 2021, 2:04:41 AM4/30/21

to mybatis-user

No, you cannot have the same statement ID used multiple times in a single mapper; if you try, you'll get a runtime error related to "duplicate statement id". I would recommend using two different XML files, one for Oracle and one for PostgreSQL, and loading them into two different SQLSessionFactory instances. Also, since mappers are associated with a connection, you can't really have a single mapped SQL statement switching between two different databases anyway. Even if you decide to use dynamic configuration, the datasource is defined in a Configuration, then you addMapper to that same configuration. So you can see that mappers are tied to a particular datasource.

You *can* have two different datasources, then load the *same* mapper XML file in both, using databaseId within that XML file to alter the construction of the SQL statements. Hopefully that distinction is clear.

--

Guy Rouillier

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/8060bb03-684e-4d0f-ac3c-e8bf05a5b822n%40googlegroups.com.

Iwao AVE!

unread,

Apr 30, 2021, 9:43:53 PM4/30/21

to mybatis-user

Actually, you can specify `databaseId` in `<select />`, `<update />`, etc.. :)

https://mybatis.org/mybatis-3/sqlmap-xml.html

Regards,

Iwao

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/eme125d3af-e7e5-42aa-8278-8825795c6a7f%40asus.

zhou xiaohu

unread,

May 12, 2021, 11:14:19 AM5/12/21

to mybatis-user

thanks for reply, I've resolved it by override class Configuration's method - 'getMappedStatement(String id)' of mybatis

and defined different datasource sql like this:

when mapper need a SQL to use, it will enter the 'getMappedStatement' method,

then i can change the target statement in application runtime

Guy Rouillier

unread,

Jun 13, 2021, 2:09:47 AM6/13/21

to mybatis-user

Iwao, sorry for the delay in responding, but you are correct. I did a test and had multiple statements in an XML file with the same id, but with a different databaseId. That indeed did not result in a duplicate ID error. Thanks for pointing that out. Also works if one statement has a databaseId and a second has none.

--

Guy Rouillier

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2QsVNnhujHvBxn6mM3J8Q_BUF%2BEzQh-nLYDMskv-cT-Rg%40mail.gmail.com.

Reply all

Reply to author

Forward