mybatis入门-3 基于注解的curd 和注解一对一一对多查询
环境sql
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程ID', `title` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程标题', `subtitle` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '课程副标题', `status` enum('draft','published','closed') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'draft' COMMENT '课程状态', `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'normal' COMMENT '课程类型', `maxStudentNum` int(11) NOT NULL DEFAULT 0 COMMENT '直播课程最大学员数上线', `showStudentNumType` enum('opened','closed') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'opened' COMMENT '学员数显示模式', `serializeMode` enum('none','serialize','finished') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'none' COMMENT '连载模式', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 749 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (9, 'test', '', 'published', 'normal', 0, 'opened', 'none'); INSERT INTO `course` VALUES (11, '人员管理培训', '', 'published', 'normal', 0, 'opened', 'none'); INSERT INTO `course` VALUES (14, '行政培训', '', 'published', 'normal', 0, 'opened', 'none'); SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for course_lesson -- ---------------------------- DROP TABLE IF EXISTS `course_lesson`; CREATE TABLE `course_lesson` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课时ID', `courseId` int(10) UNSIGNED NOT NULL COMMENT '课时所属课程ID', `chapterId` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '课时所属章节ID', `number` int(10) UNSIGNED NOT NULL COMMENT '课时编号', `seq` int(10) UNSIGNED NOT NULL COMMENT '课时在课程中的序号', `status` enum('unpublished','published') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'published' COMMENT '课时状态', `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课时标题', `summary` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '课时摘要', `tags` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '课时标签', `type` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'text' COMMENT '课时类型', `content` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '课时正文', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9433 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course_lesson -- ---------------------------- INSERT INTO `course_lesson` VALUES (24, 9, 23, 1, 3, 'published', '视频一', '', NULL, 'video', ''); INSERT INTO `course_lesson` VALUES (161, 14, 109, 1, 2, 'published', '五险一金', '', NULL, 'video', ''); INSERT INTO `course_lesson` VALUES (317, 9, 26, 4, 9, 'published', 'testpdf', '', NULL, 'document', ''); INSERT INTO `course_lesson` VALUES (330, 9, 26, 5, 10, 'published', 'testcname', NULL, NULL, 'testpaper', NULL); INSERT INTO `course_lesson` VALUES (359, 9, 26, 6, 11, 'published', '测试漏题', NULL, NULL, 'testpaper', NULL); INSERT INTO `course_lesson` VALUES (367, 9, 26, 7, 12, 'published', 'test1', NULL, NULL, 'testpaper', NULL); INSERT INTO `course_lesson` VALUES (369, 9, 26, 8, 13, 'published', 'test3', NULL, NULL, 'testpaper', NULL); INSERT INTO `course_lesson` VALUES (566, 9, 23, 2, 4, 'published', 'aaaaaa', '', NULL, 'video', '');INSERT INTO `course_lesson` VALUES (903, 14, 426, 7, 10, 'published', '邮箱设置', '如何设置公司邮箱', NULL, 'video', ''); INSERT INTO `course_lesson` VALUES (918, 14, 432, 20, 25, 'published', 'CRC学习资料', 'CRC学习资料、行政学习资料讲解', NULL, 'video', ''); INSERT INTO `course_lesson` VALUES (921, 14, 432, 24, 29, 'published', '工作日志', '', NULL, 'video', '');; INSERT INTO `course_lesson` VALUES (3237, 14, 426, 8, 11, 'published', '钉钉', '', NULL, 'video', ''); INSERT INTO `course_lesson` VALUES (3357, 11, 279, 26, 34, 'published', 'How To Use KPI', '1. Purpose of KPI\r\n2. KPI Execution\r\n3. Case Study', NULL, 'video', ''); INSERT INTO `course_lesson` VALUES (3919, 11, 1347, 42, 58, 'published', '成长之路---“PM和LM合作篇”', '', NULL, 'video', ''); INSERT INTO `course_lesson` VALUES (3995, 11, 1071, 47, 65, 'published', '8月人员管理会议', 'People management meeting of Aug.', NULL, 'video', '');INSERT INTO `course_lesson` VALUES (5376, 11, 231, 4, 6, 'published', '人员管理项目安排技巧', '', NULL, 'video', ''); INSERT INTO `course_lesson` VALUES (5419, 14, 429, 19, 23, 'published', '第五版员工手册修改内容详细讲解', '', NULL, 'video', '');SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE IF EXISTS `course_chapter`; CREATE TABLE `course_chapter` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程章节ID', `courseId` int(10) UNSIGNED NOT NULL COMMENT '章节所属课程ID', `type` enum('chapter','unit') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'chapter' COMMENT '章节类型：chapter为章节，unit为单元。', `parentId` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'parentId大于０时为单元', `number` int(10) UNSIGNED NOT NULL COMMENT '章节编号', `seq` int(10) UNSIGNED NOT NULL COMMENT '章节序号', `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '章节名称', `createdTime` int(10) UNSIGNED NOT NULL COMMENT '章节创建时间', `copyId` int(10) NOT NULL DEFAULT 0 COMMENT '复制章节的id', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1894 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course_chapter -- ---------------------------- INSERT INTO `course_chapter` VALUES (22, 9, 'chapter', 0, 1, 1, '视频', 1490679375, 0); INSERT INTO `course_chapter` VALUES (23, 9, 'unit', 22, 1, 2, '第一课', 1490679388, 0); INSERT INTO `course_chapter` VALUES (24, 9, 'unit', 22, 2, 5, '第二课', 1490679398, 0); INSERT INTO `course_chapter` VALUES (25, 9, 'unit', 22, 3, 7, '第三课', 1490679405, 0); INSERT INTO `course_chapter` VALUES (26, 9, 'chapter', 0, 2, 8, '考试', 1490679412, 0); INSERT INTO `course_chapter` VALUES (109, 14, 'chapter', 0, 1, 1, '五险一金培训', 1491905133, 0); INSERT INTO `course_chapter` VALUES (228, 11, 'chapter', 0, 1, 1, '各人员管理岗位的工作职责和考核标准', 1492401052, 0); INSERT INTO `course_chapter` VALUES (231, 11, 'chapter', 0, 2, 3, '人员管理工作流程指导', 1492401110, 0); INSERT INTO `course_chapter` VALUES (261, 11, 'chapter', 0, 6, 27, '沟通相关知识&案例分享', 1492401289, 0); INSERT INTO `course_chapter` VALUES (273, 11, 'chapter', 0, 7, 32, '员工情绪的管理和疏导', 1492401337, 0); INSERT INTO `course_chapter` VALUES (276, 11, 'chapter', 0, 5, 18, '团队管理', 1492401346, 0); INSERT INTO `course_chapter` VALUES (279, 11, 'chapter', 0, 8, 33, 'KPI的沟通和评分', 1492401354, 0); INSERT INTO `course_chapter` VALUES (282, 11, 'chapter', 0, 9, 36, '离职原因分析和离职人员管理', 1492401362, 0); INSERT INTO `course_chapter` VALUES (405, 11, 'chapter', 0, 17, 60, 'co-v相关培训', 1495607913, 0); INSERT INTO `course_chapter` VALUES (423, 14, 'chapter', 0, 2, 3, 'SMO新员工入职流程', 1497426318, 0); INSERT INTO `course_chapter` VALUES (426, 14, 'unit', 423, 1, 4, '入职第一天准备事宜', 1497426412, 0); INSERT INTO `course_chapter` VALUES (429, 14, 'unit', 423, 2, 13, '入职第一周学习事宜', 1497426433, 0); INSERT INTO `course_chapter` VALUES (432, 14, 'unit', 423, 3, 24, '试用期学习事宜', 1497426454, 0); INSERT INTO `course_chapter` VALUES (594, 11, 'chapter', 0, 10, 40, '投诉处理流程培训', 1505384579, 0); INSERT INTO `course_chapter` VALUES (693, 14, 'chapter', 0, 3, 35, '关于考勤打卡、timesheet、工作量审核表的培训', 1514371064, 0); INSERT INTO `course_chapter` VALUES (696, 14, 'unit', 693, 1, 36, '关于考勤打卡、timesheet、工作量审核表的培训', 1514371256, 0); INSERT INTO `course_chapter` VALUES (916, 11, 'chapter', 0, 11, 42, 'CAPA&Issue Escalation', 1529054797, 0); INSERT INTO `course_chapter` VALUES (923, 11, 'chapter', 0, 12, 44, 'Confidential Policy&Anti-bribery Policy', 1529546561, 0); INSERT INTO `course_chapter` VALUES (990, 11, 'chapter', 0, 13, 46, 'PPT和Excel技巧培训', 1530770535, 0); INSERT INTO `course_chapter` VALUES (1030, 11, 'chapter', 0, 14, 50, 'Business skills', 1533000144, 0); INSERT INTO `course_chapter` VALUES (1071, 11, 'chapter', 0, 18, 62, '2018年人员管理会议', 1535694767, 0); INSERT INTO `course_chapter` VALUES (1344, 11, 'chapter', 0, 15, 54, '小项目相关培训', 1548755253, 0); INSERT INTO `course_chapter` VALUES (1347, 11, 'chapter', 0, 16, 57, '其它', 1548852220, 0); INSERT INTO `course_chapter` VALUES (1355, 11, 'chapter', 0, 3, 10, '角色定位', 1548855441, 0); INSERT INTO `course_chapter` VALUES (1358, 11, 'chapter', 0, 4, 15, '时间管理', 1548856637, 0); INSERT INTO `course_chapter` VALUES (1384, 11, 'chapter', 0, 19, 69, '2019年人员管理会议', 1551346870, 0); INSERT INTO `course_chapter` VALUES (1426, 11, 'chapter', 0, 20, 79, '2019年人员管理培训', 1553075668, 0); INSERT INTO `course_chapter` VALUES (1636, 11, 'chapter', 0, 21, 99, '考试专区', 1560145776, 0); INSERT INTO `course_chapter` VALUES (1660, 11, 'unit', 1636, 1, 100, '考试1', 1560415978, 0); INSERT INTO `course_chapter` VALUES (1663, 11, 'unit', 1636, 2, 103, '考试2', 1560416011, 0); INSERT INTO `course_chapter` VALUES (1712, 11, 'chapter', 0, 22, 106, '述职分享', 1562937734, 0); SET FOREIGN_KEY_CHECKS = 1;
实体类：
@Data @NoArgsConstructor public class CourseLesson implements Serializable { private Integer id; private Integer courseId; private Integer chapterId; private Integer number; private Integer seq; private String status; private String title; private String type; }
/** * 课程章节 */ @Data public class CourseChapter { private Integer id; private Integer courseId; private String type; private Integer parentId; private Integer number; private Integer seq; private String title; private Integer createdTime; private Integer copyId; private Course course; }
@Data @NoArgsConstructor public class Course implements Serializable { private Integer id; private String title; private String subtitle; private String status; private String type; private Integer maxstudentnum; private String showstudentnumtype; private String serializemode; private List<CourseLesson> courseLessons; }
dao层：
public interface CourseMapper { @Select("select * from course") List<Course> selectCourse(); @Select("select * from course where id = #{id}") @Results({ @Result(property = "id",column = "id",id = true), @Result(property = "courseLessons",column = "id",many = @Many(select = "cm.mbs.annotation.CourseLessonMapper.selectByCourseId")) }) List<Course> selectCourseById(String id); @Select({ "<script>", "select * from course", "<where>", "<if test='title != null'> title = #{title}</if>", "</where>", "</script>" }) List<Course> selectByTitle(@Param("title") String title); }
public interface CourseLessonMapper { @Select("select * from course_lesson") List<CourseLesson> selectAll(); @Select("select * from course_lesson where courseId = #{courseId}") List<CourseLesson> selectByCourseId(int courseId); }
public interface CourseChapterMapper { @Select("select * from course_chapter") List<CourseChapter> selectAll(); @Select("select * from course_chapter where id = #{id}") @Results({ @Result(property = "id" ,column = "id"), @Result(property = "courses",column = "courseId", one = @One(select = "cm.mbs.annotation.CourseMapper.selectCourseById")) }) List<CourseChapter> selectById(int id); }
测试：
public class CourseChapterMapperTest { private static SqlSessionFactory sqlSessionFactory; @BeforeClass public static void init() { InputStream is = null; try { String resource = "mybatis-config.xml"; is = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); } catch (Exception e) { e.printStackTrace(); } finally { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } } @Test public void testSelectAll(){ SqlSession sqlSession = sqlSessionFactory.openSession(); CourseChapterMapper mapper = sqlSession.getMapper(CourseChapterMapper.class); List<CourseChapter> courseChapters = mapper.selectAll(); System.out.println(courseChapters.size()); } @Test public void testSelectById(){ SqlSession sqlSession = sqlSessionFactory.openSession(); CourseChapterMapper mapper = sqlSession.getMapper(CourseChapterMapper.class); List<CourseChapter> courseChapters = mapper.selectById(22); System.out.println(courseChapters.size()); } }
public class CourseLessonMapperTest { private static SqlSessionFactory sqlSessionFactory; @BeforeClass public static void init() { InputStream is = null; try { String resource = "mybatis-config.xml"; is = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); } catch (Exception e) { e.printStackTrace(); } finally { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } } @Test public void testSelectAll(){ SqlSession sqlSession = sqlSessionFactory.openSession(); CourseLessonMapper mapper = sqlSession.getMapper(CourseLessonMapper.class); List<CourseLesson> courses = mapper.selectAll(); System.out.println(courses.size()); } }
public class CourseMapperTest { private static SqlSessionFactory sqlSessionFactory; @BeforeClass public static void init() { InputStream is = null; try { String resource = "mybatis-config.xml"; is = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); } catch (Exception e) { e.printStackTrace(); } finally { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } } @Test public void testSelectAll(){ SqlSession sqlSession = sqlSessionFactory.openSession(); CourseMapper mapper = sqlSession.getMapper(CourseMapper.class); List<Course> courses = mapper.selectCourse(); System.out.println(courses.size()); } @Test public void testSelectByTitle(){ SqlSession sqlSession = sqlSessionFactory.openSession(); CourseMapper mapper = sqlSession.getMapper(CourseMapper.class); List<Course> courses = mapper.selectByTitle("行政培训"); System.out.println(courses.size()); } @Test public void testSelectCourseById(){ SqlSession sqlSession = sqlSessionFactory.openSession(); CourseMapper mapper = sqlSession.getMapper(CourseMapper.class); List<Course> courses = mapper.selectCourseById("11"); System.out.println(courses); } }
总结：注解的级联查询其实和xml很像，主要使用的注解就是@Results这个注解，和@Result这个注解。
如果是一对一的话就使用@One这个注解例如：
@Select("select * from course_chapter where id = #{id}")
@Results({
@Result(property = "id" ,column = "id"),
@Result(property = "courses",column = "courseId", one = @One(select = "cm.mbs.annotation.CourseMapper.selectCourseById"))
})
List<CourseChapter> selectById(int id);
一对多的话就使用@Many这个注解例如：
@Select("select * from course where id = #{id}")
@Results({
@Result(property = "id",column = "id",id = true),
@Result(property = "courseLessons",column = "id",many = @Many(select = "cm.mbs.annotation.CourseLessonMapper.selectByCourseId"))
})
List<Course> selectCourseById(String id);
如何使用注解的时候需要使用动态的sql的话需要借住script这个标签：例如：
@Select({ "<script>", "select * from course", "<where>", "<if test='title != null'> title = #{title}</if>", "</where>", "</script>" }) List<Course> selectByTitle(@Param("title") String title);
