本模块主要是对mybatis关联映射中一对一关联的添加和查询操作的整理,希望可以帮助到大家,建表和创建maven工程就不再赘述了~

1.1  一对一映射:添加

1.1.1 实体类:

实体类
Users类
Details类
package com.qhit.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Users {
    private int UserId;
    private String UserName;
    private String UserPwd;
    private String UserRealName;
    private String UserImg;
    
}
package com.qhit.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import org.omg.PortableInterceptor.INACTIVE;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Details {
    private int DetailId;
    private  String UserAddr;
    private  String UserTel;
    private  String UserDesc;
    private  int Uid;
}

1.1.2 mybatis主配置文件(mybatis-config.xml): 

        配置mybatis-config.xml文件,使用properties标签引入外部文件来配置mysql连接信息,并注入UserMapper.xml文件以及DetailsMapper.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>
    <properties resource="jdbc.properties"></properties>
    <typeAliases>
        <typeAlias type="com.qhit.pojo.Users" alias="Users"></typeAlias>
        <typeAlias type="com.qhit.pojo.Details" alias="Details"></typeAlias>
    </typeAliases>
<environments default="mysql">
    <environment id="mysql">
        <transactionManager type="JDBC"></transactionManager>
        <dataSource type="POOLED">
            <property name="driver" value="${mysql_driver}"/>
            <property name="url" value="${mysql_url}"/>
            <property name="username" value="${mysql_user}"/>
            <property name="password" value="${mysql_password}"/>
        </dataSource>
    </environment>
</environments>
    <mappers>
        <mapper resource="mappers/UserMapper.xml"></mapper>
        <mapper resource="mappers/DetailsMapper.xml"></mapper>
    </mappers>
</configuration>

1.1.3 SqlSession-工具类封装 

        封装SqlSession类,内置getSqlSession(boolean IsAutoCommit)设置是否自动提交(默认为false手动提交,true是自动提交)、getMapper()方法;代码如下:

package com.qhit.Untils;

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.InputStream;

/**
 * Description: NewSsm
 * Created by WuHuaSen .
 * Created Date:  2022/4/1 16:23
 * Version:  V1.0
 */
public class MybatisUtil {
    //封装会话工厂
    private static SqlSessionFactory Factory;
    /**在进行对象的跨层传递的时候,使用ThreadLocal可以避免多次传递,打破层次间的约束
     */
    private static ThreadLocal<SqlSession> local = new ThreadLocal<SqlSession>();

    static {
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            Factory = sqlSessionFactoryBuilder.build(is);

        } catch (
                IOException e)

        {
            e.printStackTrace();
        }
    }
    //封装factory方法
    public  static SqlSessionFactory getFactory(){
        return Factory;
    }

    //封装sqlSession会话
    public static SqlSession getSqlSession(boolean IsAutoComiit) {
        SqlSession sqlSession = local.get();
        if (sqlSession == null) {
            sqlSession = Factory.openSession(IsAutoComiit);
            local.set(sqlSession);
        }
        return sqlSession;
    }

    //使用泛型封装getMapper
    public static <T extends Object> T getMapper(Class<T> c) {
        SqlSession sqlSession = getSqlSession(true);
        return sqlSession.getMapper(c);
    }
}

1.1.4 接口层(Dao层):

         DAO接口层有两个UserDao接口、detailsDo接口,user表中有用户的基本的信息,details表中有用户的详细信息,同一个用户有一个基本信息和一个详信息,所以在做添加操作的时候不仅要添加User信息,也要添加Detail类信息吗,而且二者共同有一个Uid;代码如下:

Dao层

UseDao接口

DetailsDao 接口
 * Created Date:  2022/4/5 10:05
 * Version:  V1.0
 */
public interface UserDao {
    //添加
    public int InsertUser(Users users);
}
package com.qhit.Dao;

import com.qhit.pojo.Details;

import java.util.List;


public interface DetailsDao {
    public int InsertDetail(Details details);

 
}

