Mysql分页技术

1.使用mysql进行分页比较简单，
下面我结合查询条件来加以说明。这里我使用原生态JDBC进行查询，分页查询要用到以下SQL语句：

   sql  = "select  * from sems_fractional_record  Limit "+(currentPage-1)*pageSize+", "+pageSize+" "

Limit关键字后面接从第几个记录开始，然后逗号号隔开，后面再接显示多少条记录。

2. 查询总页码数
     结合查询条件，这里会有点儿复杂，只是因为它的查询条件比较复杂，它有集中器UID还有时间日期，而且它们都是变化的，UID有两个变化，而时间日期有四个变量，结合起来2*4 =8，则共有8种查询语句，如下所示：

try {
                         sql  = "select  * from sems_fractional_record  where 1=1";
                        switch(mark){
                        case 1:
                                
                                break;
                        case 2:
                                 sql += "concentUID='"+concentUID+"'";
                                break;
                        case 3:
                                sql += "datetime='"+datetime+"'";
                                break;
                        case 4:
                                sql += "concentUID='"+concentUID+"' and datetime='"+datetime+"'";
                                break;
                        case 5:
                                sql += "datetime like'%"+datetime+"%'";
                                break;
                        case 6:
                                sql += "datetime like'%"+datetime+"%' and concentUID='"+concentUID+"'";
                                break;
                        case 7:
                                sql+="datetime between '"+datetime+"' and '"+endTime+"'";
                                break;
                        case 8:
                                sql += "datetime  between '"+datetime+"' and '"+endTime+"'and concentUID='"+concentUID+"'" ;
                                break;
                                
                        }

//加上后面的分页条件,后缀suffix
                          String sqlSuffix = " Limit "+(currentPage-1)*pageSize+", "+pageSize+" ";//"select uid from sems_fractional_record";
                        
                          //组成复合查询
                          sql=sql+ sqlSuffix;
                           
                          rs = this.query(sql);

//加上后面的分页条件,后缀suffix
                          String sqlSuffix = " Limit "+(currentPage-1)*pageSize+", "+pageSize+" ";//"select uid from sems_fractional_record";
                        
                          //组成复合查询
                          sql=sql+ sqlSuffix;
                           
                          rs = this.query(sql);

        } catch (SQLException e) {

                        logger.error("获取所有的集中器UID异常!");

                }

3.查询每页的记录数
这里的方法是分页进行查询，是要多少数据才加载多少条数据，比如默认加载第一页的数据，则它不会加其他页中的数据，从而效率和性能比较好，加载每页记录数的代码如下所示：

  try {
                         sql  = "select  * from sems_fractional_record where 1=1 ";
                        switch(mark){
                        case 1:
                                
                                break;
                        case 2:
                                 sql += "concentUID='"+concentUID+"'";
                                break;
                        case 3:
                                sql += "datetime='"+datetime+"'";
                                break;
                        case 4:
                                sql += "concentUID='"+concentUID+"' and datetime='"+datetime+"'";
                                break;
                        case 5:
                                sql += "datetime like'%"+datetime+"%'";
                                break;
                        case 6:
                                sql += "datetime like'%"+datetime+"%' and concentUID='"+concentUID+"'";
                                break;
                        case 7:
                                sql+="datetime between '"+datetime+"' and '"+endTime+"'";
                                break;
                        case 8:
                                sql += "datetime  between '"+datetime+"' and '"+endTime+"'and concentUID='"+concentUID+"'" ;
                                break;
                                
                        }

        rs = this.query(sql);
                        if(rs.next()){
                                
                                //得到总记录数
                                rowCount = rs.getInt(1);
                                
                                //计算总页数
                                pageCount = rowCount%pageSize ==0 ? rowCount/pageSize : rowCount/pageSize+1 ;
                        }
                }catch(Exception evt){
                        
                }

注意： 在使用原生态JDBC的时候，一定要将数据库连接对象关闭或即时放入连接池中，不然会影响服务器性能。甚至于内存溢出等问题。

4.最后再举一例mysql联合（union）查询的例子，代码如下：
1.SQL代码程式:

------------------------
-----电表复杂报表
select w.uid,w.theyear year,max(january)january,max(february)february,max(march)march,max(april)april,max(may)may,max(june)june,max(july)july,max(august)august,
max(september)september,max(october)october,max(november)november,max(december)december 
from(
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,IF(SUBSTR(t.datetime,6,2)='01',t.curMonthCoulometer,0)january,0 february,0 march,0 april,0 may,0 june,0 july,
0 august,0 september, 0 october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,IF(SUBSTR(t.datetime,6,2)='02',t.curMonthCoulometer,0)february,0 march,0 april,0 may,0 june,0 july,
0 august,0 september, 0 october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,IF(SUBSTR(t.datetime,6,2)='03',t.curMonthCoulometer,0) march,0 april,0 may,0 june,0 july,
0 august,0 september, 0 october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february ,0 march ,IF(SUBSTR(t.datetime,6,2)='04',t.curMonthCoulometer,0) april,0 may,0 june,0 july,
0 august,0 september, 0 october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,IF(SUBSTR(t.datetime,6,2)='05',t.curMonthCoulometer,0) may,0 june,0 july,
0 august,0 september, 0 october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,IF(SUBSTR(t.datetime,6,2)='06',t.curMonthCoulometer,0) june,0 july,
0 august,0 september, 0 october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,IF(SUBSTR(t.datetime,6,2)='07',t.curMonthCoulometer,0) july,
0 august,0 september, 0 october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,
IF(SUBSTR(t.datetime,6,2)='08',t.curMonthCoulometer,0) august,0 september, 0 october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,
0 august,IF(SUBSTR(t.datetime,6,2)='09',t.curMonthCoulometer,0) september, 0 october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,
0 august,0 september, IF(SUBSTR(t.datetime,6,2)='10',t.curMonthCoulometer,0) october,0 november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,
0 august,0 september, 0 october,IF(SUBSTR(t.datetime,6,2)='11',t.curMonthCoulometer,0) november,0 december from qo_1_nfschina.sems_ammeter_history_data t
UNION
SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,
0 august,0 september, 0 october,0 november,IF(SUBSTR(t.datetime,6,2)='12',t.curMonthCoulometer,0) december from qo_1_nfschina.sems_ammeter_history_data t
)w
group by w.uid,w.theyear

