Oracle分页2

create table "t_items" 
   (	
	  "item_no" varchar2(10) not null enable, 
		"item_category_id" char(3), 
		"item_unit_id" char(3), 
		"item_name" varchar2(20) not null enable, 
		"spec" varchar2(20), 
		"pattern" varchar2(20), 
		 constraint "pk_t_items" primary key ("item_no")
	 )


insert into t_items (item_no,item_category_id,item_unit_id,item_name,spec,pattern) values ('a001','301','402','撒旦发生地方都是','1.x','333.xxx');
insert into t_items (item_no,item_category_id,item_unit_id,item_name,spec,pattern) values ('a002','302','400','头炮','wwww','aaaaa');
insert into t_items (item_no,item_category_id,item_unit_id,item_name,spec,pattern) values ('a003','301','400','康必得','888','99999');
insert into t_items (item_no,item_category_id,item_unit_id,item_name,spec,pattern) values ('a004','300','400','金银花','得淡淡的','淡淡的');
insert into t_items (item_no,item_category_id,item_unit_id,item_name,spec,pattern) values ('a009','300','400','dsfdsf',null,null);


-- 2<= rownum <= 3
select * from
(
  select t.*, rownum rn
  from
  (
    select * from t_items
  ) t
  where rownum <=3
)
where rn > 1

package com.ankangqiao.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 
 * @author Kevin
 * 
 */
public class ConnectionTest {


	/**
	 * @param args
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		Connection connection = null;
		PreparedStatement preparedStatetment = null;
		ResultSet resultSet = null;

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:orcl", "system", "oracleRoot123");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw e;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		
		try {
			int pageNumber = 2;
			int pageSize = 2;
			StringBuilder sql = new StringBuilder();
			sql.append("select * from ").append("( ")
					.append("select t.*, rownum rn ").append("from ").append("( ")
					.append("select * from t_items ").append(") t ")
					.append("where rownum <=? ").append(") ")
					.append("where rn > ? ");

			preparedStatetment = connection.prepareStatement(sql.toString());
			preparedStatetment.setInt(1, pageSize*pageNumber);
			preparedStatetment.setInt(2, pageSize*(pageNumber -1));
			
			resultSet = preparedStatetment.executeQuery();
			
			while (resultSet.next()) {
				System.out.println(resultSet.getObject(1) + " " + resultSet.getObject(2) + 
						" " + resultSet.getObject(3) + " " + resultSet.getObject(4) +
						" " + resultSet.getObject(5) + " " + resultSet.getObject(6) + 
						" " + resultSet.getObject(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (preparedStatetment != null) {
				try {
					preparedStatetment.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				} finally {
					if (connection != null) {
						try {
							connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
							throw e;
						} finally {
							if (resultSet != null) {
								try {
									resultSet.close();
								} catch (SQLException e) {
									e.printStackTrace();
									throw e;
								}
							}
						}
					}
				}
			}
		}

	}
	

}
/*
*/