学习目标
- 分页查询
- 使用注解
- 高级查询
- 延迟加载
- xml中的字符实体
- 逆向工程
12.分页查询
物理分页与逻辑分页
一 概述
1.物理分页
物理分页依赖的是某一物理实体,这个物理实体就是数据库,比如MySQL数据库提供了limit关键字,程序员只需要编写带有limit关键字的SQL语句,数据库返回的就是分页结果。
2.逻辑分页
逻辑分页依赖的是程序员编写的代码。数据库返回的不是分页结果,而是全部数据,然后再由程序员通过代码获取分页数据,常用的操作是一次性从数据库中查询出全部数据并存储到List集合中,因为List集合有序,再根据索引获取指定范围的数据。
二 对比
1.数据库负担
物理分页每次都访问数据库,逻辑分页只访问一次数据库,物理分页对数据库造成的负担大。
2.服务器负担
逻辑分页一次性将数据读取到内存,占用了较大的内容空间,物理分页每次只读取一部分数据,占用内存空间较小。
3.实时性
逻辑分页一次性将数据读取到内存,数据发生改变,数据库的最新状态不能实时反映到操作中,实时性差。物理分页每次需要数据时都访问数据库,能够获取数据库的最新状态,实时性强。
4.适用场合
逻辑分页主要用于数据量不大、数据稳定的场合,物理分页主要用于数据量较大、更新频繁的场合。
不能仅仅停滞在实现上,应该去追求代价更小、性能更优的实现
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>
执行:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-avlEPDbO-1639813251052)(assets/1602615249375.png)]
16.xml中的字符实体
1.使用xml中的字符实体
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-70jOQGvU-1639813251053)(assets/1456626-20190327213221528-1107290735.png)]
因为业务,需要在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 > to_date(#{startDateTime},'yyyy-mm-dd hh24:mi:ss')</if>
2.<if test="endDateTime!=null"> and mm.ttime <= 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语句中的 等标签不会被解析。