【原创】JAVA实现关系数据库的翻页

Question

【原创】JAVA实现关系数据库的翻页 by masse@CSDN：    这个方法没有什么特别的，其实也算不上原创。也是在学习中得出来的。因此如果侵犯了别人的权益，请来信通知。为了方便初学者了解原理，代码中我没有使用任何框架。代码可以适用于mysql，oracle，sqlserver，db2等等主流关系数据库。
    以下代码是我从自己以前写的程序中抽取出来的核心，只能表述思路，如要使用，可能还需要很多调整。(1) 数据库表sys_User定义如下：
           ID   Name    Email                  Desc        …
           1    masse   [email protected]    当午        …
       其中，ID为整型，其它均为字符串。(2) User类，请自行为每个方法添加getter和setter方法。为了方便，我写了User(ResultSet)这个构造函数。
public class User
{
    public User(ResultSet rs)
    {
        id = rs.getInt("ID");
        name = rs.getString("Name");
        email = rs.getString("Email");
        desc = rs.getString("Desc");
    }
    private int id;
    private String name;
    private String email;
    private String desc;
}(3) sysUserTable类，用于数据库读取,我这里仅仅给出了最核心部分。其中对于start,count等的有效性参数，请自行判断；sql语句如果有查询的filter，或者order等语句，也可根据情况自行调整。我只给出程序框架。// 取出从start开始的count条记录。如，start为1，count为20，则取出数据库前20条记录
public class SysUserTable
{
    
    public List getUsers(Connection conn, int start, int count) throws
        Exception
    {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        List result = new ArrayList();
        try
        {
            String sql = "SELECT * FROM sys_User";
            pStmt = conn.preparedStatement(sql);
            rs = pStmt.executeQuery();
            if (rs.absolute(startRow))
            {
                result.add(new User(rs));
                for (int rowIndex = 0; rowIndex < rowCount - 1; rowIndex++)
                {
                    if (!rs.next())
                    {
                        break;
                    }
                    result.add(new User(rs));
                }
            }
        }
        catch (Exception e)
        {
            throw e; // process exception here
        }
        finally
        {
            // close rs & pStmt here
        }
        return result;
    }
}(4) JSP页面的实现。基本的思路就是，根据当前页数，计算出需要的start即可。如指定每页count条，当前在第n页，那么start=(n-1)*count+1。然后作为参数，即可得到翻页结果集。具体的实现可以用传统的翻页，也可用ajax。这里灵活性很大，就不再赘述了。

Answer 1

方法很简单，都有些不要意思贴出来。
但是觉得优势有以下
（1）没有使用任何框架，适合初学者，也适合对某些成型系统进行升级修改。同时扩展性很强。
（2）使用几乎所有的关系数据库，因为没有使用任何特殊的sql语法。比如top ，limit，rownum等等，sql也可以根据情况修改，实现任何查询的翻页。
（3）性能：具体性能没有经过测试，以前在一个复杂的应用下，测过50万的数据，还不错。如果应用简单，这个性能指数会有所上升。

Answer 2

分页 
感觉分2种一种逻辑上实现分页 一种数据上实现分页
2种加起来才算一个分页
感觉你的逻辑和数据混合了你的table类写成接口感觉比较好
public interface SysUserTable
{
    public List getUsers(Connection conn, int start, int count) throws
        Exception;
}
你的table类写成接口感觉比较好 愚见了 哈哈

Answer 3

我具体实现里面全是接口，
而且代码是写了另外的代码来自动生成的，
相当于是自己实现的一套框架（那个框架最初是一个同事写的，很牛的人）。最后封装的形式如下public interface Service
{
   public int getUserCount()throws Exception;
   public int getUserCount(String filter)throws Exception;
   public List getUsers()throws Exception;
   public List getUsers(String filter)throws Exception; 
   public List getUsers(int start ,int count)throws Exception;  
   public List getUsers(String filter,int start ,int count)throws Exception;
   public List getUsers(String filter,int start ,int count,String[] sortNames,int[] sortTypes)throws Exception;
}仅一个单独的实体表相关的接口就有几十个。
因为是用自己写的框架生成的，所以也不存在工作量的问题。

Answer 4

