MyBatis_5

最新推荐文章于 2024-01-29 10:08:30 发布

blake2

最新推荐文章于 2024-01-29 10:08:30 发布

阅读量52

点赞数

文章标签：数据库 java mybatis mysql

本文链接：https://blog.csdn.net/blake2/article/details/115456686

版权

1.mybatis的高级查询中一对一查询操作有几种方式，每一种如何操作？

测试一对一查询

需求：查询指定用户信息。

分析：可以将用户的信息保存在两张表中，第一张表保存用户的基本信息，第二张表保存用户的身份证信息，这时用户的基本信息与用户的身份证信息就是一组一对一的信息。

#用户基本信息表
create  table t_user(
user_id int primary key auto_increment,
user_name varchar(20),
user_age int,
user_address varchar(30)
);
#用户身份证信息表
create  table t_card(
card_id int primary key auto_increment,
user_id_fk int not null unique,
card_code varchar(20),
card_year int,
foreign key(user_id_fk) references t_user(user_id)
);
#测试数据
insert into t_user values(null,'zhangsan',23,'西安');
insert into t_card values(null,1,'111111111',20);

创建工程项目--javabean

package com.wangxing.mybatis.bean;

public class UserBean {
    private  int  userid;
    private  String  username;
    private  int userage;
    private  String useraddress;
    //由于当我们在查询用户基本信息的时候需要连同用户身份证信息一起得到，
    //所以我们就需要在保存用户基本信息的java实体类中新增一个成员变量，用来保存用户身份证信息。
    private CardBean cardBean;
    public int getUserid() {
        return userid;
    }

    public void setUserid(int userid) {
        this.userid = userid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public int getUserage() {
        return userage;
    }

    public void setUserage(int userage) {
        this.userage = userage;
    }

    public String getUseraddress() {
        return useraddress;
    }

    public void setUseraddress(String useraddress) {
        this.useraddress = useraddress;
    }

    public CardBean getCardBean() {
        return cardBean;
    }

    public void setCardBean(CardBean cardBean) {
        this.cardBean = cardBean;
    }
}

package com.wangxing.mybatis.bean;

public class CardBean {
    private int cardid;
    private int useridfk;
    private String cardcode;
    private int cardyear;

    public int getCardid() {
        return cardid;
    }

    public void setCardid(int cardid) {
        this.cardid = cardid;
    }

    public int getUseridfk() {
        return useridfk;
    }

    public void setUseridfk(int useridfk) {
        this.useridfk = useridfk;
    }

    public String getCardcode() {
        return cardcode;
    }

    public void setCardcode(String cardcode) {
        this.cardcode = cardcode;
    }

    public int getCardyear() {
        return cardyear;
    }

