Mybatis之基于XML的表之间映射

时间:2022-11-19 11:24:16

数据库表之间的关系有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 '',
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>

Mybatis之基于XML的表之间映射

我们可以这样写来映射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());
}

Mybatis之基于XML的表之间映射

五、总结

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