整合MyBatis
官方文档:http://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/
Maven仓库地址:https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter/2.1.2
整合测试
1、导入 MyBatis 所需要的依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
2、配置数据库连接信息(不变)
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/springbootdata?useUnicode=true&characterEncoding=utf-8&useSSL=true&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
3、测试数据库是否连接成功!
4、创建实体类,导入 Lombok!
User.java
package com.jia.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data // 注在类上,提供类的get、set、equals、hashCode、canEqual、toString方法
@AllArgsConstructor // 注在类上,提供类的全参构造
@NoArgsConstructor // 注在类上,提供类的无参构造
public class User {
private Integer id;
private String name;
private String pwd;
}
5、创建mapper目录以及对应的 Mapper 接口
UserMapper.java
package com.jia.mapper;
import com.jia.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper
//@Repository
public interface UserMapper {
List<User> queryUserList();
User queryUserById(int id);
int addUser(User user);
int updateUser(User user);
int deleteUser(int id);
}
6、对应的Mapper映射文件
UserMapper.xml
<?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.jia.mapper.UserMapper">
<select id="queryUserList" resultType="User">
SELECT * from `user`
</select>
<select id="queryUserById" resultType="User">
SELECT * from `user` where id = #{id}
</select>
<insert id="addUser" parameterType="User">
insert into user (id,name,pwd) values (#{id},#{name},#{pwd})
</insert>
<update id="updateUser" parameterType="User">
update user set name = #{name},pwd = #{pwd} where id = #{id}
</update>
<delete id="deleteUser" parameterType="int">
delete from user where id = #{id}
</delete>
</mapper>
7、maven配置资源过滤问
在 application.properties 和 mybatis-config.xml 中配置
mybatis.config-location = classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations= classpath:mybatis/mapper/*.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>
<typeAliases>
<package name="com.jia.pojo"/>
</typeAliases>
</configuration>
8、编写 UserController 进行测试!
package com.jia.controller;
import com.jia.mapper.UserMapper;
import com.jia.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserMapper userMapper;
@RequestMapping("/queryUserList")
public List<User> queryUserList() {
return userMapper.queryUserList();
}
@RequestMapping("/queryUserById")
public User queryUserById(int id) {
return userMapper.queryUserById(id);
}
@RequestMapping("/addUser")
public int addUser() {
return userMapper.addUser(new User(4,"123","456"));
}
@RequestMapping("/updateUser")
public int updateUser() {
return userMapper.updateUser(new User(4,"111","222"));
}
@RequestMapping("/deleteUser")
public int deleteUser(int id) {
return userMapper.deleteUser(id);
}
}
启动项目访问进行测试!
配置分页功能
增加分页支持
添加 pagehelper 和 thymleaf 依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.6</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
设置方言
pagehelper.helper-dialect=mysql
创建分页配置类
@Configuration
public class PageHelperConfig {
@Bean
public PageHelper pageHelper(){
PageHelper pageHelper = new PageHelper();
Properties p = new Properties();
//1.offsetAsPageNum:设置为true时,会将RowBounds第一个参数offset当成pageNum页码使用.
p.setProperty("offsetAsPageNum", "true");
//2.rowBoundsWithCount:设置为true时,使用RowBounds分页会进行count查询.
p.setProperty("rowBoundsWithCount", "true");
//3.reasonable:启用合理化时,如果pageNum<1会查询第一页,如果pageNum>pages会查询最后一页。
p.setProperty("reasonable", "true");
pageHelper.setProperties(p);
return pageHelper;
}
}
实现分页控制器
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.jia.mapper.UserMapper;
import com.jia.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
@Controller
public class UserListController {
@Autowired
UserMapper userMapper;
@Autowired
@Qualifier("pageH")
PageHelper pageHelper;
@RequestMapping("/listall")
public String listCategory(Model m, @RequestParam(value = "start", defaultValue = "0") int start, @RequestParam(value = "size", defaultValue = "5") int size) {
//1. 在参数里接受当前是第几页 start ,以及每页显示多少条数据 size。 默认值分别是0和5。
//2. 根据start,size进行分页,并且设置id 倒排序
pageHelper.startPage(start, size, "id desc");
//3. 因为PageHelper的作用,这里就会返回当前分页的集合了
List<User> cs = userMapper.queryAll();
//4. 根据返回的集合,创建PageInfo对象
PageInfo<User> page = new PageInfo<>(cs);
//5. 把PageInfo对象扔进model,以供后续显示
m.addAttribute("page", page);
//6. 跳转到list.html
return "list";
}
@RequestMapping("/listall2")
@ResponseBody
//http://localhost:8080/listall2?pageNum=1&pageSize=2
// 如果方法的参数不指定默认值,且请求地址也没有指定参数值,则项目运行时会报错。
public Page<User> getUserList(@RequestParam(value = "pageNum", defaultValue = "0") int pageNum, @RequestParam(value = "pageSize", defaultValue = "5") int pageSize)
{
PageHelper.startPage(pageNum, pageSize);
Page<User> userList = userMapper.getUserList();
return userList;
}
}
创建分页视图
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8"/>
<title>1</title>
</head>
<body>
<div class="with:80%">
<div th:each="u : ${page.list}">
<span scope="row" th:text="${u.id}">id</span>
<span th:text="${u.name}">name</span>
<span th:text="${u.pwd}">name</span>
<span th:text="${u.perms}">name</span>
</div>
</div>
<div>
<a th:href="@{listall?start=1}">[首页]</a>
<a th:if="${not page.IsFirstPage}" th:href="@{/listall(start=${page.pageNum-1})}">[上页]</a>
<a th:if="${not page.IsLastPage}" th:href="@{/listall(start=${page.pageNum+1})}">[下页]</a>
<a th:href="@{/listall(start=${page.pages})}">[末页]</a>
<div>当前页/总页数:<a th:text="${page.pageNum}" th:href="@{/listall(start=${page.pageNum})}"></a>
/<a th:text="${page.pages}" th:href="@{/listall(start=${page.pages})}"></a></div>
</div>
</body>
</html>
Mapper
UserMapper.java
List<User> queryAll();
@Select("SELECT * from `myuser` ")
Page<User> getUserList();
UserMapper.xml
<?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.jia.mapper.UserMapper">
<select id="queryAll" resultType="User">
SELECT * from `myuser`
</select>
</mapper>