    public void setCardyear(int cardyear) {
        this.cardyear = cardyear;
    }
}

创建数据访问接口

package com.wangxing.mybatis.mapper;

import com.wangxing.mybatis.bean.UserBean;

public interface UserMapper {
    //根据用户id得到用户信息，包括用户的身份证信息
    UserBean getUserAndCardByUserId(int userid);
}

创建SQL映射文件

<?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.wangxing.mybatis.mapper.UserMapper">
        <!--嵌套resultMap:方式1-->
        <!--association一对一配置 -->
        <!-- property:成员变量名称-->
        <!-- javaType: 成员变量的类型-->
        <!--
        <resultMap id="userMap" type="com.wangxing.mybatis.bean.UserBean">
            <id column="user_id" property="userid"></id>
            <result column="user_name" property="username"></result>
            <result column="user_age" property="userage"></result>
            <result column="user_address" property="useraddress"></result>
            <association property="cardBean" javaType="com.wangxing.mybatis.bean.CardBean">
                <id column="card_id" property="cardid"></id>
                <result column="user_id_fk" property="useridfk"></result>
                <result column="card_code" property="cardcode"></result>
                <result column="card_year" property="cardyear"></result>
            </association>
        </resultMap>
        -->
        <!--嵌套resultMap:方式2-->
        <!--association一对一配置 -->
        <!-- property:成员变量名称-->
        <!-- javaType: 成员变量的类型-->
        <!-- resultMap: 引入其他的resultMap-->
    <resultMap id="userMap" type="com.wangxing.mybatis.bean.UserBean">
        <id column="user_id" property="userid"></id>
        <result column="user_name" property="username"></result>
        <result column="user_age" property="userage"></result>
        <result column="user_address" property="useraddress"></result>
        <association property="cardBean" javaType="com.wangxing.mybatis.bean.CardBean" resultMap="cardMap"></association>
    </resultMap>
    <resultMap id="cardMap" type="com.wangxing.mybatis.bean.CardBean">
        <id column="card_id" property="cardid"></id>
        <result column="user_id_fk" property="useridfk"></result>
        <result column="card_code" property="cardcode"></result>
        <result column="card_year" property="cardyear"></result>
    </resultMap>
    <select id="getUserAndCardByUserId" parameterType="int" resultMap="userMap">
            select user_id,user_name,user_age,user_address,
                    card_id,user_id_fk,card_code,card_year
                    from t_user inner join t_card
                    on user_id=user_id_fk
                    where user_id=#{userid};
        </select>
</mapper>

测试代码

//根据用户id得到用户信息，包括用户的身份证信息
public static void getUserAndCardByUserId(){
    SqlSession sqlSession=null;
    try {
        sqlSession=getSqlSession();
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
        UserBean userBean=userMapper.getUserAndCardByUserId(1);
        sqlSession.commit();
        System.out.println(userBean.getUsername()+"\t"+userBean.getCardBean().getCardcode());
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        sqlSession.close();
    }
}

运行结果

CardBean

package com.wangxing.mybatis.bean;

public class CardBean {
    private int cardid;
    private int useridfk;
    private String cardcode;
    private int cardyear;
    //由于当我们在查询用户身份证信息的时候需要连同用户基本信息一起得到，
    //所以我们就需要在保存用户身份证信息的java实体类中新增一个成员变量，用来保存用户基本信息。
    private UserBean userBean;
    public int getCardid() {
        return cardid;
    }

    public void setCardid(int cardid) {
        this.cardid = cardid;
    }

    public int getUseridfk() {
        return useridfk;
    }

    public void setUseridfk(int useridfk) {
        this.useridfk = useridfk;
    }

    public String getCardcode() {
        return cardcode;
    }

    public void setCardcode(String cardcode) {
        this.cardcode = cardcode;
    }

    public int getCardyear() {
        return cardyear;
    }

    public void setCardyear(int cardyear) {
        this.cardyear = cardyear;
    }

    public UserBean getUserBean() {
        return userBean;
    }

