mybatis实现分页、批量插入等操作时,mysql跟oracle的sql写法不同,可以通过databaseId来识别不同数据库,写不同的sql
1、spring配置文件中增加
<bean id="vendorProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
<property name="properties">
<props>
<prop key="Oracle">oracle</prop> <!-- 取别名,名称可任意,并不是上面配置的oralce数据源bean的id -->
<prop key="MySQL">mysql</prop> <!-- 取别名,名称可任意,并不是上面配置的mysql数据源的bean的id-->
</props>
</property>
</bean>
<bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
<property name="properties" ref="vendorProperties" />
</bean>
2、sqlSessionFactory中增加<property name="databaseIdProvider" ref="databaseIdProvider" />
3、mybatis mapper中增加databaseId="oracle"或者databaseId="mysql"
示例:
<insert id="addBillObjAttributeByList" parameterType="java.util.List" databaseId="oracle">
insert into pap_bcr_candidateprop
(
pk,
pk_bcr_obj,
en_prop_name,
display_name,
elem_type,
mapping_entity,
create_date
)
<foreach close=")" collection="list" item="item" open="(" separator="union all">
select
#{item.pk, jdbcType=VARCHAR},
#{item.pkBcrObj, jdbcType=VARCHAR},
#{item.enPropName, jdbcType=VARCHAR},
#{item.displayName, jdbcType=VARCHAR},
#{item.elemType, jdbcType=VARCHAR},
#{item.mappingEntity, jdbcType=VARCHAR},
#{item.createDate, jdbcType=TIMESTAMP}
from dual
</foreach>
</insert>
<insert id="addBillObjAttributeByList" parameterType="java.util.List" databaseId="mysql">
insert into pap_bcr_candidateprop
(
pk,
pk_bcr_obj,
en_prop_name,
display_name,
elem_type,
mapping_entity,
create_date
)
values
<foreach collection="list" item="attribute" index= "index" separator =",">
(
#{attribute.pk, jdbcType=VARCHAR},
#{attribute.pkBcrObj, jdbcType=VARCHAR},
#{attribute.enPropName, jdbcType=VARCHAR},
#{attribute.displayName, jdbcType=VARCHAR},
#{attribute.elemType, jdbcType=VARCHAR},
#{attribute.mappingEntity, jdbcType=VARCHAR},
#{attribute.createDate, jdbcType=TIMESTAMP}
)
</foreach>
</insert>
这样mybatis会根据数据库的不同自动读取相应数据库下的sql语句。