引言

分页有基于查询分页和基于缓存分页。

I 分页

1.1 基于查询分页

通过数据库中的伪列，每次查询一部分数据返回。

适合大数据量的分页操作
分页执行效率低，与数据库交互频繁。

select x.* from (select e.* ,rownum rr from emp e)x where x.rr between start and end;

package com.zhongx.jdbc;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class PageDemo {
 public static void main(String[] args) {
  selectPage(3,5);
 }
 
 /**
  * 
  * @param page 显示页码
  * @param pageSize 每页显示记录数
  * start=(page-1)*pageSize+1
  * end = page*pageSize
  * endPage = (sum/page)+(sum%page==0?0:1)
  */
 public static void selectPage(int page,int pageSize){
  Connection conn =  null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  String sql = "select e.* from " +
  "(select emp.*,rownum rr from emp ) e " +
  "where e.rr>=? and e.rr<=?";
  System.out.println(sql);
  try {
   int start = (page-1)*pageSize+1;
   int end = page*pageSize;
   
   conn = ConnectionUtil.getConnection();
   pstmt = conn.
   prepareStatement(sql);
   pstmt.setInt(1, start);
   pstmt.setInt(2, end);
   rs = pstmt.executeQuery();
   while(rs.next()){
    System.out.println(rs.getString(2));
   }
  } catch (SQLException e) {
   
   e.printStackTrace();
  }finally{
   try {
    ConnectionUtil.close(conn, pstmt, rs);
   } catch (SQLException e) {
    
    e.printStackTrace();
   }
  }
 }
}

1.2 基于缓存分页

执行一次sql，将结果集的所有数据查询出来，存储在当前应用服务器的缓冲中，然后通过可滚动的结果集进行分页显示；

会将结果集全部放入内存，对于内存压力大。
不适合大数据量的分页操作。
分页时，第一次执行效率慢，以后执行效率高。

II 查询优化

案例：【Java小技能：多级菜单排序并返回树结构菜单列表】

https://blog.csdn.net/z929118967/article/details/128025800

2.1 分页查询树结构菜单列表

PageHelper直接对List进行分页

    /*
     * PageHelper直接对List进行分页
     */
    private PageInfo getPageInfoByList(List all,Integer pageNum,Integer pageSize) {
        //创建Page类
        Page page = new Page(pageNum, pageSize);
//为Page类中的total属性赋值
        int total = all.size();
        page.setTotal(total);
//计算当前需要显示的数据下标起始值
        int startIndex = (pageNum - 1) * pageSize;
        int endIndex = Math.min(startIndex + pageSize,total);
//从链表中截取需要显示的子链表，并加入到Page
        if (total > 0) {
//从链表中截取需要显示的子链表，并加入到Page
            page.addAll(all.subList(startIndex, endIndex));
        }
//以Page创建PageInfo
        PageInfo pageInfo = new PageInfo<>(page);
        return pageInfo;
    }

PageHelper.startPage 开启分页,通过拦截MySQL的方式,把你的查询语句拦截下来加limit.将查询语句放到PageHelper.startPage后面进行执行

        PageHelper.startPage(input.getPageNum(), input.getPageSize());
         List<TSysTaoCollege> collegeList = tSysTaoCollegeService.list(lambda);
         PageInfo pageInfo = new PageInfo(collegeList);
         PageHelper.clearPage();

2.2 地区和菜单数据添加到Redis缓存

使用StringRedisTemplate

写入Redis时，手动把对象序列化为json格式字符串。
读取Redis时，手动把读取到的JSON反序列化成对象。

    /*
      先获取全部菜单，然后再对菜单进行装配，生成树形结构
     */
    public List<SysMenuDto> getMenus(ETagsType tagsType, Boolean isReadDb) throws Exception {
        List<TSysMenu> list = getTmenus(tagsType,isReadDb);//读取表数据
        List<SysMenuDto> listDto = getSortMenus(list);//序列化数据
        return listDto;
    }

    public List<TSysMenu> getTmenus(ETagsType tagsType, Boolean isReadDb) throws Exception{
        if (tagsType == null) {
            throw CommonException.create(ServerResponse.createByError("菜单对象类型不能为空"));
        }
        //rediskey名：rediskey常量类+菜单对象类型
        String keyName = RedisKeyConstant.MENU + "." + tagsType.getCode();//
        QueryWrapper<TSysMenu> queryWrapper = new QueryWrapper<>();
        //是否读取数据库的菜单，true读取，false不读取
        if (isReadDb) {
            queryWrapper.eq("tags_type", tagsType);
            List<TSysMenu> list = tSysMenuMapper.selectList(queryWrapper);
            stringRedisTemplate.opsForValue().set(keyName, JSONUtil.toJsonStr(list));//写入数据到缓存
            return list;
        } else {
            //读取缓存
            if (!stringRedisTemplate.hasKey(keyName)) {
                queryWrapper.eq("tags_type", tagsType);
                List<TSysMenu> list = tSysMenuMapper.selectList(queryWrapper);
                stringRedisTemplate.opsForValue().set(keyName, JSONUtil.toJsonStr(list));//写入数据到缓存
                return list;
            } else {
                //直接读取缓存数据进行序列化
                String jsonStr = stringRedisTemplate.opsForValue().get(keyName);
                List<TSysMenu> list = JSONUtil.toList(jsonStr, TSysMenu.class);
                return list;
            }
        }
    }

