框架专题 Mybatis进阶及使用总结 + 案例

多表联查 resultMap复杂关系

一对一

模型：

一个人有一个身份证

create table person(
  pid int primary key  auto_increment,
  pname varchar(20),
  address varchar(20)
);
create table card(
  cid int primary key auto_increment,
  cnum varchar(20),
  fk_pid int
);
insert person( pname, address) values ('张三','中州'),('李四','商州'),('王五','下州')

insert card(cnum, fk_pid) values ('110',1),('120',2),('119',3);

框架专题 Mybatis进阶及使用总结 + 案例

personMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ujiuye.dao.PersonDao">
    <select id="getAll" resultMap="newPerson">
             /* 第一种，第二种sql*/
            /*select p.*,c.* from person p,card  c where p.pid=c.fk_pid*/
            /*第三种sql*/
            select * from person
    </select>

    <resultMap id="newPerson" type="person">
        <id column="pid" property="pid"/>
        <result property="pname" column="pname"/>
        <result column="address" property="address"/>
        <!--第一种一对一-->
       <!-- <association property="card" javaType="card">
            <id property="cid" column="cid"/>
            <result column="cnum" property="cnum"/>
        </association>-->
        <!--第二种一对一，引用别人的resultMap-->
       <!-- <association property="card" javaType="card"  resultMap="com.ujiuye.dao.CardDao.newCard">
        </association>-->
        <!--第三种一对一 调用别人的sql  ,传person的pid-->
        <association property="card" column="pid"
                     javaType="card" select="com.ujiuye.dao.CardDao.getByFK">
        </association>
    </resultMap>
</mapper>

cardMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ujiuye.dao.CardDao">
    <select id="getByFK" parameterType="int" resultType="card">
      select * from card where  fk_pid=#{0}
    </select>
    
    <resultMap id="newCard" type="card">
        <id property="cid" column="cid"/>
        <result property="cnum" column="cnum"/>
    </resultMap>
</mapper>

一对多

模型：

一个学生有多本书

Student

Book

知识点：

Collection：集合

property实体类中集合属性名

column传给新sql的值

ofType：集合的类型

resultMap：结果集

Select：调用的sql语句

表：

create  table student(
  sid int primary key  auto_increment,
  sname  varchar(10)
);

create table  book(
bid int primary key auto_increment,
bname varchar(10),
fk_sid int
);

insert student(sname)values ('张三'),('李四'),('王五');
insert  book(bname, fk_sid) values ('java',1),('php',1),('c++',2),('python',2)

实体：

public class StudentBean {
    private int sid;
    private  String sname;
private List<BookBean> books;

public class BookBean {
    private int bid;
private String bname;


StudentMapper.xml

Mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ujiuye.dao.StudentDao">
    <select id="getAllStudent" resultMap="stu">
       /* 第一种和第二种的sql*/
         /* select s.*,b.* from student s,book b where  s.sid=b.fk_sid;*/
         select *from student
    </select>

    <resultMap id="stu" type="com.ujiuye.bean.StudentBean">
        <id column="sid" property="sid"/>
        <result column="sname" property="sname"/>
        <!--第一种  一对多集合必须用ofType-->
        <!--<collection property="books" ofType="com.ujiuye.bean.BookBean">
                <id property="bid" column="bid"/>
                <result property="bname" column="bname"/>
        </collection>-->
        <!--第二种一对多-->
       <!-- <collection property="books" ofType="com.ujiuye.bean.BookBean"
                    resultMap="com.ujiuye.dao.BookDao.bookMapper"></collection>-->
        <!--第三种一对多-->
        <collection property="books" column="sid" ofType="com.ujiuye.bean.BookBean"
                    select="com.ujiuye.dao.BookDao.getBooksByFK"></collection>
    </resultMap>
</mapper>

bookMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ujiuye.dao.BookDao">

    <select id="getBooksByFK" resultMap="bookMapper">
        select *from book where  fk_sid=#{0}
    </select>

