1. 首页
  2. 软件开发
  3. mybatis复杂映射开发

mybatis复杂映射开发

  • 发布于 2024-08-19
  • 0 次阅读

官网地址

一对一查询

模型介绍

⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对⼀查询的需求 ：查询⼀个订单，与此同时查询出该订单所属的⽤户

对应的sql语句：

select * from orders o,user u where o.uid=u.id;

查询结果

创建实体类

public class Order {
   
    private Integer id;
    private Date orderTime;
    private Double total;
	//每个订单对应唯一一个用户
    private User user;
}

public class User {
   
    private Integer id;
    private String username;
    //每个用户可以有多个订单
    private List<Order> orders;
}

创建Dao接口

public interface IOrderMapper {
   
    List<Order> findAll();
}

编写SqlMapConfig配置文件

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <typeAliases>
        <package name="com.elvis.pojo"/>
    </typeAliases>
    
  	<environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.137.144:3306/self_mybatis?useSSL=false&amp;characterEncoding=UTF-8"></property>
                <property name="username" value="root"></property>
                <property name="password" value="123456"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.elvis.mapper"/>
    </mappers>
</configuration>

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.elvis.mapper.IOrderMapper">
    <!--<cache type=""></cache>-->
    <cache type="org.mybatis.caches.redis.RedisCache"></cache>

    <resultMap id="orderMap" type="com.elvis.pojo.Order">
        <result column="id" property="id"></result>
        <result column="ordertime" property="orderTime"></result>
        <result column="total" property="total"></result>
        <association property="user" javaType="com.elvis.pojo.User">
            <result column="uid" property="id"></result>
            <result column="username" property="username"></result>
        </association>
    </resultMap>

    <select id="findAll" resultMap="orderMap">
        SELECT * FROM orders o, user_t u WHERE o.`uid`=u.`id`;
    </select>
</mapper>

也可以使用下面这种配置方式

<mapper namespace="com.lagou.mapper.OrderMapper">
<resultMap id="orderMap" type="com.lagou.domain.Order">
<result column="uid" property="user.id"></result>
<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"></result>
</resultMap>
<select id="findAll" resultMap="orderMap">
select * from orders o,user u where o.uid=u.id
</select>
</mapper>

测试方法

    @Test
    public void findAllTest() throws IOException {
   
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = builder.build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession();
        IOrderMapper mapper = session.getMapper(IOrderMapper.class);
        List<Order> all = mapper.findAll();
        for (Order order : all) {
   
            System.out.println(order);
        }
    }

测试结果

一对多查询

模型介绍

⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对多查询的需求 ：查询⼀个⽤户，与此同时查询出该⽤户具有的订单

对应的sql语句：

SELECT u.*, o.`id` oid, o.`ordertime`,o.`total` FROM user_t u LEFT JOIN orders o ON o.`uid`=u.`id`;

查询结果：

创建IUserDao接口

public interface IUserMapper {
   
    List<User> findAll();
}

编写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.elvis.mapper.IUserMapper">
    <resultMap id="userMap" type="com.elvis.pojo.User">
        <result column="id" property="id"></result>
        <result column="username" property="username"></result>
        <collection property="orders" ofType="com.elvis.pojo.Order">
            <result column="oid" property="id"></result>
            <result column="ordertime" property="orderTime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>


    <select id="findAll" resultMap="userMap">
        SELECT u.*, o.`id` oid, o.`ordertime`,o.`total` FROM user_t u LEFT JOIN orders o ON o.`uid`=u.`id`;
    </select>

</mapper>

测试方法

@Test
    public void findAllUserTest() throws IOException {
   
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = builder.build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession();
        IUserMapper mapper = session.getMapper(IUserMapper.class);
        List<User> users = mapper.findAll();
        for (User user : users) {
   
            System.out.println(user);
        }
    }

测试结果

多对多查询

模型介绍

⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤
多对多查询的需求 ：查询⽤户同时查询出该⽤户的所有⻆⾊

查询语句：

SELECT u.*, su.`id` rid,su.`rolename`,su.`roleDesc` FROM user_t u LEFT JOIN sys_user_role sur ON u.`id` = sur.`userid` INNER JOIN sys_role su ON sur.`roleid`=su.`id`

查询结果：

创建实体类

public class Role {
   
    private Integer id;
    private String roleName;
    private String roleDesc;
}

//修改User实体类
public class User {
   
    private Integer id;
    private String username;
    private List<Order> orders;
    private List<Role> roles;
}

创建Dao接口

public interface IUserMapper {
   
    List<User> findAll();

    List<User> findAllUserRole();
}

修改Mapper映射文件，添加对应标签

    <resultMap id="userRoleMap" type="com.elvis.pojo.User">
        <result column="id" property="id"></result>
        <result column="username" property="username"></result>
        <collection property="roles" ofType="com.elvis.pojo.Role">
            <result column="rid" property="id"></result>
            <result column="rolename" property="roleName"></result>
            <result column="roledesc" property="roleDesc"></result>
        </collection>
    </resultMap>

    <select id="findAllUserRole" resultMap="userRoleMap">
        SELECT u.*, su.`id` rid,su.`rolename`,su.`roleDesc` FROM user_t u LEFT JOIN sys_user_role sur ON u.`id` = sur.`userid` INNER JOIN sys_role su ON sur.`roleid`=su.`id`
    </select>

测试方法

    @Test
    public void findAllUserRoleTest() throws IOException {
   
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = builder.build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession();
        IUserMapper mapper = session.getMapper(IUserMapper.class);
        List<User> users = mapper.findAllUserRole();
        for (User user : users) {
   
            System.out.println(user);
        }
    }

原文链接: https://blog.csdn.net/Kiven_ch/article/details/117838648