Mybatis学习四（表关联）

1.概述

实际项目中，经常是关联表的查询，比如：最常见到的多对一，一对多等。

在Java实体对象中，一对多可以根据List和Set来实现，两者在mybatis中都是通过collection标签来配合来加以实现。

2.表关联一对多示例

应用场景：首先根据用户 ID 读取一个用户信息，然后再读取这个用户所发布贴子(post)。

第一步：创建表（省略）

第二步：创建java对象

创建Post.java

import java.io.Serializable;

public class Post implements Serializable{
   private int id;
   private User user;
   private String title;
   private String content;
   }

创建User.java，包含Post.java集合

import java.io.Serializable;

import java.util.Date;
import java.util.List;

public class User implements Serializable{
private int id;
private String username;
private String mobile;
private List<Post> posts;
}

第三步：配置数据源和对象和mapperwen的文件（src/config/Configure.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>
<typeAliases>
<typeAlias alias="User" type="com.yiibai.pojo.User" />
<typeAlias alias="Post" type="com.yiibai.pojo.Post" />
</typeAliases>

第四步：配置映射文件User.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">

<select id="getUser" resultMap="resultUserMap" parameterType="int">
   SELECT u.*,p.*
   FROM user u, post p
WHERE u.id=p.userid AND id=#{user_id}
   </select>
   </mapper>

第五步：测试

private static SqlSessionFactory sqlSessionFactory;

