day15_mybatis2

1resultMap 结果集自定义映射

resultType 指定数据类型 需要数据库字段与java属性名一一对应 对不上不能封装数据    
resultMap  指定自定义映射表
               1默认效果 与resultType相同
               2自定义映射 有配置 先走配置 没配置 根据属性一一对应
                 id     配置主键   column(数据库列)   property(java对象属性) 对应关系
                 result 其他列

               3高级映射功能 对象与对象的关系

注意:resultMap 可以代替 resultType

1.1默认对应

    <resultMap id="empMap" type="com.javasm.entity.Emp">

    </resultMap>

不自定义列对应关系默认可以与数据库列一一对应

1.2自定义映射

   select empno,ename,sal+ IFNULL(comm,0) as totalsal  from emp


	<resultMap id="empMap" type="com.javasm.entity.Emp">
<!--        数据库列   java对象属性    -->
        <id column="empno" property="empno"></id>
        <result column="totalsal" property="sal"></result>

    </resultMap>

查询语句中如果有个别列不想建立java对象属性可以自己指定传入哪个属性中 totalsal -->传入sal属性

1.3高级映射

3高级映射功能 对象与对象的关系
     1需要搭配表连接使用 需要查出有关系的对象的列
     2使用高级映射 默认的一一对应会失效
     3通过继承属性 继承baseMap 避免重复写映射表
        <resultMap id="empWithDeptMap" type="com.javasm.entity.Emp" extends="EmpBaseMap">

举例 emp与dept的关系

Emp.java

package com.javasm.entity;

import lombok.Data;

import java.util.Date;
import java.io.Serializable;

/**
 * (Emp)实体类
 *
 * @author makejava
 * @since 2025-08-13 09:34:11
 */
@Data
public class Emp implements Serializable {
    private static final long serialVersionUID = -83501157304743071L;

    private Integer empno;

    private String ename;

    private String job;

    private Integer mgr;

    private String hiredate;

    private Double sal;

    private Double comm;

    private Integer deptno;

    //员工对应的部门
    private Dept dept;

}

Dept.java

package com.javasm.entity;

import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 * (Dept)实体类
 *
 * @author makejava
 * @since 2025-08-13 09:34:15
 */
@Data
public class Dept implements Serializable {
    private static final long serialVersionUID = 759301199990564576L;

    private Integer deptno;

    private String dname;

    private String loc;

    //部门中有员工
    private List<Emp> emps;



}

1.3.1 association 关联关系对象对应另外一个对象(单个)


<association property="dept" javaType="com.javasm.entity.Dept">
    <result property="deptno" column="deptno" jdbcType="INTEGER"/>
    <result property="dname" column="dname" jdbcType="VARCHAR"/>
    <result property="loc" column="loc" jdbcType="VARCHAR"/>
</association>

emp需要对应部门单个对象

    <resultMap type="com.javasm.entity.Emp" id="EmpBaseMap">
        <id property="empno" column="empno" jdbcType="INTEGER"/>
        <result property="ename" column="ename" jdbcType="VARCHAR"/>
        <result property="job" column="job" jdbcType="VARCHAR"/>
        <result property="mgr" column="mgr" jdbcType="INTEGER"/>
        <result property="hiredate" column="hiredate" jdbcType="TIMESTAMP"/>
        <result property="sal" column="sal" jdbcType="NUMERIC"/>
        <result property="comm" column="comm" jdbcType="NUMERIC"/>
        <result property="deptno" column="deptno" jdbcType="INTEGER"/>
    </resultMap>
	
    <resultMap id="empWithDeptMap" type="com.javasm.entity.Emp" extends="EmpBaseMap">
        <!--        数据库列   java对象属性    -->
        <association property="dept" javaType="com.javasm.entity.Dept">
            <result property="deptno" column="deptno" jdbcType="INTEGER"/>
            <result property="dname" column="dname" jdbcType="VARCHAR"/>
            <result property="loc" column="loc" jdbcType="VARCHAR"/>
        </association>

    </resultMap>

    <select id="listEmpWithDept" resultMap="empWithDeptMap">
        select * from emp e inner JOIN  dept d on  e.deptno = d.deptno
    </select>

结果中员工对应部门信息

dept需要对应部门单个对象

1.3.2 collection 聚合关系对象包含另一个对象集合

                <collection property="emps" ofType="com.javasm.entity.Emp">
                    <id property="empno" column="empno" jdbcType="INTEGER"/>
                    <result property="ename" column="ename" jdbcType="VARCHAR"/>
                    <result property="job" column="job" jdbcType="VARCHAR"/>
                    <result property="mgr" column="mgr" jdbcType="INTEGER"/>
                    <result property="hiredate" column="hiredate" jdbcType="TIMESTAMP"/>
                    <result property="sal" column="sal" jdbcType="NUMERIC"/>
                    <result property="comm" column="comm" jdbcType="NUMERIC"/>
                </collection>

