MyBatis之基于XML的表之间映射

 数据库表之间的关系有3种，一对一、一对多、多对多。既然是ORM，这肯定是必须有的。在学习EF的时候也有涉及，今天就是参考着EF的来学习下MyBatis的表关系映射。

一、准备工作

1.准备Model和Table

既然是要涉及表的一对一、一对多、多对多的关系，那肯定得先准备好实体关系，以及表。下面建了四个对象四个表。User<->Card是一对一关系，User<->Course一对多关系，User<->Role多对多关系。

User

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `status` varchar(20) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

package Cuiyw.MyBatis.Model;

import java.util.List;

public class User {
    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return  "User [id=" + Id + ", name=" + Name + ", age=" + Age + ",status="+Status+"]";

    }
    public int getId() {
        return Id;
    }
    public void setId(int id) {
        Id = id;
    }
    public String getName() {
        return Name;
    }
    public void setName(String name) {
        Name = name;
    }
    public int getAge() {
        return Age;
    }
    public void setAge(int age) {
        Age = age;
    }
    private int Id;
    private String Name;
    private int Age;
    private UserState Status;
    
    private Card card;
    public Card getCard() {
        return card;
    }
    public void setCard(Card card) {
        this.card = card;
    }
    private List<Role> roles;
    private List<Course> courses;
    
    public List<Course> getCourses() {
        return courses;
    }
    public void setCourses(List<Course> courses) {
        this.courses = courses;
    }
    public List<Role> getRoles() {
        return roles;
    }
    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }

    public UserState getStatus() {
        return Status;
    }
    public void setStatus(UserState status) {
        this.Status = status;
    }
    
}

Card

CREATE TABLE `card` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cardNo` varchar(20) NOT NULL,
  `city` varchar(45) NOT NULL,
  `address` varchar(100) NOT NULL,
  `userid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

package Cuiyw.MyBatis.Model;

public class Card {
    
    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return  "Card [id=" + getId() + ", cardNo=" + cardNo + ", city=" + city + ",address="+address+",userid="+userid+"]";

    }
    private int id;
    private String cardNo;
    private String city;
    private String address;
    private int userid;
    public int getUserid() {
        return userid;
    }
    public void setUserid(int userid) {
        this.userid = userid;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCardNo() {
        return cardNo;
    }
    public void setCardNo(String cardNo) {
        this.cardNo = cardNo;
    }
    public String getCity() {
        return city;
    }
    public void setCity(String city) {
        this.city = city;
    }

}

Course

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `userid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

package Cuiyw.MyBatis.Model;

public class Course {

    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return  "Course [id=" + getId() + ", name=" + name +",userid="+userid+"]";

    }
    private int id;
    private String name;
    private int userid;
    public int getUserid() {
        return userid;
    }
    public void setUserid(int userid) {
        this.userid = userid;
    }
    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;
    }
}

Role

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `desp` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

package Cuiyw.MyBatis.Model;

import java.util.List;

public class Role {
    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return  "Role [id=" + getId() + ", name=" + name +",desp="+desp+"]";

    }
    private int id;
    private String name;
    private String desp;
    private List<User> users;
    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 String getDesp() {
        return desp;
    }
    public void setDesp(String desp) {
        this.desp = desp;
    }
    public List<User> getUsers() {
        return users;
    }
    public void setUsers(List<User> users) {
        this.users = users;
    }
}

User_Role

CREATE TABLE `user_role` (
  `userid` int(11) NOT NULL,
  `roleid` int(11) NOT NULL,
  PRIMARY KEY (`userid`,`roleid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.准备映射

上一篇博客是单个表的映射关系，我们也先把但表的映射准备好。

    <resultMap type="Card" id="cardResult">
        <result column="id" property="id"/>
        <result column="no" property="cardNo"/>
        <result column="city" property="city"/>
        <result column="address" property="address"/>
        <result column="userid" property="userid"/>
    </resultMap>

    <resultMap type="Course" id="courseResult">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="userid" property="userid"/>
    </resultMap>
    <resultMap type="Role" id="roleResult">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="desp" property="desp"/>
        <result column="userid" property="userid"/>
    </resultMap>

二、关系映射

