Mybatis-9
一.多对多关联
1.多对多的应用场景
老师和学生，店铺顾客，医生和患者
2.具体的案例
2.1 分析
2.2 数据库表的创建
-- 创建教师表
CREATE TABLE teacher(
tid INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20),
step VARCHAR(20)
);
-- 创建学生表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
telephone VARCHAR(11)
);
-- 创建中间表
CREATE TABLE t_s(
id INT PRIMARY KEY AUTO_INCREMENT,
tid INT,
sid INT,
FOREIGN KEY fk_tid (tid) REFERENCES teacher(tid),
FOREIGN KEY fk_sid (sid) REFERENCES student(sid)
);
-- 测试数据
INSERT INTO teacher(tname,step)VALUES('张涛','一阶段');
INSERT INTO teacher(tname,step)VALUES('潘峰','三阶段');
INSERT INTO teacher(tname,step)VALUES('翟吉喆','三阶段');
INSERT INTO student(sname,telephone)VALUES('季龙','13324567888');
INSERT INTO student(sname,telephone)VALUES('张磊','13324567887');
INSERT INTO student(sname,telephone)VALUES('卢昌昊','13324567868');
INSERT INTO t_s(tid,sid)VALUES(1,1);
INSERT INTO t_s(tid,sid)VALUES(1,2);
INSERT INTO t_s(tid,sid)VALUES(2,3);
INSERT INTO t_s(tid,sid)VALUES(3,1);
INSERT INTO t_s(tid,sid)VALUES(3,2);
INSERT INTO t_s(tid,sid)VALUES(3,3);
COMMIT;
2.2 实体类设计
@Data
public class Teacher {
private int tid;
private String tname;
private String step;
private List<Student> students;
}
@Data
public class Student {
private int sid;
private String sname;
private String telephone;
private List<Teacher> teachers;
}
2.3 创建Mapper接口
public interface TeacherMapper {
public Teacher selectTeacherByTid(@Param("tid") int tid);
}
public interface StudentMapper {
public List<Student> selectStudentsByTid(@Param("tid") int tid);
}
2.4 在resource/mapper目录下创建映射文件
<mapper namespace="com.woniuxy.mapper.TeacherMapper">
<resultMap id="teacherMap" type="teacher">
<id property="tid" column="tid"></id>
<result property="tname" column="tname"></result>
<result property="step" column="step"></result>
<collection property="students" select="com.woniuxy.mapper.StudentMapper.selectStudentsByTid" column="tid">
</collection>
</resultMap>
<select id="selectTeacherByTid" parameterType="int" resultMap="teacherMap">
SELECT * FROM teacher WHERE tid=#{tid}
</select>
</mapper>
<mapper namespace="com.woniuxy.mapper.StudentMapper">
<select id="selectStudentsByTid" parameterType="int" resultType="student">
SELECT * FROM student WHERE sid IN(
SELECT sid FROM t_s WHERE tid=#{tid}
)
</select>
</mapper>
2.5 编写测试类
public class AppTest
{
private Logger logger= Logger.getLogger(this.getClass());
@Test
public void shouldAnswerWithTrue()
{
SqlSession sqlSession= MyBatisUtil.createSession();
TeacherMapper teacherMapper=sqlSession.getMapper(TeacherMapper.class);
Teacher teacher=teacherMapper.selectTeacherByTid(3);
logger.info("教员姓名:"+teacher.getTname());
logger.info("授课阶段;"+teacher.getStep());
List<Student> studentList=teacher.getStudents();
logger.info("共有"+studentList.size());
if(studentList!=null&&!studentList.isEmpty()){
for(Student student:studentList){
logger.info(student.getSname()+" " +
student.getTelephone());
}
}
MyBatisUtil.closeSession(sqlSession);
}
}
二.动态SQL
1.为什么需要动态SQL
2.什么是动态SQL
3.if+where实现多条件查询
<!--
<select id="selectEmpsByCodition" resultType="emp">
SELECT * FROM emp WHERE 1=1
<if test="ename!=null and ename!=''">
AND ename LIKE CONCAT('%',#{ename},'%')
</if>
<if test="sal!=null&&sal!=0.0">
AND sal>#{sal}
</if>
</select>
-->
<select id="selectEmpsByCodition" resultType="emp">
SELECT * FROM emp
<where>
<if test="ename!=null and ename!=''">
AND ename LIKE CONCAT('%',#{ename},'%')
</if>
<if test="sal!=null&&sal!=0.0">
AND sal>#{sal}
</if>
</where>
</select>
4. if+set实现更新操作
<update id="update" parameterType="emp">
UPDATE emp
<set>
<if test="ename!=null and ename!=''">ename=#{ename},</if>
<if test="job!=null and job!=''">job=#{job},</if>
<if test="mgr!=null and mgr!=''">mgr=#{mgr},</if>
<if test="hiredate!=null and hiredate!=''">hiredate=#{hiredate},</if>
<if test="sal!=null and sal!=''">sal=#{sal},</if>
<if test="comm!=null and comm!=''">comm=#{comm},</if>
<if test="dept.deptno!=null and dept.deptno!=0">deptno=#{dept.deptno},</if>
</set>
<where>
empno=#{empno}
</where>
</update>
5.trim的使用
5.1 trim实现多条件查询
<select id="selectEmpsByCoditionWithTrim" resultType="emp">
SELECT * FROM emp
<trim prefix="where" prefixOverrides="and">
<if test="ename!=null and ename!=''">
AND ename LIKE CONCAT('%',#{ename},'%')
</if>
<if test="sal!=null&&sal!=0.0">
AND sal>#{sal}
</if>
</trim>
</select>
5.2 trim实现修改操作
<update id="updateWithTrim" parameterType="emp">
UPDATE emp
<trim prefix="set" suffix="where empno=#{empno}" suffixOverrides=",">
<if test="ename!=null and ename!=''">ename=#{ename},</if>
<if test="job!=null and job!=''">job=#{job},</if>
<if test="mgr!=null and mgr!=''">mgr=#{mgr},</if>
<if test="hiredate!=null and hiredate!=''">hiredate=#{hiredate},</if>
<if test="sal!=null and sal!=''">sal=#{sal},</if>
<if test="comm!=null and comm!=''">comm=#{comm},</if>
<if test="dept.deptno!=null and dept.deptno!=0">deptno=#{dept.deptno},</if>
</trim>
</update>
6.forEach的使用
1）数组
public List<Emp> selectEmpsByEmpnos(Short[] empnos);
<select id="selectEmpsByEmpnos" resultType="emp">
SELECT * FROM emp WHERE empno IN
<foreach collection="array" item="empnos" open="(" separator="," close=")">
#{empnos}
</foreach>
</select>
2）List类型
public List<Emp> selectEmpsByEmpnos1(List empnos);
<select id="selectEmpsByEmpnos1" resultType="emp">
SELECT * FROM emp WHERE empno IN
<foreach collection="list" item="empnos" open="(" separator="," close=")">
#{empnos}
</foreach>
</select>
3）Map类型
public List<Emp> selectEmpsByEmpnos2(Map empnos);
<select id="selectEmpsByEmpnos2" resultType="emp">
SELECT * FROM emp WHERE empno IN
<foreach collection="rkey" item="empnos" open="(" separator="," close=")">
#{empnos}
</foreach>
</select>
@Test
public void testForEach3()
{
SqlSession sqlSession= MyBatisUtil.createSession();
EmpMapper empMapper=sqlSession.getMapper(EmpMapper.class);
List<Short> all=new ArrayList<>();
all.add((short)7788);
all.add((short)7902);
Map<String,List<Short>> empnos=new HashMap<>();
empnos.put("rkey",all);
List<Emp> emps=empMapper.selectEmpsByEmpnos2(empnos);
for(Emp emp:emps){
logger.info(emp);
}
MyBatisUtil.closeSession(sqlSession);
}
浙公网安备 33010602011771号