1.1.5 UserDao的映射文件:

        UserDaomapper.xml中主要是对UserDao层中添加方法的实现,需要注意的是,在实现添加方法时,要对User_id进行回填,因为要根据User_id对Details进行数据添加,代码如下:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qhit.Dao.UserDao">
    <sql id="User_column_list">
        user_id,user_name,user_pwd,user_realname,user_img
    </sql>
    <resultMap id="UserMappers" type="Users">
        <id column="user_id" property="UserId"></id>
        <result column="user_name" property="UserName"></result>
        <result column="user_pwd" property="UserPwd"></result>
        <result column="user_realname" property="UserRealName"></result>
        <result column="user_img" property="UserImg"></result>
    </resultMap>
    <!-- 添加-->
    <insert id="InsertUser" useGeneratedKeys="true" keyProperty="UserId">
        INSERT INTO users(user_id,user_name,user_pwd,user_realname,user_img)VALUES (#{UserId},#{UserName},#{UserPwd},#{UserRealName},#{UserImg})
    </insert>
</mapper>     

1.1.6 DetailDao的映射文件: 

        而DetailMapper.xml中则是对DetailDao层中的InsertDetail()方法的实现,代码如下:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qhit.Dao.DetailsDao">
    <resultMap id="DetalisMaper" type="Details">
        <id column="detail_id" property="DetailId"></id>
        <result column="user_addr" property="UserAddr"/>
        <result column="user_tel" property="UserTel"/>
        <result column="user_desc" property="UserDesc"/>
        <result column="uid" property="Uid"/>
    </resultMap>
    <sql id="detail_column_list">
        detail_id,user_addr,user_tel,user_desc,uid
    </sql>
    <insert id="InsertDetail">
        INSERT INTO details (detail_id,user_addr,user_tel,user_desc,uid) VALUES (#{DetailId},#{UserAddr},#{UserTel},#{UserDesc},#{Uid})
    </insert>
</mapper>     

1.1.7 测试类  

        测试类中首先通过工具类获取到UserDao中的InsertUser(users user)方法对用户的基本信息进行添加,由于在UserMapper.xml文件中设置了主键回填,所以在添加完成后就可以获取到用户的id(user_id)也就是Details中的Uid,将user_id通过detail中的setUid(users.getUserId())赋值给Uid,然后在执行detailDao中的nsertDetail(Detail details)方法即可;

package com.qhit.Dao;

import com.qhit.Untils.MybatisUtil;
import com.qhit.pojo.Details;
import com.qhit.pojo.Users;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * Description: mybatis002
 * Created by WuHuaSen .
 * Created Date:  2022/4/5 10:38
 * Version:  V1.0
 */
public class UserDaoTest {
    MybatisUtil mybatisUtil = new MybatisUtil();

    @Test
    public void InsertUser() {
        Users users = new Users(0, "wanger", "123456", "王二", "03.jpg",null);
        Details details = new Details(0, "南通市", "1235333", "宁愿笑着流泪", 0);
        // 用户注册,提交了基本信息和详情到servlet,servlet接收注册信息封装到User和details对象中
        SqlSession sqlSession = mybatisUtil.getSqlSession(false);//手动提交
        try {

            UserDao userDao = sqlSession.getMapper(UserDao.class);
            int i = userDao.InsertUser(users);
            details.setUid(users.getUserId());
            DetailsDao detailsDao = sqlSession.getMapper(DetailsDao.class);
            int j = detailsDao.InsertDetail(details);
            if (i == 1 && j == 1) {
                sqlSession.commit();
                System.out.println("-------添加成功!!!!------");
                System.out.println(users);
                System.out.println(details);
            }


        } catch (Exception e) {
            System.out.println(e);
            sqlSession.rollback();

        }


    }

}

         需要注意的是,测试的时候调用MybatisUtil中的getSession(Boolean isAutoCommit)的时候需要传递一个false,或者调用MybatisUtil中getSession()无参构造方法,设置为手动提交,因为要保证数据的一致性,即用户基本表和详情表同时添加成功才可以提交,否者回滚

2.1  一对一映射:查询(连接查询)

查询案例需求:根据用户的用户昵称(user_name)进行查询users表以及 details表中用户的详细信息。

2.1.1 实体类

        在进行查询的时候,由于需要两张表的信息,为了能把两张表关联起来,需要对实体类User类新增一个属性即private Details detailsInfo,而Details类无需进行改变;以此即可建立起两张表的信息。代码如下;

实体类
Users类
Details类
package com.qhit.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Users {
    private int UserId;
    private String UserName;
    private String UserPwd;
    private String UserRealName;
    private String UserImg;
    private Details detailsInfo;
    
}
package com.qhit.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import org.omg.PortableInterceptor.INACTIVE;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Details {
    private int DetailId;
    private  String UserAddr;
    private  String UserTel;
    private  String UserDesc;
    private  int Uid;
}

2.1.2  接口层(Dao层):

        在userDao中定义一个public List<Users> queryUser(String UserName)方法,根据用户名查询用户的详细信息,而detailDao中在连接查询时无需操作即可,所以这里不需要在其定义方法。代码如下;

Dao层
UserDao 接口
package com.qhit.Dao;

import com.qhit.pojo.Users;

import java.util.List;

/**
 * Description: mybatis002
 * Created by WuHuaSen .
 * Created Date:  2022/4/5 10:05
 * Version:  V1.0
 */
public interface UserDao {
    //根据user_name查询
    public List<Users> queryUser(String UserName);
}

2.1.3 UserDao的映射文件: 

        由于是查询操作所以需要指定映射关系,故需要建立ResultMap映射关系,而以往不同的时候,我们在此要做的是关联查询,在User实体类中添加了private Details detailsInfo,所以在建立对应关系的时候也需要对Details中的属性进行映射。代码如下:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qhit.Dao.UserDao">
    <sql id="User_column_list">
        user_id,user_name,user_pwd,user_realname,user_img
    </sql>
    <resultMap id="UserMappers" type="Users">
        <id column="user_id" property="UserId"></id>
        <result column="user_name" property="UserName"></result>
        <result column="user_pwd" property="UserPwd"></result>
        <result column="user_realname" property="UserRealName"></result>
        <result column="user_img" property="UserImg"></result>
        <result column="detail_id" property="detailsInfo.DetailId"></result>
        <result column="user_addr" property="detailsInfo.UserAddr"></result>
        <result column="user_tel" property="detailsInfo.UserTel"></result>
        <result column="user_desc" property="detailsInfo.UserDesc"></result>
       
    <!-- 根据 userName查询-->
    <select id="queryUser" resultMap="UserMappers">
  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 WHEREuser_name=#{UserName};
    </select>
   
</mapper>     

2.1.4 测试类

        测试类直接通过工具类调用getSession()来获取查询方法就可以;

package com.qhit.Dao;

import com.qhit.Untils.MybatisUtil;
import com.qhit.pojo.Details;
import com.qhit.pojo.Users;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * Description: mybatis002
 * Created by WuHuaSen .
 * Created Date:  2022/4/5 10:38
 * Version:  V1.0
 */
public class UserDaoTest {
    MybatisUtil mybatisUtil = new MybatisUtil();
    //根据姓名查询
    @Test
    public void queryUser() {
        UserDao userDao = mybatisUtil.getMapper(UserDao.class);
        List<Users> users = userDao.queryUser("wanger");
        System.out.println(users);
    }
  
}

2.1.5 效果展示:

 2.2 一对一映射:查询(子查询)

2.2.1 接口层(Dao)层

        子查询的实体类与连接查询一致无需改动,主要是DetailsDao接口中添加了一个QueryDatailByUid方法以及mapper映射文件的改动。具体改动如下:

Dao层

UseDao接口

DetailsDao 接口
 * Created Date:  2022/4/5 10:05
 * Version:  V1.0
 */
public interface UserDao {
    //根据userid查询
public List<Users> queryUser(String UserName);
}
package com.qhit.Dao;

import com.qhit.pojo.Details;

import java.util.List;


public interface DetailsDao {
    public List<Details> QueryDatailByUid(int Uid);
 
}

2.2.2 UserDao的映射文件: 

        在UserDaomapper.xml相比关联查询,取消了部分映射关系的添加,而是使用accociation标签调用了子查询,关联查询一个对象,通过查询获取User_id,并通过accociation标签传递给com.qhit.Dao.DetailsDao.QueryDatailByUid(),而在UserDaomapper.xml中写SQL语句的时候只需要写查询User表即可;代码如下:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qhit.Dao.UserDao">
    <sql id="User_column_list">
        user_id,user_name,user_pwd,user_realname,user_img
    </sql>
    <resultMap id="UserMappers" type="Users">
        <id column="user_id" property="UserId"></id>
        <result column="user_name" property="UserName"></result>
        <result column="user_pwd" property="UserPwd"></result>
        <result column="user_realname" property="UserRealName"></result>
        <result column="user_img" property="UserImg"></result>
        <!-- accociation 调用了子查询,关联查询一个对象-->
        <association property="detailsInfo" select="com.qhit.Dao.DetailsDao.QueryDatailByUid" column="user_id"/>
    </resultMap>
    <!-- 根据 userName查询-->
    <select id="queryUser" resultMap="UserMappers">
        SELECT user_id,user_name,user_pwd,user_realname,user_img FROM users WHERE user_name=#{UserName};
    </select>
 
</mapper>     

2.2.3 DetalisMaper的映射文件: 

        在DetalisMaper.xml主要是实现了Detalis接口中的QueryDatailByUid()方法;代码如下:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qhit.Dao.DetailsDao">
    <resultMap id="DetalisMaper" type="Details">
        <id column="detail_id" property="DetailId"></id>
        <result column="user_addr" property="UserAddr"/>
        <result column="user_tel" property="UserTel"/>
        <result column="user_desc" property="UserDesc"/>
        <result column="uid" property="Uid"/>
    </resultMap>
    <sql id="detail_column_list">
        detail_id,user_addr,user_tel,user_desc,uid
    </sql>
    <select id="QueryDatailByUid" resultMap="DetalisMaper">
        SELECT
        <include refid="detail_column_list"/>
        FROM details WHERE uid=#{Uid};
    </select>
</mapper>     

2.2.4 Test测试类: 

        子查询的测试类与关联查询代码一致,无需任何更改,即可完成对数据的一对一的关联映射查询。代码如下:


package com.qhit.Dao;

import com.qhit.Untils.MybatisUtil;
import com.qhit.pojo.Details;
import com.qhit.pojo.Users;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * Description: mybatis002
 * Created by WuHuaSen .
 * Created Date:  2022/4/5 10:38
 * Version:  V1.0
 */
public class UserDaoTest {
    MybatisUtil mybatisUtil = new MybatisUtil();
    //根据姓名查询
    @Test
    public void queryUser() {
        UserDao userDao = mybatisUtil.getMapper(UserDao.class);
        List<Users> users = userDao.queryUser("wanger");
        System.out.println(users);
    }
  
}

 

2.2.5 效果展示:

        可以发现,无论是子查询还是连接查询,最后的结果都是一样的,相比之下连接查询更容易理解,但是书写的SQL语句较长;而子查询SQL语句较简单~


—–Thanks


版权声明:本文为weixin_45295447原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/weixin_45295447/article/details/123973383