接下来我将详细讲解“SpringMVC+Mybatis实现的Mysql分页数据查询的示例”的完整攻略,过程中将给出两条示例说明。
准备工作
在开始实现分页数据查询之前,需要确保以下几点:
- JDK版本在1.8及以上
- Maven依赖管理工具
- SpringMVC框架
- Mybatis持久层框架
- Mysql数据库
添加依赖
在Maven的pom.xml文件中添加SpringMVC和Mybatis的相关依赖,如下所示:
<!-- SpringMVC依赖 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.2.5.RELEASE</version>
</dependency>
<!-- Mybatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
数据库准备
在Mysql数据库中创建一张示例表,如下所示:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
插入示例数据,如下所示:
INSERT INTO `user` (`username`, `password`, `age`) VALUES ('Alice', '123456', 18);
INSERT INTO `user` (`username`, `password`, `age`) VALUES ('Bob', '123456', 20);
INSERT INTO `user` (`username`, `password`, `age`) VALUES ('Charlie', '123456', 22);
INSERT INTO `user` (`username`, `password`, `age`) VALUES ('David', '123456', 24);
INSERT INTO `user` (`username`, `password`, `age`) VALUES ('Ellie', '123456', 26);
配置Mybatis
在SpringMVC项目中添加Mybatis的配置文件,如下所示:
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/mapper/UserMapper.xml"/>
</mappers>
</configuration>
配置Mybatis的Mapper接口,如下所示:
public interface UserMapper {
List<User> findAllUsers();
int countAllUsers();
List<User> findUsersByPage(@Param("offset") int offset, @Param("limit") int limit);
}
编写Mapper接口的对应的SQL语句,如下所示:
<mapper namespace="com.mapper.UserMapper">
<resultMap type="com.pojo.User" id="userResultMap">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="age" property="age"/>
</resultMap>
<select id="findAllUsers" resultMap="userResultMap">
select * from user
</select>
<select id="countAllUsers" resultType="int">
select count(*) from user
</select>
<select id="findUsersByPage" parameterType="map" resultMap="userResultMap">
select * from user limit #{offset}, #{limit}
</select>
</mapper>
实现数据分页查询
在Controller层中编写实现数据分页查询的方法,如下所示:
@RequestMapping("/findAllUsersByPage")
public String findAllUsersByPage(@RequestParam(defaultValue = "1") int currentPage, Model model) {
int limit = 2;
int offset = (currentPage - 1) * limit;
List<User> users = userMapper.findUsersByPage(offset, limit);
int totalPage = (int) Math.ceil((double) userMapper.countAllUsers() / limit);
model.addAttribute("currentPage", currentPage);
model.addAttribute("totalPage", totalPage);
model.addAttribute("users", users);
return "list";
}
在页面中添加分页的HTML代码,如下所示:
<nav>
<ul class="pagination">
<li class="page-item"><a class="page-link" href="?currentPage=1">首页</a></li>
<li class="page-item"><a class="page-link" href="?currentPage=${currentPage - 1}">上一页</a></li>
<c:forEach begin="1" end="${totalPage}" var="i">
<li class="page-item ${currentPage == i ? 'active' : ''}"><a class="page-link" href="?currentPage=${i}">${i}</a></li>
</c:forEach>
<li class="page-item"><a class="page-link" href="?currentPage=${currentPage + 1}">下一页</a></li>
<li class="page-item"><a class="page-link" href="?currentPage=${totalPage}">尾页</a></li>
</ul>
</nav>
示例说明
以下为两条示例说明:
示例1:修改每页显示的数据数量
在Controller层的findAllUsersByPage方法中修改每页显示的数据数量,如下所示:
int limit = 5; // 修改每页显示的数据数量
int offset = (currentPage - 1) * limit;
List<User> users = userMapper.findUsersByPage(offset, limit);
int totalPage = (int) Math.ceil((double) userMapper.countAllUsers() / limit);
model.addAttribute("currentPage", currentPage);
model.addAttribute("totalPage", totalPage);
model.addAttribute("users", users);
示例2:实现高级别的查询功能
在Mapper接口中添加高级别的查询方法,如下所示:
List<User> findUsersByUsernameAndPassword(@Param("username") String username, @Param("password") String password);
在Mapper接口的对应的SQL语句中添加高级别的查询方法,如下所示:
<select id="findUsersByUsernameAndPassword" parameterType="map" resultMap="userResultMap">
select * from user where username=#{username} and password=#{password}
</select>
在Controller层中编写高级别的查询方法,如下所示:
@RequestMapping("/findUsersByUsernameAndPassword")
public String findUsersByUsernameAndPassword(String username, String password, Model model) {
List<User> users = userMapper.findUsersByUsernameAndPassword(username, password);
model.addAttribute("users", users);
return "list";
}
以上就是“SpringMVC+Mybatis实现的Mysql分页数据查询的示例”的完整攻略了,希望能对您有所帮助。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:SpringMVC+Mybatis实现的Mysql分页数据查询的示例 - Python技术站