dept中包含emp的集合

    <resultMap type="com.javasm.entity.Dept" id="DeptBaseMap">
        <result property="deptno" column="deptno" jdbcType="INTEGER"/>
        <result property="dname" column="dname" jdbcType="VARCHAR"/>
        <result property="loc" column="loc" jdbcType="VARCHAR"/>
    </resultMap>

    <resultMap type="com.javasm.entity.Dept" id="DeptHaveEmpMap" extends="DeptBaseMap">
        <collection property="emps" ofType="com.javasm.entity.Emp">
            <id property="empno" column="empno" jdbcType="INTEGER"/>
            <result property="ename" column="ename" jdbcType="VARCHAR"/>
            <result property="job" column="job" jdbcType="VARCHAR"/>
            <result property="mgr" column="mgr" jdbcType="INTEGER"/>
            <result property="hiredate" column="hiredate" jdbcType="TIMESTAMP"/>
            <result property="sal" column="sal" jdbcType="NUMERIC"/>
            <result property="comm" column="comm" jdbcType="NUMERIC"/>
        </collection>

    </resultMap>

    <select id="listDeptHaveEmp" resultMap="DeptHaveEmpMap">
        select * from dept d left join emp e  on  e.deptno = d.deptno
        order by d.deptno

    </select>

结果中 dept中包含emp集合

总结:

注意: java中 对象是如何对应的
      对象中的单个对象  association
      对象中的集合对象  collection

2数据库中对应关系与java对应关系的区别

一对一:

一对多:

多对多:

多对多示例:

order.java 订单

package com.javasm.entity;

import lombok.Data;

import java.util.Date;
import java.io.Serializable;
import java.util.List;

/**
 * (Order)实体类
 *
 * @author makejava
 * @since 2025-08-13 14:34:49
 */
@Data
public class Order implements Serializable {
    private static final long serialVersionUID = 941525559314951362L;

    private Integer oid;

    private Date time;

    private String owener;
    //用来体现 订单中 包含商品集合
    private List<Product>  prods;


}

sql映射文件

    <resultMap type="com.javasm.entity.Order" id="OrderAndProductMap" >
        <id property="oid" column="oid" jdbcType="INTEGER"/>
        <result property="time" column="time" jdbcType="TIMESTAMP"/>
        <result property="owener" column="owener" jdbcType="VARCHAR"/>
        <collection property="prods" ofType="com.javasm.entity.Product">
            <id property="pid" column="pid" jdbcType="INTEGER"/>
            <result property="pname" column="pname" jdbcType="VARCHAR"/>
            <result property="price" column="price" jdbcType="NUMERIC"/>
        </collection>

    </resultMap>

    <select id="listOrders" resultMap="OrderAndProductMap">
        select o.*,p.* from order_product op
                                inner join `order` o on  op.oid = o.oid
                                inner join product p on  op.pid = p.pid
    </select>

测试案例

    @Test
    public void listOrdersTest(){
        SqlSession sqlSession = MyBatisHealper.getSqlSession();
        OrderDao mapper = sqlSession.getMapper(OrderDao.class);
        List<Order> orders = mapper.listOrders();
        System.out.println(JSON.toJSONString(orders) );
        MyBatisHealper.backAndSaveSqlSession(sqlSession);

    }

product.java 商品

package com.javasm.entity;

import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 * (Product)实体类
 *
 * @author makejava
 * @since 2025-08-13 14:34:49
 */
@Data
public class Product implements Serializable {
    private static final long serialVersionUID = -93173881148169905L;

    private Integer pid;

    private String pname;

    private Double price;

    //商品在哪些订单中存在
    private List<Order> orders;


}

sql映射文件

    <resultMap type="com.javasm.entity.Product" id="ProductBaseMap">
        <result property="pid" column="pid" jdbcType="INTEGER"/>
        <result property="pname" column="pname" jdbcType="VARCHAR"/>
        <result property="price" column="price" jdbcType="NUMERIC"/>
    </resultMap>


    <resultMap type="com.javasm.entity.Product" id="ProductAndOreerMap" extends="ProductBaseMap">
        <collection property="orders" ofType="com.javasm.entity.Order">
            <id property="oid" column="oid" jdbcType="INTEGER"/>
            <result property="time" column="time" jdbcType="TIMESTAMP"/>
            <result property="owener" column="owener" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <select id="listProds" resultMap="ProductAndOreerMap">
        select p.*,o.* from product p
                                left join order_product op on op.pid = p.pid
                                left join `order` o on op.oid = o.oid


    </select>

