mybatis支持关联查询,即多个表之间的查询。这里介绍三种关联查询——联合查询,构造查询,子查询
联合查询要用到resultMap映射标记,在map配置文件中要用到<association>
子节点(即复杂类型的关联)。下面依旧通过一个例子来说明:
由于可能小伙伴们没耐性看之前的案例,此处我会将所有代码一起贴上来
- 首先在数据库中创建如下两个表
- 创建如下目录(仅创建框起来的文件),请自行添加mybatis所需的jar包
- MyBatisConfig.xml基础配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//iBATIS.com//DTD SQL Map Config 3.0//EN"
"mybatis-3-config.dtd" >
<configuration>
<!-- 定义别名,这样在map.xml中不用写全类名了 -->
<typeAliases>
<typeAlias type="jike.book.pojo.JikeUser" alias="JikeUser" />
<typeAlias type="jike.book.pojo.Author" alias="Author" />
</typeAliases>
<!-- 定义数据库信息,默认使用development数据库构造环境 -->
<environments default="development">
<environment id="development">
<!-- 采用jdbc事物管理 -->
<transactionManager type="JDBC" />
<!-- 配置数据库链接信息 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
<property name="username" value="root" />
<property name="password" value="zyf1998don" />
</dataSource>
</environment>
</environments>
<!-- 定义映射器,即引用上面的那个SQL语句文件 -->
<mappers>
<mapper resource="jike/book/map/jikeUser.xml" />
<mapper class="jike.book.map.InterfaceJikeUserMap" />
<mapper resource="jike/book/map/author.xml" />
</mappers>
</configuration>
4 . jikeUser.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>
<select id="findById" parameterType="int" resultType="jike.book.pojo.JikeUser">
select *
from jikeUser where id=#{id}
</select>
</mapper>
5 . author.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="domain.blog.mappers.AuthorMapper">
<insert id="insertAuthor" parameterType="Author" statementType="PREPARED">
insert into Author(userID,realName,IDCard)
values(#{jikeUser.id},#{realName},#{IDCard})
</insert>
<!-- 联合查询 -->
<resultMap type="Author" id="JikeAuthorMap">
<id property="id" column="author.id" />
<result property="realName" column="realName" />
<result property="IDCard" column="IDCard" />
<!-- 会在userID中引用jikeUser表。用jileUser属性 -->
<association property="jikeUser" column="userID" javaType="JikeUser">
<id property="id" column="jikeUser.id" />
<result property="userName" column="username" />
<result property="passWord" column="password" />
</association>
</resultMap>
<select id="selectAuthorJoin" resultMap="JikeAuthorMap">
select * from author
inner join jikeUser
on jikeuser.id=author.userID
</select>
</mapper>
说明:联合查询需要用到resultMap元素。接下里一一说明其代表含义:
第一行:type代表引用了Author.java,此处用了别名机制,id不多解释
第二行:id子节点表示Author.java中的id属性是主键,所对应的列是author数据表中的id列。
第三行:Author.java中有个realName属性,对应着author数据表中的realName列。
第四行:如第三行,不多说
第五行:<association>
标签表示要用到复杂映射了。此处的column表示使用userID列来关联”jikeUser.java”(即property中的内容,此处用了别名机制)。javaType表示返回个JikeUser类的实例。
第六行:如第二行,只不过此处的id是jikeUser.java中的id属性。后面两行一样。
到此为止,两个表关联起来了
6 .Author.java
package jike.book.pojo;
public class Author {
private Integer id;
private JikeUser jikeUser;
private String realName;
private String IDCard;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public JikeUser getJikeUser() {
return jikeUser;
}
public void setJikeUser(JikeUser jikeUser) {
this.jikeUser = jikeUser;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public String getIDCard() {
return IDCard;
}
public void setIDCard(String IDCard) {
this.IDCard = IDCard;
}
}
7 .JikeUser.java
package jike.book.pojo;
public class JikeUser {
private int id;
private String userName;
private String passWord;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
public JikeUser() {
super();
}
//用于构造关联
public JikeUser(String userName, String passWord) {
super();
this.userName = userName;
this.passWord = passWord;
}
}
8 . TestAssociation.java
package jike.book.test;
import java.io.Reader;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import jike.book.pojo.Author;
import jike.book.pojo.JikeUser;
public class TestAssociation {
public static void main(String[] args) {
// TODO Auto-generated method stub
String resource="jike/book/map/MybatisConfig.xml";
Reader reader=null;
SqlSession sqlSession;
try{
reader=Resources.getResourceAsReader(resource);
}catch(Exception e){
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
sqlSession=sqlSessionFactory.openSession();
try{
List<Author> ap=sqlSession.selectList("selectAuthorJoin");
for(Author temp:ap){
System.out.println("作者真实姓名="+temp.getRealName()+" 对应的用户名="+temp.getJikeUser().getUserName());
}
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
}
运行结果:
构造关联仅仅是在JikeUser.java类中使用了构造器public JikeUser(String userName, String passWord){...}
然后在author.xml中改动了下:
<!-- 构造器查询 -->
<resultMap type="Author" id="JikeAuthorMapByCon">
<id property="id" column="author.id" />
<result property="realName" column="realName" />
<result property="IDCard" column="IDCard" />
<!-- 会在userID中引用jikeUser表。用jileUser属性 -->
<association property="jikeUser" column="userID" javaType="JikeUser">
<constructor>
<arg column="userName" javaType="String" />
<arg column="password" javaType="String" />
</constructor>
</association>
</resultMap>
<select id="selectAuthorJoin" resultMap="JikeAuthorMapByCon">
select * from author
inner join jikeUser
on jikeuser.id=author.userID
</select>
子查询则将author.xml改动如下,其他地方不用改动。:
<!-- 子查询 -->
<resultMap type="Author" id="JikeAuthorMapSubMap">
<id property="id" column="author.id" />
<result property="realName" column="realName" />
<result property="IDCard" column="IDCard" />
<!-- 子查询就是多了个select属性,该属性使用jikeUser.xml中的findById,将属性赋值给userID -->
<association property="jikeUser" column="userID" javaType="JikeUSer"
select="findById">
</association>
</resultMap>
<select id="selectAuthorJoin" resultMap="JikeAuthorMapSubMap">
select * from author
</select>
可以看到 子查询是引用了jikeUser.xml中的SQL,说明它查询了两次。