创建表
创建表对应的 JavaBean 对象
package com.tanlei.newer.model; import java.util.List; /** * @author:Mr.Tan * @Create:2018-11-05-15-07 **/ public class User { private int id; private String username; private String mobile; private List<Group> groups; 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 getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile; } public List<Group> getGroups() { return groups; } public void setGroups(List<Group> groups) { this.groups = groups; } }
package com.tanlei.newer.model; import java.util.List; /** * @author:Mr.Tan * @Create:2018-11-05-15-07 **/ public class Group { private int groupId; private String groupName; private List<User> users; public int getGroupId() { return groupId; } public void setGroupId(int groupId) { this.groupId = groupId; } public String getGroupName() { return groupName; } public void setGroupName(String groupName) { this.groupName = groupName; } public List<User> getUsers() { return users; } public void setUsers(List<User> users) { this.users = users; } }
package com.tanlei.newer.model; /** * @author:Mr.Tan * @Create:2018-11-05-15-08 **/ public class UserGroup { private int userId; private int groupId; public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } public int getGroupId() { return groupId; } public void setGroupId(int groupId) { this.groupId = groupId; } }
配置文件
<?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.tanlei.newer.model.User" /> <typeAlias alias="UserGroup" type="com.tanlei.newer.model.UserGroup" /> <typeAlias alias="Group" type="com.tanlei.newer.model.Group" /> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8" /> <property name="username" value="root" /> <property name="password" value="password" /> </dataSource> </environment> </environments> <mappers> <!-- // power by http://www.yiibai.com --> <mapper resource="com/tanlei/newer/model/UserMaper.xml" /> <mapper resource="com/tanlei/newer/model/GroupMaper.xml" /> <mapper resource="com/tanlei/newer/model/UserGroupMaper.xml" /> </mappers> </configuration>
Group.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.tanlei.newer.model.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.tanlei.newer.model.UserGroupMaper.getUsersByGroupId" /> </resultMap> <select id="getGroup" resultMap="resultGroupMap_1" parameterType="int"> SELECT * FROM 'group' WHERE group_id=#{id} </select> </mapper>
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"> <mapper namespace="com.tanlei.newer.model.UserMaper"> <parameterMap type="User" id="parameterUserMap"> <parameter property="id"/> <parameter property="username"/> <parameter property="mobile"/> </parameterMap> <insert id="insertUser" parameterMap="parameterUserMap"> INSERT INTO user(username,mobile) VALUES(#{username},#{mobile}); </insert> <resultMap type="User" id="resultUser"> <result property="id" column="group_id"/> <result property="name" column="name"/> <collection property="groups" column="id" select="com.tanlei.newer.model.UserGroupMaper.getGroupsByUserId"/> </resultMap> <select id="getUser" resultMap="resultUser" parameterType="int"> SELECT * FROM user WHERE id=#{id} </select> </mapper>
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.tanlei.newer.model.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> <!-- 根据一个用户组ID,查看这个用户组下的所有用户 --> <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> <!-- 根据一个用户ID,查看这个用户所对应的组--> <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>
测试程序运行
package com.tanlei.newer.test; import com.tanlei.newer.model.*; 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; /** * @author:Mr.Tan * @Create:2018-11-07-13-14 **/ public class UserGroup { public static Reader reader; public static SqlSessionFactory sqlSessionFactory; static { try { reader= Resources.getResourceAsReader("config/UGP.xml"); sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader); } catch (IOException e) { e.printStackTrace(); } } public static SqlSessionFactory getSqlSession(){ return sqlSessionFactory; } public static void main(String[] args) { //添加一个组织 //testAddGroup(); //添加一个用户 //testAddUser(); //添加一个用户和组织 //testAddUserGroup(); //根据组织id查询用户信息 //testGetGroupAndUsers(); //根据用户id查询对应的组织 //getGroupsByUserId(); } private static void getGroupsByUserId() { SqlSession session=sqlSessionFactory.openSession(); try { //通过接口类 UserGroupMapper userGroupMapper=session.getMapper(UserGroupMapper.class); //调用接口类的添加方法 List<Group> groups=userGroupMapper.getGroupsByUserId(1); for(Group group:groups){ System.out.println(group.getGroupName()); System.out.println(group.getGroupId()); } //提交会话 session.commit(); }finally { //关闭会话 session.close(); } } private static void testGetGroupAndUsers() { SqlSession session=sqlSessionFactory.openSession(); try { //通过接口类 UserGroupMapper userGroupMapper=session.getMapper(UserGroupMapper.class); //调用接口类的添加方法 List<User> users=userGroupMapper.getUsersByGroupId(1); for(User user:users){ System.out.println(user.getUsername()); System.out.println(user.getMobile()); System.out.println(user.getId()); } //提交会话 session.commit(); }finally { //关闭会话 session.close(); } } public static void testAddUserGroup(){ SqlSession session=sqlSessionFactory.openSession(); try { UserGroups usergroup=new UserGroups(); usergroup.setUserId(2); usergroup.setGroupId(2); //通过接口类 UserGroupMapper userGroupMapper=session.getMapper(UserGroupMapper.class); //调用接口类的添加方法 userGroupMapper.insertUserGroup(usergroup); //提交会话 session.commit(); }finally { //关闭会话 session.close(); } } public static void testAddUser(){ SqlSession session=sqlSessionFactory.openSession(); try { User user=new User(); user.setUsername("User-name-1"); user.setMobile("13838009988"); //通过接口类 UserMapper userMapper=session.getMapper(UserMapper.class); //调用接口类的添加方法 userMapper.insertUser(user); //提交会话 session.commit(); }finally { //关闭会话 session.close(); } } public static void testAddGroup(){ SqlSession session=sqlSessionFactory.openSession(); try { Group group=new Group(); group.setGroupName("用户组-1"); //通过接口类 GroupMaper groupMaper=session.getMapper(GroupMaper.class); //调用接口类的添加方法 groupMaper.insertGroup(group); //提交会话 session.commit(); System.out.println(group.getGroupId()); //返回一个组织信息 System.out.println(groupMaper.getGroup(2)); }finally { //关闭会话 session.close(); } } }