MyBatis笔记（二）

最新推荐文章于 2024-05-31 10:46:10 发布

chengmi6360

最新推荐文章于 2024-05-31 10:46:10 发布

阅读量145

点赞数

文章标签： java

原文链接：https://my.oschina.net/u/4107179/blog/3033099

版权

1.映射Mapper

1.1.Mapper.xml中映射空间名称必须是Mapper接口的全限定名

<mapper namespace="cn.zy.mapper.EmployeeMapper">

1.2.Mapper接口定义方法

public interface EmployeeMapper {
    // 查一条
    Employee findOne(Long id);
    // 查询所有符合条件的数据
    List<Employee> findAll(EmployeeQuery employeeQuery);
    // 添加
    void add(Employee employee);
    // 删除
    void delete(Long id);
    // 修改
    void update(Employee employee);
}

1.3.获取Mapper实例

// 获取sql会话
SqlSession session = MyBatisUtils.getSession();
// 通过反射拿到EmployeeMapper实例
EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);

2.高级查询

2.1.准备一个query类，用于封装查询条件

public class EmployeeQuery {
    private String name;
    private Integer minAge;
    private Integer maxAge;
    @Override
    public String toString() {
        return "EmployeeQuery{" +
                "name='" + name + '\'' +
                ", minAge=" + minAge +
                ", maxAge=" + maxAge +
                '}';
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getMinAge() {
        return minAge;
    }
    public void setMinAge(Integer minAge) {
        this.minAge = minAge;
    }
    public Integer getMaxAge() {
        return maxAge;
    }
    public void setMaxAge(Integer maxAge) {
        this.maxAge = maxAge;
    }
    public EmployeeQuery(String name, Integer minAge, Integer maxAge) {
        this.name = name;
        this.minAge = minAge;
        this.maxAge = maxAge;
    }
    public EmployeeQuery() {
    }
}

2.2.高级查询

（1）模糊查询：concat(“%”, #{name}, ”%”)

（2）特殊符号处理：

·转义 <

·CDATA段 <![CDATA[…]]>

（3）使用where标签使第一个and变成where

（4）if中有多个条件使用 and/or 进行连接

（5）如果有相同的sql语句可以用sql标签进行抽取，使用include 引入

<!--查全部,嵌套高级查询语句-->
<select id="findAll" parameterType="cn.zy.query.EmployeeQuery" resultType="Employee">
  select * from employee <include refid="search"/>
</select>

<sql id="search">
    <where>
        <if test="name != null and name != ''">
            and name like concat("%",#{name},"%")
        </if>
        <if test="minAge != null">
            and age > #{minAge}
        </if>
        <if test="maxAge != null">
            and <![CDATA[ age < #{maxAge}]]>
        </if>
    </where>
</sql>

测试：
@Test
public void search(){
    // 设置查询条件
    EmployeeQuery employeeQuery = new EmployeeQuery();
    employeeQuery.setMaxAge(30);
    employeeQuery.setName("x");
    // 获取sql会话
    SqlSession session = MyBatisUtils.getSession();
    // 通过反射拿到EmployeeMapper实例
    EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
    // 调用查询方法
    List<Employee> search = mapper.findAll(employeeQuery);
    search.forEach(employee -> System.out.println(employee));
}

3.批量删除

（1）collection="array/list":如果传过来的是数组,写array(集合就写list)
（2）item:循环的每一个数据
（3）open:拼接字符串以什么开始
（4）close:拼接字符串以什么结尾
（5）separator:拼接的时候每个值使用,隔开
（6）index:遍历的下标

<!--批量删除-->
<delete id="delete" parameterType="list">
    delete from productdir where id in
    <foreach collection="list" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</delete>

@Test
public void delete(){
    // 获取sql会话
    SqlSession session = MyBatisUtils.getSession();
    // 通过反射拿到ProductMapper实例
    ProductMapper mapper = session.getMapper(ProductMapper.class);
    // 调用方法
    mapper.delete(Arrays.asList(26L, 27L));
    // 提交事务
    session.commit();
}

4.批量添加

<!—批量添加-->
<insert id="add" parameterType="list">
    insert into productdir (dirName, parent_id) values
    <foreach collection="list" item="p" separator=",">
        (#{p.dirName},#{p.productdir.id})
    </foreach>
</insert>

@Test
public void add(){
    // 获取sql会话
    SqlSession session = MyBatisUtils.getSession();
    // 通过反射拿到ProductMapper实例
    ProductMapper mapper = session.getMapper(ProductMapper.class);
    // 调用方法
    mapper.add(Arrays.asList(mapper.findOne(2L),mapper.findOne(3L),mapper.findOne(4L)));
    // 提交事务
    session.commit();
}

5.动态修改

只修改改变了的数据，使用set标签

<!--动态修改-->
<update id="update" parameterType="productdir">
    update productdir
    <set>
        <!--判断是否为空-->
        <if test="dirName != null and dirName != '' ">
            dirName = #{dirName},
        </if>
        <if test="productdir != null">
            parent_id = #{productdir.id},
        </if>
    </set>
    where id = #{id}
</update>

@Test
public void update(){
    // 获取sql会话
    SqlSession session = MyBatisUtils.getSession();
    // 通过反射拿到ProductMapper实例
    ProductMapper mapper = session.getMapper(ProductMapper.class);
    // 调用方法
    Product product = mapper.findOne(6L);
    product.setProductName("双飞燕");
    mapper.update(product);
    // 提交事务
    session.commit();
}

6.多对一

6.1.嵌套结果

一句sql关联查询

准备domain

public class Product {
  private Long id;
  private String productName;
  private Double salePrice;
  private String supplier;
  private String brand;
  private Double cutoff;
  private Double costPrice;
  // 多对一
  private Productdir productdir;


public class Productdir {
  private Long id;
  private String dirName;
  private Productdir productdir;
// getter/setter略

mapper.xml映射配置

<!--对象关系映射-->
<resultMap id="ProductMap" type="product">
    <!--如果主键对应不上，用id标签-->
    <id column="id" property="id"></id>

    <!--column表示表中的字段名，property表示列中的字段名-->
    <result column="productName" property="productName"></result>
    <result column="salePrice" property="salePrice"></result>
    <result column="supplier" property="supplier"></result>
    <result column="brand" property="brand"></result>
    <result column="cutoff" property="cutoff"></result>
    <result column="costPrice" property="costPrice"></result>

    <!--多对一配置-->
    <association property="productdir" javaType="productdir">
        <id column="did" property="id"></id>
        <result column="dirName" property="dirName"></result>

        <!--多对一嵌套配置-->
        <association property="productdir" javaType="productdir">
            <id column="pdid" property="id"></id>
            <result column="pdName" property="dirName"></result>
        </association>
    </association>

</resultMap>

mapper.xml查询语句

<!--查一条-->
<select id="findOne" parameterType="long" resultMap="ProductMap">
  select p.*,d.id did,d.dirName,pd.id pdid,pd.dirName pdName from product p
  join productdir d on p.dir_id = d.id
  join productdir pd on d.parent_id = pd.id
  where p.id = #{id}
</select>

<!--查全部-->
<select id="findAll" resultMap="ProductMap">
  select p.*,d.id did,d.dirName,pd.dirName pdName from product p 
join productdir d on p.dir_id = d.id 
join productdir pd 
on d.parent_id = pd.id
</select>

测试

@Test
public void findOne(){
    SqlSession session = null;
    try {
        // 获取sql会话
        session = MyBatisUtils.getSession();
        // 通过反射拿到ProductMapper实例
        ProductMapper mapper = session.getMapper(ProductMapper.class);
        // 调用方法
        Product product = mapper.findOne(2L);
        System.out.println(product);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
    }
}
@Test
public void findAll(){
    // 获取sql会话
    SqlSession session = MyBatisUtils.getSession();
    // 通过反射拿到ProductMapper实例
    ProductMapper mapper = session.getMapper(ProductMapper.class);
    // 调用方法
    List<Product> products = mapper.findAll();
    products.forEach(product -> System.out.println(product));
}

6.2.嵌套查询

整合两条sql数据

准备domian

public class Productstock {
  private Long id;
  private Long storeNum;
  private Date lastIncomeDate;
  private Date lastOutcomeDate;
  private Long warningNum;
  // 多对一
  private Product product;

mapper.xml映射配置（select属性，调用查询语句）

<!--对象关系映射-->
<resultMap id="ProductstockMap" type="productstock">
    <!--如果主键对应不上，用id标签-->
    <id column="id" property="id"></id>
    <!--column表示表中的字段名，property表示列中的字段名-->
    <result column="storeNum" property="storeNum"></result>
    <result column="lastIncomeDate" property="lastIncomeDate"></result>
    <result column="lastOutcomeDate" property="lastOutcomeDate"></result>
    <result column="warningNum" property="warningNum"></result>
    <!--多对一配置-->
    <association property="product" javaType="product" column="product_id" select="cn.zy.mapper.ProductMapper.findOne">
    </association>
</resultMap>

mapper.xml查询语句

<!--查一条-->
<select id="findOne" parameterType="long" resultMap="ProductstockMap">
  select * from productstock
  where id = #{id}
</select>

<!--查全部-->
<select id="findAll" resultMap="ProductstockMap">
  select * from productstock
</select>

测试

@Test
public void findOne(){
    SqlSession session = null;
    try {
        // 获取sql会话
        session = MyBatisUtils.getSession();
        // 通过反射拿到ProductstockMapper实例
        ProductstockMapper mapper = session.getMapper(ProductstockMapper.class);
        // 调用方法
        Productstock productstock = mapper.findOne(2L);
        System.out.println(productstock);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
    }
}
@Test
public void findAll(){
    // 获取sql会话
    SqlSession session = MyBatisUtils.getSession();
    // 通过反射拿到ProductstockMapper实例
    ProductstockMapper mapper = session.getMapper(ProductstockMapper.class);
    // 调用方法
    List<Productstock> productstocks = mapper.findAll();
    productstocks.forEach(productstock -> System.out.println(productstock));
}

6.3.野路子

<!--对象关系映射-->
<resultMap id="ProductMap" type="product">
    <!--如果主键对应不上，用id标签-->
    <id column="id" property="id"></id>

    <!--column表示表中的字段名，property表示列中的字段名-->
    <result column="productName" property="productName"></result>
    <result column="salePrice" property="salePrice"></result>
    <result column="supplier" property="supplier"></result>
    <result column="brand" property="brand"></result>
    <result column="cutoff" property="cutoff"></result>
    <result column="costPrice" property="costPrice"></result>

    <!--野路子，直接映射属性-->
    <result property="productdir.id" column="did" />
    <result property="productdir.dirName" column="dirName" />
    <result property="productdir.productdir.id" column="pdid" />
    <result property="productdir.productdir.dirName" column="pdName" />

</resultMap>

<!--查一条-->
<select id="findOne" parameterType="long" resultMap="ProductMap">
  select p.*,d.id did,d.dirName,pd.id pdid,pd.dirName pdName from product p
  join productdir d on p.dir_id = d.id
  join productdir pd on d.parent_id = pd.id
  where p.id = #{id}
</select>
<!--查全部-->
<select id="findAll" resultMap="ProductMap">
  select p.*,d.id did,d.dirName,pd.dirName pdName from product p join productdir d on p.dir_id = d.id join productdir pd on d.parent_id = pd.id
</select>

7.一对多

collection标签，表示集合

ofType表示集合中装的数据类型

其他地方与多对一没有太大区别

7.1.嵌套结果

<resultMap id="departmentMap" type="department">
    <id property="id" column="did" />
    <result property="name" column="dname" />
    <collection property="employees" ofType="employee">
        <id property="id" column="eid" />
        <result property="name" column="ename" />
        <result property="age" column="age" />
        <result property="sex" column="sex" />
    </collection>
</resultMap>

<select id="findAll" resultMap="departmentMap">
    select d.id did,d.name dname,e.id eid,e.name ename,e.age,e.sex
    from department d left join employee e on e.dept_id = d.id
</select>

7.2.嵌套查询

<resultMap id="departmentMap" type="department">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <collection property="employees" ofType="employee" column="id" select="cn.zy.mapper.EmployeeMapper.findOne">
    </collection>
</resultMap>
<select id="findAll" resultMap="departmentMap">
    select *  from department
</select>

8.缓存

8.1.一级缓存

·自带一级缓存，不需要任何配置

·一级缓存存在于SqlSession中

8.2.二级缓存

·二级缓存需要在Mapper.xml中配置<cache />

·二级缓存存在于SqlSessionFactory中

转载于:https://my.oschina.net/u/4107179/blog/3033099

chengmi6360

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis笔记（二）

1.映射Mapper 1.1.Mapper.xml中映射空间名称必须是Mapper接口的全限定名 <mapper namespace="cn.zy.mapper.EmployeeMapper"> 1.2.Mapper接口定义方法 public interface Em...
复制链接

扫一扫