MyBatis reuse query to get count(*)

Question

I have a big search query defined in my XML file

<select id="searchItems" resultMap="myMap" parameterType="map">
SELECT
  a lot of field
FROM multiple table with few joins
WHERE with few conditions
LIMIT x,y
</select>

The query above uses limit to be able to return paginate result and avoid returning the entire items on a search. But for a requirement I also need to return the total number of item found by the query.

My question is: How can I reuse the query above to instead just select count(*) and obviously without the LIMIT? Is there a way to separate each query part and reuse them in a <select> tag ?

Answer 1

you can extract the common sql and reuse like this:

    <sql id="queryUserCondition">
         where 1=1           
    </sql>
    <select id="countAll" resultType="int">
        select count(*) from user_info <include refid="queryUserCondition" />
    </select>
    <select id="findAll" resultMap="UserResultMap">
        select <include refid="UserColumns" />
        from user_info
        <include refid="queryUserCondition" />
    </select>