    public void setUserBean(UserBean userBean) {
        this.userBean = userBean;
    }
}

创建数据访问接口

package com.wangxing.mybatis.mapper;

import com.wangxing.mybatis.bean.CardBean;

public interface CardMapper {
    //根据用户的身份证号码得到用户的身份证信息，包含用户基本信息
    CardBean  getCardAndUserByCardCode(String cardcode);
}

创建SQL映射文件

<?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.wangxing.mybatis.mapper.CardMapper">
    <!--嵌套select-->
    <resultMap id="cardMap" type="com.wangxing.mybatis.bean.CardBean">
        <id column="card_id" property="cardid"></id>
        <result column="user_id_fk" property="useridfk"></result>
        <result column="card_code" property="cardcode"></result>
        <result column="card_year" property="cardyear"></result>
        <!-- association一对一查询的配置元素-->
        <!--property:成员变量名称 -->
        <!--javaType:成员变量类型-->
        <!--column:查询用户信息的外键列 -->
        <!--select:嵌套查询 -->
        <association property="userBean"
                     javaType="com.wangxing.mybatis.bean.UserBean"
                     column="user_id_fk"
                     select="getUserByuser_id_fk"></association>
    </resultMap>
    <select id="getCardAndUserByCardCode" parameterType="java.lang.String" resultMap="cardMap">
         select * from t_card where card_code=#{cardcode};
    </select>
    <resultMap id="userMap" type="com.wangxing.mybatis.bean.UserBean">
        <id column="user_id" property="userid"></id>
        <result column="user_name" property="username"></result>
        <result column="user_age" property="userage"></result>
        <result column="user_address" property="useraddress"></result>
    </resultMap>
    <select id="getUserByuser_id_fk" parameterType="int" resultMap="userMap">
        select * from t_user where user_id=#{useridfk};
    </select>

</mapper>

测试代码

//根据用户的身份证号码得到用户的身份证信息，包含用户基本信息
public static void getCardAndUserByCardCode(){
    SqlSession sqlSession=null;
    try {
        sqlSession=getSqlSession();
        CardMapper cardMapper=sqlSession.getMapper(CardMapper.class);
        CardBean cardBean =cardMapper.getCardAndUserByCardCode("111111111");
        sqlSession.commit();
        System.out.println(cardBean.getCardcode()+"\t"+cardBean.getUserBean().getUsername());
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        sqlSession.close();
    }
}

2.mybatis的高级查询中一对多查询操作有几种方式，每一种如何操作？

测试一对多的查询

需求：根据班级编号查询班级信息

分析：我们需要准备2张数据库表，第一张数据库表是保存班级信息的，第二张表是保存这个班级中的学生信息，那么一个班级中有多个学生，这就是典型的一对多关系。

创建数据库表
#创建班级表
create  table t_class(
class_id int primary key auto_increment,
class_code varchar(20),
class_name varchar(20)
);
#创建学生表
create  table t_student(
stu_id int primary key auto_increment,
class_id_fk int not null,
stu_name varchar(20),
stu_age int,
stu_address varchar(20),
foreign key(class_id_fk) references t_class(class_id)
);
#测试数据
insert into t_class values(null,'20190123','javaEE班');
insert into t_student values(null,1,'zhangsan',23,'西安');
insert into t_student values(null,1,'lisi',24,'北京');
insert into t_student values(null,1,'wangwu',25,'上海');

创建javabean

package com.wangxing.mybatis.bean;

import java.util.List;

public class ClassBean {
    private int classid;
    private String classcode;
    private String classname;
    //当我们查询班级信息的时候需要连同这个班级中的所有学生信息一起得到，
    //所以我们需要在保存班级信息的java实体类中新增一个集合类型的成员变量，用来保存这个班级的所有学生信息
    private List<StudentBean> studentBeanList;
    public int getClassid() {
        return classid;
    }
    public void setClassid(int classid) {
        this.classid = classid;
    }
    public String getClasscode() {
        return classcode;
    }
    public void setClasscode(String classcode) {
        this.classcode = classcode;
    }
    public String getClassname() {
        return classname;
    }
    public void setClassname(String classname) {
        this.classname = classname;
    }
    public List<StudentBean> getStudentBeanList() {
        return studentBeanList;
    }
    public void setStudentBeanList(List<StudentBean> studentBeanList) {
        this.studentBeanList = studentBeanList;
    }
}

package com.wangxing.mybatis.bean;

public class StudentBean {
    private  int stuid;
    private  int classidfk;
    private  String stuname;
    private  int stuage;
    private  String stuaddress;

    public int getStuid() {
        return stuid;
    }
    public void setStuid(int stuid) {
        this.stuid = stuid;
   }
    public int getClassidfk() {
        return classidfk;
    }
    public void setClassidfk(int classidfk) {
        this.classidfk = classidfk;
    }
    public String getStuname() {
        return stuname;
    }
    public void setStuname(String stuname) {
        this.stuname = stuname;
    }
    public int getStuage() {
        return stuage;
    }
    public void setStuage(int stuage) {
        this.stuage = stuage;
    }
    public String getStuaddress() {
        return stuaddress;
    }
    public void setStuaddress(String stuaddress) {
        this.stuaddress = stuaddress;
    }
}

创建数据访问接口

package com.wangxing.mybatis.mapper;

import com.wangxing.mybatis.bean.ClassBean;

public interface ClassMapper {
    //根据班级名称查询班级信息，包含这个班级的所有学生
    ClassBean getClassAndStudentByClassName(String classname);
}

创建SQL映射文件

<?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.wangxing.mybatis.mapper.ClassMapper">
    <!--嵌套resultMap：方式1-->
    <!--collection 一对多的配置元素 -->
    <!--property：成员变量名称  -->
    <!--ofType：集合中元素的类型 -->
    <!--
    <resultMap id="classMap" type="com.wangxing.mybatis.bean.ClassBean">
        <id column="class_id"  property="classid"></id>
        <result column="class_code" property="classcode"></result>
        <result column="class_name" property="classname"></result>
        <collection property="studentBeanList" ofType="com.wangxing.mybatis.bean.StudentBean">
            <id column="stu_id"  property="stuid"></id>
            <result column="class_id_fk" property="classidfk"></result>
            <result column="stu_name" property="stuname"></result>
            <result column="stu_age" property="stuage"></result>
            <result column="stu_address" property="stuaddress"></result>
        </collection>
    </resultMap>
     -->

