MyBatis multi-datasource configuration issue

Question

Using mybatis 3.2.8 ,Spring 3.2.9 release,mybatis-spring 1.2.2 to configure my project's DAO and the project need to convenient both mysql and oracle so i use the databaseIdProvider to adapt multi-datasource here is my configuration:

datasource

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
  <property name="jndiName">
    <value>xxxxx</value>
  </property>
</bean>

vendorProperties

<bean id="vendorProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
  <property name="properties">
    <props>
      <prop key="SQL Server">sqlserver</prop>
      <prop key="DB2">db2</prop>
      <prop key="Oracle">oracle</prop>
      <prop key="MySQL">mysql</prop>
      <prop key="H2">h2</prop>
    </props>
  </property>
</bean>

databaseIdProvider

<bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
  <property name="properties" ref="vendorProperties" />
</bean>

sqlSessionFactory

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="databaseIdProvider" ref="databaseIdProvider" />
  <property name="configLocation" value="classpath:mybatis-config.xml"></property>
</bean>

because mysql's paging is different from oracle's so i wrote two ways of paging and depends on runtime environment to chose to use which.here is the mapper.xml:

<sql databaseId="oracle" id="PagePrefix">
  <!--
          WARNING - @mbggenerated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
  <if test="page != null">
    select * from ( select row_.*, rownum rownum_ from (
  </if>
</sql>
<sql databaseId="mysql" id="PagePrefix">
  <!--
          WARNING - @mbggenerated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
  <if test="page != null">
    select * from (
  </if>
</sql>

and use it like this:

<select id="selectByExample" parameterType="xxx.xxx.xxx" resultMap="BaseResultMap">
  <!--
          WARNING - @mbggenerated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
  <include refid="PagePrefix" />select
  <if test="distinct">
    distinct
  </if>
  from xxx
  <if test="_parameter != null">
    <include refid="Example_Where_Clause" />
  </if>
  <include refid="PageSuffix" />
</select>

the above is all my configuration. when i am running the query operation or any other DML,the console will print:

org.apache.ibatis.builder.IncompleteElementException: Could not find SQL statement to include with refid 'xxx.xxx.xxx.PagePrefix'

i find this exception was caused by org.apache.ibatis.session.Configuration.sqlFragments did not had the value that the key is 'PagePrefix'. so i following the blow steps to find what is wrong:

my local environment is oracle,so i modified the mapper.xml, removed the mysql config just left the oracle config:

<sql databaseId="oracle" id="PagePrefix">
  <!--
              WARNING - @mbggenerated
              This element is automatically generated by MyBatis Generator, do not modify.
            -->
  <if test="page != null">
    select * from ( select row_.*, rownum rownum_ from (
  </if>
</sql>

but it's also not work,the exception still remained.

then i thought maybe the mybatis did not recognized my runtime environment,so i looked into the source code to find out the whether the information was set into the mybatis or not.i find the startup class org.mybatis.spring.SqlSessionFactoryBean#buildSqlSessionFactory() method has:

    if (this.databaseIdProvider != null) {
          try {
            configuration.setDatabaseId(this.databaseIdProvider.getDatabaseId(this.dataSource));
          } catch (SQLException e) {
            throw new NestedIOException("Failed getting a databaseId", e);
          }
        }

and i toggled breakpoint in here and find the value was right.

Then i started to find where and when the sqlFragments was assigned value.i found that sqlFragments was assigned when parsing the mapper.xml: org.apache.ibatis.builder.xml.XMLMapperBuilder#configurationElement()

     private void configurationElement(XNode context) {
        try {
          String namespace = context.getStringAttribute("namespace");
          if (namespace.equals("")) {
        	  throw new BuilderException("Mapper's namespace cannot be empty");
          }
          builderAssistant.setCurrentNamespace(namespace);
          cacheRefElement(context.evalNode("cache-ref"));
          cacheElement(context.evalNode("cache"));
          parameterMapElement(context.evalNodes("/mapper/parameterMap"));
          resultMapElements(context.evalNodes("/mapper/resultMap"));
          sqlElement(context.evalNodes("/mapper/sql"));
          buildStatementFromContext(context.evalNodes("select|insert|update|delete"));
        } catch (Exception e) {
          throw new BuilderException("Error parsing Mapper XML. Cause: " + e, e);
        }
      }

and the method sqlElement do the actual assign value on sqlFragments and according the databaseId to find the tag ''

    private void sqlElement(List<XNode> list, String requiredDatabaseId) throws Exception {
        for (XNode context : list) {
          String databaseId = context.getStringAttribute("databaseId");
          String id = context.getStringAttribute("id");
          id = builderAssistant.applyCurrentNamespace(id, false);
          if (databaseIdMatchesCurrent(id, databaseId, requiredDatabaseId)) sqlFragments.put(id, context);
        }
      }

but in that time the databaseId hasn't been set yet beacuse the parsing phase is before the setDatabaseId operation what i mentioned before. Now i have no idea how to solve this problem.does any people encountered the same situation like me?

Are you sure that all namespaces are correct? What is the namespace of the fragment where PagePrefix is declared? when you reference a fragment defined in antoher file the referenced id must contain the namespace — Giovanni
– Giovanni, Commented Mar 19, 2015 at 7:35
tks in advance,i removed the the databaseId attribute of the sql tag and it not throw exception at all,so the namespaces are correct i think. — Gospel
– Gospel, Commented Mar 19, 2015 at 7:42

Giovanni · Accepted Answer · 2015-03-19 09:21:18Z

1

I managed to create a working sample. The trick was getting the right map for the properties. You must be able to specify what the

DataSource().getConnection().getMetaData().getDatabaseProductName()

returns and user it as the key in your property file my mapping for postgres is

<bean id="vendorProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
    <property name="properties">
        <props>
            <prop key="PostgreSQL">postgres</prop>
            <prop key="Oracle">oracle</prop>
        </props>
    </property>
</bean>

PostgresSQL is the result of

DataSource().getConnection().getMetaData().getDatabaseProductName()

In the mapping file I have

<sql databaseId="postgres" id="PagePrefix">
    select sum(a) from (
</sql>

and it's correctly loaded

answered Mar 19, 2015 at 9:21

Giovanni

4,0253 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gospel · Accepted Answer · 2015-03-19 10:29:50Z

0

I found that problem was causing that i wrote the mappers tag in mybatis-config.xml so that the mybatis will do it's own configure and despite Spring's configure.so if you want to use multi-datasource through Spring that you can not write the mybatis' own config xml.

answered Mar 19, 2015 at 10:29

Gospel

812 silver badges10 bronze badges

Collectives™ on Stack Overflow

MyBatis multi-datasource configuration issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related