1. 将定义的sql映射文件放在和Mapper接口同一目录下，可在resources中创建同级文件，但是在resources文件创建下级目录需要使用/隔开

2.设置sql映射文件的namespace属性为Mapper接口全限定名

<mapper namespace=”com.lichang.mapper.UserMapper”>
3.Mapper接口中定义方法，方法名就是SQL映射文件中sql语句的id，并保持参数类型和返回值类型一致
/
在Mapper接口中定义SQL映射文件sql语句
其中映射文件的id以及参数类型和返回值类型需要和方法保持一致
映射文件id为selectAll 和 返回值是一个集合List的User
/
List<User> selectAll()**;
4.编码

1. 通过SqlSession的getMapper方法获取Mapper接口的代理对象
2. 调用对应方法完成sql的执行
3. 配置mybatis-config文件中如果使用Mapper代理可以使用包扫描方式，简化mappers配置
   1. 
      <package name=”com.lichang.mapper”/>

// 获取Mapper接口代理对象
// Mapper实现路线，通过开启SqlSession对象反射到对应的接口类,从中找到接口类方法
// 通过接口类的方法在找到同目录下sql映射文件的id标识(方法和id一致以及返回值一致)
// 1.通过sqlSessionFactory的openSession()开启sqlSession使用
// 2.使用开启的sqlSession的getMapper方法反射对应的Mapper接口类
// 3.使用mapper.selectAll()查询返回值为集合的对象
final UserMapper mapper = sqlSession.getMapper(UserMapper.class);
final List<User> users = mapper.selectAll();
System.out.println(users);

查询所有数据

数据库表字段名称和实体类对象名称不一致，则不能封装
1. 起别名,使用as将字段名和实体类对象名称一致（每次查询都要更改）
但是可以使用sql片段，最后引用，并不灵活
2. resultMap：添加id：唯一标识，type：映射类型，支持别名
使用resultMap替换resultType

<sql id=”brand_column”> id, brand_name as brandName,company_name as companyName,ordered,description,status
</sql>
<select id=”selectAll” resultType=”Brand”> select
— 引入sql片段
<include refid=”brand_column”/>
from tb_brand;
</select>

<resultMap id=”brandResultMap” type=”Brand”>
<result column=”brand_name” property=”brandName”/>
<result column=”company_name” property=”companyName”/>
</resultMap>
<select id=”selectAll” resultMap=”brandResultMap”> select * from tb_brand;
</select>
<select id=”selectById” resultMap=”brandResultMap”>
select * from tb_brand where id = #{id};
</select>**

查询单个商品详情

<select id=”selectById” parameterType=”int” resultMap=”brandResultMap”> select * from tb_brand where id <![CDATA[ < ]]> #{id};
</select>
_@Test
_public void testSelectById() throws Exception {
// 接收前端参数
int id = 1;
// 获取SqlSessionFactory对象
String source = “mybatis-config.xml”;
final InputStream inputStream = Resources.getResourceAsStream(source);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream)**;

// 获取SqlSession对象<br />    SqlSession sqlSession = sqlSessionFactory.openSession**()**;
// 获取Mapper代理对象<br />    final BrandMapper mapper = sqlSession.getMapper**(**BrandMapper.class**)**;
// 根据id查询商品详情<br />    final Brand brand = mapper.selectById**(**id**)**;<br />    System.out.println**(**brand**)**;<br />    // 释放资源<br />    sqlSession.close**()**; **}**

条件查询

