概述
一对多查询语句：
select p.id,p.title,p.create_time,p.user_id,u.username from user u,post p where u.id=p.user_id
查询发帖的 id、标题、创建时间、用户id、用户名
其中用户名在 user 表中，其他字段在 post 表中
难点
如何配置下面的 resultType:
<select id="query" resultType="?">
select p.id,p.title,p.create_time,p.user_id,u.username from user u,post p where u.id=p.user_id
</select>
由于查询的字段分别在2个实体类中，所以此处配置哪个实体类，都不合理
解决
实现一个类，该类中的属性就是 上面sql中的查询字段，然后再配置到 mapper.xml 中
优点
将结果封装成类对象，直观，取值容易
缺点
需要实现 类，并配置到mapper.xml中
实现封装类
package org.malaoshi.entity;
import java.util.Date;
public class PostUser {
private String id;
private String title;
private String userId;
private Date createTime;
private String username;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
}
修改 IPostMapper
public List<PostUser> query();
修改 postMapper.xml
<resultMap id="PostUserMap" type="PostUser">
<id property="id" column="id" ></id>
<result property="title" column="title"/>
<result property="createTime" column="create_time" javaType="java.util.Date" jdbcType="TIMESTAMP"/>
</resultMap>
<select id="query" resultMap="PostUserMap">
select p.id,p.title,p.create_time,p.user_id,u.username from user u,post p where u.id=p.user_id
</select>
测试
@Test
public void testSelect() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IPostMapper mapper = sqlSession.getMapper(IPostMapper.class);
List<PostUser> list=mapper.query();
for(PostUser p:list) {
SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd HH:mm");
String createTime=sdf.format(p.getCreateTime());
String info = String.format("id：%s,标题：%s,用户名：%s,创建时间：%s", p.getId(), p.getTitle(), p.getUsername(),createTime);
System.out.println(info);
}
sqlSession.close();
inputStream.close();
}