Mybatis框架开发(三)（分页）
1. mybatis的分页插件(pagehelper)
1.1 数据库分页SQL
SELECT * FROM 表 LIMIT n
SELECT * FROM 表 LIMIT m,n
1.2 PageHelper
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.1</version>
</dependency>
-
配件拦截器
<plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"> <!-- 使用下面的方式配置参数，后面会有所有的参数介绍 --> <property name="reasonable" value="true"/> </plugin>常用参数
分页实现
```
PageHelper.startPage(1, 10);
List<Address> list = mapper.findAll();
PageInfo<Address> page = new PageInfo<Address>(list);
```
1.3 项目中应用pageHelper-页面分页
1.3.1 后端实现
1.3.1项目结构
1.3.2 pom项目依赖
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.1</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.32</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.32</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.8.7</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
</dependencies>
1.3.3 实体类
@Alias("Address")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Address {
private Integer id;
private String name;
private Integer pid;
}
1.3.4 mapper接口
public interface IAddressMapper {
List<Address> findAll();
}
1.3.5 数据源实现
public class MybatisDataSource extends UnpooledDataSourceFactory {
public MybatisDataSource() {
this.dataSource = new DruidDataSource();
}
}
1.3.6 mybatis配置
-
mybatis-configuration.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> <!-- druid的配置文件的位置 ，${druid.username}取出配置文件druid.username的值 --> <properties resource="mybatis/druid/druid.properties" /> <!-- 配置别名，配合@Alais,扫描指定包内的锁有@Alais注解的类 --> <typeAliases> <package name="com.dyit.mybatis.entity" /> <!-- <typeAlias type="com.dyit.mybatis.entity.Book" alias="ABC"/> --> </typeAliases> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"> <!-- 使用下面的方式配置参数，后面会有所有的参数介绍 --> <property name="reasonable" value="true" /> </plugin> </plugins> <!--数据库的配置信息 --> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /><!--JDBC事务管理 --> <dataSource type="com.dyit.mybatis.util.MybatisDataSource"> <!---数据源 --> <property name="driverClassName" value="${druid.driver}" /> <property name="url" value="${druid.url}" /> <property name="username" value="${druid.username}" /> <property name="password" value="${druid.password}" /> </dataSource> </environment> </environments> <!--配置表和类的映射文件 --> <mappers> <mapper resource="mybatis/mapper/Address.mapper.xml" /> </mappers> </configuration> -
Address.mapper.xml映射
<mapper namespace="com.dyit.mybatis.mapper.IAddressMapper"> <resultMap type="Address" id="AddressMap"> <id property="id" column="china_id"/> <result property="name" column="china_name"/> <result property="pid" column="china_pid"/> </resultMap> <select id="findAll" resultMap="AddressMap"> SELECT * FROM china_tab </select> </mapper>
1.3.7 封装MybatisUtil对象
@Slf4j
public class MybatisUtil {
private volatile static MybatisUtil instance;
private MybatisUtil() {
init();
}
private SqlSessionFactory sqlSessionFactory;
private void init() {
try {
this.sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("mybatis/config/mybatis-configuration.xml"));
} catch (IOException e) {
log.debug(e.getMessage());
e.printStackTrace();
}
}
public static MybatisUtil getInstance() {
if (instance == null) {
synchronized (MybatisUtil.class) {
if (instance == null) {
instance = new MybatisUtil();
}
}
}
return instance;
}
public SqlSession openSession() {
return this.sqlSessionFactory.openSession();
}
}
1.3.8 业务service
- 业务接口IAddressService
public interface IAddressService {
PageInfo<Address> findAll(int page, int pageSize);
}
- 业务实现类
public class AddressServiceImpl implements IAddressService {
private MybatisUtil db = MybatisUtil.getInstance();
@Override
public PageInfo<Address> findAll(int page, int pageSize) {
SqlSession session = db.openSession();
IAddressMapper mapper = session.getMapper(IAddressMapper.class);
PageHelper.startPage(page, pageSize);
List<Address> list = mapper.findAll();
PageInfo<Address> pageInfo = new PageInfo<Address>(list);
session.commit();
session.close();
return pageInfo;
}
}
1.3.9 数据传输对象(DTO)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class HttpResp {
private int code;
private String msg;
private Object results;
private LocalDateTime time;
}
1.3.10 控制层(Servlet)
@WebServlet("/page")
public class PageAddressServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String spage = request.getParameter("page");
int page = spage == null || "".equals(spage) ? 1 : Integer.parseInt(spage);
String spageSize = request.getParameter("pageSize");
int pageSize = spageSize == null || "".equals(spageSize) ? 10 : Integer.parseInt(spageSize);
IAddressService ias = new AddressServiceImpl();
PageInfo<Address> pageInfo = ias.findAll(page, pageSize);
HttpResp resp = new HttpResp(20001, "查询分页成功", pageInfo, LocalDateTime.now());
PrintWriter out = response.getWriter();
out.println(new Gson().toJson(resp));
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
1.3. 11 postman测试后端
1.3.2 前端实现(vue+axios)
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>分页页面</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css"
integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.slim.js"
integrity="sha256-fNXJFIlca05BIO2Y5zh1xrShK3ME+/lYZ0j+ChxX2DA=" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"
integrity="sha384-aJ21OjlMXNL5UyIl/XNwTMqvzeRMZH2w8c5cRVpzpU8Y5bApTppSuUkhZXN0VxHd"
crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/vue@2"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
</head>
<div class="container">
<nav class="navbar navbar-inverse">
<a class="navbar-brand" href="#">使用mybatis分页页面面</a>
<ul class="nav navbar-nav">
<li class="active">
<a href="#">首页</a>
</li>
</ul>
</nav>
<table class="table table-striped table-hover">
<thead>
<tr>
<th>序号</th>
<th>地址名称</th>
<th>pid</th>
</tr>
</thead>
<tbody>
<template v-for="(addr,index) in addresses">
<tr>
<td>{{addr.id}}</td>
<td>{{addr.name}}</td>
<td>{{addr.pid}}</td>
</tr>
</template>
</tbody>
<tfoot>
<tr>
<td>
<div class="rows">
<div class="col-md-6">
<select @change="jump" v-model="currentPage" class="form-control">
<template v-for="n in pages">
<option :value="n">第{{n}}页</option>
</template>
</select>
</div>
<div class="col-md-6">
<select v-model="pageSize" class="form-control">
<template v-for="(n,index) in pageSizeNums">
<option :value="n">每页{{n}}条数据</option>
</template>
</select>
</div>
</div>
</td>
<td></td>
<td>
<nav aria-label="...">
<ul class="pager">
<li><a href="#" @click="prevPage">上一页</a></li>
<li><a href="#" @click="nextPage">下一页</a></li>
</ul>
</nav>
</td>
</tr>
</tfoot>
</table>
</div>
<script>
new Vue({
el: '.container',
data: {
addresses: [],
page: 1,
pageSize: 10,
pages: 1,
currentPage:1,
pageSizeNums:[10,50,100,150]
},
created() {
let _this = this;
// 为给定 ID 的 user 创建请求
axios.get('http://localhost:8090/mybatis-address/page?page=1&pageSize='+this.pageSize)
.then(function (response) {
console.log(response.data.results.list);
_this.addresses = response.data.results.list;
_this.pages = response.data.results.pages;
console.log(_this.pages);
})
.catch(function (error) {
console.log(error);
});
},
methods: {
jump:function(){
// alert(this.currentPage);
let _this = this;
// 为给定 ID 的 user 创建请求
axios.get('http://localhost:8090/mybatis-address/page?page=' + this.currentPage + '&pageSize='+this.pageSize)
.then(function (response) {
console.log(response.data.results.list);
_this.addresses = response.data.results.list;
})
.catch(function (error) {
console.log(error);
});
},
nextPage: function () {
let _this = this;
this.currentPage=this.currentPage+1;
// 为给定 ID 的 user 创建请求
axios.get('http://localhost:8090/mybatis-address/page?page=' + this.currentPage + '&pageSize=10')
.then(function (response) {
console.log(response.data.results.list);
_this.addresses = response.data.results.list;
})
.catch(function (error) {
console.log(error);
});
},
prevPage: function () {
let _this = this;
this.currentPage=this.currentPage-1;
// 为给定 ID 的 user 创建请求
axios.get('http://localhost:8090/mybatis-address/page?page=' + this.currentPage + '&pageSize=10')
.then(function (response) {
console.log(response.data.results.list);
_this.addresses = response.data.results.list;
})
.catch(function (error) {
console.log(error);
});
}
}
});
</script>
mybatis的动态标签
<sql>标签
含义： 定义一个sql语句
<sql id="findAllBook">
SELECT * FROM book_tab
</sql>
<includ> 标签
含义: 引用定义的sql语句
<select id="findAll" resultMap="BookMap">
<include refid="findAllBook"/>
</select>
<if>标签
含义： 判断
if test=“对象是否为空”
<if test="id!=null">
book_id = #{id}
</if>
<if test="title!=null">
AND book_title = #{title}
</if>
<if test="price!=null">
AND book_price = #{price}
</if>
<if test="category!=null">
AND book_category = #{category}
</if>
<where>标签
含义： 构成where子句内容
<select id="findByBooks" resultMap="BookMap">
<include refid="findAllBook" />
<where>
<if test="id!=null">
book_id = #{id}
</if>
<if test="title!=null">
AND book_title = #{title}
</if>
<if test="price!=null">
AND book_price = #{price}
</if>
<if test="category!=null">
AND book_category = #{category}
</if>
</where>
</select>
<foreach>标签
含义：循环拼接输入的条件
List<String> list = new ArrayList<>();
list.add("小说类");
list.add("神话类");
List<Book> list2 = mapper.findBooksByCategories(list);
<select id="findBooksByCategories" resultMap="BookMap">
<include refid="findAllBook" /> WHERE
<foreach collection="list" item="c" separator="OR">
book_category=#{c}
</foreach>
</select>
</if>
<if test="price!=null">
AND book_price = #{price}
</if>
<if test="category!=null">
AND book_category = #{category}
</if>
</where>
<foreach>标签
含义：循环拼接输入的条件
List<String> list = new ArrayList<>();
list.add("小说类");
list.add("神话类");
List<Book> list2 = mapper.findBooksByCategories(list);
<select id="findBooksByCategories" resultMap="BookMap">
<include refid="findAllBook" /> WHERE
<foreach collection="list" item="c" separator="OR">
book_category=#{c}
</foreach>
</select>
1076
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