/ 根据条件查询商品 /
// 1.散装参数：添加字段条件需要添加@Param(“SQL占位符名称”)
Brand selectAllByConditions(@Param(“status”) int status, @Param(“companyName”) String companyName, @Param(“brandName”) String brandName);
// ———————————-
_@Test
_public void testByConditions() throws Exception {
int status =1;
String name = “华为”;
String companyName = “华为”;
// 处理参数
companyName = “%” + companyName + “%”;
name = “%” + name + “%”;
// 获取SqlSessionFactory对象
String source = “mybatis-config.xml”;
final InputStream inputStream = Resources.getResourceAsStream(source);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

// 获取SqlSession对象<br />    SqlSession sqlSession = sqlSessionFactory.openSession**()**;
// 获取Mapper代理对象<br />    final BrandMapper mapper = sqlSession.getMapper**(**BrandMapper.class**)**;
final Brand brand = mapper.selectAllByConditions**(**status, name, companyName**)**;<br />    System.out.println**(**brand**)**;<br />    sqlSession.close**()**;  **}**<br />**-------------------------------------------**<br />**// **2.传递对象：对象的属性名称和参数占位符一致<br />// 如果参数都来自同一对象可以直接传递对象<br />Brand selectAllByConditions**(**Brand _brand_**)**;<br />        int status =1;<br />        String brandName = "华为";<br />        String companyName = "华为";<br />        // 处理参数<br />        companyName = "%" + companyName + "%";<br />        brandName = "%" + brandName + "%";<br />        // 封装对象<br />/*        Brand brand = new Brand();<br />        brand.setStatus(status);<br />        brand.setBrandName(name);<br />        brand.setCompanyName(companyName);*/<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/12745457/1644400649599-777ac80a-cc4c-4eaf-8541-379326a02727.png#clientId=u5c73c411-1a55-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=279&id=u065df1d9&margin=%5Bobject%20Object%5D&name=image.png&originHeight=419&originWidth=1077&originalType=binary&ratio=1&rotation=0&showTitle=false&size=175321&status=done&style=none&taskId=u30f84063-da2e-4ddb-a24d-42a9163831d&title=&width=718)<br />int status =1;<br />String brandName = "华为";<br />String companyName = "华为";<br />// 处理参数<br />companyName = "%" + companyName + "%";<br />brandName = "%" + brandName + "%";<br />// 创建Map集合<br />Map map = new HashMap**()**;<br />map.put**(**"status",status**)**;<br />map.put**(**"brandName",brandName**)**;<br />map.put**(**"companyName",companyName**)**;<br />---final Brand brand1 = mapper.selectAllByConditions**(**map**)**;<br />/* 根据条件查询商品 */<br />// 1.散装参数：添加字段条件需要添加@Param("SQL占位符名称")<br />// 2.传递对象：对象的属性名称和参数占位符一致<br />// 3.Map集合，SQL中的参数名和map集合的键名要对应<br />Brand selectAllByConditions**(**Map _map_**)**;

动态条件查询

<select id=”selectAllByConditions” resultMap=”brandResultMap”>
select
from tb_brand
<where>
<if test=”status != null”>
status = #{status}
</if>
<if test=”companyName!= null and companyName != ‘’ “>
and company_name like #{companyName}
</if>
<if test=”brandName != null and brandName != ‘’”>
and brand_name like #{brandName};
</if>
</where>
</select*>

单条件动态查询

<select id=”selectAllByConditionsSingle” resultMap=”brandResultMap”>
select
from tb_brand
<where>
<choose>
<when test=”status != null”>
status = #{status}
</when>
<when test=”companyName!= null and companyName != ‘’ “>
and company_name like #{companyName}
</when>
<when test=”brandName != null and brandName != ‘’”>
and brand_name like #{brandName};
</when>



</choose>
</where>
</select*>

添加

