精华内容
下载资源
问答
  • mybatis一对多关联查询

    2021-05-08 08:40:03
  • Mybatis一对多关联查询

    千次阅读 2016-09-18 11:04:13
    Mybatis一对多关联查询一个班级对应多个学生,需求是根据classId查询对应的班级信息,包括学生,老师创建学生表studentCREATE TABLE student( s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(20), class_id INT...

    Mybatis一对多关联查询

    一个班级对应多个学生,需求是根据classId查询对应的班级信息,包括学生,老师

    创建学生表student

    CREATE TABLE student(
    s_id INT PRIMARY KEY AUTO_INCREMENT,
    s_name VARCHAR(20),
    class_id INT
    );
    INSERT INTO student(s_name, class_id) VALUES('xs_A', 1);
    INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);
    INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);
    INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);
    INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);
    INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);
    

    创建实体类
    创建Student

    package mybatis.bean;
    
    public class Student {
    
        private int id;
        private String name;
    
        public Student() {
        }
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Student(int id, String name) {
            super();
            this.id = id;
            this.name = name;
        }
    
        @Override
        public String toString() {
            return "Student [id=" + id + ", name=" + name + "]";
        }
    
    
    
    }
    

    修改Class类,添加新的属性

    private List<Student> students;
    

    一对多关联查询

    使用collection标签,collection做一对多关联查询,ofType属性指定集合中元素对象的类型。

    1.嵌套结果
    使用嵌套结果映射来处理重复的联合结果的子集,想当于

    SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.c_id=s.class_id AND c.c_id=1

    classMapper.xml中的配置如下:

    <select id="getClass" parameterType="int" resultMap="ClassResultMap3">
        select *
        from class c, teacher t,student s where c.teacher_id=t.t_id and
        c.c_id=s.class_id and
        c.c_id=#{id}
    </select>
    
    <resultMap type="mybatis.bean.Class" id="ClassResultMap3">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id" javaType="mybatis.bean.Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
        <!-- ofType 指定students 集合中的对象类型 -->
        <collection property="students" ofType="mybatis.bean.Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
        </collection>
    </resultMap>
    

    查询测试如下:

        mybatis.bean.Class clazz = session.selectOne("mybatis.test6.classMapper.getClass", 1);
        System.out.println(clazz);
    

    控制台输出结果如下:

    2016-09-18 10:54:29,739 [main] DEBUG [mybatis.test6.classMapper.getClass] - ==>  Preparing: select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.c_id=s.class_id and c.c_id=? 
    2016-09-18 10:54:29,826 [main] DEBUG [mybatis.test6.classMapper.getClass] - ==> Parameters: 1(Integer)
    2016-09-18 10:54:29,972 [main] DEBUG [mybatis.test6.classMapper.getClass] - <==      Total: 3
    Class [id=1, name=bj_a, teacher=Teacher [id=1, name=LS1], students=[Student [id=1, name=xs_A], Student [id=2, name=xs_B], Student [id=3, name=xs_C]]]
    

    2.嵌套查询

    通过执行另外一个SQL 映射语句来返回预期的复杂类型
    相当于

    SELECT * FROM class WHERE c_id=1;
    SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id 的值
    SELECT * FROM student WHERE class_id=1 //1 是第一个查询得到的c_id 字段的值

    classMapper.xml中的配置如下:

    <select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
        select * from class where c_id=#{id}
    </select>
    <resultMap type="mybatis.bean.Class" id="ClassResultMap4">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id" javaType="mybatis.bean.Teacher" select="getTeacher2">
        </association>
        <collection property="students" ofType="mybatis.bean.Student" column="c_id" select="getStudent">
        </collection>
    </resultMap>
    <select id="getTeacher2" parameterType="int" resultType="mybatis.bean.Teacher">
        SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
    </select>
    <select id="getStudent" parameterType="int" resultType="mybatis.bean.Student">
        SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
    </select>
    

    测试,控制台输出结果如下:

    2016-09-18 11:00:48,529 [main] DEBUG [mybatis.test6.classMapper.getClass4] - ==>  Preparing: select * from class where c_id=? 
    2016-09-18 11:00:48,611 [main] DEBUG [mybatis.test6.classMapper.getClass4] - ==> Parameters: 1(Integer)
    2016-09-18 11:00:48,671 [main] DEBUG [mybatis.test6.classMapper.getTeacher2] - ====>  Preparing: SELECT t_id id, t_name name FROM teacher WHERE t_id=? 
    2016-09-18 11:00:48,671 [main] DEBUG [mybatis.test6.classMapper.getTeacher2] - ====> Parameters: 1(Integer)
    2016-09-18 11:00:48,689 [main] DEBUG [mybatis.test6.classMapper.getTeacher2] - <====      Total: 1
    2016-09-18 11:00:48,690 [main] DEBUG [mybatis.test6.classMapper.getStudent] - ====>  Preparing: SELECT s_id id, s_name name FROM student WHERE class_id=? 
    2016-09-18 11:00:48,690 [main] DEBUG [mybatis.test6.classMapper.getStudent] - ====> Parameters: 1(Integer)
    2016-09-18 11:00:48,692 [main] DEBUG [mybatis.test6.classMapper.getStudent] - <====      Total: 3
    2016-09-18 11:00:48,692 [main] DEBUG [mybatis.test6.classMapper.getClass4] - <==      Total: 1
    Class [id=1, name=bj_a, teacher=Teacher [id=1, name=LS1], students=[Student [id=1, name=xs_A], Student [id=2, name=xs_B], Student [id=3, name=xs_C]]]
    

    参考

    展开全文
  • 一对一配置差不多 一个用户拥有多种角色 创建数据脚本 # 用户表 create table user( id int(11) primary key auto_increment, name varchar(20), age int(2) )engine=InnoDB default charset=utf8; # 角色表...

    方法一、多表查询(一次性查询)

    跟一对一配置差不多

    一个用户拥有多种角色

    创建数据脚本
    # 用户表
    create table user(
    	id int(11) primary key auto_increment,
    	name varchar(20),
    	age int(2)
    )engine=InnoDB default charset=utf8;
    
    # 角色表
    create table role(
    	id int(11) primary key auto_increment,
    	name varchar(20),
    	user_id int(11)
    )engine=InnoDB default charset=utf8;
    
    创建实体类
    @Data
    public class User implements Serializable {
    
        private Integer id;
    
        private String name;
    
        private Integer age;
    
        private IdCard idCard;
    
        // 一个用户拥有多种角色
        private List<Role> roleList;
    }
    
    @Data
    public class Role implements Serializable {
    
        private Integer id;
    
        private String name;
    }
    
    
    映射文件配置
    <?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.test.quickstart.mapper.UserMapper">
    
        <!-- 开启二级缓存 -->
        <cache />
    
        <!-- 结果集映射 -->
        <resultMap id="UserMap" type="User">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age" />
        </resultMap>
    
        <!-- 创建sql片段,通过include引用-->
        <sql id="BaseColumnList">
            id, name, age
        </sql>
    
        <resultMap id="UserAndRoleMap" type="User" extends="UserMap">
            <!--
                 collection:关联映射
                 property: 映射的属性值
                 javaType: 映射的类型
                 ofType: 映射实体bean类型,这里使用类型别名
                 columnPrefix: 指定关联表列前缀。好处:避免列同名出现数据出错
              -->
            <collection property="roleList" javaType="java.util.ArrayList" ofType="Role" columnPrefix="role_">
                <id column="id" property="id"/>
                <result column="name" property="name"/>
            </collection>
        </resultMap>
    
        <select id="selectOne2Many" resultMap="UserAndRoleMap">
             select
              u.id,
              u.name,
              u.age,
              r.id as role_id,
              r.name as role_name
              from user as u,role as r
              where u.id=r.user_id
        </select>
    </mapper>
    
    配置文件参考 mybatis-config.xml

    方法二、使用关联的XXMapper对象方法嵌套查询(多次查询)

    UserMapper.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.test.quickstart.mapper.UserMapper">
    
        <!-- 开启二级缓存 -->
        <cache />
    
        <!-- 结果集映射 -->
        <resultMap id="UserMap" type="User">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age" />
        </resultMap>
    
        <!-- 创建sql片段,通过include引用-->
        <sql id="BaseColumnList">
            id, name, age
        </sql>
    
        <resultMap id="UserAndRoleMap" type="User" extends="UserMap">
            <!--
                 collection:关联映射
                 property: 映射的属性值
                 javaType: 映射的类型
                 ofType: 映射实体bean类型,这里使用类型别名
                 column: 指定主查询中列的结果作为嵌套查询的参数
                 多参数时在column中用"{}"将参数包起来,=左侧的为mapper中定义的@Param,=右侧为主表列名
                 fetchType: lazy 延迟加载,用到该对象时才发送sql查询
                 默认值是eager,将发出sql,查找关联数据
              -->
            <collection property="roleList" fetchType="lazy" javaType="java.util.ArrayList" ofType="Role"
                column="{userId=id}" select="com.test.quickstart.mapper.RoleMapper.selectByUserId"/>
        </resultMap>
    
        <select id="selectOne2Many2" resultMap="UserAndRoleMap">
             select * from user;
        </select>
    </mapper>
    
    RoleMapper.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.test.quickstart.mapper.RoleMapper">
    
        <!-- 结果集映射 -->
        <resultMap id="RoleMap" type="Role">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
        </resultMap>
    
        <select id="selectByUserId" resultMap="RoleMap">
             select * from role where user_id = #{userId}
        </select>
    </mapper>
    
    UserMapper.java
    @Mapper
    public interface UserMapper {
        User selectOne2Many(@Param("id") Integer id);
    
        User selectOne2Many2(@Param("id") Integer id);
    }
    
    RoleMapper.java
    @Mapper
    public interface RoleMapper {
    
        List<Role> selectByUserId(@Param("userId") Integer userId);
    }
    
    测试
    public class Demo01Test {
    
        private SqlSessionFactory sessionFactory;
    
        @Before
        public void before() throws IOException {
            // 读取配置文件
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            sessionFactory = new SqlSessionFactoryBuilder().build(is);
        }
    
        /**
         * 测试一对多
         */
        @Test
        public void testOne2Many1() {
            SqlSession session = sessionFactory.openSession();
            UserMapper userMapper = session.getMapper(UserMapper.class);
            User user = userMapper.selectOne2Many(2);
            System.out.println(user);
        }
        
        /**
         * 测试一对多
         */
        @Test
        public void testOne2Many2() {
            SqlSession session = sessionFactory.openSession();
            UserMapper userMapper = session.getMapper(UserMapper.class);
            User user = userMapper.selectOne2Many2(2);
            // 使用关联对象时才发出sql查询
            System.out.println(user.getRoleList());
        }
    }
    
    展开全文
  • 案例:个老师对应个学生 第种配置方式 1.在teacher类里面增加student属性 并生成get set方法 private List&lt;Student&gt; students = new ArrayList&lt;Student&gt;(); 2....

    案例:一个老师对应多个学生

    第一种配置方式

    1.在teacher类里面增加student属性

    并生成get set方法

    private List<Student> students = new ArrayList<Student>();

    2.StudentMapper

    接口增加

    List<Student> selectStudentsByTid(Integer tid);

    xml增加

    <select id="selectStudentsByTid" resultType="Student">
    		select * from student where tid = #{tid}
    	</select>

    3. TeacherMapper

    接口类增加

    Teacher selectTeacherById(Integer tid);

    xml文件增加

    注:property:多方对应的集合名
           ofType:集合里面元素的类型
           select:引用关联查询sql对应的ID
            column:引用的关联查询sql语句需要的参数 

    <resultMap type="Teacher" id="teaMap">
    		<id property="tid" column="tid"></id>
    		<result property="tname" column="tname"/>
    		<collection property="students" ofType="Student" 
    		select="com.yc.dao.StudentMapper.selectStudentsByTid" column="tid"></collection>
    	</resultMap>
    	<select id="selectTeacherById" resultMap="teaMap">
    		select * from teacher where tid = #{tid}
    	</select>

    4.测试代码

    @Test
    	public void test2() {
    		Teacher teacher = teacherMapper.selectTeacherById(1001);
    		System.out.println(teacher);
    	}

     

     第二种配置方式

    1.修改Teacher类

    2.修改TeacherMapper.xml

    <resultMap type="Teacher" id="teaMap">
    		<id property="tid" column="tid"/>
    		<result property="tname" column="tname"/>
    		<collection property="students" ofType="Student">
    			<id property="sid" column="sid"/>
    			<result property="sname" column="sname"/>
    			<result property="age" column="age"/>
    			<result property="email" column="email"/>
    		</collection>
    	</resultMap>
    	<select id="selectTeacherById" resultMap="teaMap">
    		select * from teacher t,student s where t.tid = #{tid} and t.tid = s.tid
    	</select>

    3.测试代码相同 

    第三种配置方式 自动映射

    ps:1.实体类的属性名要和表字段名保存一致,

               或按照驼峰命名规则(属性名:aColumn,字段名:A_COLUMN),settion属性mapUnderscoreToCamelCase设置成true
            2.关联表的字段名不能有相同的名字
            3.一对多至少要配制一个主键

    1.修改Teacher类

    2.修改全局配置文件

    <setting name="autoMappingBehavior" value="FULL" />
    <setting name="mapUnderscoreToCamelCase" value="true" /> 

    3. 修改TeacherMapper.xml

            <resultMap type="Teacher" id="teaMap">
    		<id property="tid" column="tid"/>
    		<collection property="students" ofType="Student"></collection>
    	</resultMap>
    	<select id="selectTeacherById" resultMap="teaMap">
    		select * from teacher t,student s where t.tid = #{tid} and t.tid = s.tid
    	</select>

     

    展开全文
  • 一对多关联查询指的是在查询一方对象的时候同时把跟他所关联的多方对象也查询出来,这里以篮球队和篮球运动员为例,一个篮球队关联着多个篮球队员。创建数据库表创建球队的表,里面有两个字段:id:主键name:球队...
  • MyBatis一对多关联查询
  • mybatis一对多关联查询两种方式

    万次阅读 2018-12-24 00:01:06
    mybatis一对多关联查询两种方式前提:方式一:方式二: 前提: 现在有两张表,学生表跟教师表,一个教师对应多个学生 教师表: CREATE TABLE `teacher` ( `id` int(11) PRIMARY KEY, `name` varchar(20) , `age` ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,560
精华内容 1,024
关键字:

mybatis一对多关联查询