mybatis实战教程二：多对一关联查询(一对多)

2021-10-04 18:47:02

多对一关联查询

一、数据库关系。article表和user表示多对一的关系

CREATE TABLE `article` (

  `id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int() NOT NULL,

  `title` varchar(100) NOT NULL,

  `content` text NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
`id` int() NOT NULL AUTO_INCREMENT,

  `userName` varchar(50) DEFAULT NULL,

  `gender` int(11) DEFAULT NULL,

  `hobby` varchar(255) DEFAULT NULL,

  `member` int(11) DEFAULT NULL,

  `userAge` int(11) DEFAULT NULL,

  `userAddress` varchar(200) DEFAULT NULL,

  `reg_time` varchar(200) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

二、创建跟表对应关系的model

public class Article {

    private int id;
private User user;
private String title;

    private String content;

    setter()... getter()...

}

public class User {

    private int id;

    private String userName;

    private String userAge;

    private Gender gender;

    private Hobby hobby;

    private Member member;

    private String userAddress;

    private Date regTime;

    setters()&getters()

}

三、新建对应的mapper映射器组合

 public interface ArticleMapper {

    public List<Article> getUserArticles();

}

　　组合一：

<?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.yihaomen.mybatis.dao.ArticleMapper">

    <!-- User 联合文章进行查询 方法之一的配置 (多对一的方式)  -->

    <resultMap id="resultUserArticleList" type="Article">

        <!--注意这个column=aid一定要跟下面的<association>中的<id property='id' column='id' >区分开来-->

        <id property="id" column="aid" />

        <result property="title" column="title" />

        <result property="content" column="content" />
<association property="user" javaType="User">

            <id property="id" column="id" />

            <result property="userName" column="userName" />

            <result property="userAddress" column="userAddress" />
　　　　　　  <result property="gender" column="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>
　　　　  </association>

    </resultMap>

    <select id="getUserArticles" resultMap="resultUserArticleList">

        <!--如果两个表的id字段有关联，那必须都查询出来-->

        SELECT u.id,u.userName,u.gender,u.userAddress,a.id aid,a.title,a.content

        FROM `user` u,`article` a

        WHERE u.id=a.userid

    </select>

</mapper>

　　组合二：

<?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.yihaomen.mybatis.dao.ArticleMapper">

    <resultMap id="resultUserArticleList2" type="Article">

        <id property="id" column="aid" />

        <result property="title" column="title" />

        <result property="content" column="content" />
<association property="user" javaType="User" resultMap="resultListUser2" />

    </resultMap>

    <resultMap id="resultListUser2" type="User">

        <id column="id" property="id" />

        <result column="userName" property="userName" />

        <result column="userAge" property="userAge" />

        <result column="gender" property="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>

        <result column="userAddress" property="userAddress" />

    </resultMap>

    <select id="getUserArticles" resultMap="resultUserArticleList2">

        <!--如果两个表的id字段有关联，那必须都查询出来-->

        SELECT u.id,u.userName,u.gender,u.userAddress,a.id aid,a.title,a.content

        FROM `user` u,`article` a

        WHERE u.id=a.userid

    </select>

</mapper>

　　组合三：

<?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.yihaomen.mybatis.dao.ArticleMapper">

    <resultMap id="resultUserArticleList3" type="Article">

        <id property="id" column="aid" />

        <result property="title" column="title" />

        <result property="content" column="content" />
<association property="user" column="userid" select="com.yihaomen.mybatis.dao.UserMapper.selectUserByID" />

    </resultMap>

    <select id="getUserArticles" resultMap="resultUserArticleList3">

        <!--如果两个表的id字段有关联，那必须都查询出来-->
　　　　 <!--这个a.userid一定要查出来，否则上面的column="userid"就没有值，在查询的时候就会报空指针异常-->
        SELECT u.id,u.userName,u.gender,u.userAddress,a.id aid,a.userid,a.title,a.content

        FROM `user` u,`article` a

        WHERE u.id=a.userid

    </select>

</mapper>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yihaomen.mybatis.dao.UserMapper">

    <resultMap id="resultListUser" type="User">

        <id column="id" property="id" />

        <result column="userName" property="userName" />

        <result column="userAge" property="userAge" />

        <result column="gender" property="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>

        <result column="hobby" property="hobby" typeHandler="org.apache.ibatis.type.EnumTypeHandler"/>

        <result column="member" property="member" typeHandler="com.yihaomen.mybatis.type.MyMemberTypeHandler"/>

        <result column="userAddress" property="userAddress" />

        <result column="reg_time" property="regTime" javaType="java.util.Date" jdbcType="VARCHAR"

                typeHandler="com.yihaomen.mybatis.type.MyDateTypeHandler"/>

    </resultMap>

    <select id="selectUserByID" parameterType="int" resultType="User" >

        select * from `user` where id = #{id}

    </select>

</mapper>

四、在configuration.xml 配置别名和注册映射器：

<typeAliases>

    <typeAlias alias="User" type="com.yihaomen.mybatis.model.User"/>

    <typeAlias alias="Article" type="com.yihaomen.mybatis.model.Article" />

</typeAliases>

<mappers>

    <mapper resource="com/yihaomen/mybatis/model/User.xml"/>

    <mapper resource="com/yihaomen/mybatis/model/Article.xml"/>

</mappers>

五、测试

public class ArticleTest extends BaseTest{

    public static void main(String[] args) {

        selectArticlesWithUser();

    }

    public static void selectArticlesWithUser() {

        SqlSessionFactory sessionFactory =getSession();

        SqlSession session = sessionFactory.openSession();

        ArticleMapper mapper = session.getMapper(ArticleMapper.class);

        List<Article> list = mapper.getUserArticles();

        for(Article a : list) {

            System.out.println(a.getTitle() + "," + a.getUser().getUserName() + "," + a.getUser().getGender());

        }

    }

}

一对多关联查询

一、数据库关系。user表和article表示一对多的关系

二、创建跟表对应关系的model

public class User {

    private int id;

    private String userName;

    private String userAge;

    private Gender gender;

    private Hobby hobby;

    private Member member;

    private String userAddress;

    private Date regTime;
private List<Article> articleList;

    setters()&getters()

}

三、新建对应的mapper映射器组合

@Repository

public interface UserMapper {

    public List<User> getUserArticlesById(Long id);

}

组合一：

<?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.yihaomen.mybatis.dao.UserMapper">

    <resultMap id="resultUserArticleList3" type="User">

        <id column="id" property="id" />

        <result column="userName" property="userName" />

        <result column="userAge" property="userAge" />

        <result column="gender" property="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>

        <result column="hobby" property="hobby" typeHandler="org.apache.ibatis.type.EnumTypeHandler"/>

        <result column="member" property="member" typeHandler="com.yihaomen.mybatis.type.MyMemberTypeHandler"/>

        <result column="userAddress" property="userAddress" />

        <result column="reg_time" property="regTime" javaType="java.util.Date" jdbcType="VARCHAR"

                typeHandler="com.yihaomen.mybatis.type.MyDateTypeHandler"/>

        <!--一对多-->
<collection property="articleList" ofType="Article">

            <!--这个column=aid 一定要跟上面id的区分开来,否则会查询不出来-->

            <id property="id" column="aid"></id>

            <result column="title" property="title" />

            <result column="content" property="content"/>

        </collection>

    </resultMap>

    <select id="getUserArticlesById" resultMap="resultUserArticleList3" parameterType="Long">

        SELECT u.id, u.userName, u.userAddress, u.gender, a.id aid,a.userid, a.title, a.content

        FROM `user` u,`article` a

        WHERE u.id=a.userid and u.id=#{id}

    </select>

</mapper>

组合二：这种方法适合SQL类似 SELECT * FROM table_name，就是没有where条件的情况

<resultMap id="resultUserArticleList4" type="User">

        <id column="id" property="id" />

        <result column="userName" property="userName" />

        <result column="userAge" property="userAge" />

        <result column="gender" property="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>

        <result column="hobby" property="hobby" typeHandler="org.apache.ibatis.type.EnumTypeHandler"/>

        <result column="member" property="member" typeHandler="com.yihaomen.mybatis.type.MyMemberTypeHandler"/>

        <result column="userAddress" property="userAddress" />

        <result column="reg_time" property="regTime" javaType="java.util.Date" jdbcType="VARCHAR"

                typeHandler="com.yihaomen.mybatis.type.MyDateTypeHandler"/>

        <!--一对多-->
<collection property="articleList" column="userid"

                    select="com.yihaomen.mybatis.dao.ArticleMapper.getArticlesByUserId">


        </collection>

    </resultMap>

    <select id="getUserArticlesById" resultMap="resultUserArticleList4" parameterType="Long">

        SELECT u.id, u.userName, u.userAddress, u.gender, a.id aid,a.userid, a.title, a.content

        FROM `user` u,`article` a

        WHERE u.id=a.userid and u.id=#{id}

    </select>

Article.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.yihaomen.mybatis.dao.ArticleMapper">

    <resultMap id="resultArticles" type="Article">

        <id column="id" property="id" />

        <result column="title" property="title" />

        <result column="content" property="content" />

    </resultMap>

    <select id="getArticlesByUserId" parameterType="Integer" resultMap="resultArticles">

        SELECT

            id, title, content

        FROM

            article a

        WHERE

            a.userid=#{userId}

    </select>

</mapper>

四、测试

import com.yihaomen.mybatis.dao.UserMapper;

import com.yihaomen.mybatis.model.Article;

import com.yihaomen.mybatis.model.User;

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 java.io.IOException;

import java.io.Reader;

import java.util.List;

public class TestGetUser {

    private static SqlSessionFactory sqlSessionFactory;

    private static Reader reader;

    static {

        try {

            reader = Resources.getResourceAsReader("configuration.xml");

            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    public static SqlSessionFactory getSession() {

        return sqlSessionFactory;

    }

    public static void getUserArticlesById(Long id) {

        SqlSession session = sqlSessionFactory.openSession();

        UserMapper userMapper = session.getMapper(UserMapper.class);

        List<User> users = userMapper.getUserArticlesById(id);

        for(User u : users) {

            System.out.println("------------------");

            System.out.println(u.getUserName());

            for(Article a : u.getArticleList()) {

                System.out.println(a.getTitle() + "," + a.getContent());

            }

        }

    }

    public static void main(String[] args) {

        getUserArticlesById(11L);

    }

}

https://gitee.com/huayicompany/springmvc-mybatis

参考：

[1] 《mybati实战教程》

[2] CSDN博客，http://www.cnblogs.com/hq233/p/6752335.html

码农公寓

多对一关联查询

一、数据库关系。article表和user表示多对一的关系

二、创建跟表对应关系的model

三、新建对应的mapper映射器组合

组合一：

组合二：

组合三：

四、在configuration.xml 配置别名和注册映射器：

五、测试

一对多关联查询

一、数据库关系。user表和article表示一对多的关系

二、创建跟表对应关系的model

三、新建对应的mapper映射器组合

组合一：

组合二：这种方法适合SQL类似 SELECT * FROM table_name，就是没有where条件的情况

四、测试

参考：

相关文章

　　组合一：

　　组合二：

　　组合三：