MyBatis/iBatis - reusable sql fragments in a separate SQL Map file?

Question

I would like to put sql fragments used by several of my SQL Map XML files in a separate file. At the moment, the <sql> elements with these fragments are in one of the mappers together with other elements like <select>, which makes them hard to find.

Can I have a mapper that defines just a few <sql> elements and is not used to generate an implementation to an interface? What would be the correct namespace of this mapper?

This is the SQL Map file with the framents:

<mapper namespace="com.company.project.dao.someDao">

    <sql id="whereDate">
        WHERE date(`time`) BETWEEN #{startDate} AND #{endDate}
    </sql>  

    <sql id="someOtherSqlFragment">
        ...
    </sql>

    <select id="getSomeData" resultType="SomeClass" parameterType="DateParam" >
        SELECT some_column, another_column          
        FROM some_table
        <include refid="whereDate"/>
        <include refid="otherSqlFragment"/>
    </select>

</mapper>

I'd like to separate the elements like this:
First Sql Map file:

<mapper namespace="com.company.project.dao.???">

    <sql id="whereDate">
        WHERE date(`time`) BETWEEN #{startDate} AND #{endDate}
    </sql>  

    <sql id="someOtherSqlFragment">
        ...
    </sql>

</mapper>

Second Sql Map file:

<mapper namespace="com.company.project.dao.someDao">

    <select id="getSomeData" resultType="SomeClass" parameterType="DateParam" >
        SELECT some_column, another_column          
        FROM some_table     
        <include refid="whereDate"/>
        <include refid="otherSqlFragment"/>
    </select>

</mapper>

score 35 · Accepted Answer · answered May 20 '11 at 08:13

35

This is exactly what a project I used to work on did. Common fragments were defined in a separate file which was included in the main iBATIS config file.

We had a SQL map file at the root named Core.ism.xml which looked like this:

<sqlMap namespace="Core" >

    <sql id="fragmentBasicAuditFieldNames">
        CreateDate, CreateUser, 
        UpdateDate, UpdateUser, UpdateCode 
    </sql>

    ....

And then in our SQL map files we could reference it like this:

<include refid="Core.fragmentBasicAuditFieldNames" />

I hope I've understood what you were asking correctly!

answered May 20 '11 at 08:13

Thanks! I also noticed that I'm using MapperFactoryBean class from mybatis-spring to "load" the other SQL Map files. The file with common fragments wasn't "loaded" anywhere, so I had to add it manually to the mybatis config file. – brabec May 20 '11 at 08:26
1

If the Core mapper file is the only thing in your mybatis config file, it may make more sense to use the `mapperLocations` property in SqlSessionFactoryBean to load the common mapper file. – AngerClown May 20 '11 at 12:02
4

If you are using MyBatis, replace "sqlMap" with "mapper". see http://code.google.com/p/mybatis/wiki/DocUpgrade3. I bluntly followed the above solution and ran into other issues. Then I realized I'm using MyBatis and have to use mapper instead of sqlMap. – Srikanth Jan 09 '12 at 20:33

score 5 · Answer 2 · edited May 23 '17 at 10:30

Say, you have some

<mapper namespace="Common">
   <sql id="idsIn">
        ${column} IN
        <foreach item="id" collection="ids" separator="," open="(" close=")">
            #{id}
        </foreach>
    </sql>
</mapper>

Than in another mapper you can use it like:

<mapper namespace="OtherMapper">
    <sql id="someSql">
        ...
        <include refid="Common.idsIn">
            <property name="column" value="${column}"/>
            <!-- OR hardcode: <property name="column" value="id"/> -->
            <property name="filterPksTable" value="${filterPksTable}"/>
        </include>
        ...
    </sql>
</mapper>

Also, you can have a look here

score 0 · Answer 3 · answered Jan 18 '23 at 18:13

0

You can access to ResultMap tag via its repository classname, like

... resultMap="com.example.repository.UserRepository.UserMapResult" ...

answered Jan 18 '23 at 18:13

Ilya Lisov

138
2
11

MyBatis/iBatis - reusable sql fragments in a separate SQL Map file?

3 Answers3

Linked