驼峰命名自动映射
总结springboot开启mybatis驼峰命名自动映射的三种方式
方式一:通过springboot的配置文件application.yml
mybatis:
configuration:
map-underscore-to-camel-case: true
此方式是最简单的,但是要注意,通过springboot的配置文件配置mybatis的设置,则不能够再使用mybatis的配置文件,例如:下边代码中标红的两个设置不能同时存在,要么使用config-location指定mybatis的配置文件,在通过mybatis的配置文件配置相关设置,要么通过springboot配置文件的mybatis.configuration进行相关设置,二者只能选其一,否则会报错。
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
configuration:
map-underscore-to-camel-case: true
方式二:通过mybatis的配置文件
首先需要在springboot的配置文件application.yml中指定mybatis配置文件的位置。
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
然后在mybatis配置文件中进行设置
<?xml version="1.0" encoding="UTF-8" ?> 方式三:通过@Comfiguration注解和@Bean注解,向容器中添加ConfigurationCustomizer类型的组件,在ConfigurationCustomizer中进行设置
@Configuration
public class MybatisConfig {
@Bean
public ConfigurationCustomizer configurationCustomizer(){
return new ConfigurationCustomizer() {
@Override
public void customize(org.apache.ibatis.session.Configuration configuration) {
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}
分页查询
分页查询
🚀背景
- 查询返回的记录太多,查起来很不方便,想分页查看返回结果
- 表里面有4条数据,只想显示2、3条数据
🛸实现规则
🚗分页原理
分页显示: 将数据库中的结果集,一段一段显示出来需要的条件
🚙格式
当位置偏移量为0的时候,位置偏移量可以省略
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#708090"># 放在查询语句的末尾</span>
<span style="color:#0077aa">LIMIT</span> 【位置偏移量<span style="color:#999999">,</span>】 行数
</code></span></span>
- 1
- 2
🛩️分页查询案例
1.每页显示20条记录,此时显示第1页
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">SELECT</span>
employee_id<span style="color:#999999">,</span>
last_name
<span style="color:#0077aa">FROM</span>
employees
<span style="color:#0077aa">LIMIT</span> <span style="color:#986801">0</span><span style="color:#999999">,</span> <span style="color:#986801">20</span> <span style="color:#999999">;</span>
</code></span></span>
- 1
- 2
- 3
- 4
- 5
- 6
此时位置偏离量为0,也可以省略位置偏移量
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">SELECT</span>
employee_id<span style="color:#999999">,</span>
last_name
<span style="color:#0077aa">FROM</span>
employees
<span style="color:#0077aa">LIMIT</span> <span style="color:#986801">20</span> <span style="color:#999999">;</span>
</code></span></span>
- 1
- 2
- 3
- 4
- 5
- 6
2.每页显示20条记录,此时显示第2页
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">SELECT</span>
employee_id<span style="color:#999999">,</span>
last_name
<span style="color:#0077aa">FROM</span>
employees
<span style="color:#0077aa">LIMIT</span> <span style="color:#986801">20</span><span style="color:#999999">,</span> <span style="color:#986801">20</span> <span style="color:#999999">;</span>
</code></span></span>
- 1
- 2
- 3
- 4
- 5
- 6
3.每页显示20条记录,此时显示第3页
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">SELECT</span>
employee_id<span style="color:#999999">,</span>
last_name
<span style="color:#0077aa">FROM</span>
employees
<span style="color:#0077aa">LIMIT</span> <span style="color:#986801">40</span><span style="color:#999999">,</span> <span style="color:#986801">20</span> <span style="color:#999999">;</span>
</code></span></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
🚓分页查询公式
通过以上三个案例,我们可以总结出分页查询公式:
(当前页数-1) * 每页条数,每页条数
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">SELECT</span>
<span style="color:#a67f59">*</span>
<span style="color:#0077aa">FROM</span>
<span style="color:#0077aa">TABLE</span>
<span style="color:#0077aa">LIMIT</span> <span style="color:#999999">(</span>Page <span style="color:#a67f59">-</span> <span style="color:#986801">1</span><span style="color:#999999">)</span> <span style="color:#a67f59">*</span> PageSize<span style="color:#999999">,</span> PageSize <span style="color:#999999">;</span>
</code></span></span>
- 1
- 2
- 3
- 4
- 5
4.显示employees表中第 32、33 条数据
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">SELECT</span>
<span style="color:#a67f59">*</span>
<span style="color:#0077aa">FROM</span>
employees
<span style="color:#0077aa">LIMIT</span> <span style="color:#986801">31</span><span style="color:#999999">,</span> <span style="color:#986801">2</span> <span style="color:#999999">;</span>
</code></span></span>
- 1
- 2
- 3
- 4
- 5
- 6
5.查询员工表中工资最高的员工信息
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">SELECT</span>
<span style="color:#a67f59">*</span>
<span style="color:#0077aa">FROM</span>
employees
<span style="color:#0077aa">ORDER</span> <span style="color:#0077aa">BY</span> salary <span style="color:#0077aa">DESC</span>
<span style="color:#0077aa">LIMIT</span> <span style="color:#986801">1</span> <span style="color:#999999">;</span>
</code></span></span>
- 1
- 2
- 3
- 4
- 5
- 6
🚁MySQL8.0新特性
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">LIMIT</span> <span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span> <span style="color:#0077aa">OFFSET</span> <span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span>
</code></span></span>
- 1
例如,LIMIT 2 OFFSET 3意思是获取从第4条记录开始后面的2条记录
所以上述案例4也可以改成如下
<span style="color:#000000"><span style="background-color:#fafafa"><code class="language-sql"><span style="color:#0077aa">SELECT</span>
<span style="color:#a67f59">*</span>
<span style="color:#0077aa">FROM</span>
employees
<span style="color:#0077aa">LIMIT</span> <span style="color:#986801">2</span> <span style="color:#0077aa">OFFSET</span> <span style="color:#986801">31</span> <span style="color:#999999">;</span>
</code></span></span>
- 1
- 2
- 3
- 4
- 5
🛰️分页查询的好处
约束返回结果的数量可以减少数据表的网络传输量
,也可以 提升查询效率
。如果我们知道返回结果只有1 条,就可以使用 LIMIT 1
,告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT
不需要扫描完整的表,只需要检索到一条符合条件的记录即可返回。