2.JAVA代码程式.

try {
  String sql = "select w.uid,w.theyear            year,max(january)january,max(february)february,max(march)march,max(april)april," +  "max(may)may,max(june)june,max(july)july,max(august)august, max(september)september,max(october)october," +
"max(november)november,max(december)december from(" +

  "SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,IF(SUBSTR(t.datetime,6,2)='01',t.curMonthCoulometer,0)january," +  "0 february,0 march,0 april,0 may,0 june,0 july,0 august,0 september, 0 october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"'   " +

" UNION  SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,IF(SUBSTR(t.datetime,6,2)='02',t.curMonthCoulometer,0)february,0 march,0 april,0 may," +
"0 june,0 july,0 august,0 september, 0 october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"' 

  UNION " +  "SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,IF(SUBSTR(t.datetime,6,2)='03',t.curMonthCoulometer,0) march,0 april,0 may,0 june,0 july,0" +
  " august,0 september, 0 october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+

"'UNION " + "SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february ,0 march ,IF(SUBSTR(t.datetime,6,2)='04',t.curMonthCoulometer,0) april,0 may,0 june,0 july,0" +
" august,0 september, 0 october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"'  " + 

"  UNION  SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,IF(SUBSTR(t.datetime,6,2)='05',t.curMonthCoulometer,0) may,0 june,0 " +
"july,0 august,0 september, 0 october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"'    " +

"UNION  SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,IF(SUBSTR(t.datetime,6,2)='06',t.curMonthCoulometer,0) june,0 july,0" +
" august,0 september, 0 october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"'   " +

"UNION SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,IF(SUBSTR(t.datetime,6,2)='07',t.curMonthCoulometer,0) july,0" +
" august,0 september, 0 october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"'   " +

"UNION SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,IF(SUBSTR(t.datetime,6,2)='08'," +
"t.curMonthCoulometer,0) august,0 september, 0 october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"'   " +
"UNION SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,0 august,IF(SUBSTR(t.datetime,6,2)='09'," +"t.curMonthCoulometer,0) september, 0 october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"'  " 

+ " UNION SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,0 august,0 september, IF(SUBSTR(t.datetime,6,2)='10'," +
  "t.curMonthCoulometer,0) october,0 november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"'   " +

"UNION SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,0 august,0 september, 0 october," +  "IF(SUBSTR(t.datetime,6,2)='11',t.curMonthCoulometer,0) november,0 december from sems_ammeter_history_data t where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"'   " + 

" UNION SELECT t.uid,SUBSTR(t.datetime,1,4)theyear,0 january,0 february,0 march,0 april,0 may,0 june,0 july,0 august,0 september, 0 october,0 november," +  "IF(SUBSTR(t.datetime,6,2)='12',t.curMonthCoulometer,0) december from sems_ammeter_history_data t 

where uid='"+ammeterUID.trim()+"' and concent_uid='"+concenUID.trim()+"')" +  "w" +" group by w.uid,w.theyear ";
                               
    //执行查询
   rs = query(sql);

它用到的两个表(sems_ammeter_real_data 和sems_ammeter_history_data ):

----创建实时电表数据
DROP TABLE IF EXISTS `sems_ammeter_real_data`;
CREATE TABLE `sems_ammeter_real_data` (
  `Id` bigint(20) NOT NULL auto_increment,
  `uid` varchar(255) default NULL,
  `type` int(255) default NULL,
  `phaseA` varchar(255) default NULL,
  `voltageA` double(20,2) default NULL,
  `electricityA` double(20,2) default NULL,
  `powerA` double(20,2) default NULL,
  `powerFactorA` double(20,2) default NULL,
  `phaseB` varchar(255) default NULL,
  `voltageB` double(20,2) default NULL,
  `electricityB` double(20,2) default NULL,
  `powerB` double(20,2) default NULL,
  `powerFactorB` double(20,2) default NULL,
  `phaseC` varchar(255) default NULL,
  `voltageC` double(20,2) default NULL,
  `electricityC` double(20,2) default NULL,
  `powerC` double(20,2) default NULL,
  `powerFactorC` double(20,2) default NULL,
  `coulometer` double(20,3) default NULL,
  `datetime` varchar(255) default NULL,
  `concent_uid` varchar(12) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


------创建历史电表数据表格
DROP TABLE IF EXISTS `sems_ammeter_history_data`;
CREATE TABLE `sems_ammeter_history_data` (
  `Id` bigint(20) NOT NULL auto_increment,
  `uid` varchar(255) default NULL,
  `totalCoulometer` double(20,3) default NULL,
  `curMonthCoulometer` double(20,3) default NULL,
  `datetime` varchar(255) default NULL,
  `concent_uid` varchar(12) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这部分代码比较复杂，有想了解的可以私下一起讨论之，共同进步.
(完....待续)