mybatis-generator能自动生成简单的CRUD操作,而条件及多表查询需自定义,下述为常用的SQL操作的mybatis示例
合并结果集
UNION
操作符用于合并两个或多个SELECT
语句的结果集。UNION
操作符选取不同的值。如果允许重复的值,请使用UNION ALL
。
DAO
List<City> selectCity(List<String> wordList);
Mapper
<select id="selectCity" resultType="com.test.City">
<foreach collection="list" index="index" item="word">
<if test="index > 0">
UNION ALL
</if>
SELECT
id, city, province
FROM hanzilib
WHERE city = #{word}
OR province = #{word}
</foreach>
</select>
IN 操作符常量拼接
IN
操作符允许我们在WHERE
子句中规定多个值。
DAO
List<City> selectCity(List<String> wordList);
Mapper
<select id="selectCity" resultType="com.test.City">
SELECT
<include refid="Common_Column_List"/>
FROM test
WHERE
id IN
<foreach item="item" collection="list" separator="," open="(" close=")" index="">
#{item}
</foreach>
</select>