db2 union 分页问题

    对于db2 union 分页sql的写法

如：

最终sql:

sql="

select * from (
select t.INSTANCEID as instanceid,a.WORKSHEETID as worksheetid,a.WORKSHEETTITLE as worksheettitle, a.CREATOR as
creator,t.WFSIGN as wfsign,t.NODEID as nodeid,t.WFNAME as wfname,t.APPSIGN as appsign,t.NODENAME as nodename,a.DEALPERSONS as
transactor,a.DEALPOSTS as deallorgname,a.DEALUNITS as dealactiionname,up.CHANGELEVEL as level,up.ISURGENT as isUrgent,0 as reportTime,0
as occurTime,'' as source,up.CREATORGROUP as department,up.CHANGECLASSIC as changeClassic,up.EXPECTEDCOMPLETIONTIME
as expectedcompletiontime,up.PLANBEGINTIME as planBeginTime,up.PLANENDTIME as planEndTime,up.CHANGEIMPLEMENTATEDEP
as changeEimplementatedep,up.RISKLEVEL as riskLevel ,rownumber() over() as rowid from wf_worklist as t join WF_NODE_ACTION_RECORD
as n on n.instanceid=t.instanceid,UP_BASEINFOR a,UP_CHANGE up where a.ENTRYID=t.INSTANCEID and a.ENTRYID=up.ENTRYID and t.BDRAFT<>'1'
and t.WFSIGN='CHANGE' and n.ROUTEID in('123_121','123_122','123_200','123_124','123_127','123_129','123_136','123_138','123_146','123_151',
'123_154','123_181','123_183','123_184','123_185','123_186','123_187','123_188','123_189','123_190','123_191','123_192','123_193',
'123_194','123_196','123_197','123_198','123_199') and t.ALLREADERSLIST like '%admin%'
and not exists(select * from WF_NODE_ACTION_RECORD nn where nn.instanceid=n.instanceid and nn.acttime>n.acttime)
union
select t.INSTANCEID as instanceid,a.WORKSHEETID as worksheetid,a.WORKSHEETTITLE as worksheettitle, a.CREATOR as creator,t.WFSIGN as wfsign,
'' as nodeid,t.WFNAME as wfname,t.APPSIGN as appsign,'' as nodename,a.DEALPERSONS as transactor,a.DEALPOSTS as deallorgname,a.DEALUNITS
as dealactiionname,up.CHANGELEVEL as level,up.ISURGENT as isUrgent,0 as reportTime,0 as occurTime,'' as source,up.CREATORGROUP
as department,up.CHANGECLASSIC as changeClassic,up.EXPECTEDCOMPLETIONTIME as expectedcompletiontime,up.PLANBEGINTIME as planBeginTime,
up.PLANENDTIME as planEndTime,up.CHANGEIMPLEMENTATEDEP as changeEimplementatedep,up.RISKLEVEL as riskLevel,rownumber() over()
as rowid from WF_INSTANCE_END as t join WF_NODE_ACTION_RECORDEND as n on n.instanceid=t.instanceid,UP_BASEINFOR a,UP_CHANGE up
where a.ENTRYID=t.INSTANCEID and a.ENTRYID=up.ENTRYID and t.WFSIGN='CHANGE' and n.ROUTEID in('123_121','123_122','123_200','123_124',
'123_127','123_129','123_136','123_138','123_146','123_151','123_154','123_181','123_183','123_184','123_185','123_186','123_187',
'123_188','123_189','123_190','123_191','123_192','123_193','123_194','123_196','123_197','123_198','123_199')
and t.LASTUSER like '%admin%' and days(date('2012-1-11'))-days (date(n.ACTTIME))<=0
and not exists(select * from WF_NODE_ACTION_RECORDEND nn where nn.instanceid=n.instanceid and nn.acttime>n.acttime))as a
where a.rowid >=1 and a.rowid <11"

第一步：

拼装自己的sql：

sqlBuf=“

