MyBatis之基于XML的表之间映射

时间：2022-09-11 14:45:31

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

一、准备工作

1.准备Model和Table

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

User

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
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;
View Code
MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
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;
    }

}
View Code

Card

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
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;
View Code
MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
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;
    }

}
View Code

Course

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
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;
View Code
MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
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;
    }
}
View Code

Role

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
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;
View Code
MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
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;
    }
}
View Code

User_Role

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
CREATE TABLE `user_role` (
  `userid` int(11) NOT NULL,
  `roleid` int(11) NOT NULL,
PRIMARY KEY (`userid`,`roleid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
View Code

2.准备映射

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

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
    <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>
View Code

二、关系映射

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

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
    <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>
View Code

MyBatis之基于XML的表之间映射

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

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
    <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>
View Code

上面的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。

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
    <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>
View Code

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

四、验证

MyBatis之基于XML的表之间映射MyBatis之基于XML的表之间映射
        //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());
         }
View Code

MyBatis之基于XML的表之间映射

五、总结

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

标签：

相关文章