querybean.java

使用jsp开发的一个基于HSQLDB的快餐订购管理系统

package com.uiwz.db;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class QueryBean
{
	private Database db = null;
	private ResultSet rs = null;
	private String query = null;
	private int startRow = 1;
	private int stopRow = 0;
	private int sumRow = 0;
	private int sumCol = 0;
	private String[][] datas = null;
	private boolean emptyFlag = true;

	public QueryBean()
	throws SQLException
	{
		db = new Database();
	}
	
	public QueryBean(Database db)
	throws SQLException
	{
		this.db = db;
	}
	
	public void setStatement(String query)
	{
		this.query = query;
	}

	public String getStatement()
	{
		return query;
	}

	public void setStartRow(int startRow)
	throws SQLException
	{
		if(startRow < 1)
			throw new SQLException("StartRow number can not be smaller than 1");

		this.startRow = startRow;
	}

	public int getStartRow()
	{
		return startRow;
	}

	public void setStopRow(int stopRow)
	throws SQLException
	{
		if(stopRow < startRow)
			throw new SQLException("StopRow number can not be smaller than StartRow");

		this.stopRow = stopRow;
	}

	public int getStopRow()
	{
		return stopRow;
	}
	
	public void setEmptyFlag(boolean emptyFlag)
	{
		this.emptyFlag = emptyFlag;
	}
	
	public void execute()
	throws SQLException
	{
		if(query == null)
			throw new SQLException("Query statement can not be null");

		rs = db.query(query);
		rs.last();
		sumRow = rs.getRow();//取得结果集的行数
		rs.beforeFirst();
		ResultSetMetaData rsmd = rs.getMetaData();
		sumCol = rsmd.getColumnCount();//取得结果集的列数

		if(startRow > sumRow)
			startRow = sumRow + 1;
		if(stopRow < 1 || stopRow > sumRow)
			stopRow = sumRow;
		
		if(sumRow < 1 && !emptyFlag) //返回一个长度为1，宽度为查询的字段数，内容全为empty的数组
		{
			datas = new String[1][sumCol];
			for(int i=0; i<sumCol; i++)
				datas[0][i] = "";
			db.close();
			return;
		}
		else
		{
			datas = new String[stopRow - startRow + 1][sumCol];			
			if(sumRow < 1)
			{
				db.close();
				return;
			}
		}
		
		//if(rs.relative(startRow - 1))
		rs.relative(startRow - 1);
		{
			int i = 0;
			while(rs.next() && i < sumRow && i < stopRow - startRow + 1)
			{
				for(int j=0; j<sumCol; j++)
				{
					datas[i][j] = rs.getString(j+1) == null ? "" : rs.getString(j+1);
				}

				i++;
			}
		}

		db.close();
	}

	public String[][] getResult()
	{
		return datas;
	}

	public int getSumRow()
	{
		return sumRow;
	}

	public int getSumCol()
	{
		return sumCol;
	}
}