3.2、练习2
- 查询所有的班级，同时
- 查询班级对应的老师
- 查询班级学生的总人数
- 查询班级包含的学生详情
CourseMapper
@Mapper public interface CourseMapper { public List<Course> selectAll(); }
CourseMapper.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.czxy.mapper.CourseMapper"> <resultMap id="courseResultMap" type="course"> <result property = "cid" column = "c_id"></result> <association property="total" column="c_id" select="com.czxy.mapper.StudentMapper.countByCourseId"></association> <collection property="studentList" column="c_id" select="com.czxy.mapper.StudentMapper.selectAllByCourseId"></collection> </resultMap> <select id="selectAll" resultMap="courseResultMap"> select * from tb_course </select> </mapper>
StudentMapper.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.czxy.mapper.StudentMapper"> <select id="countByCourseId" resultType="long"> select count(1) from tb_student s inner join tb_student_course sc on s.s_id = sc.s_id where sc.c_id = #{courseId} </select> <select id="selectAllByCourseId" resultType="student"> select * from tb_student s inner join tb_student_course sc on s.s_id = sc.s_id where sc.c_id = #{courseId} </select> </mapper>
3.3、练习3
- 查询所有的学生，同时
- 查询学生所属班级
- 查询学生省市县信息
- 查询学生所学课程
StudentMapper
@Mapper public interface StudentMapper { /** * 查询所有 * @return */ List<Student> selectAll(); }
StudentMapper.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.czxy.mapper.StudentMapper"> <resultMap id="studentResultMap" type="student"> <result property="sid" column="s_id"></result> <result property="cid" column="c_id"></result> <result property="provinceId" column="province_id"></result> <result property="cityId" column="city_id"></result> <result property="countyId" column="county_id"></result> <association property="classes" column="c_id" select="com.czxy.mapper.ClassesMapper.selectById"></association> <association property="province" column="province_id" select="com.czxy.mapper.CityMapper.selectById"></association> <association property="city" column="city_id" select="com.czxy.mapper.CityMapper.selectById"></association> <association property="county" column="county_id" select="com.czxy.mapper.CityMapper.selectById"></association> <collection property="courseList" column="s_id" select="com.czxy.mapper.CourseMapper.selectAllByStudentId"></collection> </resultMap> <select id="selectAll" resultMap="studentResultMap"> select * from tb_student </select> </mapper>
ClassesMapper.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.czxy.mapper.ClassesMapper"> <select id="selectById" resultType="classes"> select * from tb_class where cid = #{classesId} </select> </mapper>
CityMapper.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.czxy.mapper.CityMapper"> <select id="selectById" resultType="city"> select * from tb_city where c_id = #{cid} </select> </mapper>
CourseMapper.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.czxy.mapper.CourseMapper"> <select id="selectAllByStudentId" resultType="course"> select c.* from tb_course c inner join tb_student_course sc on c.c_id = sc.c_id where sc.s_id = #{studentId} </select> </mapper>
3.4、练习4
- 查询所有课程，同时
- 查询学生学习该课程总人数
- 查询学生学习该课程详情
CourseMapper
@Mapper public interface CourseMapper { public List<Course> selectAll(); }
CourseMapper.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.czxy.mapper.CourseMapper"> <resultMap id="courseResultMap" type="course"> <result property = "cid" column = "c_id"></result> <association property="total" column="c_id" select="com.czxy.mapper.StudentMapper.countByCourseId"></association> <collection property="studentList" column="c_id" select="com.czxy.mapper.StudentMapper.selectAllByCourseId"></collection> </resultMap> <select id="selectAll" resultMap="courseResultMap"> select * from tb_course </select> </mapper>
StudentMapper.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.czxy.mapper.StudentMapper"> <select id="countByCourseId" resultType="long"> select count(1) from tb_student s inner join tb_student_course sc on s.s_id = sc.s_id where sc.c_id = #{courseId} </select> <select id="selectAllByCourseId" resultType="student"> select * from tb_student s inner join tb_student_course sc on s.s_id = sc.s_id where sc.c_id = #{courseId} </select> </mapper>
3.5、练习5
- 创建或修改表，完成老师与课程关联关系
- 不同的老师可以教授不同的课程
- 不同的课程可以由不同的老师教授
3.6、优化
- 优化：2.1、2.2、2.3、2.4，该
查询所有为指定若干
4、总结
@Param作用：给参数命名 sql语句使用#{}或${}取出
mybatis.mapper-locations:classpath:mapper/*.xml：扫描mapper mapper接口与mapper xml
才能绑定
mybatis.type-aliases-package：扫描自定义POJO 映射不用全类名 可以直接写类名
mybatis.configuration.map-underscore-to-camel-case：开启自动驼峰命名转换
<ResultMap>
ResultMap：自定义结果集映射规则，自定义某个JavaBean的封装规则。
type：返回的类型
id：指定主键列的封装规则（主键也可以使用result来定义）
property：指定对应的javaBean属性
column：指定哪一列
<association>
association：配置一对一关联
property：指定对应的javaBean属性
column：指定哪一列
select：dao层进行查询的方法
<collection>
collection：一对多关联
property：指定对应的javaBean属性
column：指定哪一列
select：dao层进行查询的方法
select、update、delete的parameterType
传递参数的类型
<foreach>
foeach是mybatis的动态标签 一般用来批量操作
collection：传入的集合 【必选】
item：集合中元素迭代时的别名 【必选】
open：代码开始符号 一般时(和close)合用 常用in(),values()时 【可选】
separator：元素之间的分隔符 一般用，【可选】
close：代码关闭符号 ) 【可选】