    <!--嵌套resultMap：方式2-->
    <!--collection 一对多的配置元素 -->
    <!--property：成员变量名称  -->
    <!--ofType：集合中元素的类型 -->
    <!--resultMap:引入其他的resultMap-->
    <resultMap id="classMap" type="com.wangxing.mybatis.bean.ClassBean">
        <id column="class_id"  property="classid"></id>
        <result column="class_code" property="classcode"></result>
        <result column="class_name" property="classname"></result>
        <collection property="studentBeanList"
                    ofType="com.wangxing.mybatis.bean.StudentBean"
                    resultMap="studentMap">
        </collection>
    </resultMap>
    <resultMap id="studentMap" type="com.wangxing.mybatis.bean.StudentBean">
        <id column="stu_id"  property="stuid"></id>
        <result column="class_id_fk" property="classidfk"></result>
        <result column="stu_name" property="stuname"></result>
        <result column="stu_age" property="stuage"></result>
        <result column="stu_address" property="stuaddress"></result>
    </resultMap>
    <select id="getClassAndStudentByClassName" parameterType="java.lang.String" resultMap="classMap">
            select class_id,class_code,class_name,
                    stu_id,class_id_fk,stu_name,stu_age,stu_address
                    from t_class inner join t_student
                    on class_id=class_id_fk
                    where class_name=#{classname};
    </select>
</mapper>

测试代码

//根据班级名称查询班级信息，包含这个班级的所有学生
public static void getClassAndStudentByClassName(){
    SqlSession sqlSession=null;
    try{
        sqlSession=getSqlSession();
        ClassMapper classMapper=sqlSession.getMapper(ClassMapper.class);
        ClassBean classBean=classMapper.getClassAndStudentByClassName("javaEE班");
        sqlSession.commit();
        List<StudentBean> studentBeanList=classBean.getStudentBeanList();
        for(StudentBean studentBean:studentBeanList){
            System.out.println(classBean.getClassname()+"\t"+classBean.getClasscode()+"\t"+studentBean.getStuname());
        }
    }catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
    }finally {
        sqlSession.commit();
    }
}

创建javabean

package com.wangxing.mybatis.bean;
public class StudentBean {
    private  int stuid;
    private  int classidfk;
    private  String stuname;
    private  int stuage;
    private  String stuaddress;
    //当我们查询学生信息的时候需要连同这个学生所在班级的信息一起得到，
    // 所以我们需要在保存学生信息的java实体类中新增一个成员变量，用来班级信息
    private ClassBean classBean;
    public int getStuid() {
        return stuid;
    }
    public void setStuid(int stuid) {
        this.stuid = stuid;
    }
    public int getClassidfk() {
        return classidfk;
    }
    public void setClassidfk(int classidfk) {
        this.classidfk = classidfk;
    }
    public String getStuname() {
        return stuname;
    }
    public void setStuname(String stuname) {
        this.stuname = stuname;
    }
    public int getStuage() {
        return stuage;
    }
    public void setStuage(int stuage) {
        this.stuage = stuage;
    }
    public String getStuaddress() {
        return stuaddress;
    }
    public void setStuaddress(String stuaddress) {
        this.stuaddress = stuaddress;
    }
    public ClassBean getClassBean() {
        return classBean;
    }
    public void setClassBean(ClassBean classBean) {
        this.classBean = classBean;
    }
}

创建数据访问接口

package com.wangxing.mybatis.mapper;

import com.wangxing.mybatis.bean.StudentBean;

public interface StudentMapper {
    //根据学生id查询学生信息，包含学生所在班级的信息
    StudentBean getStudentAndClassById(int stuid);
}

SQL映射文件

<?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.wangxing.mybatis.mapper.StudentMapper">
    <!--嵌套select-->
    <resultMap id="studentMap" type="com.wangxing.mybatis.bean.StudentBean">
        <id column="stu_id"  property="stuid"></id>
        <result column="class_id_fk" property="classidfk"></result>
        <result column="stu_name" property="stuname"></result>
        <result column="stu_age" property="stuage"></result>
        <result column="stu_address" property="stuaddress"></result>
        <collection property="classBean" javaType="com.wangxing.mybatis.bean.ClassBean"
                    column="class_id_fk"
                    select="getClass"></collection>
    </resultMap>
    <select id="getStudentAndClassById" parameterType="int" resultMap="studentMap">
        select * from t_student where stu_id=#{stuid};
    </select>
    <resultMap id="classMap" type="com.wangxing.mybatis.bean.ClassBean">
        <id column="class_id"  property="classid"></id>
        <result column="class_code" property="classcode"></result>
        <result column="class_name" property="classname"></result>
    </resultMap>
    <select id="getClass" parameterType="int" resultMap="classMap">
        select * from t_class where class_id=#{classidfk};
    </select>
