[应用架构] Mysql分页技术

最新推荐文章于 2024-10-07 23:41:09 发布

haha_mingg

最新推荐文章于 2024-10-07 23:41:09 发布

阅读量558

点赞数

分类专栏：数据库学习文章标签： mysql null sql table jdbc exception

本文链接：https://blog.csdn.net/haha_mingg/article/details/7613447

版权

数据库学习专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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

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

复制代码

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

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

try{
if(mark == 1){
   //后面可以加上附加条件
   sql  = "select count(*) from sems_fractional_record ";
}
if(mark == 2){
   sql = "select count(*) from sems_fractional_record where concentUID='"+concentUID+"'";
}
if(mark == 3){
   sql = "select count(*) from sems_fractional_record where datetime='"+datetime+"'";
}
if(mark == 4){
   sql = "select count(*) from sems_fractional_record where concentUID='"+concentUID+"' and datetime='"+datetime+"'";
}
if(mark == 5){
   sql = "select count(*) from sems_fractional_record where datetime like'%"+datetime+"%'";  //'%"+userName+"%'"
}
if(mark == 6){
   sql = "select count(*) from sems_fractional_record where datetime like'%"+datetime+"%' and concentUID='"+concentUID+"'" ;
}
if(mark == 7){
   sql = "select count(*) from sems_fractional_record where datetime between '"+datetime+"' and '"+endTime+"'";
}
if(mark == 8){
   sql = "select count(*) from sems_fractional_record where datetime  between '"+datetime+"' and '"+endTime+"'and concentUID='"+concentUID+"'" ;
}

rs = this.query(sql);
if(rs.next()){

//得到总记录数
rowCount = rs.getInt(1);

//计算总页数
pageCount = rowCount%pageSize ==0 ? rowCount/pageSize : rowCount/pageSize+1 ;
}
}catch(Exception evt){
      logger.info("查询总页数出现异常");
}

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

try {
if(mark == 1){
   //后面可以加上附加条件
   sql  = "select  * from sems_fractional_record ";
}
if(mark == 2){
   sql = "select *  from sems_fractional_record where concentUID='"+concentUID+"'";
}
if(mark == 3){
   sql = "select *  from sems_fractional_record where datetime='"+datetime+"'";
}
if(mark == 4){
   sql = "select * from sems_fractional_record where concentUID='"+concentUID+"' and datetime='"+datetime+"'";
}
if(mark == 5){
   sql = "select * from sems_fractional_record where datetime like'%"+datetime+"%'";  //'%"+userName+"%'"
}
if(mark == 6){
   sql = "select * from sems_fractional_record where datetime like'%"+datetime+"%' and concentUID='"+concentUID+"'" ;
}
if(mark == 7){
   sql = "select  * from sems_fractional_record where datetime between '"+datetime+"' and '"+endTime+"'";
}
if(mark == 8){
   sql = "select *  from sems_fractional_record where datetime  between '"+datetime+"' and '"+endTime+"'and concentUID='"+concentUID+"'" ;
}
   //加上后面的分页条件,后缀suffix
   String sqlSuffix = " Limit "+(currentPage-1)*pageSize+", "+pageSize+" ";//"select uid from sems_fractional_record";

   //组成复合查询
   sql=sql+ sqlSuffix;

   rs = this.query(sql);

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

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

------------------------
-----电表复杂报表
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;