<selectid="selectUserAndCompanyMethod1"resultMap="UserAndCompanyMethod1"> select * from tb_user left outer join tb_company on company_id = c_id where id = #{id} </select>
<selectid="selectUserAndCompanyMethod2"resultMap="UserAndCompanyMethod2"> select * from tb_user left outer join tb_company on tb_company.company_id = tb_user.c_id where tb_user.id = #{id} </select>
<selectid="selectCompanyAndUserMethod1"resultMap="CompanyAndUserMethod1"> select * from tb_company left outer join tb_user on company_id = c_id where company_id = # {companyId} </select>
<selectid="selectByUser"resultType="Pojo.user"> select * from tb_user where 1=1 <iftest="id!=null "> id = #{id} </if> <iftest="username!=null and username!='' "> and username = #{username} </if> <iftest="password!=null and password!='' "> and password = #{password} </if> <iftest="cId!=null"> and c_id = #{cId} </if> <iftest="age!=null"> and age = #{age} </if> </select>
在where后面加上1=1恒等式。是为了避免当 if test=”id!=null”不成立时,后面执行sql语句时就会出出现sql语法错误。
1
select * from tb_user where and c_id = ? and age = ? <!--where多了一个and语法错误-->
1
select * from tb_user where 1=1 and c_id = ? and age = ? <!--加上后,这样就不会报错-->
<selectid="selectByUser"resultType="Pojo.user"> select * from tb_user <where> <iftest="id!=null "> and id = #{id} </if> <iftest="username!=null and username!='' "> and username = #{username} </if> <iftest="password!=null and password!='' "> and password = #{password} </if> <iftest="c_id!=null"> and c_id = #{cId} </if> <iftest="age!=null "> and age = #{age} </if> </where> </select>
where标签不能去掉条件后多余的and/or
trim
trim用于去掉或添加标签中的内容
常用属性
prefix:在trim标签中的内容的前面添加某些内容
suffix:在trim标签中的内容的后面添加某些内容
prefixOverrides:在trim标签中的内容的前面去掉某些内容
suffixOverrides:在trim标签中的内容的后面去掉某些内容
若trim中的标签都不满足条件,则trim标签没有任何效果,也就是只剩下select * from t_emp
<selectid="selectByUser"resultType="Pojo.user"> select * from tb_user <where> <choose> <whentest="id!=null"> id = #{id} </when> <whentest="username!=null and username!=''"> username = #{username} </when> <whentest="password!=null and password !='' "> password = #{password} </when> <whentest="cId!=null"> c_id=#{cId} </when> <whentest="age!=null"> age = #{age} </when> <otherwise> 1=1 </otherwise> </choose> </where> </select>
foreach
属性:
collection:设置要循环的数组或集合
item:表示集合或数组中的每一个数据
separator:设置循环体之间的分隔符,分隔符前后默认有一个空格,如,
open:设置foreach标签中的内容的开始符
close:设置foreach标签中的内容的结束符
1 2 3 4 5 6
<deleteid="deleteMoreById"> delete from tb_user where id in <foreachcollection="ids"item="id"separator=","open="("close=")"> #{id} </foreach> </delete>
1 2 3 4 5 6
<insertid="insertMoreByList"> insert into tb_user(id, username, password, age, c_id) value <foreachcollection="userList"item="user"separator="," > (#{user.id},#{user.username},#{user.password},#{user.age},#{user.cId}) </foreach> </insert>