测试案例

    @Test
    public void listProductsTest(){
        SqlSession sqlSession = MyBatisHealper.getSqlSession();
        ProductDao mapper = sqlSession.getMapper(ProductDao.class);
        List<Product> products = mapper.listProds();
        System.out.println(JSON.toJSONString(products) );
        MyBatisHealper.backAndSaveSqlSession(sqlSession);

    }

3自连接使用

菜单封装菜单中套菜单

package com.javasm.entity;

import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 * (Menu)实体类
 *
 * @author makejava
 * @since 2025-08-13 14:52:43
 */
@Data
public class Menu implements Serializable {
    private static final long serialVersionUID = 515508920225688950L;

    private Long id;
/**
     * 菜单名称
     */
    private String menuName;
/**
     * 菜单地址
     */
    private String menuSite;
/**
     * 父菜单的id  0代表顶级菜单
     */
    private Long parentMenuId;

    //对象中包含自己对象
    private List<Menu> subMenu;


}

sql映射

 <resultMap type="com.javasm.entity.Menu" id="MenuWithSubMenuMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="menuName" column="menu_name" jdbcType="VARCHAR"/>
        <result property="menuSite" column="menu_site" jdbcType="VARCHAR"/>
        <result property="parentMenuId" column="parent_menu_id" jdbcType="INTEGER"/>
        <collection property="subMenu" ofType="com.javasm.entity.Menu">
            <result property="id" column="s1id" jdbcType="INTEGER"/>
            <result property="menuName" column="s1name" jdbcType="VARCHAR"/>
            <result property="menuSite" column="s1url" jdbcType="VARCHAR"/>
            <collection property="subMenu" ofType="com.javasm.entity.Menu">
                <result property="id" column="s2id" jdbcType="INTEGER"/>
                <result property="menuName" column="s2name" jdbcType="VARCHAR"/>
                <result property="menuSite" column="s2url" jdbcType="VARCHAR"/>

            </collection>
        </collection>

    </resultMap>

select m3.*,
m2.id as s1id ,m2.menu_name as s1name ,m2.menu_site as s1url ,
m1.id as s2id ,m1.menu_name as s2name ,m1.menu_site as s2url 
											
											from menu m1 inner join menu m2 on m1.parent_menu_id = m2.id

											inner join menu m3 on m2.parent_menu_id = m3.id

where m3.parent_menu_id = 0

测试案例

    @Test
    public void listMenussTest(){
        SqlSession sqlSession = MyBatisHealper.getSqlSession();
        MenuDao mapper = sqlSession.getMapper(MenuDao.class);
        List<Menu> menus = mapper.listMenuWithSubMenu();
        System.out.println(JSON.toJSONString(menus) );
        MyBatisHealper.backAndSaveSqlSession(sqlSession);

    }

4动态sql

1查询功能

动态查询条件

根据用户选填条件不同拼出不同条件的sql语句

    <select id="listEmpByCondition" resultMap="EmpBaseMap">
        select * from emp
            <where>
