1.MyBatis的分页
通过分页插件来完成对数据库表记录的分页查询。
在javaWeb的学习过程中,使用JDBC方式实现对数据的分页,主要实现原理是在查询的语句后面添加limit关键字,然后实现查询的数据分页功能,但是如果需要在所有的查询中都添加分页的话,那么就需要在所有的select语句后面添加limit关键字,这样的工作量就很大,同时也要改动大量的代码。
mybatis中提供插件plugin机制,本质就是拦截指定的查询操作,然后在查询的操作添加相关的分页逻辑。
PageHelper插件
依赖包:
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
(1)创建数据库表和对应的javabean类
public class UserBean {
private int userid;
private String usrename;
private int userage;
private String useraddress;
get和set方法……
}
使用之前的表
public interface UserMapper {
Boolean insertMore(List<UserBean> userBeanList);
List<UserBean> selectUser();
}
drivername=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/student_db?serverTimezone=UTC
username=root
password=123456
initialSize=100
maxActive=300
maxWait=60000
<?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>
<properties resource="mydata.properties"/>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<typeAlias type="com.weiwei.mybatisdemo6.bean.UserBean" alias="user"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${drivername}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
<?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.weiwei.mybatisdemo6.mapper.UserMapper">
<insert id="insertMore" parameterType="java.util.List">
insert into t_user values
<foreach collection="list" item="user" separator=",">
(#{user.userid},#{user.username},#{user.userage},#{user.useraddress})
</foreach>
</insert>
</mapper>
添加数据:
public static void insertTest(){
SqlSession sqlSession=null;
try {
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
sqlSession= sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<UserBean> List = new ArrayList<>();
for (int i=1;i<=50;i++) {
UserBean userBean = new UserBean();
userBean.setUserid(4+i);
userBean.setUsername("李四"+i);
userBean.setUserage(i);
userBean.setUseraddress("西安"+i);
List.add(userBean);
}
mapper.insertMore(List);
sqlSession.commit();
} catch (IOException e) {
e.printStackTrace();
}finally {
sqlSession.close();
}
}
分页查询:
配置分页插件的拦截器:位置在environments上面
<!--配置分页插件的拦截器-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 配置方言,使用的是那个库 ,在PageHelper5.0之后 不需要配置-->
<!-- <property name="dialect" value="mysql"/> -->
</plugin>
</plugins>
测试分页操作重点在测试方法这
public static void selectTest(){
SqlSession sqlSession=null;
PageInfo<UserBean> pageInfo=null;
try {
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
sqlSession= sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//设置分页参数
//设置分页的起始页码和每页的显示数据的行数
//PageHelper.startPage(pagecode,pageSize);
PageHelper.startPage(3,10);
List<UserBean> userBeanList = mapper.selectUser();
pageInfo=new PageInfo<UserBean>(userBeanList);
sqlSession.commit();
System.out.println("当前页===="+pageInfo.getPageNum());
System.out.println("每页数量===="+pageInfo.getPageSize());
System.out.println("当前页数量===="+pageInfo.getSize());
System.out.println("总记录数===="+pageInfo.getTotal());
System.out.println("总页数===="+pageInfo.getPages());
} catch (IOException e) {
e.printStackTrace();
}finally {
sqlSession.close();
}
}
可以进行封装:
可以遍历查到的数据
List<UserBean> userList=pageInfo.getList();
for(UserBean userBean:userList){
System.out.println(userBean.getUserid()+"----"+userBean.getUsername());
}
public static PageInfo<UserBean> selectTest(int pagecode,int pageSize){
SqlSession sqlSession=null;
PageInfo<UserBean> pageInfo=null;
try {
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
sqlSession= sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//设置分页参数
//设置分页的起始页码和每页的显示数据的行数
//PageHelper.startPage(pagecode,pageSize);
PageHelper.startPage(pagecode,pageSize);
List<UserBean> userBeanList = mapper.selectUser();
pageInfo=new PageInfo<UserBean>(userBeanList);
sqlSession.commit();
System.out.println("当前页===="+pageInfo.getPageNum());
System.out.println("每页数量===="+pageInfo.getPageSize());
System.out.println("当前页数量===="+pageInfo.getSize());
System.out.println("总记录数===="+pageInfo.getTotal());
System.out.println("总页数===="+pageInfo.getPages());
List<UserBean> userList=pageInfo.getList();
for(UserBean userBean:userList){
System.out.println(userBean.getUserid()+"----"+userBean.getUsername());
}
} catch (IOException e) {
e.printStackTrace();
}finally {
sqlSession.close();
}
return pageInfo;
}
selectTest(2,10);
2.mybatis逆向工程
2.1 generator自动生成工具
1.根据数据库表结构去自动生成项目中的java代码【实体类Javabean,数据访问接口,Mapper文件】
2.需要4个文件:
数据库驱动 核心 可执行文件 配置文件
3.需要修改的地方
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--数据库驱动-->
<classPathEntry location="mysql-connector-java-8.0.16.jar"/>
<context id="DB2Tables" targetRuntime="MyBatis3">
<commentGenerator>
<property name="suppressDate" value="true"/>
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--数据库链接地址账号密码-->
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3306/student_db?serverTimezone=UTC"
userId="root"
password="123456">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!--生成javabean类存放位置-->
<javaModelGenerator targetPackage="com.weiwei.mybatisdemo7.bean" targetProject="src">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!--生成SQL映射文件存放位置-->
<sqlMapGenerator targetPackage="com.weiwei.mybatisdemo7.mapper" targetProject="src">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!--生成Mapper接口存放位置-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.weiwei.mybatisdemo7.mapper" targetProject="src">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!--生成对应表及类名-->
<table tableName="t_person" domainObjectName="PersonBean" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true"/>
</context>
</generatorConfiguration>
4.修改完成之后,直接双击mybatis.bat即可,在src下面就会有对应的javabean类,mapper文件,和接口
5.然后按照以往的做法,生成的文件代码复制到该去的地方
6.测试
public class Main {
public static void insertTest(){
SqlSession sqlSession=null;
try {
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
sqlSession = sqlSessionFactory.openSession();
PersonBeanMapper mapper = sqlSession.getMapper(PersonBeanMapper.class);
PersonBean personBean = new PersonBean();
personBean.setPersonName("张三");
personBean.setPersonAge(22);
personBean.setPersonAddress("西安");
mapper.insert(personBean);
sqlSession.commit();
} catch (IOException e) {
e.printStackTrace();
}finally {
sqlSession.close();
}
}
public static void main(String[] args) {
insertTest();
}
}
添加成功!!!
7.错误解决
mybatis映射xml配置文件报错:<statement> or DELIMITER expected, got ‘id‘
编写mybatis生成的xml文件时,发现sql节点内报错。
这是IDEA的bug,发生在MyBatis的Mapper的XML文件的sql节点上,第一个字段是什么错误中最后一个单引号内部就是什么。
删除方框中的sql/