连表查询
连表查询分为一对多、多对一和多对多，连表查询的话就只能使用resultMap属性来查询。
子表多对一
要先在子表的bean层添加主表对象并且进行封装
private Grade grade=new Grade(); public Grade getGrade() { return grade; } public void setGrade(Grade grade) { this.grade = grade; }
多对一MapperXML层
resultMap标签的 id属性下面用自己命名，type属性映射子表实体类的地址
resultMap标签的 id标签要和数据库id对应，property是和实体类对应column和数据库对应，
result标签也一样
association标签 property属性对应刚才实体类添加的主表实体类的名称，javaType属性映射父表实体类的地址
<?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.xinxi2.dao.StudentMapper"> <resultMap id="StudentMap" type="com.xinxi2.bean.Student"> <id property="id" column="id"></id> <result property="name" column="name"></result> <result property="bigname" column="bigname"></result> <result property="riqi" column="riqi"></result> <result property="age" column="age"></result> <association property="grade" javaType="com.xinxi2.bean.Grade"> <id property="gradeID" column="GradeID"></id> <result property="gradeName" column="GradeName"></result> </association> </resultMap> <select id="getList" resultMap="StudentMap"> SELECT s.id,name,bigname,age,g.GradeName FROM `student` s,grade g where s.GradeID=g.GradeID </select> </mapper>
多对一测试类
StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class); List<Student> list=studentMapper.getList(); for (Student student1:list){ //getGrade()是父类对象的get方法后面的getGradeName()方法是父类的班级名称的get方法 System.out.println(student1.getName()+student1.getGrade().getGradeName()); }
父表一对多
要先在父表的bean层添加子表列表方法并且进行封装，因为一个班级有多个学生
private List<Student> studentList=new ArrayList<>(); public List<Student> getStudentList() { return studentList; } public void setStudentList(List<Student> studentList) { StudentList = studentList; }
一对多MapperXML层
一对多和多对一的sql语句都不用动，resultMap标签type属性和下面的互换一下
collection标签 property属性对应刚才实体类添加的子表实体类列表的名称，ofType属性映射子表实体类的地址
<?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.xinxi2.dao.GradeMapper"> <resultMap id="GradeMap" type="com.xinxi2.bean.Grade"> <id property="gradeID" column="GradeID"></id> <result property="gradeName" column="GradeName"></result> <collection property="studentList" ofType="com.xinxi2.bean.Student"> <id property="id" column="id"></id> <result property="name" column="name"></result> <result property="bigname" column="bigname"></result> <result property="riqi" column="riqi"></result> <result property="age" column="age"></result> </collection> </resultMap> <select id="getGradeList" resultMap="GradeMap"> SELECT s.id,name,bigname,age,g.GradeName FROM `student` s,grade g where s.GradeID=g.GradeID </select> </mapper>
多对一测试类
GradeMapper gradeMapper=sqlSession.getMapper(GradeMapper.class); List<Grade> list=gradeMapper.getGradeList(); for (Grade grade:list){ System.out.println(grade.getGradeName());//输出班级name for (Student student1:grade.getStudentList()){ System.out.println(student1.getName()+student1.getBigname()); } }
动态Sql
动态Sql更方便业务实现，mybatis提供了许多标签来使用，使代码更加的简便
where标签
<select id="getListbyStudent" resultType="com.xinxi2.bean.Student" parameterType="com.xinxi2.bean.Student"> select * form student <where> <if test="name!=null"> and name=#{name} </if> <if test="bigname!=null"> and bigname=#{bigname} </if> </where> </select>
and一定要加在条件前面，mybatis会自动添加where 和 and，带#{}的name是实体类里面的，不带的是数据库的
set标签
<update id="updateStudent" parameterType="com.xinxi2.bean.Student"> update student <set> <if test="name!=null"> name=#{name}, </if> <if test="bigname!=null"> bigname=#{bigname}, </if> </set> where id=#{id} </update>
set标签多数用于修改，而set标签是在条件后面加英语符号的”,“。
foreach标签
接口：
List<Student> getListbyList(List<String> list);//参数是列表
实现类：
<select id="getListbyNames" resultType="com.xinxi2.bean.TSysUser"> SELECT * FROM `t_sys_user` where realName in <foreach collection="list" open="(" separator="," close=")" item="goods"> #{goods} </foreach> </select>
foreach标签 collection属性对应接口的参数列表，open列表遍历完的开头，separator是列表中格格元素之间的分隔符，open列表遍历完的结尾，item集合中元素迭代时的别名。
trim标签
trim标签说白了就是可以自定义动态sql，前面的where标签和set标签都可以使用trim来自定义。
自定义where
<select id="getListbyStudent01" resultType="com.xinxi2.bean.Student" parameterType="com.xinxi2.bean.Student"> select * form student <trim prefix="where" prefixOverrides="or|and"> <if test="name!=null"> and name=#{name} </if> <if test="bigname!=null"> and bigname=#{bigname} </if> </trim> </select>
自定义update
<update id="updateListbyStudent02" parameterType="com.xinxi2.bean.Student"> update student <trim prefix="set" suffixOverrides="," suffix="where id=#{id}"> <if test="name!=null"> and name=#{name}, </if> <if test="bigname!=null"> and bigname=#{bigname}, </if> </trim> </update>
trim属性
| prefix | 给sql语句拼接的前缀 |
| suffix | 给sql语句拼接的后缀 |
| prefixOverrides | 去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND" |
| suffixOverrides | 去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定 |
