Mybatis实现多表联合查询
上篇实现利用mybatis实现单表增删改查，今天利用mybatis实现多表联合查询。
1.创建数据库mybatis2，建立student、class、student_class三张表
DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `class_id` int(11) NOT NULL AUTO_INCREMENT, `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`class_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES (1, '一班'); INSERT INTO `class` VALUES (2, '二班'); INSERT INTO `class` VALUES (3, '三班'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, '张三', '男'); INSERT INTO `student` VALUES (2, '李四', '女'); INSERT INTO `student` VALUES (3, '王五', '男'); INSERT INTO `student` VALUES (4, '王麻子', '女'); INSERT INTO `student` VALUES (5, ' 赵六', '男'); -- ---------------------------- -- Table structure for student_class -- ---------------------------- DROP TABLE IF EXISTS `student_class`; CREATE TABLE `student_class` ( `sid` int(11) NOT NULL, `cid` int(11) NOT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of student_class -- ---------------------------- INSERT INTO `student_class` VALUES (1, 2); INSERT INTO `student_class` VALUES (2, 2); INSERT INTO `student_class` VALUES (4, 4); INSERT INTO `student_class` VALUES (3, 3); INSERT INTO `student_class` VALUES (5, 3); SET FOREIGN_KEY_CHECKS = 1;
2.在com.domain包下创建实体类
package com.domain; public class Student { private int id; private String name; private String sex; //学生所在的班级 private Class sClass; //getter/setter/toString【已省略】 }
package com.domain; import java.util.List; public class Class { private int class_id; private String class_name; private List<Student> students; //getter/setter/toString }
package com.domain; /** * @author admin * @version 1.0.0 * @ClassName Student_Class.java * @Description TODO * @createTime 2020年01月15日 16:13:00 */ public class Student_Class { private int sid; private int cid; //getter/setter/toString }
package com.domain; /** * @author admin * @version 1.0.0 * @ClassName StudentClass.java * @Description TODO * @createTime 2020年01月15日 18:10:00 */ public class StudentClass { private int id; private String name; private String sex; private int class_id; private String class_name; //getter/setter/toString }
3.在com.dao下创建接口StudentDao
package com.dao; import com.domain.Class; import com.domain.Student; import com.domain.StudentClass; import java.util.List; /** * @author admin * @version 1.0.0 * @ClassName StudentDao.java * @Description TODO * @createTime 2020年01月15日 16:20:00 */ public interface StudentDao { //获取某学生的学号、姓名以及某学生所在的班级名 一对一关联查询 一个学生对应一个班级 List<Student> findByName(String studentName); List<StudentClass> findByName2(String studentName); //获取指定班级下的所有学生 【班级编号、班级名、学生姓名、性别】 一对多关联查询 一个班级对应多个学生 List<Class> findAllStudent(int cid); }
4.创建主约束文件SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <!-- mybatis的主配置文件 --> <configuration> <!-- 配置环境 --> <environments default="mysql"> <!-- 配置mysql的环境--> <environment id="mysql"> <!-- 配置事务的类型--> <transactionManager type="JDBC"></transactionManager> <!-- 配置数据源（连接池） --> <dataSource type="POOLED"> <!-- 配置连接数据库的4个基本信息 --> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis2?useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <!-- 配置映射文件的位置 --> <mappers> <package name="com.dao"></package> </mappers> </configuration>
5.在com.dao下创建子约束文件studentDao.xml
<?xml version="1.0" encoding="UTF-8"?> <!--mybaits头约束 --> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!--配置约束--> <mapper namespace="com.dao.StudentDao"> <!--1.获取学生编号、姓名、对应的班级名称--> <!-- 可以显示指出列名，如果用"*"代替，则mybatis会自动匹配resultMap中提供的列名 --> <select id="findByName" parameterType="String" resultMap="studentClassMap"> select a.id,a.name,b.class_name from student a,class b, student_class c where a.id=c.sid and b.class_id = c.cid and a.name=#{name} </select> <!--resultMap中的type表示返回什么类型的对象--> <resultMap id="studentClassMap" type="com.domain.Student"> <!--主键字段--> <!--property表示com.domain.Student的字段,coloum为表中的字段，进行配置映射--> <id property="id" column="id"/> <!--非主键字段--> <result property="name" column="name"/> <!--association字面意思关联，这里只专门做一对一关联； property表示是com.domain.Student的属性名称 javaType表示该属性是什么类型对象--> <association property="sClass" javaType="com.domain.Class"> <!-- property 表示com.domain.Class中的属性； column 表示表中的列名 --> <result property="class_name" column="class_name"/> </association> </resultMap> <!--第二种方式，新建一个类，类似于视图--> <select id="findByName2" parameterType="String" resultType="com.domain.StudentClass"> select * from student a,class b, student_class c where a.id=c.sid and b.class_id = c.cid and a.name=#{name} </select> <select id="findAllStudent" parameterType="int" resultMap="getClassStudent"> select * from class a left join student_class b on a.class_id=b.cid left join student c on b.sid = c.id where a.class_id = #{class_id} </select> <resultMap id="getClassStudent" type="com.domain.Class"> <id property="class_id" column="class_id"/> <result property="class_name" column="class_name"/> <!-- property表示集合类型属性名称，ofType表示集合中的对象是什么类型 --> <collection property="students" ofType="com.domain.Student"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex"/> </collection> </resultMap> </mapper>
6.编写测试类test
package com.dao; import com.domain.Class; import com.domain.Student; import com.domain.StudentClass; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.InputStream; import java.util.List; /** * @author admin * @version 1.0.0 * @ClassName test2.java * @Description TODO * @createTime 2020年01月11日 23:22:00 */ public class test2 { private InputStream in; private SqlSession sqlSession; private StudentDao studentDao; @Before//用于在测试方法执行之前执行 public void init() throws Exception { //1.读取配置文件，生成字节输入流 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.获取SqlSessionFactory工厂对象 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //3.获取SqlSession对象 sqlSession = factory.openSession(); //4.获取dao的代理对象 studentDao = sqlSession.getMapper(StudentDao.class); } @After//用于在测试方法执行之后执行 public void destroy() throws Exception { //提交事务 sqlSession.commit(); //6.释放资源 sqlSession.close(); in.close(); } @Test public void findByName(){ List<Student> lists = studentDao.findByName("张三"); for(Student s:lists){ System.out.println(s.getId()+" "+s.getName()+" "+s.getsClass().getClass_name()); } } @Test public void findByName2(){ List<StudentClass> lists = studentDao.findByName2("张三"); for(StudentClass s:lists){ System.out.println(s.toString()); } } @Test public void findAllStudent(){ List<Class> lists = studentDao.findAllStudent(2); for(Class c:lists){ System.out.println(c.toString()); } } }
7.小结
以上实现了一对一查询与一对多查询,有配置resultMap的方式也可以通过新建一个实体类的方式
mybatis讲究一一对应映射进去，得按照人家规则来，熟练程度不如自己封装一个操作JDBC工具类来的快。
