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

多对一关联查询

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

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) 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(11) 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