MyBatis-3

最新推荐文章于 2022-12-07 15:34:48 发布

为什么暴躁

最新推荐文章于 2022-12-07 15:34:48 发布

阅读量93

点赞数

分类专栏： java基础文章标签： java 开发语言后端

本文链接：https://blog.csdn.net/chenguoao1999/article/details/121039327

版权

java基础专栏收录该内容

42 篇文章 2 订阅

订阅专栏

学习目标

分页查询
使用注解
高级查询
延迟加载
xml中的字符实体
逆向工程

12.分页查询

12.1.使用Limit分页

使用Mybatis实现分页，核心SQL

接口

    //limit分页
    List<User> getUserByLimit(Map<String,Integer> map);

mapper.xml

    <!--limit分页-->
    <select id="getUserByLimit" parameterType="java.util.Map" resultType="com.zpc.mybatis.pojo.User">
        select * from tb_user limit #{startIndex},#{pageSize};
    </select>

测试

    @Test
    public void getUserByLimit(){

        Map<String,Integer> map=new HashMap<String,Integer>();
        map.put("startIndex",0);
        map.put("pageSize",1);

        List<User> userByLimit = this.userMapper.getUserByLimit(map);
        for (User user : userByLimit) {
            System.out.println(user);
        }
    }

12.2.使用RowBounds分页

不再使用SQL实现分页

接口

    //RowBounds分页
    List<User> getUserByRowBounds();

mapper.xml

    <!-- RowBounds分页 -->
    <select id="getUserByRowBounds" resultType="com.zpc.mybatis.pojo.User">
        select * from tb_user
    </select>

测试

    @Test
    public void getUserByRowBoundsTest(){
        RowBounds rowBounds = new RowBounds(0,1);
        List<User> users=sqlSession.selectList("com.zpc.mybatis.dao.UserMapper.getUserByRowBounds",null,rowBounds);
        for (User user : users) {
            System.out.println(user);
        }
    }

12.3.分页插件pagehelper

pom.xml

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.2</version>
        </dependency>

mybatis-config.xml

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

测试

    @Test
    public void getUserByPageHelper(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        PageHelper.startPage(1,1);
        List<User> users = userMapper.queryUserAll();
        users.forEach(user -> System.out.println(user));
    }

13.使用注解

接口

package com.zpc.mybatis.dao;

import com.zpc.mybatis.pojo.User;
import org.apache.ibatis.annotations.*;

/***
 * 注解方式的CRUD
 */
public interface UserMapper2 {

    //根据id查询用户
    @Select("select * from tb_user where id = #{id}")
    User selectUserById(@Param("id") String id);

    //添加一个用户
    @Insert("INSERT INTO tb_user(id,user_name,password,name,age,sex,birthday,created,updated) VALUES(#{id},#{userName},#{password},#{name},#{age},#{sex},#{birthday},NOW(),NOW())")
    int addUser(User user);

    //修改一个用户
    @Update("update tb_user set name=#{name},password=#{password} where id = #{id}")
    int updateUser(User user);

    //根据id删除用
    @Delete("delete from tb_user where id = #{id}")
    int deleteUser(@Param("id") String id);
}

mybatis-config.xml

 	<mappers>
        <mapper resource="mappers/UserMapper.xml"/>
        <mapper class="com.zpc.mybatis.dao.UserMapper2"/>
    </mappers>

测试

package com.zpc.mybatis.test;

import com.zpc.mybatis.dao.UserMapper2;
import com.zpc.mybatis.pojo.User;
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 org.junit.Before;
import org.junit.Test;

import java.io.InputStream;

public class UserMapperTest2 {
    public SqlSession sqlSession;
    public UserMapper2 userMapper2;

    @Before
    public void setUp() throws Exception {
        // 指定配置文件
        String resource = "mybatis-config.xml";
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 构建sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 获取sqlSession
        sqlSession = sqlSessionFactory.openSession(true);
        this.userMapper2 = sqlSession.getMapper(UserMapper2.class);
    }

