- <where></where>标签可以去掉前and
<sql id="selectall" >
SELECT * FROM USER
</sql>
<select id="selectUserByUsernameAndSex" parameterType="com.company.combine.model.User"
resultType="com.company.combine.model.User">
<include refid="selectall"/>
<where>
<if test="sex!=null and sex!=''">
and sex = #{sex}
</if>
<if test="username!=null and username!=''">
and username = #{username}
</if>
</where>
</select>
- foreach 在Javabean中传递List
<!--bean中传List ,在User中有一个名叫idList的List-->
<select id="selectByPrimaryKeys" parameterType="com.company.combine.model.User"
resultType="com.company.combine.model.User">
SELECT *
FROM USER
<where>
<foreach collection="idList" item="id" separator="," open="id in(" close=")">
#{id}
</foreach>
</where>
</select>
- foreach 直接传递数组
<!--直接传数组-->
<select id="selectByPrimaryKeys"
resultType="com.company.combine.model.User">
SELECT *
FROM USER
<where>
<foreach collection="array" item="id" separator="," open="id in(" close=")">
#{id}
</foreach>
</where>
</select>
- foreach 直接传递List
<!--直接传list-->
<select id="selectByPrimaryKeys"
resultType="com.company.combine.model.User">
SELECT *
FROM USER
<where>
<foreach collection="list" item="id" separator="," open="id in(" close=")">
#{id}
</foreach>
</where>
</select>
- 模糊查询
<!--#{} 占位符-->
<!--${} 拼接符-->
<select id="selectUserByUsername" parameterType="String"
resultType="com.company.combine.model.User">
SELECT * FROM USER WHERE username LIKE "%${value}%"
</select>
- 一对一关联
<resultMap id="result2" type="com.company.combine.model.Orders">
<id property="id" column="id"/>
<result property="userId" column="userId"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<association property="user" javaType="com.company.combine.model.User">
<result property="username" column="username"/>
</association>
</resultMap>
<!--一对一-->
<select id="selectOrdersList"
resultMap="result2">
SELECT
o.id,
o.user_id as userId,
o.number,
o.createtime,
u.username
FROM orders o LEFT JOIN user u ON o.user_id=u.id
</select>
- 一对多关联
<!--一对多-->
<resultMap id="result3" type="com.company.combine.model.User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<collection property="ordersList" ofType="com.company.combine.model.Orders">
<id property="id" column="id"/>
<result property="userId" column="userId"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
</collection>
</resultMap>
<select id="selectUserList"
resultMap="result3">
SELECT
o.id,
o.user_id as userId,
o.number,
o.createtime,
u.username
FROM user u LEFT JOIN orders o ON u.id=o.user_id
</select>