private static Reader reader;

   static {
   try {
   reader = Resources.getResourceAsReader("config/Configure.xml");
   sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
   }

   public static SqlSessionFactory getSession() {
   return sqlSessionFactory;
   }

   /**
   * @param args
   */
public static void main(String[] args) {
   // TODO Auto-generated method stub
SqlSession session = sqlSessionFactory.openSession();
try {
int userid = 1;
User user = session.selectOne("com.yiibai.userMaper.getUser", 1);
System.out.println("username: "+user.getUsername()+",");
List<Post> posts = user.getPosts();
for(Post p : posts) {
System.out.println("Title:" + p.getTitle());
System.out.println("Content:" + p.getContent());
}
   } finally {
   session.close();
}
}

3.表关联多对一示例

和多对对比不同点：

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

<select id="getPosts" resultMap="resultPostsMap" parameterType="int">
SELECT u.*,p.*
FROM user u, post p
WHERE u.id=p.userid AND p.post_id=#{post_id}
</select>
</mapper>

注：在上面的配置文件中，使用到了一个 <association>标签，关联对应的 User 类。

测试：

int postId = 1;
Post post = session.selectOne("com.yiibai.userMaper.getPosts", postId);
System.out.println("title: "+post.getTitle());
System.out.println("userName: "+post.getUser().getUsername());

4.表关联多对多示例

要求：用户表 user，用户组表 group 和用户组映射表 user_group ，

一个户用户可以在多个用户组中，一个用户组中有多个用户。

第一步：建表，建对象

表：

  CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `username` varchar(64) NOT NULL DEFAULT '',
   `mobile` varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('1', 'yiibai', '13838009988');
INSERT INTO `user` VALUES ('2', 'User-name-1', '13838009988');

   CREATE TABLE `group` (
`group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`group_name` varchar(254) NOT NULL DEFAULT '',
   PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
   INSERT INTO `group` VALUES ('1', 'Group-1');
   INSERT INTO `group` VALUES ('2', 'Group-2');

user.java：

import java.util.List;

   public class User {
   private int id;
private String username;
private String mobile;
   private List<Group> groups;
}

Group.java :

  public class Group {
   private int groupId;
   private String groupName;
   private List<User> users;

   }

UserGroup.java :

  public class UserGroup {
   private int userId;
   private int groupId;
   }

第二步：配置数据源和对象和mapper映射（只拿只要配置）

第三步：映射

GroupMaper.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.yiibai.maper.GroupMaper">

<parameterMap type="Group" id="parameterGroupMap">
<parameter property="groupId"/>
<parameter property="groupName"/>
</parameterMap>
<insert id="insertGroup" parameterMap="parameterGroupMap">
   INSERT INTO `group` (group_name)
   VALUES(#{groupName});
   </insert>

   <resultMap type="Group" id="resultGroupMap_1">
   <result property="id" column="id" />
   <result property="groupName" column="group_name" />
   <collection property="users" column="group_id" select="com.yiibai.maper.UserGroupMaper.getUsersByGroupId" />
</resultMap>
<select id="getGroup" resultMap="resultGroupMap_1"   parameterType="int">

   SELECT *
FROM `group`
   WHERE group_id=#{id}
</select>
   </mapper>

User.xml:

UserGroup.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.yiibai.maper.UserGroupMaper">
<parameterMap type="UserGroup" id="parameterUserGroupMap">
<parameter property="userId"/>
<parameter property="groupId"/>
</parameterMap>

<insert id="insertUserGroup" parameterMap="parameterUserGroupMap">
   INSERT INTO user_group(user_id, group_id)
   VALUES(#{userId},#{groupId})
   </insert>

   
<resultMap type="User" id="resultUserMap_2">
<result property="id" column="id"/>
<result property="username" column="username"/>
<result property="mobile" column="mobile"/>
</resultMap>

<select id="getUsersByGroupId" resultMap="resultUserMap_2" parameterType="int">
   SELECT u.*, ug.group_id
   FROM user u, user_group ug
WHERE u.id=ug.user_id AND ug.group_id=#{group_id}
   </select>

   
   <resultMap type="Group" id="resultGroupMap_2">
   <result property="groupId" column="group_id"/>
<result property="groupName" column="group_name"/>
   </resultMap>

<select id="getGroupsByUserId" resultMap="resultGroupMap_2" parameterType="int">
   SELECT g.*, u.user_id
FROM group g, user_group u
WHERE g.group_id=u.group_id AND u.user_id=#{user_id}
</select>
   </mapper>

第四步：测试

public static void testGetGroupAndUsers() {

   UserGroup userGroup = new UserGroup();
   SqlSession session = sqlSessionFactory.openSession();
try {
GroupMaper groupMaper = session.getMapper(GroupMaper.class);
Group group = groupMaper.getGroup(1);
System.out.println("Group => " + group.getGroupName());
   List<User> users = group.getUsers();
   for (User user : users) {
   System.out.println("\t:" + user.getId() + "\t"
   + user.getUsername());
   }
   } finally {
   session.close();
}
   }

public static void testAddUserGroup() {
UserGroup userGroup = new UserGroup();
   userGroup.setGroupId(1);
   userGroup.setUserId(2);
   SqlSession session = sqlSessionFactory.openSession();
   try {
   UserGroupMaper userGroupMaper = session
   .getMapper(UserGroupMaper.class);
   userGroupMaper.insertUserGroup(userGroup);

   session.commit();
   } finally {
session.close();
   }

}

public static void testAddUser() {
// TODO Auto-generated method stub
SqlSession session = sqlSessionFactory.openSession();
try {
User user = new User();
user.setUsername("User-name-1");
user.setMobile("13838009988");
UserMaper userMaper = session.getMapper(UserMaper.class);
userMaper.insertUser(user);
   session.commit();
   // System.out.println(user.getGroupId());
   } finally {
session.close();
   }
   }

public static void testAddGroup() {
// TODO Auto-generated method stub
SqlSession session = sqlSessionFactory.openSession();
try {
Group group = new Group();
group.setGroupName("用户组-1");

   //获取mapper
   GroupMaper groupMapper = session.getMapper(GroupMaper.class);
   groupMapper.insertGroup(group);
session.commit();
System.out.println(group.getGroupId());
   } finally {
session.close();
   }
   }

学习来源：https://www.yiibai.com/mybatis/mybatis-one2many.html#article-start

posted @ 2020-09-11 16:23 小窝蜗阅读(275) 评论(0) 编辑收藏举报

刷新页面返回顶部

小窝蜗

Mybatis学习四 （表关联）

公告

Mybatis学习四（表关联）