</mapper>

测试代码

3.mybatis的高级查询中多对多查询操作如何配置？

需求：根据角色id查询角色信息，并且得到角色所属组的信息

分析：通常情况下一个角色可以隶属于多个组，也可以是一个角色组中有多个角色，那么这个角色与这个角色所属组就是一中多对多关系，此时我们需要3张表，第一张表保存角色信息，第

二张表保存角色所属组信息，第三张表是用来保存角色与角色所属组的关系的信息表。注意的是多对多的关系是需要一张独立数据表来维护多对多的关系用的。

#创建数据库表结构
#创建角色表
create  table t_role(
role_id int primary key auto_increment,
role_name varchar(20),
role_info varchar(20)
);
#创建角色所属组表
create  table t_group(
group_id int primary key auto_increment,
group_name varchar(20),
group_info varchar(20)
);
#创建一个维护数据关系的中间表
create  table t_role_group(
id int primary key auto_increment,
role_id_fk int,
group_id_fk int
);     
#创建外键
ALTER TABLE t_role_group ADD CONSTRAINT role_id_fk FOREIGN KEY(role_id_fk) REFERENCES t_role(role_id);
ALTER TABLE t_role_group ADD CONSTRAINT group_id_fk FOREIGN KEY(group_id_fk) REFERENCES t_group(group_id);
#添加测试数据
insert  into t_role values(null,'管理员','管理其他用户');
insert  into t_role values(null,'普通用户','具有普通权限');
insert  into t_group values(null,'CRM组','负责开发维护CRM项目');
insert  into t_group values(null,'ERP组','负责开发维护ERP项目');
insert  into t_role_group values(null,1,1);
insert  into t_role_group values(null,1,2);
insert  into t_role_group values(null,2,1);
insert  into t_role_group values(null,2,2);

创建javabean

package com.wangxing.mybatis.bean;

import java.util.List;

public class RoleBean {
    private  int  roleid;
    private  String rolename;
    private  String roleinfo;
    //由于我们在查询角色信息的时候需要得到角色所属的信息，
    //因此我们需要在保存角色信息的java类中创建一个集合类型成员变量，
    //用来保存角色所属组信息
    private List<GroupBean> grouplist;

    public int getRoleid() {
        return roleid;
    }
    public void setRoleid(int roleid) {
        this.roleid = roleid;
    }
    public String getRolename() {
        return rolename;
    }
    public void setRolename(String rolename) {
        this.rolename = rolename;
    }
    public String getRoleinfo() {
        return roleinfo;
    }
    public void setRoleinfo(String roleinfo) {
        this.roleinfo = roleinfo;
    }
    public List<GroupBean> getGrouplist() {
        return grouplist;
    }
    public void setGrouplist(List<GroupBean> grouplist) {
        this.grouplist = grouplist;
    }
}

package com.wangxing.mybatis.bean;

import java.util.List;

public class GroupBean {
    private  int  groupid;
    private  String groupname;
    private  String groupinfo;
    //由于我们在查询角色所属组信息的时候需要得到角色的基本信息，
    //所以我们需要在保存角色所属组信息的java实体类中，创建一个集合类型的成员变量，
    //用来保存这个组中的角色信息
    private  List<RoleBean> rolelist;

    public int getGroupid() {
        return groupid;
    }
    public void setGroupid(int groupid) {
        this.groupid = groupid;
    }
    public String getGroupname() {
        return groupname;
    }
   public void setGroupname(String groupname) {
        this.groupname = groupname;
    }
    public String getGroupinfo() {
        return groupinfo;
    }
    public void setGroupinfo(String groupinfo) {
        this.groupinfo = groupinfo;
    }
    public List<RoleBean> getRolelist() {
        return rolelist;
    }
    public void setRolelist(List<RoleBean> rolelist) {
        this.rolelist = rolelist;
    }
}

创建数据访问接口

package com.wangxing.mybatis.mapper;

import com.wangxing.mybatis.bean.RoleBean;

