Oracle分页1

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 rn, item_no, item_category_id, item_unit_id, item_name, spec, pattern
from
(
  select rownum rn, item_no, item_category_id, item_unit_id, item_name, spec, pattern 
  from t_items
  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 rn, item_no, item_category_id, item_unit_id, item_name, spec, pattern ")
			.append("from ")
			.append("( ")
			  .append("select rownum rn, item_no, item_category_id, item_unit_id, item_name, spec, pattern ")
			  .append("from t_items ")
			  .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.getInt("rn") + " " + resultSet.getString("item_no") + 
						" " + resultSet.getString("item_category_id") + " " + resultSet.getString("item_unit_id") +
						" " + resultSet.getString("item_name") + " " + resultSet.getString("spec") + 
						" " + resultSet.getString("pattern"));
			}
		} 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;
								}
							}
						}
					}
				}
			}
		}

	}
	

}
/*
*/