mybatis之多表关联查询_mybatiswhere标签夺表联合查询-优快云博客

本文详细介绍了MyBatis框架下如何进行多表关联查询，包括一对一和一对多查询的实现方式，通过具体示例展示了如何配置resultMap，以及如何在SQL语句中引用这些配置。

多表关联查询

resultMap通常用于比较复杂的结果集映射(如：多表关联查询)的情况，使用步骤如下：

显示定义标签映射结果集

<resultMap id="userResultMap" type="User">
  <id property="id" column="user_id" />
  <result property="username" column="user_name"/>
  <result property="password" column="hashed_password"/>
</resultMap>

在查询语句中引用我们定义的resultMap：

<select id="selectUsers" resultMap="userResultMap">
  select user_id, user_name, hashed_password
  from some_table
  where id = #{id}
</select>

多表关联查询（级联查询）示例

一对一查询
jdbc.properties数据源

jdbc.url=jdbc:mysql://localhost:3306/yanfa5
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root
jdbc.characterEncoding=utf8

BookMapper.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">

<!-- 级联查询,一对一. -->
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.BookDao">
    <resultMap id="book" type="com.lanou3g.mybatis.bean.Book">
        <id property="id" column="id" />
        <result property="bname" column="bname" />
        <result property="author" column="author" />
        <result property="authorGender" column="author_gender" />
        <result property="price" column="price" />
        <result property="description" column="description" />
        <association property="bookType" javaType="com.lanou3g.mybatis.bean.BookType">
            <id property="id" column="id" />
            <result property="tname" column="tname" />
        </association>
    </resultMap>
    
    <select id="queryBook" resultMap="book">
        select b.*,bt.*,b.id,bt.id from book b,booktype bt where b.btype = bt.id;
    </select>
    
</mapper>

mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties" />
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/BookMapper.xml" />
    </mappers>
</configuration>

对应数据库表的java类
注意：此处可以体现出一对一

@Setter
@Getter
public class Book {
    private Integer id;
    private String bname;
    private BookType bookType;	// ————>一对一体现在此处<————
    private String author;
    private Integer authorGender;
    private Float price;
    private String description;

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", bname='" + bname + '\'' +
                ", bookType=[" + bookType + ']' +
                ", author='" + author + '\'' +
                ", authorGender=" + authorGender +
                ", price=" + price +
                ", description='" + description + '\'' +
                "}\n";
    }
}


@Getter
@Setter
public class BookType {
    private Integer id;
    private  String tname;

    @Override
    public String toString() {
        return "BookType{" +
                "id=" + id +
                ", tname='" + tname + '\'' +
                "}\n";
    }
}

dao层接口（操作数据库）

public interface BookDao {
    List<Book> queryBook();
}

程序运行入口

public class App {
    public static void main(String[] args) throws IOException {
        //1、读入配置文件
        String confPath = "mybatis.xml";
        InputStream is = Resources.getResourceAsStream(confPath);

        //2、构建SqlSessionFactory(用于获取sqlSession)
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);

        //3、获取sqlSession对象（用于具体的RUID)
        SqlSession sqlSession = sessionFactory.openSession();

        //4、具体的RUID
        BookDao bookDao = sqlSession.getMapper(BookDao.class);
        System.out.println(bookDao.queryBook());
    }
}

一对多查询
jdbc.properties数据源

jdbc.url=jdbc:mysql://localhost:3306/yanfa5
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root
jdbc.characterEncoding=utf8

ProvinceMapper.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">

<!-- 级联查询,一对多.例如:Province类中的一个属性 List<City> cityList ,对应City类的集合 -->
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.ProvinceDao">
    <resultMap id="province" type="com.lanou3g.mybatis.bean.Province">
        <id property="id" column="p_id" />
        <result property="pname" column="pname" />
        <collection property="cityList" ofType="com.lanou3g.mybatis.bean.City">
            <id property="id" column="c_id" />
            <result property="cname" column="cname" />
            <result property="pid" column="pid" />
        </collection>
    </resultMap>

    <select id="queryProvince" resultMap="province">
        select p.*,c.*,p.id p_id,c.id c_id from province p,city c where p.id = c.pid
    </select>

</mapper>

mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties" />
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/ProvinceMapper.xml" />
    </mappers>
</configuration>

对应数据库表的java类
注意：此处可以体现出一对多

@Setter
@Getter
public class Province {
    private Integer id;
    private String pname;
    private List<City> cityList;	// ————>一对多体现在此处<————

    @Override
    public String toString() {
        return "Province{" +
                "id=" + id +
                ", pname='" + pname + '\'' +
                ", cityList=" + cityList +
                "}\n";
    }
}


@Getter
@Setter
public class City {
    private Integer id;
    private String cname;
    private Integer pid;

    @Override
    public String toString() {
        return "City{" +
                "id=" + id +
                ", cname='" + cname + '\'' +
                ", pid=" + pid +
                "}\n";
    }
}

dao层接口（操作数据库）

public interface ProvinceDao {
    List<Province> queryProvince();
}

程序运行入口

public class App {
    public static void main(String[] args) throws IOException {
        //1、读入配置文件
        String confPath = "mybatis.xml";
        InputStream is = Resources.getResourceAsStream(confPath);

        //2、构建SqlSessionFactory(用于获取sqlSession)
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);

        //3、获取sqlSession对象（用于具体的RUID)
        SqlSession sqlSession = sessionFactory.openSession();

        //4、具体的RUID
        ProvinceDao provinceDao = sqlSession.getMapper(ProvinceDao.class);
        System.out.println(provinceDao.queryProvince());
    }
}