整个系统的底层都从上面的接口作为入口，所有相关的数据库操作，都封装以达到事务安全。
也不会把连接暴露出来。
在security方面，可以再加入接口调用的登陆认证。如果要针对数据库优化，只需要改动生成代码的框架，改里面的代码模板，
改几行，就能实现代码的重构。。我给出的仅仅是通用数据库无框架实现翻页的雏形罢了

Answer 5

这种分页查询时会把记录全部查出，感觉效率还是要差一些，所以还是用SQL分页好一些，不过由于各种数据库对SQL查询记录的限制语句差别都比较大，oracel用rownum，mysql用limit，sqlserver用top，所以没法写成比较通用的语句

Answer 6

所以我的这个是通用的。而且我也说了，只适合数据量50万左右的。
看我上面帖子里面，不使用，top ，limit，rownum同时我在具体项目的实现里面是做了数据库区分优化的，会根据不同的数据库进行不同的优化策略调整。

Answer 7

jianghuxiaoxiami() up you!是什么意思??上你????????

Answer 8

最近用Java做了一点和分页有关的开发，用的是SQL分页的方式，总觉着有点麻烦，需要将分页的代码嵌到从数据库查询到页面代码所有的方面，什么时候能像.net那么方便就好了

Answer 9

早已用过，翻页，java模式之一

Answer 10

.net的分页虽然简单,但是太垃圾了,100w数据翻页就不会动了

Answer 11

rs.absolute(startRow)这个不知道快不快阿，但感觉应该不会太慢阿！

Answer 12

这是一种思路,实际上我也用过,很简便,不过最好根据mysql/sql server/oracle单独实现分页类

Answer 13

有点疑惑：
多次调用getUsers(Connection conn, int start, int count)时，它不是多次查询了吗？
如果这中间表里的数据发生变化了呢？？岂不是两次的rs都不一样?

Answer 14

作者:写在类文件中的语句  String sql = "SELECT * FROM sys_User";编译后就固定了如果分页的要求是有选择的  比如在学生数据库中选择男生 如 String sql = "SELECT * FROM sys_User where sex='m'";后再分页显示. 作者的方法还有效吗?有没有更好的办法?不成熟的看法,欢迎指正!

Answer 15

to javailoveu() 这个简单，
比如我写一个通用的/**
*  filter：查询串，sortNames排序字段，sortTypes排序类型，1为升序，0为降序
*  start为开始记录，count为返回记录数
*/
public List getUsers(Connection conn, String filter,String[] sortNames,int[] sortTypes,int start, int count) throws
        Exception
{
   String sql = "SELECT * FROM sys_User";
   if(filter!=null)
  { 
     sql += " " + filter;
  }
  if(sortNames!=null && sortNames.length>0)
  {
     sql += " ORDER BY";
     for(int i=0;i<sortNames.length;i++)
     {
           sql += " " + sortNames[i] + (sortTypes[i]==1?" ASC":" DESC");
           if(i<sortNames.length-1) sql +=",";
     }
  }
  // 下面的同我上面的代码 
}这样就能实现分页＋查询＋排序了。
此时，原来的方法，就可以写成
public List getUsers(Connection conn, int start, int count) throws
        Exception
{
   return getUsers(conn,null,null,null,start,count);
}当然，这个为了实现通用，代价就是效率问题。上面已经提到。
但是这个非常简单，
即便以后数据库换成xml，也可用xml-jdbc进行转换，
不必要关心数据库类型。

Answer 16

关于效率：另外一个就是在底层封装。
sqlserver有人说用top，但是用top的效率很低，尤其是在要排序的情况下分页。mysql用limit
oracle用rownum都在底层用接口，根据数据库类型得到不同的sql语句，
如果是未知关系数据库，就缺省用通用的。鱼和熊掌不可兼得。有时候为了效率，的确要麻烦点。但通常自己做个东西玩，没有多少数据量，为了移植方便，最好通用。
有时候甚至直接用xml就代替了数据库。

Answer 17

纠正一下那个带filter得方法   if(filter!=null)
  { 
     sql += " WHERE " + filter;
  }

Answer 18

我们也学hibenate搞配置文件把完整的sql文写道配置文件里XXXXX=select * from table用RsourceBundle读配置文件这样也省去配sql文的麻烦了而且可以达到修改文件而不修改类的目的

Answer 19

其实我的模型是这样的，
定义一个xml文件
<table intf='User' name='sys_User' version="1.0" >
   <column name="id" type="int" primary-key="true"/>
   <column name="username" type="string" length="20"/>
   <column name="password" type="string"/>
