在前面一章mybatis数据库增删改查操作中,我们已经能完成简单的增删改查操作,可是在真实项目中,必定还会涉及到多张表关联查询的操作,那么在进行多张表关联查询时,在mybatis中又该怎样配置呢?
在前面几章中,数据库中已经有了一张t_user表,现在创建一张与t_user相关联的表t_article,并插入几条关联数据:
由图可见,t_article的user_id关联的是t_user表中的id字段,并且这几条数据都是关联的是id为1的用户,article与user为多对一关系。
之前的代码目录结构如下:
现在,在mapper包下新建一个Article类属性与t_article表相对应,并重写toString方法,注意:在Article类中关联属性并没有使用int类型的user_id,而是一个User对象:
package cn.bdyl.model;
public class Article {
private Integer id;
private String title;
private String content;
private User user;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Article [id=" + id + ", title=" + title + ", content="+ content + ", user=" + user + "]";
}
}
在configuration.xml文件中配置Article类的别名
<typeAlias alias="Article" type="cn.bdyl.model.Article"/>
在UserMapper接口中添加方法queryUserArticles:
public List<Article> queryUserArticles(int userId);
配置User.xml文件(方式一):
<resultMap type="Article" id="articleListMap">
<id column="id" property="id"/>
<result column="title" property="title"/>
<result column="content" property="content"/>
<association property="user" javaType="User">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="queryUserArticles" parameterType="int" resultMap="articleListMap">
select a.id,a.content,a.title,u.id,u.name,u.sex,u.address from t_user u,t_article a where u.id=a.user_id and u.id=#{userId}
</select>
配置User.xml文件(方式二:将association中对应的映射独立出来,达到复用的目的)
<resultMap type="User" id="userListMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</resultMap>
<resultMap type="Article" id="articleListMap">
<id column="id" property="id"/>
<result column="title" property="title"/>
<result column="content" property="content"/>
<association property="user" javaType="User" resultMap="userListMap"></association>
</resultMap>
<select id="queryUserArticles" parameterType="int" resultMap="articleListMap">
select a.id,a.content,a.title,u.id,u.name,u.sex,u.address from t_user u,t_article a where u.id=a.user_id and u.id=#{userId}
</select>
注:当两张表中有相同的字段时,在查询时应根据映射先后顺序查询,否则查询不出完整的结果。
测试代码:
public void testRelatedQuery(){
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper userMapper = session.getMapper(UserMapper.class);
List<Article> articles = userMapper.queryUserArticles(1);
for (Article article : articles) {
System.out.println(article);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
if(session!=null){
session.close();
}
}
}
执行结果:
Article [id=1, title=标题1, content=内容1, user=User [id=1, name=小明, sex=男, address=四川成都]]
Article [id=2, title=标题2, content=内容2, user=User [id=2, name=小明, sex=男, address=四川成都]]
Article [id=3, title=标题3, content=内容3, user=User [id=3, name=小明, sex=男, address=四川成都]]