一对一查询
需求：一个用户只能有一个身份证信息，查询所有用户的身份证信息和用户信息以及查询指定用户的身份证信息和用户信息。
- 创建表Idcard并录入数据
create table idCard ( id int auto_increment primary key, idNumber varchar(18) null, address text null, birth date null, constraint idCard_int_uindex unique (id) ); INSERT INTO mybatisLesson.idCard (id, idNumber, address, birth) VALUES (1, '111555198811091111', 'A省B市C街道D小区E栋F室', '1988-11-09'); INSERT INTO mybatisLesson.idCard (id, idNumber, address, birth) VALUES (2, '111555199101091112', 'A省B市C街道D小区E栋F室', '1991-01-09'); INSERT INTO mybatisLesson.idCard (id, idNumber, address, birth) VALUES (3, '111555200005131113', 'A省B市C街道D小区E栋F室', '2000-05-13'); - 创建表user并生成数据
create table user ( id int auto_increment primary key, name varchar(15) null, age int default 1 null, idCard int null, constraint user_id_uindex unique (id), constraint user_idCard_id_fk foreign key (idCard) references idCard (id) on update cascade on delete cascade ); INSERT INTO mybatisLesson.user (id, name, age, idCard) VALUES (1, '张三', 34, 1); INSERT INTO mybatisLesson.user (id, name, age, idCard) VALUES (2, '李四', 31, 2); INSERT INTO mybatisLesson.user (id, name, age, idCard) VALUES (3, '王五', 22, 3); - 创建实体类IdCard
public class IdCard { private long id; private String idNumber; private String address; private java.sql.Date birth; //Getter&Setter&ToString } - 创建实体类User
public class User { private long id; private String name; private long age; private IdCard idCard; //Getter&Setter&ToString } - 创建UserMapper
@Mapper public interface UserMapper { List<User> selectAllUsers(); List<User> selectUsersByIdards(List<IdCard> idCards); } - 创建UserMapper.xml
<mapper namespace="com.blacol.mapper.UserMapper"> <resultMap id="userInfo" type="user"> <id property="id" column="userId"></id> <result property="name" column="name"></result> <result property="age" column="age"></result> <association property="idCard" javaType="idCard"> <id property="id" column="idcardId"></id> <result property="idNumber" column="idNumber"></result> <result property="address" column="address"></result> <result property="birth" column="birth"></result> </association> </resultMap> <select id="selectAllUsers" resultMap="userInfo"> select user.id userId, name, age, idCard, idinfo.id idcardId, idNumber, address, birth from mybatisLesson.user,mybatisLesson.idCard idinfo where user.idCard=idinfo.id </select> <select id="selectUsersByIdards" resultMap="userInfo"> select user.id userId, name, age, idCard, idinfo.id idcardId, idNumber, address, birth from mybatisLesson.user,mybatisLesson.idCard idinfo <where> idCard=idinfo.id and idinfo.id in <foreach collection="idCards" item="idc" open="(" separator="," close=")"> #{idc} </foreach> </where> </select> </mapper> - 创建Mybaits配置文件MyBatis-cfg.xml
<configuration> <typeAliases> <package name="com.blacol.entity"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///mybatisLesson"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <package name="com.blacol.mapper"/> </mappers> </configuration> - 创建测试类
public class OneToOneTest { public OneToOneTest() throws IOException { } private SqlSession getSqlSession() throws IOException { SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder(); InputStream is = Resources.getResourceAsStream("MyBatis-cfg.xml"); SqlSessionFactory fac = builder.build(is); return fac.openSession(true); } private UserMapper userMapper=getSqlSession().getMapper(UserMapper.class); @Test public void test1(){ System.out.println("获取所有用户"); List<User> users = userMapper.selectAllUsers(); System.out.println(users); System.out.println("获取Idcard-id为1，3的用户"); List<Integer> ids=new ArrayList<>(); ids.add(1);ids.add(3); List<User> users1 = userMapper.selectUsersByIdards(ids); System.out.println(users1); } } - 让xml文件参与打包（略，可以看第2.1章）
- 运行测试类
总结：
mapper映射文件中有一个<resultMap>标签。当返回值的类中还有其他类或者其他类的数组/集合那么就需要使用resultMap。以上面的例子为例，User类中还有一个IdCard类的List，所以要使用resultMap标签来对属性和数据表列做映射。
要使用select需要将select的resultType属性改为resultMap并输入resultMap的id作为值。（不使用resultMap属性会报错）
resultMap有下列子标签：
| 子标签名 | 用途 | 属性 |
|---|---|---|
| id | 主键列需要使用id | column：对应的数据表列 property：对应的属性 |
| result | 非主键列需要使用这个 | column和property |
| association | 另一个类的属性要通过它来确立映射关系 | property：哪个属性是另一个类 javaType：另一个类是什么类（支持简写） |
| collection | 另一个类的数组或者集合 | property：哪个属性是另一个类的数组或集合 ofType：这个数组或集合是什么类型的 |
以上面的例子为例：
<resultMap id="userInfo" type="user">
<id property="id" column="userId"></id>
<result property="name" column="name"></result>
<result property="age" column="age"></result>
<association property="idCard" javaType="idCard">
<id property="id" column="idcardId"></id>
<result property="idNumber" column="idNumber"></result>
<result property="address" column="address"></result>
<result property="birth" column="birth"></result>
</association>
</resultMap>
resultMap的属性id是该resultMap的唯一标识（名字），type是数据类型。我们做的是User类的映射，所以type是user（也可以是com.blacol.entity.User）。
因为user表中id列是主键，它对应的是User对象中的id属性，所以property的值是id，而column并不是id，因为在查找user和idcard表时都有id列，为了避免歧义，给user.id列起了一个别名叫userId（这个是列名歧义问题，在本章后面会详细介绍该问题），所以column的值是userId而不是id。
其他属性如name和age都不是主键所以用result标签，property是name和age,column是name和age。
因为User类有一个IdCard类的对象叫idCard，所以对于idCard属性要使用association标签。association标签的property=idCad，javaType=idCard（也可以是com.blacol.entity.IdCard）。
现在关注点来到IdCard类里，因为在idcard数据表中id列是主键，所以使用id标签，property为id。又因为在user表中也有一个Id列所以起了一个别名叫idcardId，column=“idcardId”，其他的属性如idNumber,address,birth都和数据表中的列一一对应，所以直接用result即可。
使用上面的resultMap后Mybatis就可以准确的返回结果。
一对多查询
需求：一个用户可以参加多个活动，要求显示某个用户的信息以及该用户参加的活动有哪些。
继续使用一对一查询时的代码。
- 复制User类并起名为User2
- 删除User2中的idCard属性并添加activities属性以及相应的getter/setter/toString
public User2{ ... // private IdCard idCard; private List<Activity> activities; //Getter、Setter、ToString } - 创建activity表并插入数据
create table activity ( id int auto_increment primary key, name varchar(10) null, constraint activity_id_uindex unique (id) ); INSERT INTO mybatisLesson.activity (id, name) VALUES (1, '养老院义工'); INSERT INTO mybatisLesson.activity (id, name) VALUES (2, '孤儿院义工'); INSERT INTO mybatisLesson.activity (id, name) VALUES (3, '支教活动'); INSERT INTO mybatisLesson.activity (id, name) VALUES (4, '一起做饭'); - 创建actjoin表并插入数据
create table actjoin ( user int null, activity int null, constraint actjoin_activity_id_fk foreign key (activity) references activity (id) on update cascade on delete cascade, constraint actjoin_user_id_fk foreign key (user) references user (id) on update cascade on delete cascade ); INSERT INTO mybatisLesson.actjoin (user, activity) VALUES (1, 1); INSERT INTO mybatisLesson.actjoin (user, activity) VALUES (1, 4); INSERT INTO mybatisLesson.actjoin (user, activity) VALUES (1, 2); INSERT INTO mybatisLesson.actjoin (user, activity) VALUES (2, 1); INSERT INTO mybatisLesson.actjoin (user, activity) VALUES (2, 3); INSERT INTO mybatisLesson.actjoin (user, activity) VALUES (3, 2); INSERT INTO mybatisLesson.actjoin (user, activity) VALUES (3, 3); - 创建Activity、User2Mapper和User2Mapper.xml
Activity：
User2Mapperpublic class Activity { private long id; private String name;//Getter,Setter,ToString }
User2Mapper.xml@Mapper public interface User2Mapper { User2 selectUserJoinedActivityByUserId(int id); }<mapper namespace="com.blacol.mapper.User2Mapper"> <resultMap id="userWithActivity" type="user2"> <id column="userId" property="id"></id> <result column="name" property="name"></result> <collection property="activities" ofType="activity"> <id column="id" property="id"></id> <result column="actName" property="name"></result> </collection> </resultMap> <select id="selectUserJoinedActivityByUserId" resultMap="userWithActivity"> select user.id userId,user.name,activity.id,activity.name actName from mybatisLesson.user,mybatisLesson.activity,mybatisLesson.actjoin where actjoin.activity=activity.id and actjoin.user=user.id and user.id=#{id} </select> </mapper> - 创建测试类-OneToMore
public class OneToMoreTest { public OneToMoreTest() throws IOException { } private SqlSession getSqlSession() throws IOException { SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder(); InputStream is = Resources.getResourceAsStream("MyBatis-cfg.xml"); SqlSessionFactory fac = builder.build(is); return fac.openSession(true); } private User2Mapper user2Mapper=getSqlSession().getMapper(User2Mapper.class); @Test public void test1(){ int num=3; User2 users = user2Mapper.selectUserJoinedActivityByUserId(num); System.out.println("获取"+num+"号用户参加的活动"); System.out.println("姓名："+users.getName()+"\n参加的活动："); users.getActivities().forEach(e-> System.out.println(e.getName())); } } - 运行测试类
从运行结果可以得知Mybatis自动将查询到的数据装填进数组中。
多对多查询
需求：一个用户可以参加多个活动，一个活动可以由多个用户参与。要求显示某个用户的信息、该用户参加的活动以及参加这些活动的还有哪些用户。
在一对多查询案例的基础上进行修改
-
修改Activity类，添加属性
List<User2> users以及相应的getter,setter和tostringpublic Activity{ ... private List<User2> users; //Getter，Setter，toString } -
创建ActivityMapper和ActivityMapper.xml
@Mapper public interface ActivityMapper { List<User2> selectUsersByActivity(@Param("active") int active ,@Param("user") User2 user); }<mapper namespace="com.blacol.mapper.ActivityMapper"> <select id="selectUsersByActivity" resultType="user2"> select user.id ,user.name from mybatisLesson.activity,mybatisLesson.actjoin,mybatisLesson.user where user.id=actjoin.user and activity.id=actjoin.activity and user.id!=#{user.id} and activity.id =#{active} </select> </mapper> -
创建测试类MoreToMore
public class MoreToMoreTest { public MoreToMoreTest() throws IOException { } private SqlSession getSqlSession() throws IOException { SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder(); InputStream is = Resources.getResourceAsStream("MyBatis-cfg.xml"); SqlSessionFactory fac = builder.build(is); return fac.openSession(true); } private User2Mapper user2Mapper=getSqlSession().getMapper(User2Mapper.class); private ActivityMapper activityMapper=getSqlSession().getMapper(ActivityMapper.class); @Test public void test1(){ int num=1; User2 users = user2Mapper.selectUserJoinedActivityByUserId(num); System.out.println("获取"+num+"号用户参加的活动"); System.out.println("姓名："+users.getName()+"\n参加的活动："); List<Activity> activities = users.getActivities(); for (Activity ac:activities){ System.out.println("参加"+ac.getName()+"的还有："); List<User2> users1 = activityMapper.selectUsersByActivity((int) ac.getId(), users); users1.forEach(u-> System.out.println(u.getName()+" ")); } } } -
运行测试类。
网友评论