</table>然后根据数据库类型参数（mysql/oracle/sqlserver）
自动生成对应的sql语句（用以初始化数据库）
自动生成User类
自动生成User的底层接口和apache-db下的一个项目很类似。

Answer 20

不如学用iBatis吧，把sql语句写在xml文件里，根据不同数据库作sql映射，
可以参考他的官方网。

Answer 21

如过要通用的话。
用Hibernate吧，感觉如果要作成通用的话，工程很大。
楼主的封装方法和Hibernate的行封装方法很象。^-^

Answer 22

to:masse(当午) 
谢谢你的答疑.  学了不少.虽说可能会降低效率,不过你的解决方法是我见过最简单简洁的了(非框架)!

Answer 23

这个方法就是处理外键和关联的时候很麻烦。一直想抽点时间把这个框架整理一下，
直接定义简单的xml文件(1)生成通用数据库初始化sql脚本，数据库说明文档
(2)生成底层类和接口（能够处理外键、关联等复杂逻辑，并且根据数据库类型进行特定优化）
(3)生成界面元素和布局（div+css，支持autocomplete，级联select等常见ajax特性，支持日期选择框等常见控件）
(4)支持数据库脚本的版本，能够自动生成数据库升级脚本（如从1.0到2.0改动了数据库，可以取出任意两个版本之间的差异升级脚本），数据库升级的说明文档单个的功能都已经在平时的工作中做过，尽管效果没有理想的好。
最近一直太忙，这个工作量也非常大，实在抽不出时间总结。呵呵。

Answer 24

上面很多提到“效率”问题，不知道大家是不是特指“时间效率”。其实，“空间效率”也是一个很大的问题。我就遇到过一个问题，MySQL 的 JDBC Driver（就是那个 ConnectJ），在结果集内容特别多时，会出现内存崩溃，因为它不管你是不是要提取里面的数据，总是要先把所有数据下载到 Client 端。楼主提到的 50w 条记录，我不知道是指“TABLE 里面的记录数”还是“ResultSet 里面的记录数”。如果是前者，其实，这个记录数跟楼主的“分页机制”没有太大的关系，所谓的效率，无非是数据库优化的问题；如果是后者，那么，这个问题就大了，就算你现在能支持 50w 条，可是，作为服务器端程序，如果稍微多几个并发访问，那是一定吃不消的。而“分页机制”，往往都是在“符合查询条件的记录数”非常大的情况下才需要使用的，这个“空间效率”是必需考虑的。所谓“通用的方法”，只能表面上实现功能，无法适应实际产品和生产环境的要求。anyway, 对楼主的原创精神深表敬佩。而且楼主也说了，实际项目中做了很多完善。我这里不过是一家之言，谨供参考 :)

Answer 25

个人认为，返回的resultset只包含需要的数据才算得上真正的分页。如楼上所言，楼主目前的resultset包含全部数据，内存消耗太大，网络环境下还有带宽问题。是无法经受严厉的并发考验的。

Answer 26

实际项目中的优化如下：public interface DBManager
{
    public String getPagedSqlString(String sql,int start,int end);
}public class DefaultManager implements DBManager
{ 
    public String getPagedSqlString(String sql,int start,int end)
    { // 这里就是第一篇文章中的sql语句，不再重复
     }
}public class OracleManager implements DBManager
{
    public String getPagedSqlString(String sql,int start,int end)
    {
       // sql + " WHERE ROWNUM<="+end+" AND ROWNUM>="+start;
    }
}public class MysqlManager implements DBManager
{
    public String getPagedSqlString(String sql,int start,int end)
    {
       // sql + " LIMIT "+start+","+end;
    }
}public class SybaseManager implements DBManager
{
    public String getPagedSqlString(String sql,int start,int end)
    {
       // use rowcount;
    }
}public class MssqlManager implements DBManager
{
    public String getPagedSqlString(String sql,int start,int end)
    {
       // use top;
    }
}public class Db2Manager implements DBManager
{
    public String getPagedSqlString(String sql,int start,int end)
    {
       // use fetch first n row only;
    }
}如果没有找到对应的数据库类型，就用default的。

Answer 27

hehe, 相对于前面的“分页算法”而言，这种“针对不同数据库的优化方法”更有价值，更有“框架”的味道  ;D