MySQL資料庫分頁查詢，Oracle資料庫分頁查詢，SqlServer資料庫分頁

標籤：get   manager   str   static   print   odi   varchar   val   util   

DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) NOT NULL,  `pass` varchar(255) NOT NULL,  `sex` varchar(255) NOT NULL,  `age` int(11) NOT NULL,  `address` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES (‘1‘, ‘張三1‘, ‘111‘, ‘男‘, ‘21‘, ‘湖北省十堰市‘);INSERT INTO `student` VALUES (‘2‘, ‘李四2‘, ‘123‘, ‘男‘, ‘21‘, ‘上海市靜安區‘);INSERT INTO `student` VALUES (‘3‘, ‘張三3‘, ‘111‘, ‘男‘, ‘21‘, ‘湖北省十堰市‘);INSERT INTO `student` VALUES (‘4‘, ‘李四4‘, ‘123‘, ‘男‘, ‘21‘, ‘上海市靜安區‘);INSERT INTO `student` VALUES (‘5‘, ‘張三5‘, ‘111‘, ‘男‘, ‘21‘, ‘湖北省十堰市‘);INSERT INTO `student` VALUES (‘6‘, ‘李四6‘, ‘123‘, ‘男‘, ‘21‘, ‘上海市靜安區‘);INSERT INTO `student` VALUES (‘7‘, ‘張三7‘, ‘111‘, ‘男‘, ‘21‘, ‘湖北省十堰市‘);INSERT INTO `student` VALUES (‘8‘, ‘李四8‘, ‘123‘, ‘男‘, ‘21‘, ‘上海市靜安區‘);INSERT INTO `student` VALUES (‘9‘, ‘張三9‘, ‘111‘, ‘男‘, ‘21‘, ‘湖北省十堰市‘);INSERT INTO `student` VALUES (‘10‘, ‘李四0‘, ‘123‘, ‘男‘, ‘21‘, ‘上海市靜安區‘);

 

一.查詢5~10條資料

mysql分頁查詢：

select * from student limit 5,10; 

 

oracle分頁查詢：

select * from （select *，rownum rn from student ）where rn between 6 and 10；

 

sqlserver分頁查詢：

select top 10 * from student  where  id not in(select  top 5 id from student order by id ) order by id ;

 

DB2分頁查詢：

select * from （select *，rownumber() over() as  rownum  from student ）where rn between 6 and 10；

 

二.jdbc連結資料庫代碼

 1.jdbc連結mysql

package com.zjl.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DbUtils {        private  static  final String URL="jdbc:mysql://localhost:3306/DataBaseName?useUnicode=true&characterEncoding=utf-8";                  private  static  final String USER="root";    private  static  final String PASSWORD="password";        static{          try {                Class.forName("com.mysql.jdbc.Driver");                           } catch (ClassNotFoundException e) {               e.printStackTrace();           }    }            public static Connection getConnection() throws SQLException{        return DriverManager.getConnection(URL, USER, PASSWORD);    }        //關閉方法    public  static void close(ResultSet rs, Statement stat, Connection conn) throws SQLException{        if(rs!=null){            rs.close();        }if(stat!=null){            stat.close();        }if(conn!=null){            conn.close();        }    }        }    

2.jdbc連結oracle資料庫

package com.zjl.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DbUtils {        private  static  final String URL="jdbc:oracle:thin:@192.168.0.1:1521:DataBaseName?useUnicode=true&characterEncoding=utf-8";              private  static  final String USER="root";    private  static  final String PASSWORD="password";        static{          try {                Class.forName("oracle.jdbc.driver.OracleDriver");                           } catch (ClassNotFoundException e) {               e.printStackTrace();           }    }            public static Connection getConnection() throws SQLException{        return DriverManager.getConnection(URL, USER, PASSWORD);    }        //關閉方法    public  static void close(ResultSet rs, Statement stat, Connection conn) throws SQLException{        if(rs!=null){            rs.close();        }if(stat!=null){            stat.close();        }if(conn!=null){            conn.close();        }    }        }    

MySQL資料庫分頁查詢，Oracle資料庫分頁查詢，SqlServer資料庫分頁