-- 判斷數(shù)據(jù)表是否存在，存在則刪除
DROP TABLE IF EXISTS tb_user;
 
-- 創(chuàng)建“用戶信息”數(shù)據(jù)表
CREATE TABLE IF NOT EXISTS tb_user
( 
	user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用戶編號(hào)',
	user_name VARCHAR(50) NOT NULL COMMENT '用戶姓名',
	province VARCHAR(50) NOT NULL COMMENT '省份',
	create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注冊(cè)時(shí)間'
) COMMENT = '用戶信息表';
 
-- 添加數(shù)據(jù)
INSERT INTO tb_user(user_name,province) VALUES
('pan_junbiao的博客_01','廣東省'),('pan_junbiao的博客_02','黑龍江省'),('pan_junbiao的博客_03','山東省'),('pan_junbiao的博客_04','安徽省'),('pan_junbiao的博客_05','黑龍江省'),
('pan_junbiao的博客_06','江蘇省'),('pan_junbiao的博客_07','黑龍江省'),('pan_junbiao的博客_08','廣東省'),('pan_junbiao的博客_09','陜西省'),('pan_junbiao的博客_10','廣東省'),
('pan_junbiao的博客_11','廣東省'),('pan_junbiao的博客_12','江蘇省'),('pan_junbiao的博客_13','陜西省'),('pan_junbiao的博客_14','安徽省'),('pan_junbiao的博客_15','山東省'),
('pan_junbiao的博客_16','陜西省'),('pan_junbiao的博客_17','安徽省'),('pan_junbiao的博客_18','江蘇省'),('pan_junbiao的博客_19','黑龍江省'),('pan_junbiao的博客_20','安徽省'),
('pan_junbiao的博客_21','江蘇省'),('pan_junbiao的博客_22','廣東省'),('pan_junbiao的博客_23','安徽省'),('pan_junbiao的博客_24','陜西省'),('pan_junbiao的博客_25','廣東省'),
('pan_junbiao的博客_26','廣東省'),('pan_junbiao的博客_27','安徽省'),('pan_junbiao的博客_28','山東省'),('pan_junbiao的博客_29','山東省'),('pan_junbiao的博客_30','黑龍江省'),
('pan_junbiao的博客_31','廣東省'),('pan_junbiao的博客_32','江蘇省'),('pan_junbiao的博客_33','陜西省'),('pan_junbiao的博客_34','安徽省'),('pan_junbiao的博客_35','山東省');

<!-- SpringBoot/MyBatis使用PageHelper分頁(yè)控件 -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.13</version>
</dependency>
 
<!-- MySQL的JDBC數(shù)據(jù)庫(kù)驅(qū)動(dòng) -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.20</version>
</dependency>

spring:
  #DataSource數(shù)據(jù)源
  datasource:
    url: jdbc:mysql://localhost:3306/db_admin?useSSL=false&amp
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
 
#MyBatis配置
mybatis:
  type-aliases-package: com.pjb.entity #別名定義
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #指定 MyBatis 所用日志的具體實(shí)現(xiàn)，未指定時(shí)將自動(dòng)查找
    map-underscore-to-camel-case: true #開啟自動(dòng)駝峰命名規(guī)則（camel case）映射
    lazy-loading-enabled: true #開啟延時(shí)加載開關(guān)
    aggressive-lazy-loading: false #將積極加載改為消極加載（即按需加載）,默認(rèn)值就是false
    lazy-load-trigger-methods: "" #阻擋不相干的操作觸發(fā)，實(shí)現(xiàn)懶加載
    cache-enabled: true #打開全局緩存開關(guān)（二級(jí)環(huán)境），默認(rèn)值就是true
 
#MyBatis使用pageHelper分頁(yè)
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true

package com.pjb.entity; 
import org.springframework.stereotype.Component;
import java.util.Date;
 
/**
 * 用戶信息實(shí)體類
 * @author pan_junbiao
 **/
@Component
public class UserInfo
{
    private int userId; //用戶編號(hào)
    private String userName; //用戶姓名
    private String province; //省份
    private Date createTime; //注冊(cè)時(shí)間
 
    //省略getter與setter方法...
 
    @Override
    public String toString()
    {
        return "編號(hào)：" + this.getUserId() +" 姓名：" + this.getUserName() + " 省份：" + this.getProvince();
    }
}

package com.pjb.entity; 
/**
 * 用戶查詢條件類
 * @author pan_junbiao
 **/
public class UserSearchParam
{
    private String userName; //用戶姓名
    private String province; //省份 
    //省略getter與setter方法...
}

