数据访问
整合Mybatis实现CRUD
1. 引入依赖
引入druid-starter(数据源)
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
引入mybatis依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
2.Mybatis配置实例(application.yaml)
spring:
datasource:
username: root
password: root
url: jdbc:mysql://localhost:3306/db_orange?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
mybatis:
druid:
stat-view-servlet: #监控页的配置
enabled: true
login-password: 123456
login-username: admin
reset-enable: false
web-stat-filter: # 监控web
enabled: true
url-pattern: "/*"
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
aop-patterns: com.orange.admin.*
filters: stat,wall #底层开启功能,stat(sql监控),wall(防火墙)
filter: # 对上面filters里面的stat的详细配置
stat:
slow-sql-millis: 1000
log-slow-sql: true
enabled: true
wall:
enabled: true
config:
drop-table-allow: false
# config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
configuration:
map-underscore-to-camel-case: true
2.1在application.yaml配置mybatis规则
mybatis:
config-location: classpath:mybatis/mybatis-config.xml #全局配置文件位置
mapper-locations: classpath:mybatis/mapper/*.xml #sql映射文件位置
2.2mybatis-config.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>
<!--<settings><!–驼峰命名–>-->
<!-- <setting name="mapUnderscoreToCamelCase" value="true"/>-->
<!--</settings>-->
</configuration>
也可以不写全局配置文件,在application.yaml中将配置放在都放在configuration配置项中即可。
# config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
configuration:
map-underscore-to-camel-case: true
2.3 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.orange.admin.Mapper.UserMapper">
<!-- id 对应方法名 resultType对应返回类型 注意要写全路径名 -->
<select id="getUser" resultType="com.orange.admin.bean.User">
select * from user where id=#{id}
</select>
</mapper>
整合MyBatis-Plus
1引入依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
优点:只需要我们的Mapper继承 BaseMapper 就可以拥有crud能力
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
2用mybatis-plus实现用户的查询与删除
实体类User
@AllArgsConstructor
@NoArgsConstructor
@Data
@TableName("")指定数据库的表名
public class User {
/**
* 所有属性都应该在数据库中
*/
@TableField(exist = false) //当前属性表中不存在
private String userName;
@TableField(exist = false)
private String password;
//以下是数据库字段
private Long id;
private String name;
private Integer age;
private String email;
}
接口 UserService 继承IService
//泛型就是查询哪些数据类型
public interface UserService extends IService<User> {
}
实现类 UserServiceImpl 继承ServiceImpl
@Service
//泛型中 第一个参数 操作哪个mapper 返回类型
public class UserServiceImpl extends ServiceImpl<UserMapper,User> implements UserService {
}
Controller
@Autowired
UserService userService;
@GetMapping("/dynamic_table")
//分页功能
public String dynamic_table(@RequestParam(value = "pn",defaultValue = "1")Integer pn, Model model){
Page<User> page = new Page<>(pn, 2);//2表示每页显示几条数据
//queryWrapper 查询条件
IPage<User> userspage = userService.page(page, null);
//long current = userspage.getCurrent(); 当前页
//long pages = userspage.getPages(); 总页数
model.addAttribute("users",userspage);
return "table/dynamic_table";
}
//删除功能
@GetMapping("/user/delete/{id}")
public String deleteUser(@PathVariable("id") int id,
RedirectAttributes re,
@RequestParam(value = "pn",defaultValue = "1") Integer pn) {
userService.removeById(id);
re.addAttribute("pn",pn);
return "redirect:/dynamic_table";
}
注意要实现mybatis-plus的分页功能 还需配置分页拦截器
@Configuration
public class MyBatisConfig {
/**
* MybatisPlusInterceptor
* @return
*/
@Bean
public MybatisPlusInterceptor paginationInterceptor() {
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
// 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求 默认false
// paginationInterceptor.setOverflow(false);
// 设置最大单页限制数量,默认 500 条,-1 不受限制
// paginationInterceptor.setLimit(500);
// 开启 count 的 join 优化,只针对部分 left join
//这是分页拦截器
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setOverflow(true);
paginationInnerInterceptor.setMaxLimit(500L);
mybatisPlusInterceptor.addInnerInterceptor(paginationInnerInterceptor);
return mybatisPlusInterceptor;
}
}
前端页面
当前第[[${users.current}]]页 总计 [[${users.pages}]]页 共[[${users.total}]]条记录
<!--根据页数来产生按钮--><!--#numbers.sequence(from,to)-->
<li th:class="${num == users.current?'active':''}" th:each="num:${#numbers.sequence(1,users.pages)}">
<a th:href="@{/dynamic_table(pn=${num})}">[[${num}]]</a>
</li>