MyBatis多表操作-----多对多操作

MyBatis多表操作-----多对多操作

多对多操作较之前没有太大的变化，搭配好环境之后进行查询

与之前相比在Mapper层内容有一些增加

由于是多对多的关系，相对于每一个表中的数据都是一对多的关系，那么在增加字段时需要使用@Many

 @Result(property = "teas",column = "sid",javaType = List.class,
                    many = @Many(select = "com.ryh.test.mapper.TeaMapper.selectTeasBySid"))

MyBatis自带的功能无法查询List<>中的数据，于是需要在其中增加功能

首先对TeaMapper进行操作

将如上功能模块复制后进行修改，在里面添加SQL语句

"SELECT * ",
            "FROM tea ",
            "WHERE tid in ",
            "(SELECT tid ",
            "FROM teastu ",
            "WHERE sid= #{sid,jdbcType=INTEGER}) "

意为在另一个表中查找tid属性值，其中tid来自于teastu表中，sid=tid的值

在下面将方法名重新命名以免重复

 @Select({
            "SELECT * ",
            "FROM tea ",
            "WHERE tid in ",
            "(SELECT tid ",
            "FROM teastu ",
            "WHERE sid= #{sid,jdbcType=INTEGER}) "

    })
    @Results({
            @Result(column="tid", property="tid", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="tname", property="tname", jdbcType=JdbcType.VARCHAR)
    })
    List<Tea> selectTeasBySid(Integer sid);

类比于TeaMapper，对StuMapper也进行修改

关于多对多如何进行删除操作：
在TeaStuMapper中添加部分删除的操作

在其中添加了只删除tid和只删除sid的功能

@Delete({
            "delete from teastu",
            "where tid = #{tid,jdbcType=INTEGER}"
    })
    int deleteByTid(Integer tid);

    @Delete({
            "delete from teastu",
            "where sid = #{sid,jdbcType=INTEGER}"
    })
    int deleteBySid(Integer sid);

注意：在增加数据的时候，由于多对多关系中有一个中间表，所以也需要新建中间表将数据间的关系用对象传入操作中

public class TeaService {
    private SqlSessionFactory ssf;
    public TeaService() {
        InputStream is=this.getClass().getClassLoader().getResourceAsStream("mybatis.cfg.xml");
        ssf=new SqlSessionFactoryBuilder().build(is);
    }

    @Test
    public void deleteTea(){
        SqlSession session=ssf.openSession();
        try {
            int tid=98;
            session.delete("com.ryh.test.mapper.TeaStuMapper.deleteByTid",tid);
            session.delete("com.ryh.test.mapper.TeaMapper.deleteByPrimaryKey",tid);

            session.commit();
        }catch (Exception e){
            session.rollback();
            e.printStackTrace();
        }
        session.close();
    }
    @Test
    public void add(){
        SqlSession session=ssf.openSession();
        try{
            Tea tea1=new Tea();
            tea1.setTid(98);
            tea1.setTname("鲁迅");

            Tea tea2=new Tea();
            tea2.setTid(99);
            tea2.setTname("胡适");

            Stu stu1=new Stu();
            stu1.setSid(88);
            stu1.setSname("林徽因");

            Stu stu2=new Stu();
            stu2.setSid(89);
            stu2.setSname("徐志摩");

            session.insert("com.ryh.test.mapper.TeaMapper.insert",tea1);
            session.insert("com.ryh.test.mapper.TeaMapper.insert",tea2);
            session.insert("com.ryh.test.mapper.StuMapper.insert",stu1);
            session.insert("com.ryh.test.mapper.StuMapper.insert",stu2);

            TeaStuKey st1=new TeaStuKey();
            st1.setTid(98);
            st1.setSid(88);
            TeaStuKey st2=new TeaStuKey();
            st2.setTid(98);
            st2.setSid(89);
            TeaStuKey st3=new TeaStuKey();
            st3.setTid(99);
            st3.setSid(88);
            TeaStuKey st4=new TeaStuKey();
            st4.setTid(99);
            st4.setSid(89);
            session.insert("com.ryh.test.mapper.TeaStuMapper.insert",st1);
            session.insert("com.ryh.test.mapper.TeaStuMapper.insert",st2);
            session.insert("com.ryh.test.mapper.TeaStuMapper.insert",st3);
            session.insert("com.ryh.test.mapper.TeaStuMapper.insert",st4);
            session.commit();
        }catch (Exception e){
            session.rollback();
            e.printStackTrace();
        }
        session.close();
    }
    @Test
    public void queryTeas(){
        SqlSession session = ssf.openSession();
        TeaExample example = new TeaExample();
        List<Tea> list=session.selectList("com.ryh.test.mapper.TeaMapper.selectByExample",example);
        list.forEach(t-> {
            List<Stu> stus = t.getStus();
            System.out.println(t.getTname());
            stus.forEach(s-> System.out.print(s.getSname()+","));
            System.out.println();
        });
        session.close();
    }
    @Test
    public void queryStus(){
        SqlSession session = ssf.openSession();
        StuExample example = new StuExample();
        List<Stu> list=session.selectList("com.ryh.test.mapper.StuMapper.selectByExample",example);
        list.forEach(s-> {
            List<Tea> teas = s.getTeas();
            System.out.println(s.getSname());
            teas.forEach(t-> System.out.print(t.getTname()+","));
            System.out.println();
        });
        session.close();
    }



}