net.smartlab.web.page.SQLPaginator Maven / Gradle / Ivy

Go to download
/*
 * The SmartWeb Framework
 * Copyright (C) 2004-2006
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * For further informations on the SmartWeb Framework please visit
 *
 *                        http://smartweb.sourceforge.net
 */
 
package net.smartlab.web.page;

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

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * TODO documentation
 * 
 * @author rlogiacco
 */
public class SQLPaginator extends Paginator {

	/**
	 * Logger for this class
	 */
	protected final Log logger = LogFactory.getLog(SQLPaginator.class);

	/**
	 * A constant indicating the default isoltaion level.
	 */
	private final static int DEFAULT_ISOLATION_LEVEL = -1;

	/**
	 * The SQL connection pool.
	 */
	private DataSource pool;

	/**
	 * The SQL querywhose result must be paginated.
	 */
	private String query;

	/**
	 * The selected isoltaion level on the database connection.
	 */
	private int isolation;

	/**
	 * Creates an SQLPaginator instance using an SQL connection pool and a
	 * specified query. The page size is unlimited, the selected page is
	 * undefined and the isolation level is the default one.
	 * 
	 * @param pool the SQL connection pool.
	 * @param query the selection query for pagination results.
	 * @throws SQLException if something unexpected occurs while accessing the
	 *             database.
	 */
	public SQLPaginator(DataSource pool, String query) throws SQLException {
		this(pool, query, 0);
	}

	/**
	 * Creates an SQLPaginator with default isolation level and unlimited page
	 * size.
	 * 
	 * @param pool the SQL connection pool.
	 * @param query the selection query for pagination results.
	 * @param size the number of elements composing each page.
	 * @throws SQLException if something unexpected occurs while accessing the
	 *             database.
	 */
	public SQLPaginator(DataSource pool, String query, int size) throws SQLException {
		this(pool, query, size, Paginator.UNLIMITED_PAGES);
	}

	/**
	 * Creates an SQLPaginator with default isoltaion level.
	 * 
	 * @param pool the SQL connection pool.
	 * @param query the selection query for pagination results.
	 * @param size the number of elements composing each page.
	 * @param pages the number of pages composing each page block.
	 * @throws SQLException if something unexpected occurs while accessing the
	 *             database.
	 */
	public SQLPaginator(DataSource pool, String query, int size, int pages) throws SQLException {
		this(pool, query, size, pages, DEFAULT_ISOLATION_LEVEL);
	}

	/**
	 * Creates an SQLPaginator.
	 * 
	 * @param pool the SQL connection pool.
	 * @param query the selection query for pagination results.
	 * @param size the number of elements composing each page.
	 * @param pages the number of pages composing each page block.
	 * @param isolation the isolation level to be used while accessing the
	 *            database.
	 * @throws SQLException if something unexpected occurs while accessing the
	 *             database.
	 */
	public SQLPaginator(DataSource pool, String query, int size, int pages, int isolation) throws SQLException {
		super(size, pages);
		this.pool = pool;
		this.query = query;
		this.isolation = isolation;
		Connection connection = null;
		try {
			connection = pool.getConnection();
			if (isolation != DEFAULT_ISOLATION_LEVEL) {
				connection.setTransactionIsolation(isolation);
			}
			Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			statement.setFetchDirection(ResultSet.TYPE_FORWARD_ONLY);
			ResultSet rows = connection.createStatement().executeQuery(query);
			rows.last();
			super.setCount(rows.getRow());
		} finally {
			try {
				connection.close();
			} catch (SQLException sqle) {
				logger.warn("SQLPaginator(pool = " + pool + ", query = " + query + ", size = " + size + ", pages = " + pages + ", isolation = "
						+ isolation + ") - failed connection close", sqle);
			}
		}
	}

	/**
	 * @see net.smartlab.web.Paginator#setArray()
	 */
	protected void setArray() {
		if (logger.isTraceEnabled()) {
			logger.trace("setArray() - start");
		}
		Connection connection = null;
		try {
			connection = pool.getConnection();
			if (isolation != DEFAULT_ISOLATION_LEVEL) {
				connection.setTransactionIsolation(isolation);
			}
			Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ResultSet rows = statement.executeQuery(query);
			rows.absolute(((this.getPage() - 1) * array.length) + 1);
			for (int i = 0; i < array.length; i++) {
				array[i] = this.getObject(rows);
				if (!rows.next() && !(i == array.length - 1)) {
					array[i + 1] = null;
					break;
				}
			}
		} catch (SQLException sqle) {
			throw new PaginationException(sqle);
		} finally {
			try {
				connection.close();
			} catch (SQLException sqle) {
				logger.warn("setArray() - failed connection close", sqle);
			}
		}
	}

	/**
	 * Returns the current row datas.
	 * 
	 * @param rows the paginated results.
	 * @return the current row datas as an array of objects.
	 */
	protected Object[] getObject(ResultSet rows) {
		if (logger.isTraceEnabled()) {
			logger.trace("getObject(rows = " + rows + ") - start");
		}
		try {
			ResultSetMetaData info = rows.getMetaData();
			Object[] values = new Object[info.getColumnCount()];
			for (int i = 0; i < values.length; i++) {
				values[i] = rows.getObject(i + 1);
			}
			return values;
		} catch (SQLException sqle) {
			logger.error("getObject(rows = " + rows + ") - error", sqle);
			return null;
		}
	}
}