2.3 分页新增汇总信息

汇总信息

@Data
public class PageSumInfo<T> extends PageInfo<T> {

    private T sumInfo;

    public PageSumInfo(List<? extends T> list) {
        super(list);
    }
}

用法

if (sysUserList!=null && !sysUserList.isEmpty())
            {
                queryWrapper.select("ifnull(sum(pay_amount),0) as pay_amount,ifnull(sum(fee),0) as fee");
                var tTransFlow =  tTransFlowService.getOne(queryWrapper);
                pageInfo.setSumInfo(tTransFlow);
            }

III JDBC批处理

大批量执行sql,为了提高效率采用批处理。

package com.zhongx.jdbc;
 
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
 
public class TestBatchDemo {
 public static void main(String[] args) {
  testBatch();
 }
 public static void testBatch(){
  try {
   Connection conn = ConnectionUtil.getConnection();
   Statement stmt = conn.createStatement();
   
   
   for(int i=0;i<10000;i++){
    String sql = "insert into t_user(id,username) values(test_seq.nextval,'name"+i+"')";
    //将执行的sql存入批处理 缓存
    stmt.addBatch(sql);
    if(i%1000==0){
     //执行当前批处理缓存中的所有SQL
     int[] num = stmt.executeBatch();
     System.out.println("length:"+num.length);
     System.out.println("num[0]:"+num[0]);
     
     //清除当前缓存中的SQL
     stmt.clearBatch();
    }
   }
   stmt.executeBatch();
  } catch (SQLException e) {
   
   e.printStackTrace();
  }finally{
   try {
   if(rs!=null){
    rs.close();}
   if(pstmt!=null){
    pstmt.close();}
   if(conn!=null){
    conn.close();}
   } catch (SQLException e) {
    
    e.printStackTrace();
   }
  }
  
 }
}

IV 数据库连接池

数据库连接池节省创建连接的时间；减少连接打开和关闭所消耗的资源。

4.1 导入依赖的jar包

导入数据库连接池依赖的jar包 commons-dbcp-1.4.jar和commons-pool.jar
导入数据库驱动包:ojdbc14.jar

4.2 书写连接程序

创建连接池对象

BasicDataSource dbs=new BasicDataSource();

dbs.setUrl(jdbc:oracle:thin:@ip:port:sid);

dbs.setUsername(“username”);

dbs.setPassword("password");

dbs.setDriverClassName("oracle.jdbc.OracleDriver");

通过连接池对象获得数据库连接Connection conn=bds.getConnection();

package com.zhongx.jdbc;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import org.apache.commons.dbcp.BasicDataSource;
 
public class TestJDBCPoolDemo {
 public static void main(String[] args) {
  pool();
 }
 public static void pool(){
  //创建连接池对象
  BasicDataSource bds= new BasicDataSource(); 
  bds.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
  bds.setUsername("iOS逆向");
  bds.setPassword("gzh");
  bds.setDriverClassName("oracle.jdbc.OracleDriver");
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  try {
   conn = bds.getConnection();
   pstmt = conn.prepareStatement("select * from emp");
   rs =  pstmt.executeQuery();
   while(rs.next()){
    System.out.println(rs.getString(2));
   }
  
  } catch (SQLException e) {
   e.printStackTrace();
  }finally{
   try {
   if(rs!=null){
    rs.close();}
   if(pstmt!=null){
    pstmt.close();}
   if(conn!=null){
    conn.close();}
   } catch (SQLException e) {
    e.printStackTrace();
   }
   }
  
  
 }
}

V 结果集 ResultSet

ResultSet默认只能向前遍历，并且只能遍历一次。

5.1 ResultSet的常数