<!--        通过if标签  字符串   判断ename  是否有值  ename != null and ename != ''
                       其他类型 判断deptno 是否有值  deptno != null
   -->
                <if test="ename != null and ename != ''">
                    and ename like CONCAT('%',#{ename},'%')
                </if>
                <if test="job != null and job != ''">
                    and   job = #{job}
                </if>
                <if test="deptno != null">
                    and   deptno = #{deptno}
                </if>
            </where>

    </select>

    @Test
    public void listEmpByConditionTest(){
        SqlSession sqlSession = MyBatisHealper.getSqlSession();
        EmpDao mapper = sqlSession.getMapper(EmpDao.class);
        Emp inputEmp = new Emp();
        inputEmp.setEname("张");
        inputEmp.setJob("文员");
        inputEmp.setDeptno(20);
        List<Emp> emps = mapper.listEmpByCondition(inputEmp);
        System.out.println(JSON.toJSONString(emps) );
        MyBatisHealper.backAndSaveSqlSession(sqlSession);

    }

根据list 遍历生成in语句

    <select id="listEmpByEmpno" resultMap="EmpBaseMap">
        select * from emp
        where  empno in
        <!--         遍历的集合 配合map的key   临时变量       数据分隔符      开始符号   结束符号                   -->
            <foreach collection="listEmpno" item="empno" separator="," open="("  close=")">
                #{empno}
            </foreach>
    </select>

    @Test
    public void listEmpByEmpnoTest(){
        SqlSession sqlSession = MyBatisHealper.getSqlSession();
        EmpDao mapper = sqlSession.getMapper(EmpDao.class);
        ArrayList<Integer> listEmpno = new ArrayList<>();

        listEmpno.add(1001);
        listEmpno.add(1002);

        List<Emp> emps = mapper.listEmpByEmpno(listEmpno);
        System.out.println(JSON.toJSONString(emps) );
        MyBatisHealper.backAndSaveSqlSession(sqlSession);

    }

2删除功能

多条删除

    <delete id="deleteEmpByEmpno">
        delete  from emp
                where empno in
            <foreach collection="listEmpno" item="empno" separator="," open="(" close=")">
                #{empno}
            </foreach>


    </delete>

    @Test
    public void deleteEmpByEmpnoTest(){
        SqlSession sqlSession = MyBatisHealper.getSqlSession();
        EmpDao mapper = sqlSession.getMapper(EmpDao.class);
        ArrayList<Integer> listEmpno = new ArrayList<>();

        listEmpno.add(1001);
        listEmpno.add(1002);

        Integer resNum = mapper.deleteEmpByEmpno(listEmpno);
        System.out.println(JSON.toJSONString(resNum) );
        MyBatisHealper.backSqlSession(sqlSession);

    }

3添加功能

多条插入

    <insert id="insertEmpMutiple">
        insert into emp (ename,sal) VALUES
            <foreach collection="listEmp" item="emp" separator=",">
                (#{emp.ename},#{emp.sal})
            </foreach>
    </insert>

    @Test
    public void insertEmpTest(){
        SqlSession sqlSession = MyBatisHealper.getSqlSession();
        EmpDao mapper = sqlSession.getMapper(EmpDao.class);
        ArrayList<Emp> listEmp = new ArrayList<>();
        listEmp.add(new Emp("小李子",500d));
        listEmp.add(new Emp("小孙子",600d));

        Integer resNum = mapper.insertEmpMutiple(listEmp);
        System.out.println(JSON.toJSONString(resNum) );
        MyBatisHealper.backAndSaveSqlSession(sqlSession);

    }

4动态修改

根据传入参数动态生成修改条件

    <update id="updateEmp">
        update emp
            <set>
                <if test="job != null and job != ''">
                    job = #{job},
                </if>
                <if test="sal != null ">
                    sal = #{sal},
                </if>
                <if test="deptno != null ">
                    deptno = #{deptno},
                </if>

            </set>

<!--        set job = '扫厕所',sal = '5',deptno = '50' -->

        where empno = #{empno}


    </update>

    @Test
    public void updateEmpTest(){
        SqlSession sqlSession = MyBatisHealper.getSqlSession();
        EmpDao mapper = sqlSession.getMapper(EmpDao.class);
        Emp inputEmp = new Emp();
        inputEmp.setEmpno(11119);
        inputEmp.setSal(88888d);
//        inputEmp.setJob("文员");
        inputEmp.setDeptno(20);

        Integer resNum = mapper.updateEmp(inputEmp);
        System.out.println(JSON.toJSONString(resNum) );
        MyBatisHealper.backAndSaveSqlSession(sqlSession);

    }

5公共sql标签

通过sql标签标记公共的字段

在其他标签中可以通过include引用

    <sql id="empFileds">
        empno,ename,job
    </sql>

    <select id="listEmpByCondition" resultMap="EmpBaseMap">
        select
            <include refid="empFileds"></include>

        from emp

    </select>

6mybaits缓存

一级缓存 sqlSession级别缓存可以在一次sqlsession共享数据如果在一次sql会话中使用到相同的数据只会查一次数据库

二级缓存 sqlSessionFactory级别缓存可以跨sqlsession共享数据

掌握 resultMap用法

一对多多对多自连接数据装配

自己换表换数据做测试

2.动态sql

换其他表动态查询动态删除 (添加修改)

posted @ 2025-08-16 15:32 小胡coding 阅读(12) 评论(0) 收藏举报

刷新页面返回顶部

hulixiangcoding

day15_mybatis2

day15_mybatis2

1resultMap 结果集自定义映射

1.1默认对应

1.2自定义映射

1.3高级映射

1.3.1 association 关联关系对象对应另外一个对象(单个)

1.3.2 collection 聚合关系对象包含另一个对象集合

2数据库中对应关系与java对应关系的区别

3自连接使用

4动态sql

1查询功能

动态查询条件

2删除功能

3添加功能

4动态修改

5公共sql标签

6mybaits缓存

公告

hulixiangcoding

day15_mybatis2

day15_mybatis2

1resultMap 结果集自定义映射

1.1默认对应

1.2自定义映射

1.3高级映射

1.3.1 association 关联关系 对象 对应另外一个对象(单个)

1.3.2 collection 聚合关系 对象 包含另一个对象集合

2数据库中对应关系 与java对应关系的区别

3自连接使用

4动态sql

1查询功能

动态查询条件

2删除功能

3添加功能

4动态修改

5公共sql标签

6mybaits缓存

公告

1.3.1 association 关联关系对象对应另外一个对象(单个)

1.3.2 collection 聚合关系对象包含另一个对象集合

2数据库中对应关系与java对应关系的区别