加入我们需要执行下面的sql查询数据，那怎么映射到实体model上呢？

    <select id="getuser" parameterType="int" 
        resultType="User" resultMap="userResult">
        select a.id as user_id,a.name as user_name,a.age as user_age,a.status as user_status,
b.id as card_id,b.cardNo as card_cardNo,b.userid as card_userid,
b.city as card_city,b.address as card_address,c.id as course_id,c.name as course_name,
c.userid as course_userid,e.name as role_name,e.desp as role_desp 
from user a left join card b on a.id=b.userid            
left join course c on a.id=c.userid 
left join user_role d on a.id=d.userid 
left join role e on d.roleid=e.id where a.id=#{id}
    </select>

我们可以这样写来映射User。

    <resultMap type="User" id="userResult">
        <result column="user_id" property="id"/>
        <result column="user_name" property="name"/>
        <result column="user_age" property="age"/>
        <result column="user_status" property="status" typeHandler="Cuiyw.MyBatis.Model.ValuedEnumTypeHandler"/>
        <association property="card" javaType="Card" columnPrefix="card_">
            <result column="id" property="id"/>
            <result column="no" property="cardNo"/>
            <result column="city" property="city"/>
            <result column="address" property="address"/>
            <result column="userid" property="userid"/>
        </association>
        <collection property="courses" javaType="ArrayList" ofType="Course" columnPrefix="course_">
            <result column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="userid" property="userid"/>
        </collection>
        <collection property="roles" javaType="ArrayList" ofType="Role" columnPrefix="role_">
            <result column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="desp" property="desp"/>
            <result column="userid" property="userid"/>
        </collection>
    </resultMap>

上面的xml种有两个重要的节点。

association关联:关联的结果查询，就是在查询出结果后，根据查询的列和resultMap定义的对应关系，来创建对象并写入值,例如user的card属性。

collection:用来映射class中的List列表类型的属性。

还有就是JavaType和ofType:JavaType和ofType都是用来指定对象类型的，但是JavaType是用来指定pojo中属性的类型，而ofType指定的是映射到list集合属性中pojo的类型。

 三、优化

上面的xml配置确实可以映射User，但是在映射Card、Course和Role的时候把映射关系都放在的id=userResult的resultMap中，这样如果以后还有要映射Card、Course、Role的时候还要再写一遍，复用性不高。其实我们可以在association和collection节点增加属性resultMap。

    <resultMap type="User" id="userResult">
        <result column="user_id" property="id"/>
        <result column="user_name" property="name"/>
        <result column="user_age" property="age"/>
        <result column="user_status" property="status" typeHandler="Cuiyw.MyBatis.Model.ValuedEnumTypeHandler"/>
        <association property="card" javaType="Card" resultMap="cardResult" columnPrefix="card_"></association>
        <collection property="courses" javaType="ArrayList" ofType="Course" resultMap="courseResult" columnPrefix="course_"></collection>
        <collection property="roles" javaType="ArrayList" ofType="Role" resultMap="roleResult" columnPrefix="role_"></collection>
    </resultMap>

而resultMap的值正是我们上面单表准备的映射resultMap。

四、验证

        //mybatis的配置文件
        String resource = "Config.xml";
        //使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）
        Reader reader = Resources.getResourceAsReader(resource); 
        //构建sqlSession的工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建能执行映射文件中sql的sqlSession
        SqlSession session = sessionFactory.openSession(true);
//        System.out.println("新增");
        String statement="Cuiyw.MyBatis.DBMapping.UserMapper.addUser";
        System.out.println("查询单个");
         statement="Cuiyw.MyBatis.DBMapping.UserMapper.getuser";
         User user=session.selectOne(statement, 2);
         Card card=user.getCard();
         
         List<Course>courses=user.getCourses();
         List<Role>roles=user.getRoles();
         session.close();
         System.out.println(user.toString());
         System.out.println(card.toString());
         for(int i=0;i<courses.size();i++)
         {
             System.out.println(courses.get(i).toString());
         }
         for(int i=0;i<roles.size();i++)
         {
             System.out.println(roles.get(i).toString());
         }

五、总结

这篇主要是学习表的关系映射，association、collection的使用，上面演示了一对一、一对多，对于多对多，可以把它当作两个一对多，这里只是演示了User对Role的一对多，Role对User的也是一样，这里就省了。具体可以参考官网:http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html