static int TYPE_SCROLL_INSENSITIVE The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. 设置获得的结果集是可滚动的，并且不敏感（数据库的数据变动不能及时反映到结果集）不受ResultSet底层数据更改的影响；而敏感是指将sql结果集的rowid存于缓存中，显示时通过rowId查询。
static int TYPE_SCROLL_SENSITIVE The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet.
static int CONCUR_READ_ONLY The constant indicating the concurrency mode for a ResultSet object that may NOT be updated. 设置结果集只读
static int CONCUR_UPDATABLE The constant indicating the concurrency mode for a ResultSet object that may be updated. 设置可更新的结果集。

5.2 更新

void updateString(int columnIndex, String x) Updates the designated column with a String value. 更新当前记录的字段
void updateString(String columnLabel, String x) Updates the designated column with a String value.
void updateRow() Updates the underlying database with the new contents of the current row of this ResultSet object. 更新记录到数据库（同步）

5.3 定位

int getRow() Retrieves the current row number. 获得行号
void beforeFirst() Moves the cursor to the front of this ResultSet object, just before the first row. 定位到第一条记录之前
void afterLast() Moves the cursor to the end of this ResultSet object, just after the last row. 定位到最后一条记录之后
void deleteRow() Deletes the current row from this ResultSet object and from the underlying database.删除当前行的记录，并同步到数据库。

5.4 插入

void moveToInsertRow() Moves the cursor to the insert row. 将指针指向将要插入数据的行

void updateInt(int columnIndex, int x) Updates the designated column with an int value.
void insertRow() Inserts the contents of the insert row into this ResultSet object and into the database.同步到数据库

5.5 可滚动结果集代码示例

package com.zhongx.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestScrollDemo {
 public static void main(String[] args) {
  //testUpdate();
  testDelete();
  //testInsert();
 }
 public static void testInsert(){
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  try {
   conn = ConnectionUtil.getConnection();
   pstmt = conn.prepareStatement("select empno,ename,job from emp"
     , ResultSet.TYPE_SCROLL_INSENSITIVE
     , ResultSet.CONCUR_UPDATABLE);
   //获得可更新的结果集
   rs = pstmt.executeQuery();
   //将指针指向将要插入的行
   rs.moveToInsertRow();
   rs.updateInt(1, 1001);
   rs.updateString(2, "god");
   rs.updateString(3, "xxx");
   //执行插入操作
   rs.insertRow();
   
  } catch (SQLException e) {
   
   e.printStackTrace();
  }finally{
   try {
    ConnectionUtil.close(conn, pstmt, rs);
   } catch (SQLException e) {
    
    e.printStackTrace();
   }
  }
 }
 public static void testDelete(){
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  try {
   conn = ConnectionUtil.getConnection();
   pstmt = conn.prepareStatement("select empno,ename,job from emp"
     , ResultSet.TYPE_SCROLL_INSENSITIVE
     , ResultSet.CONCUR_UPDATABLE,ResultSet.CLOSE_CURSORS_AT_COMMIT);
   //获得可更新的结果集
   rs = pstmt.executeQuery();
   rs.absolute(17);
   rs.deleteRow();
   
   rs.beforeFirst();
   while(rs.next()){
    System.out.println(rs.getString(2));
   }
  } catch (SQLException e) {
   
   e.printStackTrace();
  }finally{
   try {
    ConnectionUtil.close(conn, pstmt, rs);
   } catch (SQLException e) {
    
    e.printStackTrace();
   }
  }
  
 }
 public static void testUpdate(){
  try {
   Connection conn = ConnectionUtil.getConnection();
   PreparedStatement pstmt = conn.prepareStatement("select empno,ename,job from emp"
     , ResultSet.TYPE_SCROLL_INSENSITIVE
     , ResultSet.CONCUR_UPDATABLE);
   //获得可更新的结果集
   ResultSet rs = pstmt.executeQuery();
   //定位到结果集的第三条记录
   rs.absolute(3);
   //更新第三条记录 的第二个字段
   rs.updateString(2, "name");
   //更新第三条记录 的第三个字段
   rs.updateString(3, "teacher");
   //更新记录到数据库
   rs.updateRow();
   //将指针指向当前结果集第一条记录的前面
   rs.beforeFirst();
   //指针执行最后一条的后面
   //rs.afterLast();
   
   while(rs.next()){
    System.out.println("行号："+rs.getRow()+"\t"+rs.getString(2));
   }
   
   
   
  } catch (SQLException e) {
   
   e.printStackTrace();
  }
 }
}

Java小技能：分页

引言