    @Test
    public void testSelectUserById() {
        User user = userMapper2.selectUserById("2");
        System.out.println(user);
    }
    @Test
    public void testAddUser() {
        User user= new User();
        user.setId("11111");
        userMapper2.addUser(user);
    }

    @Test
    public void testUpdateUser() {
        User user = new User();
        user.setId("11111");
        user.setName("haha");
        userMapper2.updateUser(user);
    }

    @Test
    public void testDeleteUser() {
        userMapper2.deleteUser("11111");
    }
}

14.高级查询

14.1.表关系说明

在这里插入图片描述

数据库脚本:

CREATE TABLE tb_order (
id int(11) NOT NULL AUTO_INCREMENT,
user_id char(32) DEFAULT NULL,
order_number varchar(255) DEFAULT NULL,
created datetime DEFAULT NULL,
updated datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE tb_item (
id int(11) NOT NULL,
itemName varchar(255) DEFAULT NULL,
itemPrice decimal(10,2) DEFAULT NULL,
itemDetail varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tb_orderdetail (
id int(11) NOT NULL AUTO_INCREMENT,
order_id int(11) DEFAULT NULL,
total_price decimal(10,0) DEFAULT NULL,
item_id int(11) DEFAULT NULL,
status int(10) unsigned zerofill DEFAULT NULL COMMENT '0成功非0失败',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

需求说明：

14.2.一对一查询

在Order对象中添加User属性：

public class Order {
    private Integer id;
    private String userId;
    private String orderNumber;
    private Date created;
    private Date updated;
    private User user;
    
    //get/set/toString
}

接口：

public interface OrderMapper {
    /**
     * 根据订单号查询订单用户的信息
     * @param number
     * @return
     */
    Order queryOrderWithUserByOrderNumber(@Param("number") String number);
}

OrderMapper.xml

<mapper namespace="com.zpc.mybatis.dao.OrderMapper">
    <resultMap id="OrderUserResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
        <id column="id" property="id"/>
        <!--association:完成子对象的映射-->
        <!--property:子对象在父对象中的属性名-->
        <!--javaType:子对象的java类型-->
        <!--autoMapping:完成子对象的自动映射，若开启驼峰，则按驼峰匹配-->
        <association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true">
            <id column="user_id" property="id"/>
        </association>
    </resultMap>

    <select id="queryOrderWithUserByOrderNumber" resultMap="OrderUserResultMap">
       select * from tb_order o left join tb_user u on o.user_id=u.id where o.order_number = #{number}
    </select>
</mapper>

mybatis-config.xml

    <mappers>
        <mapper resource="mappers/UserMapper.xml"/>
        <mapper class="com.zpc.mybatis.dao.UserMapper2"/>
        <mapper resource="mappers/OrderMapper.xml"/>
    </mappers>

测试：

public class OrderMapperTest {
    public OrderMapper orderMapper;

    @Before
    public void setUp() throws Exception {
        // 指定配置文件
        String resource = "mybatis-config.xml";
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 构建sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        // 1. 映射文件的命名空间（namespace）必须是mapper接口的全路径
        // 2. 映射文件的statement的id必须和mapper接口的方法名保持一致
        // 3. Statement的resultType必须和mapper接口方法的返回类型一致
        // 4. statement的parameterType必须和mapper接口方法的参数类型一致（不一定）
        this.orderMapper = sqlSession.getMapper(OrderMapper.class);
    }

    @Test
    public void queryOrderWithUserByOrderNumber() throws Exception {
        Order order = orderMapper.queryOrderWithUserByOrderNumber("20201014");
        System.out.println(order);
    }
}

14.3.一对多查询

一对多查询：查询订单，查询出下单人信息并且查询出订单详情。

OrderDetail

public class OrderDetail {
    private Integer id;
    private Integer orderId;
    private Double totalPrice;
    private Integer status;
}

Order类：

public class Order {
    private Integer id;
    private Long userId;
    private String orderNumber;
    private Date created;
    private Date updated;
    private User user;
    private List<OrderDetail> detailList;
}

接口：

/**
 * 根据订单号查询订单用户的信息及订单详情
 * @param number
 * @return
 */
Order queryOrderWithUserAndDetailByOrderNumber(@Param("number") String number);

Mapper映射：


    <resultMap id="OrderUserDetailResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
        <id column="order_id" property="id"/>
        <!--collection:定义子对象集合映射-->
        <!--association:完成子对象的映射-->
        <!--property:子对象在父对象中的属性名-->
        <!--javaType:子对象的java类型-->
        <!--autoMapping:完成子对象的自动映射，若开启驼峰，则按驼峰匹配-->
        <association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true">
            <id column="user_id" property="id"/>
        </association>
        <collection property="detailList" javaType="List" ofType="com.zpc.mybatis.pojo.OrderDetail" autoMapping="true">
            <id column="detail_id" property="id"/>
        </collection>
    </resultMap>

    <select id="queryOrderWithUserAndDetailByOrderNumber" resultMap="OrderUserDetailResultMap">
       select *,od.id as detail_id,u.id as user_id,o.id as order_id from tb_order o
       left join tb_user u on o.user_id=u.id
       left join tb_orderdetail od on o.id=od.order_id
       where o.order_number = #{number}
    </select>

测试：

    @Test
    public void queryOrderWithUserAndDetailByOrderNumber() throws Exception {
        Order order = orderMapper.queryOrderWithUserAndDetailByOrderNumber("20201014");
        System.out.println(order);
    }

14.4.多对多查询

多对多查询：查询订单，查询出下单人信息并且查询出订单详情中的商品数据。

Item

public class Item {
    private Integer id;
    private String itemName;
    private Float itemPrice;
    private String itemDetail;
}

OrderDetail

public class OrderDetail {
    private Integer id;
    private Integer orderId;
    private Double totalPrice;
    private Integer status;
    private Item item;
}

接口：

/**
 * 根据订单号查询订单用户的信息及订单详情及订单详情对应的商品信息
 * @param number
 * @return
 */
Order queryOrderWithUserAndDetailItemByOrderNumber(@Param("number") String number);

Mapper配置：

    <resultMap id="OrderUserDetailItemResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
        <id column="id" property="id"/>
        <association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true">
            <id column="user_id" property="id"/>
        </association>
        <collection property="detailList" javaType="List" ofType="com.zpc.mybatis.pojo.OrderDetail" autoMapping="true">
            <id column="detail_id" property="id"/>
            <association property="item" javaType="com.zpc.mybatis.pojo.Item" autoMapping="true">
                <id column="item_id" property="id"/>
            </association>
        </collection>
    </resultMap>

    <select id="queryOrderWithUserAndDetailItemByOrderNumber" resultMap="OrderUserDetailItemResultMap">
       select * from tb_order o
       left join tb_user u on o.user_id=u.id
       left join tb_orderdetail od on o.id=od.order_id
       left join tb_item i on od.item_id=i.id
       where o.order_number = #{number}
    </select>

测试：

    @Test
    public void queryOrderWithUserAndDetailItemByOrderNumber() throws Exception {
        Order order = orderMapper.queryOrderWithUserAndDetailItemByOrderNumber("20201014");
        System.out.println(order);
    }

14.5.resultMap的继承

在这里插入图片描述

14.6.高级查询的整理

resutlType无法帮助我们自动的去完成映射，所以只有使用resultMap手动的进行映射
type 结果集对应的数据类型 id 唯一标识，被引用的时候，进行指定

<resultMap type="Order" id="orderUserLazyResultMap">
		<!—定义pojo中的单个对象的 property 定义对象的属性名， javaType 属性的类型，
		<association property="user" javaType="User" autoMapping="true">
			<id />
		</association>
		<!—如果属性是集合使用collection ,javaType 集合的类型，ofType 表示集中的存储的元素类型
		<collection property="details" javaType="List" ofType="OrderDetail" autoMapping="true">
			<id />
</resultMap>

15.延迟加载

在这里插入图片描述

<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>

编写接口：

    /**
     * 延迟加载
     * @param number
     * @return
     */
    Order queryOrderAndUserByOrderNumberLazy(@Param("number") String number);

Mapper配置：

在这里插入图片描述

    <resultMap id="LazyOrderUserResultMap" type="com.zpc.mybatis.pojo.Order" autoMapping="true">
        <id column="id" property="id"></id>
        <!--select:子查询的id，column:子查询的条件-->
        <association property="user" javaType="com.zpc.mybatis.pojo.User" autoMapping="true" select="queryUserById" column="user_id"></association>
    </resultMap>

    <select id="queryOrderAndUserByOrderNumberLazy" resultMap="LazyOrderUserResultMap">
        select * from tb_order where order_number=#{number}
    </select>
    <select id="queryUserById" resultType="com.zpc.mybatis.pojo.User">
        select * from tb_user where id=#{id}
    </select>

测试：

    @Test
    public void queryOrderAndUserByOrderNumberLazy() throws Exception {
        Order order = orderMapper.queryOrderAndUserByOrderNumberLazy("20201014");
        System.out.println(order);
    }

结果：
在这里插入图片描述

开启延迟加载：

在这里插入图片描述

        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>

修改测试用例：

    @Test
    public void queryOrderAndUserByOrderNumberLazy() throws Exception {
        Order order = orderMapper.queryOrderAndUserByOrderNumberLazy("20201014");
        System.out.println(order.getOrderNumber());
        System.out.println("-----------------------------------");
        System.out.println(order.getUser());
    }

执行，报错：

在这里插入图片描述

添加cglib：

<dependency>
		<groupId>cglib</groupId>
		<artifactId>cglib</artifactId>
		<version>3.1</version>
</dependency>

执行：

在这里插入图片描述

16.xml中的字符实体

1.使用xml中的字符实体

在这里插入图片描述

因为业务，需要在mybatis中，使用到大于号，小于号，所以就在SQL中直接使用了。
SELECT * FROM test WHERE 1 = 1 AND start_date <= CURRENT_DATE AND end_date >= CURRENT_DATE
可是，在执行时，总报错误：

Error creating document instance. Cause: org.xml.sax.SAXParseException; lineNumber: 74; columnNumber: 17;

元素内容必须由格式正确的字符数据或标记组成。
把AND start_date >= CURRENT_DATE AND end_date <= CURRENT_DATE去掉，就没有问题，所以确定是因为大于号，小于号引起的问题。

于是就想到了特殊符号，于是用了转义字符把>和<替换掉，然后就没有问题了。
SELECT * FROM test WHERE 1 = 1 AND start_date <= CURRENT_DATE AND end_date >= CURRENT_DATE
案例：

1.<if test="startDateTime!=null"> and mm.ttime &gt; to_date(#{startDateTime},'yyyy-mm-dd hh24:mi:ss')</if>  
2.<if test="endDateTime!=null"> and mm.ttime &lt;= to_date(#{endDateTime},'yyyy-mm-dd hh24:mi:ss')</if>

2.使用`<![CDATA[ < ]]>`

案例1：

1.<![CDATA[ 
2.       and mm.ttime > to_date(#{startDateTime},'yyyy-mm-dd hh24:mi:ss') 
3.       and mm.ttime <= to_date(#{endDateTime},'yyyy-mm-dd hh24:mi:ss') 
4.]]>

案例2：

mapper文件示例代码：

and (t1.status <![CDATA[ >= ]]> 1  and  t1.status <![CDATA[ <= ]]> 2)
上述代码其实对应的sql：
and (t1.status > =1 andt1.status <= 2)

注意：

使用标记的sql语句中的等标签不会被解析。

为什么暴躁

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis-3

学习目标分页查询使用注解高级查询延迟加载xml中的字符实体逆向工程12.分页查询12.1.使用Limit分页使用Mybatis实现分页，核心SQL接口 //limit分页 List<User> getUserByLimit(Map<String,Integer> map);mapper.xml  <select id="getUserByLimit" parameterType=
复制链接

扫一扫