select t.INSTANCEID as instanceid,a.WORKSHEETID as worksheetid,a.WORKSHEETTITLE as worksheettitle, a.CREATOR as creator,t.WFSIGN as wfsign,
t.NODEID as nodeid,t.WFNAME as wfname,t.APPSIGN as appsign,t.NODENAME as nodename,a.DEALPERSONS as transactor,a.DEALPOSTS as deallorgname,
a.DEALUNITS as dealactiionname,up.CHANGELEVEL as level,up.ISURGENT as isUrgent,0 as reportTime,0 as occurTime,'' as source,up.CREATORGROUP
as department,up.CHANGECLASSIC as changeClassic,up.EXPECTEDCOMPLETIONTIME as expectedcompletiontime,up.PLANBEGINTIME as planBeginTime,
up.PLANENDTIME as planEndTime,up.CHANGEIMPLEMENTATEDEP as changeEimplementatedep,up.RISKLEVEL as riskLevel
from wf_worklist as t join
WF_NODE_ACTION_RECORD as n on n.instanceid=t.instanceid,UP_BASEINFOR a,UP_CHANGE up where a.ENTRYID=t.INSTANCEID and a.ENTRYID=up.ENTRYID
and t.BDRAFT<>'1' and t.WFSIGN='CHANGE' and n.ROUTEID in('123_121','123_122','123_200','123_124','123_127','123_129','123_136','123_138',
'123_146','123_151','123_154','123_181','123_183','123_184','123_185','123_186','123_187','123_188','123_189','123_190','123_191','123_192',
'123_193','123_194','123_196','123_197','123_198','123_199') and t.ALLREADERSLIST like '%admin%'
and not exists(select * from WF_NODE_ACTION_RECORD nn where nn.instanceid=n.instanceid and nn.acttime>n.acttime)
union
select t.INSTANCEID as instanceid,a.WORKSHEETID as worksheetid,a.WORKSHEETTITLE as worksheettitle, a.CREATOR as creator,t.WFSIGN as wfsign,
'' as nodeid,t.WFNAME as wfname,t.APPSIGN as appsign,'' as nodename,a.DEALPERSONS as transactor,a.DEALPOSTS as deallorgname,a.DEALUNITS
as dealactiionname,up.CHANGELEVEL as level,up.ISURGENT as isUrgent,0 as reportTime,0 as occurTime,'' as source,up.CREATORGROUP as department,
up.CHANGECLASSIC as changeClassic,up.EXPECTEDCOMPLETIONTIME as expectedcompletiontime,up.PLANBEGINTIME as planBeginTime,up.PLANENDTIME
as planEndTime,up.CHANGEIMPLEMENTATEDEP as changeEimplementatedep,up.RISKLEVEL as riskLevel
,rownumber() over() as rowid from WF_INSTANCE_END as t join WF_NODE_ACTION_RECORDEND as n on n.instanceid=t.instanceid,UP_BASEINFOR a,UP_CHANGE up
where a.ENTRYID=t.INSTANCEID and a.ENTRYID=up.ENTRYID and t.WFSIGN='CHANGE' and n.ROUTEID in('123_121','123_122','123_200','123_124',
'123_127','123_129','123_136','123_138','123_146','123_151','123_154','123_181','123_183','123_184','123_185','123_186','123_187',
'123_188','123_189','123_190','123_191','123_192','123_193','123_194','123_196','123_197','123_198','123_199') and t.LASTUSER like
'%admin%' and days(date('2012-1-11'))-days (date(n.ACTTIME))<=0 and not exists(select * from WF_NODE_ACTION_RECORDEND nn
where nn.instanceid=n.instanceid and nn.acttime>n.acttime)"

第二步：

sql的构造类QueryPageDB2Util

public class QueryPageDB2Util {

 private String sql;

 private IPage page;

 public IPage getPage() {
  return page;
 }

 public void setPage(IPage page) {
  this.page = page;
 }

 public String getSql() {
  return sql;
 }

 public void setSql(String sql) {
  this.sql = getCounterQuerySql(sql);
 }

 public String getCounterQuerySql(String sql) {
  if (page == null)
   return sql;
  StringBuffer sb = new StringBuffer();

  int sIndex = sql.indexOf("select");
  int fIndex = sql.indexOf("from");
  String select = sql.substring(sIndex + 6, fIndex);

  sb.append("select * from (select").append(select).append(",rownumber() over() as rowid from ")
    .append(sql.substring(fIndex+5)).append(")as a where a.rowid >=").append(
      (page.getRecordsPerPage() * (page.getCurrentPage()-1))+1)
    .append(" and ").append("a.rowid <").append(
      (page.getRecordsPerPage() * (page.getCurrentPage())+1));
  return sb.toString();
 }

 public String getQuerySql() {
  if (page == null)
   return sql;
  StringBuffer sbsql = new StringBuffer(sql.trim()); 
  return sbsql.toString();
 }

 public static void main(String[] args) {

  String sql="select * from comments where comments";
  int sIndex = sql.indexOf("select");
  int fIndex = sql.indexOf("from");
  System.out.println(sIndex+" "+fIndex);
  String select =sql.substring(sIndex+6, fIndex);
  System.out.println(select);
 }
}

 

第三步：

QueryPageDB2Util pageUtil = new QueryPageDB2Util();

   pageUtil.setPage(page);
   pageUtil.setSql(sqlBuf.toString());
   String sql=pageUtil.getQuerySql();

得到最终 union sql 分页

注意：红色标注部分