    <resultMap id="bookMapper" type="com.ujiuye.bean.BookBean">
        <id column="bid" property="bid"/>
        <result column="bname" property="bname"/>
    </resultMap>
</mapper>

测试类：

@Test
    public  void test2() throws IOException {
        InputStream is=Resources.getResourceAsStream("mybatis-conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(is);
        SqlSession session = ssf.openSession();

        StudentDao mapper = session.getMapper(StudentDao.class);
        List<StudentBean> list= mapper.getAllStudent();
        System.out.println(list);
    }

多对多

模型：

Teacher----course 多对多

中间表 teac_cour

多对多

拆分成一对多多个一对一

创建老师表：teacher

课程表：course

中间表： teac_course

实体：

public class TeacherBean {
    private  int tid;
    private String tname;
   private List<Teac_Course> ts;


public class CourseBean {
    private int cid;
    private String cname;


public class Teac_Course {
    private CourseBean course;
    private TeacherBean  teacherBean;
}

Mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ujiuye.dao.TeacherDao">

    <select id="getAll"  resultMap="newTeacher">
      select  *   from teacher t,teac_course ts,course c  where t.tid=ts.tid and ts.cid=c.cid
    </select>

    <resultMap id="newTeacher" type="com.ujiuye.bean.TeacherBean">
        <id column="tid" property="tid"/>
        <result column="tname" property="tname"/>
        <collection property="ts" ofType="com.ujiuye.bean.Teac_Course">
            <association property="course" javaType="com.ujiuye.bean.CourseBean">
                <id column="cid" property="cid"/>
                <result property="cname" column="cname"/>
            </association>
        </collection>
    </resultMap>
</mapper>

测试结果：

@Test
    public  void test3() throws IOException {
        InputStream is=Resources.getResourceAsStream("mybatis-conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(is);
        SqlSession session = ssf.openSession();

        TeacherDao mapper = session.getMapper(TeacherDao.class);
        List<TeacherBean> list= mapper.getAll();
        System.out.println(list);
    }

框架专题 Mybatis进阶及使用总结 + 案例

动态sql

Jdbc拼接sql

Public void getByCondition(String name,String address){

String sql=”select*from person where 1=1”;

If(name!=null){

Sql+=” and name=’”+name”’”;

}

If(address!=null){

Sql+=” and address=’”+address”’”;

}

…

}

在java中判断之后拼接sql语句，他麻烦，不安全，mybtis已经帮你干了这件事情

Where标签

Where标签替代了sql中where 而且自动帮你屏蔽掉紧跟着where后面的and 或者是or

里面还可加条件

<select id="getByCondition"  resultMap="newPerson">
        select * from person
        <where>
            /*多了一个and或者orwhere标签会自动帮你删掉紧跟着where后面and或者or*/
            or pname=#{0}
        </where>
    </select>

If标签

根据入参灵活的判断，加入相应的条件

注意：

入参是独立参数不是对象或者map 参数加别名

如此那是对象获取map #{属性名}

<select id="getByCondition2" resultType="com.ujiuye.bean.PersonBean">
        select *from person
        <where>
            <!--test中可以使用java中的方法-->
            <if test="pname!=null and pname.length()>0">
                and pname like #{pname}
            </if>
            <if test="paddress!=null  and  paddress.length()>0">
                  or address=#{paddress}
            </if>
        </where>
    </select>

choose标签

相当于 java 中 switch 或者 if…ifelse …ifelse …else

<select id="getByCondition3" parameterType="com.ujiuye.bean.PersonBean" resultType="person">
        select *from person
        <where>
            <choose>
                <when test="pid!=0">
                    and pid=#{pid}
                </when>
                <when test="pname!=null">
                    and pname=#{pname}
                </when>
                <otherwise>
                   and address=#{address}
                </otherwise>
            </choose>
        </where>
    </select>

Set标签

Update person set pname=”李四”, address=”山东”, where pid=2;

Set 可以直接去掉最后一个逗号

trim标签

Trim不是去除空格的去除prefix 后面多余的内容

可以代替where或set 用到的机会很少

<select id="getByCondition4" parameterType="person" resultType="person">
        select *from person
        <trim prefix="where" prefixOverrides="and|or">
            <if test="pname!=null">
                and pname=#{pname}
            </if>
            <if test="address!=null">
                and address=#{address}
            </if>
        </trim>
    </select>

Foreach 循环批量操作

可以循环的遍历参数中的集合

批量删除 delete from person where pid in(1,2,3)

Values(),(),()

<insert id="addList"  parameterType="java.util.List">
        insert  person(pname,address) values
        <foreach collection="st" item="p" separator=",">
            (#{p.pname},#{p.address})
        </foreach>
    </insert>
 <delete id="delBatch">
        delete  from person where pid in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
</delete>

Collection：如果入参类型时集合直接写list，或者起参数别名

Item：每次遍历到的对象

Open：以什么开始

Close:以什么结束

Separator：分割器，以什么分割

框架专题 Mybatis进阶及使用总结 + 案例

多表联查 resultMap复杂关系

动态sql

Where标签

If标签

choose标签

Set标签

trim标签

Foreach 循环批量操作

继续阅读

mvc原理和mvc模式的优缺点

AS3 类库

VS2010/MFC编程入门之二（利用MFC向导生成单文档应用程序框架）

VS2010/MFC编程入门之四（MFC应用程序框架分析）

推技术聊天室的实现(下)

领域建模实现思考

myeclipse 搭建mybatis框架时报错（1）Communications link failure 通讯链接故障

项目管理那些事儿

pmbok学习笔记（1）

wecenter二次开发系列（三）——多个wc框架同域网站共享cookie

下载APP显示服务器检索信息出错 ”RPC:S-7:AEC-0“等

SSM框架（二）------------表现层的SpringMVC

阿里巴巴分布式服务框架 Dubbo 团队成员梁飞专访

MyBatis学习笔记Mybatis学习笔记（maven、Idea版）

用mybatis的generator插件在项目中自动生成dao及entity

从mybatis到mybatis plus(mp)