问题产生:
在日常的数据库查询中,避免不了需要使用多表连接查询问题,如果只是用SQL语句实现的话是非常简单的,而如何用MyBatis并返回想要的对象呢?
建立实体类:
Forum实体类:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Forum {
private Integer forumId;
private Integer userId;
private Integer departmentId;
private String forumContent;
private String forumHTMLContent;
private String forumTitle;
private String forumTabloid;
private String forumDate;
private User user;
}
而其中的userId是User表中的,即userId是外键。注意:想要关联查询结果需要在Forum实体类中添加关联类User属性 User实体类:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer userId;
private String userName;
private String password;
private Integer departmentId;
private String nickName;
private String gender;
}
Mappe层:
@Mapper
public interface ForumMapper {
List<Forum> findAllForum();
}
Mapper.xml(实现连接查询):
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.ForumMapper">
<resultMap id="ForumMap" type="Forum">
<id column="forumId" jdbcType="INTEGER" property="forumId"/>
<result column="userId" jdbcType="INTEGER" property="userId"/>
<result column="departmentId" jdbcType="INTEGER" property="departmentId"/>
<result column="forumContent" jdbcType="VARCHAR" property="forumContent"/>
<result column="forumHTMLContent" jdbcType="VARCHAR" property="forumHTMLContent"/>
<result column="forumTitle" jdbcType="VARCHAR" property="forumTitle"/>
<result column="forumTabloid" jdbcType="VARCHAR" property="forumTabloid"/>
<result column="birth" jdbcType="TIMESTAMP" property="birth"/>
<association property="user" javaType="User" >
<id column="userId" jdbcType="INTEGER" property="userId"/>
<result column="nickName" jdbcType="VARCHAR" property="nickName"/>
</association>
</resultMap>
<select id="findAllForum" resultMap="ForumMap">
select * from forum join user on forum.userId = user.userId where 0 = 0 order by forumDate desc
</select>
</mapper>
Mybatis中javaType和jdbcType对应关系:
JDBC Type Java Type
CHAR String
VARCHAR String
LONGVARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
BIT boolean
BOOLEAN boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE double
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
CLOB Clob
BLOB Blob
ARRAY Array
DISTINCT mapping of underlying type
STRUCT Struct
REF Ref
Service层:
public interface ForumService {
List<Forum> findAllForum();
}
实现类略过
Controller层:
List<Forum> allForum = forumService.findAllForum();
request.setAttribute("forumList", allForum);
System.out.println(allForum);
结果:
使用中遇到的问题,以上全为个人见解,如有不足,请指正
|