<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!-- 这个文件是映射文件,写SQL的
namespace:用来作为mapper.xml的唯一标识
resultType:类的全路径,用来封装查到的结果--><mappernamespace="userMapper"><!-- 查id=1的用户信息 --><selectid="getById"resultType="cn.tedu.pojo.User">
select * from user where id = 1
</select><!-- 查询所有用户的信息 --><selectid="selectAll"resultType="cn.tedu.pojo.User">
select * from user
</select><!--
SQL动态获取参数时,使用$和#的区别
$是底层使用了低级传输器,可能发生SQL注入攻击,低效,不拼串,可能发生SQL语法错误
#底层使用了高级传输器,安全,高效,会自动拼接字符串
--><!-- 根据用户名查询 --><selectid="getByName"resultType="cn.tedu.pojo.User">
select * from user where name = #{name}
</select></mapper>
6. 测试(TestMybatis.java)
packagecn.tedu.test;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.junit.jupiter.api.Test;importjava.io.IOException;importjava.io.InputStream;/**
* @Author Sky-haohao
* @Date 2021/8/17 9:50
* @Version 1.0
*/publicclassTestMybatis{@Testpublicvoidget(){try{//读取配置文件InputStream in =Resources.getResourceAsStream("mybatis-config.xml");//创建会话工厂SqlSessionFactory factory =newSqlSessionFactoryBuilder().build(in);//开启会话,准备执行SQLSqlSession session = factory.openSession();// 定位sql(namespace的值.id的值),并执行// 根据id 查询,selectOne返回一个结果Object o = session.selectOne("userMapper.getById");System.out.println(o);// 查询所有用户的信息,selectList 返回一个或多个结果List<Object> list = session.selectList("userMapper.selectAll");for(Object u : list){System.out.println(u);}// 根据name查询Object o2 = session.selectOne("userMapper.getByName","hanmeimei");System.out.println(o2);}catch(IOException e){
e.printStackTrace();}}}
7. $和#的区别
推荐使用# ,#在底层使用的是高级传输器,安全高效,且避免了sql注入攻击,并且会自动拼接字符串
例如,传入参数jack,select * from user where name = #{name}; 会自动加上'',转换后变成select * from user where name = 'jack';
$使用的是低级的传输器,可能发生SQL注入攻击,低效,不拼串,可能发生SQL语法错误
上述sql中,如果使用select * from user where name = ${name};则不会拼接字符串,直接变成select * from user where name = jack;,此时执行出错。
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="cn.tedu.dao.DeptMapper"><!-- 提取SQL片段,提高SQL片段的复用性 --><sqlid="cols">
id, dname, loc
</sql><!-- 根据id查询 --><selectid="getById"resultType="Dept">
select * from dept where id = 1
</select><!-- 根据name查询 --><selectid="getByName"resultType="Dept">
select
<includerefid="cols"></include>
from dept where dname = #{dname}
</select><!-- 新增部门记录 --><insertid="save">
insert into dept values (#{id},#{dname},#{loc})
</insert><!-- 根据id删除 - 方式一 --><deleteid="delete">
delete from dept where id in
<foreachcollection="array"item="ids"open="("close=")"separator=",">
#{ids}
</foreach></delete><!-- 根据id删除 - 方式二 --><deleteid="deleteByIds">
delete from dept where id in
<foreachcollection="array"item="id"open="("close=")"separator=",">
#{id}
</foreach></delete></mapper>
<!-- 提取SQL片段,提高SQL片段的复用性 --><sqlid="cols">
id, dname, loc
</sql><!-- 根据name查询 --><selectid="getByName"resultType="Dept">
select
<includerefid="cols"></include>
from dept where dname = #{dname}
</select>
16. if
在执行sql时,增加一些判断条件
如:判断字段名是否为null,不为null再执行
<selectid="getByName"resultType="Dept">
select
<includerefid="cols"></include>
from dept
<iftest="dname != null">
where dname = #{dname}
</if></select>
17. where
用于去掉条件中可能多余的and或者or
<selectid="getAll"resultType="Dept">
select
<includerefid="cols"></include>
from dept
<where><iftest="name != null"> name like #{name}</if><iftest="loc != null">and loc like #{loc}</if></where></select>
18. foreach
用于in子查询中的多个值的遍历
测试方法见上文
<!-- 根据id删除 - 方式一 --><deleteid="delete">
delete from dept where id in
<foreachcollection="array"item="ids"open="("close=")"separator=",">
#{ids}
</foreach></delete><!-- 根据id删除 - 方式二 --><deleteid="deleteByIds">
delete from dept where id in
<foreachcollection="array"item="id"open="("close=")"separator=",">
#{id}
</foreach></delete>