<insert id=”add”>
insert into tbbrand(brand_name,company_name,ordered,description,status)
values(#{brandName},#{companyName},#{ordered},#{description},#{status});
</insert>
@Test
_public void add() throws Exception {
int status =1;
int ordered = 2;
String brandName = “波导手机”;
String companyName = “波导”;
String description = “手机中的战斗机”;
//封装参数
Brand brand = new Brand();
brand.setBrandName(brandName);
brand.setStatus(status);
brand.setDescription(description);
brand.setOrdered(ordered);
brand.setCompanyName(companyName);

// 获取SqlSessionFactory对象<br />    String source = "mybatis-config.xml";<br />    final InputStream inputStream = Resources.**getResourceAsStream(**source**)**;<br />    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder**()**.build**(**inputStream**)**;
// 获取SqlSession对象，添加openSession参数为true自动开启事务<br />    SqlSession sqlSession = sqlSessionFactory.openSession**(**true**)**;
// 获取Mapper代理对象<br />    final BrandMapper mapper = sqlSession.getMapper**(**BrandMapper.class**)**;<br />    final Brand brand1 = brand;<br />    mapper.add**(**brand1**)**;
// 不添加openSession参数必须手动开启提交<br />    sqlSession.commit**()**;
System.out.println**(**brand1**)**;<br />    sqlSession.close**()**;  **}**

添加-返回主键

<insert id=”addOrder” useGeneratedKeys=”true” keyProperty=”id”>
insert into tb_brand(brand_name,company_name,ordered,description,status)
values(#{brandName},#{companyName},#{ordered},#{description},#{status});
</insert>

修改

<update id=”update”>
update tbbrand set
brand_name=#{brandName},
company_name=#{companyName},
ordered=#{ordered},
description=#{description},
status=#{status}
where id = #{id};
</update>
@Test
_public void update() throws Exception {
int status =1;
int ordered = 5;
String brandName = “达利园集团”;
String companyName = “香飘飘”;
String description = “销量环绕地球一圈”;
int id = 7;
//封装参数
Brand brand = new Brand();
brand.setBrandName(brandName);
brand.setStatus(status);
brand.setDescription(description);
brand.setOrdered(ordered);
brand.setCompanyName(companyName);
brand.setId(id);
// 获取SqlSessionFactory对象
String source = “mybatis-config.xml”;
final InputStream inputStream = Resources.getResourceAsStream(source);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象，添加openSession参数为true自动开启事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
// 不添加openSession参数必须手动开启提交
sqlSession.commit();
// 获取Mapper代理对象
final BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
// 调用mapper方法
mapper.update(brand);
// 调用方法返回行数
final int i = mapper.updateDemo(brand);
System.out.println(i);
sqlSession.close(); }

修改动态字段

<update id=”updateDemo”>
update tbbrand
<set>
<if test=”brandName!=null and brandName!=’’ “>
brand_name=#{brandName},
</if>
<if test=”companyName!=null and companyName!=’’ “>
company_name=#{companyName},
</if>
<if test=”description!=null and description!=’’ “>
description=#{description},
</if>
<if test=”status!=null and status!=’’ “>
status=#{status},
</if>
</set>
where id = #{id};
</update>
@Test
_public void updateDemo() throws Exception {
int status =1;
int ordered = 10;
String brandName = “红米K50”;
String companyName = “红米集团”;
String description = “超级战斗机”;
int id = 8;
Brand brand = new Brand();
//封装参数
brand.setBrandName(brandName);
// brand.setStatus(status);
brand.setDescription(description);
// brand.setOrdered(ordered);
brand.setCompanyName(companyName);
brand.setId(id);
String source = “mybatis-config.xml”;
final InputStream inputStream = Resources.getResourceAsStream(source);
final SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
final SqlSession sqlSession = sqlSessionFactory.openSession(true);
final BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
mapper.updateDemo(brand);
sqlSession.close(); }

删除

/ 删除商品 /
void deleteById(@Param(“id”) int id);
/ 删除多个商品 参数为int[]数组/
void deleteByIds(@Param(“ids”) int[] id);

<delete id=”deleteById”>
delete from tbbrand where id = #{id}；
</delete>
@Test
_public void delete() throws Exception {
int id = 8;

String source = "mybatis-config.xml";<br />    final InputStream inputStream = Resources.**getResourceAsStream(**source**)**;<br />    final SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder**()**.build**(**inputStream**)**;<br />    final SqlSession sqlSession = sqlSessionFactory.openSession**(**true**)**;<br />    final BrandMapper mapper = sqlSession.getMapper**(**BrandMapper.class**)**;
mapper.deleteById**(**id**)**;<br />    sqlSession.close**()**;  **}**

删除多行数据

<delete id=”deleteByIds”>
delete from tbbrand where id in (?,?,?);
</delete>

<delete id=”deleteByIds”>
delete from tb_brand
where id
in
<foreach collection=”ids” item=”id” separator=”,” open=”(“ close= “)”>
#{id}
</foreach>
</delete>
——————————————————
<delete id=”deleteByIds”> delete from tb_brand
where id
in
(
<foreach collection=”array” item=”id” separator=”,”>
#{id}
</foreach>
)
</delete>
————————————————-
/ 删除多个商品 参数为int[]数组/
void deleteByIds(**@Param(“ids”) int[] _id);
——————————————————————————
_@Test
_public void deleteAll() throws Exception {
int[] arr = {3,6,7};
String source = “mybatis-config.xml”;
final InputStream inputStream = Resources.getResourceAsStream(source);
final SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
final SqlSession sqlSession = sqlSessionFactory.openSession(true);
final BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
mapper.deleteByIds(arr);
sqlSession.close();}

参数传递

多个参数需要使用@Params(“xx”)注解完成对参数名称的封装，注解中的值和sql语句占位名称一致


两个方法都可使用：

单个参数

总结：

使用注解开发

/使用注解查询语句/
@Select(“select from tbuser where id = #{_id}“)
User selectById(int id);
/使用注解添加语句/
@Insert(“insert into tbuser (username,password,gender,addr) values (#{_userName},#{passWord},#{gender},#{addr})“)
void add(User user*);

@Delete(“delete from tbuser where id = #{_id}“)
void deleteById(int id);
——————————————
_@Test
_public void userSelect() throws Exception {
int id = 1;

String source = "mybatis-config.xml";<br />    final InputStream inputStream = Resources.**getResourceAsStream(**source**)**;<br />    final SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder**()**.build**(**inputStream**)**;<br />    final SqlSession sqlSession = sqlSessionFactory.openSession**(**true**)**;<br />    final UserMapper mapper = sqlSession.getMapper**(**UserMapper.class**)**;
final User user = mapper.selectById**(**id**)**;<br />    System.out.println**(**user**)**;<br />    sqlSession.close**()**;  **}**