一对一的关系在生活中还是比较常见的,比如一本书有一个作者(暂时不考虑一本书多个作者的情况)、一个人对应一个身份证等待。此外,一对一查询也是比较简单的,下面将以一个人、一个身份证为例,说明如何使用mybatis进行关联查询。同样的,相关的代码,也会放在GitHub上,GitHub地址:https://github.com/qiuxinfa/mybatis-study。我们先看下项目的目录结构:
1.创建maven工程,添加mybatis和mysql驱动依赖:
<dependencies>
<!--mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<!-- mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
</dependencies>
2.准备两个表:t_user和t_card
(1)t_card表及其记录:
-- ----------------------------
-- Table structure for `t_card`
-- ----------------------------
DROP TABLE IF EXISTS `t_card`;
CREATE TABLE `t_card` (
`id` varchar(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键id',
`card_num` varchar(18) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '身份证号码',
`card_time` date DEFAULT NULL COMMENT '有效日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='个人身份证';
-- ----------------------------
-- Records of t_card
-- ----------------------------
INSERT INTO `t_card` VALUES ('01', '440221199609195912', '2020-04-30');
INSERT INTO `t_card` VALUES ('02', '440221199609195915', '2020-06-06');
(2)t_user表及其记录:
-- ----------------------------
-- Table structure for `t_user`
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` varchar(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键id',
`username` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
`password` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '密码',
`is_valid` int(1) DEFAULT NULL COMMENT '用户是否有效,0无效,1有效',
`card_id` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户信息表';
-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('123', '张三', '123456', '1', '02');
INSERT INTO `t_user` VALUES ('456', '李四', '123456', '0', '01');
3.创建实体:User和Card
(1)Card.java
public class Card implements Serializable{
private String id;
private String cardNum;
private Date cardTime;
//省略set、get、toString
}
(2)User.java
public class User implements Serializable{
private String id;
private String username;
private String password;
private Integer isValid;
//一个用户一个身份证
private Card card;
//省略set、get、toString
}
注意到:User类中,持有Card对象的引用,而数据库中有一个外键card_id,指向t_card表的id
4.创建UserMapper接口:
public interface UserMapper {
//根据id查询用户及其身份证
User getUserById(String id);
}
5.创建映射文件UserMapper.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">
<!--namespace 因为要接口绑定,所以是接口的全限定类名-->
<mapper namespace="com.qxf.mapper.UserMapper">
<!-- 自定义结果映射:
id:是主键
property:是Java对象的属性名
column:是数据库中列名,或者查询中取的列的别名
-->
<resultMap id="userMap" type="User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<result property="isValid" column="is_valid"></result>
<!-- 一对一关联查询,使用association,其中:
使用association的property属性card:是User类中的属性名称,对应就可以了(private Card card);
javaType:是User类中的card这个属性,属于哪个Java类型,这里当然是属性Card
id中的column是t_user表的card_id,当然也可以取个别名,只要对应就可以了
-->
<association property="card" javaType="Card">
<id property="id" column="card_id"></id>
<result property="cardNum" column="card_num"></result>
<result property="cardTime" column="card_time"></result>
</association>
</resultMap>
<!--id 与接口的方法名相同,注意:
这里的返回值类型不再是resultType了,而是resultMap
而且resultMap的值,就是上面<resultMap>标签的id属性值
-->
<select id="getUserById" parameterType="string" resultMap="userMap">
select u.id,u.username,u.password,u.is_valid,u.card_id,c.card_num,c.card_time
from t_user u,t_card c
where u.card_id = c.id
and u.id=#{id}
</select>
</mapper>
6.创建mybatis的配置文件:
<?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>
<!-- 为了打印出执行的sql语句-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!-- 自定义别名 -->
<typeAliases>
<package name="com.qxf.pojo"></package>
</typeAliases>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<!-- & 代表特殊符号 & -->
<property name="url" value="jdbc:mysql://localhost:3306/mybatis_test?serverTimezone=UTC&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="19930919"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
注意到,比之前的配置多了一项:
<!-- 自定义别名 -->
<typeAliases>
<package name="com.qxf.pojo"></package>
</typeAliases>
这个表示,com.qxf.pojo表下面的所有类都有了别名,别名为类名,首字母大小写均可,所以我们在UserMapper.xml中可以这样写,而不用写类的全限定类名了:
<resultMap id="userMap" type="User">
7.测试
public class One2OneQuery {
public static void main(String[] args) throws IOException{
//读取配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
//根据配置信息,创建SqlSession工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
//SqlSession工厂创建SqlSession
SqlSession sqlSession = factory.openSession();
//获取接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById("123");
System.out.println(user);
}
}
查看测试结果:
可以看到,通过查询用户,把他的身份证也查出来了