public interface RoleMapper {
    //根据角色名称查询角色信息，包含角色所属组的信息
    RoleBean getRoleAndGroupByRoleName(String rolename);
}

package com.wangxing.mybatis.mapper;

import com.wangxing.mybatis.bean.GroupBean;

public interface GroupMapper {
    //根据角色组名称查询角色组信息，包含角色信息
    GroupBean  getGroupAndRoleByGroupName(String groupname);
}

创建SQL映射文件

<?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.wangxing.mybatis.mapper.RoleMapper">
<resultMap id="roleMap" type="com.wangxing.mybatis.bean.RoleBean">
    <id column="role_id" property="roleid"></id>
    <result column="role_name" property="rolename"></result>
    <result column="role_info" property="roleinfo"></result>
    <collection property="grouplist" ofType="com.wangxing.mybatis.bean.GroupBean">
        <id column="group_id" property="groupid"></id>
        <result column="group_name" property="groupname"></result>
        <result column="group_info" property="groupinfo"></result>
    </collection>
</resultMap>
<select id="getRoleAndGroupByRoleName" parameterType="java.lang.String" resultMap="roleMap">
        select role_id,role_name,role_info,
                group_id,group_name,group_info
                from t_role inner join t_role_group
                on role_id = role_id_fk
                inner join t_group
                on group_id = group_id_fk
                where role_name=#{rolename};
    </select>
</mapper>

测试代码

//根据角色名称查询角色信息，包含角色所属组的信息
public  static void getRoleAndGroupByRoleName(){
    SqlSession sqlSession=null;
    try{
        sqlSession=getSqlSession();
        RoleMapper roleMapper=sqlSession.getMapper(RoleMapper.class);
        RoleBean roleBean=roleMapper.getRoleAndGroupByRoleName("管理员");
        sqlSession.commit();
        //得到角色组
        List<GroupBean> groupBeanList=roleBean.getGrouplist();
        for(GroupBean groupBean:groupBeanList){
            System.out.println(roleBean.getRolename()+"\t"+groupBean.getGroupname());
        }
    }catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
    }finally {
        sqlSession.close();
    }
}

创建SQL映射文件

<?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.wangxing.mybatis.mapper.GroupMapper">
    <resultMap id="groupMap" type="com.wangxing.mybatis.bean.GroupBean">
        <id column="group_id" property="groupid"></id>
        <result column="group_name" property="groupname"></result>
        <result column="group_info" property="groupinfo"></result>
        <collection property="rolelist"
                    ofType="com.wangxing.mybatis.bean.RoleBean"
                    resultMap="roleMap"></collection>
    </resultMap>
    <resultMap id="roleMap" type="com.wangxing.mybatis.bean.RoleBean">
        <id column="role_id" property="roleid"></id>
        <result column="role_name" property="rolename"></result>
        <result column="role_info" property="roleinfo"></result>
    </resultMap>
    <select id="getGroupAndRoleByGroupName" parameterType="java.lang.String" resultMap="groupMap">
        select group_id,group_name,group_info,
               role_id,role_name,role_info
               from t_group inner join t_role_group
                on group_id = group_id_fk
                inner join t_role
                on role_id = role_id_fk
                where group_name=#{groupname};
    </select>
</mapper>

测试代码

//根据角色组名称查询角色组信息，包含角色信息
public  static void getGroupAndRoleByGroupName(){
    SqlSession sqlSession=null;
    try{
        sqlSession=getSqlSession();
        GroupMapper groupMapper=sqlSession.getMapper(GroupMapper.class);
        GroupBean groupBean=groupMapper.getGroupAndRoleByGroupName("ERP组");
        sqlSession.commit();
        //得到角色组中的所有角色
        List<RoleBean> roleBeanList=groupBean.getRolelist();
        for(RoleBean roleBean:roleBeanList){
            System.out.println(groupBean.getGroupname()+"\t"+roleBean.getRolename());
        }
    }catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
    }finally {
        sqlSession.close();
    }
}

运行结果

blake2

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis_5

1.mybatis的高级查询中一对一查询操作有几种方式，每一种如何操作？测试一对一查询需求：查询指定用户信息。分析：可以将用户的信息保存在两张表中，第一张表保存用户的基本信息，第二张表保存用户的身份证信息，这时用户的基本信息与用户的身份证信息就是一组一对一的信息。#用户基本信息表create table t_user(user_id int primary key auto_increment,user_name varchar(20),user_a...
复制链接

扫一扫