一对一关联
实例: 用户和详情
1、先创建数据表
用户信息表:
create table users(
user_id int primary key auto_increment,
user_name varchar(20) not null unique,
user_pwd varchar(20) not null,
user_realname varchar(32) not null,
user_img varchar(100)
);
用户详情表
create table details(
detail_id int primary key auto_increment,
user_addr varchar(50) not null,
user_tel varchar(11) not null,
user_desc varchar(200),
uid int not null unique,
constraint FX_USER foreign key(uid) references users(user_id)
);
2、创建实体类
package com.ccl.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* @author: 13749
* @date: 2021/10/15 22:46
* @description:
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Users {
private Integer user_id;
private String user_name;
private String user_pwd;
private String user_realname;
private String user_img;
}
package com.ccl.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* @author: 13749
* @date: 2021/10/15 22:48
* @description:
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Details {
private Integer detail_id;
private String user_addr;
private String user_desc;
private Integer uid;
}
3、创建dao接口
public interface DetailDao {
public int insertDetail(Detail detail);
}
public interface UserDao {
//添加
public int insertUser(User users);
//查询单个用户
public User queryUser(String userName);
}
4、配置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.ccl.dao.UserDao">
<insert id="insertUser" useGeneratedKeys="true" keyProperty="user_id">
insert into users (user_name,user_pwd,user_realname,user_img) values(#{user_name},#{user_pwd},#{user_realname},#{user_img})
</insert>
<sql id="userId">
user_id,user_name,user_pwd,user_realname,user_img
</sql>
<!-- resultType="com.ccl.pojo.User" 可以用但是不建议用 -->
<resultMap id="userMap" type="User">
<id column="user_id" property="user_id"></id>
<result column="user_name" property="user_name"></result>
<result column="user_pwd" property="user_pwd"></result>
<result column="user_realname" property="user_realname"></result>
<result column="user_img" property="user_img"></result>
</resultMap>
<select id="queryUser" resultMap="userMap">
select user_id,user_name,user_pwd,user_realname,user_img
from users where user_name=#{userName}
</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.ccl.dao.DetailDao">
<insert id="insertDetail">
insert into details (user_addr,user_tel,user_desc,uid)
values(#{user_addr},#{user_tel},#{user_desc},#{uid})
</insert>
</mapper>
5、在mybatis-config.xml配置文件中添加Mapper映射
<mappers>
<mapper resource="Mappers/UserMapper.xml"></mapper>
<mapper resource="Mappers/DetailMapper.xml"></mapper>
</mappers>
6、编写测试类
package com.ccl.dao;
import com.ccl.Utils.MyBatisUtil;
import com.ccl.pojo.Detail;
import com.ccl.pojo.User;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
/**
* @author: 13749
* @date: 2021/10/15 22:57
* @description:
*/
public class UserDaoTest {
@Test
public void testInsertUser(){
//用户注册提交的基本信息和详情到Servlet,Servlet接收注册信息封装到Detail和User中
User user = new User(0,"赵六","123123","赵六","03.jpg");
Detail detail = new Detail(0,"湖北省武汉市","14334123126","有个性才签名",0);
SqlSession sqlSession = MyBatisUtil.getSqlSession();
int i = 0;
int i1 = 0;
try {
UserDao userDao = sqlSession.getMapper(UserDao.class);
i = userDao.insertUser(user);
detail.setUid(user.getUser_id());
DetailDao detailDao = sqlSession.getMapper(DetailDao.class);
i1 = detailDao.insertDetail(detail);
System.out.println(i);
System.out.println(i1);
sqlSession.commit();
} catch (Exception e) {
sqlSession.rollback();
}
}
@Test
public void testQuerUser(){
UserDao userDao = MyBatisUtil.getMapper(UserDao.class);
User user = userDao.queryUser("王五");
System.out.println(user);
}
}
一对一关联查询
在查询用户的同时关联查询出与之对应的详情表
- 实体
映射文- 连接查询(第一种封装方案)
<resultMap id="userMap" type="User">
<id column="user_id" property="user_id"></id>
<result column="user_name" property="user_name"></result>
<result column="user_pwd" property="user_pwd"></result>
<result column="user_realname" property="user_realname"></result>
<result column="user_img" property="user_img"></result>
<result column="detail_id" property="detail.detail_id"></result>
<result column="user_addr" property="detail.user_addr"></result>
<result column="user_tel" property="detail.user_tel"></result>
<result column="user_desc" property="detail.user_desc"></result>
</resultMap>
<select id="queryUser" resultMap="userMap">
select user_id,user_name,user_pwd,user_realname,user_img,detail_id,user_addr,user_tel,user_desc
from users u
inner join details d
on u.user_id=d.uid
where u.user_name=#{userName}
</select>
- 子查询(第二种方案)
<resultMap id="userMap" type="User">
<id column="user_id" property="user_id"></id>
<result column="user_name" property="user_name"></result>
<result column="user_pwd" property="user_pwd"></result>
<result column="user_realname" property="user_realname"></result>
<result column="user_img" property="user_img"></result>
<!-- 表示调用子查询,关联查询一个对象 -->
<association property="detail" select="com.ccl.dao.DetailDao.queryDetailByUid" column="user_id"></association>
</resultMap>
<select id="queryUser" resultMap="userMap">
select user_id,user_name,user_pwd,user_realname,user_img
from users
where user_name=#{userName}
</select>
<resultMap id="detailMap" type="Detail">
<id column="detail_id" property="detail_id" ></id>
<result column="user_addr" property="user_addr" ></result>
<result column="user_tel" property="user_tel" ></result>
<result column="user_desc" property="user_desc" ></result>
</resultMap>
<select id="queryDetailByUid" resultMap="detailMap">
select detail_id,user_addr,user_tel,user_desc,uid
from details where uid=#{uid}
</select>