package com.pjb.mapper; 
import com.pjb.entity.UserInfo;
import com.pjb.entity.UserSearchParam;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Repository; 
import java.util.List;
 
/**
 * 用戶信息Mapper動(dòng)態(tài)代理接口
 * @author pan_junbiao
 **/
@Mapper
@Repository
public interface UserMapper
{
    /**
     * 查詢用戶列表
     */
    @SelectProvider(type = UserSqlBuilder.class, method = "searchUserList")
    public List<UserInfo> searchUserList(UserSearchParam param);
 
    //建議將SQL Builder以映射器接口內(nèi)部類的形式進(jìn)行定義
    public class UserSqlBuilder
    {
        public String searchUserList(UserSearchParam param)
        {
            return new SQL()
            {
                {
                    SELECT("*");
                    FROM("tb_user");
                    WHERE("1 = 1");
                    if(param!=null)
                    {
                        //用戶姓名
                        if(param.getUserName()!=null && param.getUserName().length()>0)
                        {
                            WHERE("user_name like '%${userName}%'");
                        }
 
                        //省份
                        if(param.getProvince()!=null && param.getProvince().length()>0)
                        {
                            WHERE("province = #{province}");
                        }
                    }
                }
            }.toString();
        }
    }
}

@Autowired
private UserMapper userMapper; 
/**
 * 分頁(yè)查詢用戶列表
 * @author pan_junbiao
 */
@Test
public void searchUserByParam()
{
    int pageIndex = 2; ///獲取第2頁(yè)的數(shù)據(jù)
    int pageSize = 10; //每頁(yè)10條數(shù)據(jù)
    String orderBy = "create_time ASC"; //排序
 
    //分頁(yè)信息
    PageHelper.startPage(pageIndex, pageSize, orderBy);
 
    //查詢條件類
    UserSearchParam userSearchParam = new UserSearchParam();
    //userSearchParam.setUserName("pan_junbiao的博客"); //查詢條件1
    //userSearchParam.setProvince("廣東省"); //查詢條件2
 
    //執(zhí)行分頁(yè)查詢
    PageInfo<UserInfo> userInfoPage = new PageInfo<UserInfo>(userMapper.searchUserList(userSearchParam));
 
    //打印用戶列表
    System.out.println("\n");
    List<UserInfo> userInfoList = userInfoPage.getList();
    userInfoList.stream().forEach(System.out::println);
 
    //打印分頁(yè)信息
    System.out.println("當(dāng)前頁(yè)碼：第" + userInfoPage.getPageNum() + "頁(yè)");
    System.out.println("分頁(yè)大小：每頁(yè)" + userInfoPage.getPageSize() + "條");
    System.out.println("數(shù)據(jù)總數(shù)：共" + userInfoPage.getTotal() + "條");
    System.out.println("總頁(yè)數(shù)：共" + userInfoPage.getPages() + "頁(yè)");
}

<dependency>                           
  <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper</artifactId>
     <version>4.1.4</version>
</dependency>

<?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>
  <plugins>
      <plugin interceptor="com.github.pagehelper.PageHelper">
          <!--設(shè)置數(shù)據(jù)可類型Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六種數(shù)據(jù)庫(kù)-->
          <property name="dialect" value="mysql"/>
      </plugin>
  </plugins>
</configuration>

<!--配置SqlSessionFactory對(duì)象 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:mybatis-config.xml" />
        <property name="typeAliasesPackage" value="com.aoChine.model.entity" />
        <property name="mapperLocations" value="classpath:mapper/*.xml" />
 
        <!-- 配置mybatis分頁(yè)插件PageHelper -->
        <property name="plugins">
            <array>
                <bean class="com.github.pagehelper.PageInterceptor">
                    <property name="properties">
                        <!-- 什么都不配，使用默認(rèn)的配置 -->
                        <value></value>
                    </property>
                </bean>
            </array>
        </property>
    </bean>

public class EasyUIDataGridResult implements Serializable { 
    private long total;
    private List rows; 
    public long getTotal() {
        return total;
    }
 
    public void setTotal(long total) {
        this.total = total;
    }
 
    public List getRows() {
        return rows;
    }
 
    public void setRows(List rows) {
        this.rows = rows;
    } 
}

PageHelper.startPage(1, 10);
List<Country> list;
if(param1 != null){
    list = countryMapper.selectIf(param1);
} else {
    list = new ArrayList<Country>();
}

List<Country> list;
if(param1 != null){
    PageHelper.startPage(1, 10);
    list = countryMapper.selectIf(param1);
} else {
    list = new ArrayList<Country>();
}

PageHelper.clearPage();