View Javadoc

1   /*
2    * The SmartWeb Framework
3    * Copyright (C) 2004-2006
4    *
5    * This library is free software; you can redistribute it and/or
6    * modify it under the terms of the GNU Lesser General Public
7    * License as published by the Free Software Foundation; either
8    * version 2.1 of the License, or (at your option) any later version.
9    *
10   * This library is distributed in the hope that it will be useful,
11   * but WITHOUT ANY WARRANTY; without even the implied warranty of
12   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
13   * Lesser General Public License for more details.
14   *
15   * You should have received a copy of the GNU Lesser General Public
16   * License along with this library; if not, write to the Free Software
17   * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
18   *
19   * For further informations on the SmartWeb Framework please visit
20   *
21   *                        http://smartweb.sourceforge.net
22   */
23   
24  package net.smartlab.web.page;
25  
26  import java.sql.Connection;
27  import java.sql.ResultSet;
28  import java.sql.ResultSetMetaData;
29  import java.sql.SQLException;
30  import java.sql.Statement;
31  
32  import javax.sql.DataSource;
33  
34  import org.apache.commons.logging.Log;
35  import org.apache.commons.logging.LogFactory;
36  
37  /**
38   * TODO documentation
39   * 
40   * @author rlogiacco
41   */
42  public class SQLPaginator extends Paginator {
43  
44  	/**
45  	 * Logger for this class
46  	 */
47  	protected final Log logger = LogFactory.getLog(SQLPaginator.class);
48  
49  	/**
50  	 * A constant indicating the default isoltaion level.
51  	 */
52  	private final static int DEFAULT_ISOLATION_LEVEL = -1;
53  
54  	/**
55  	 * The SQL connection pool.
56  	 */
57  	private DataSource pool;
58  
59  	/**
60  	 * The SQL querywhose result must be paginated.
61  	 */
62  	private String query;
63  
64  	/**
65  	 * The selected isoltaion level on the database connection.
66  	 */
67  	private int isolation;
68  
69  	/**
70  	 * Creates an SQLPaginator instance using an SQL connection pool and a
71  	 * specified query. The page size is unlimited, the selected page is
72  	 * undefined and the isolation level is the default one.
73  	 * 
74  	 * @param pool the SQL connection pool.
75  	 * @param query the selection query for pagination results.
76  	 * @throws SQLException if something unexpected occurs while accessing the
77  	 *             database.
78  	 */
79  	public SQLPaginator(DataSource pool, String query) throws SQLException {
80  		this(pool, query, 0);
81  	}
82  
83  	/**
84  	 * Creates an SQLPaginator with default isolation level and unlimited page
85  	 * size.
86  	 * 
87  	 * @param pool the SQL connection pool.
88  	 * @param query the selection query for pagination results.
89  	 * @param size the number of elements composing each page.
90  	 * @throws SQLException if something unexpected occurs while accessing the
91  	 *             database.
92  	 */
93  	public SQLPaginator(DataSource pool, String query, int size) throws SQLException {
94  		this(pool, query, size, Paginator.UNLIMITED_PAGES);
95  	}
96  
97  	/**
98  	 * Creates an SQLPaginator with default isoltaion level.
99  	 * 
100 	 * @param pool the SQL connection pool.
101 	 * @param query the selection query for pagination results.
102 	 * @param size the number of elements composing each page.
103 	 * @param pages the number of pages composing each page block.
104 	 * @throws SQLException if something unexpected occurs while accessing the
105 	 *             database.
106 	 */
107 	public SQLPaginator(DataSource pool, String query, int size, int pages) throws SQLException {
108 		this(pool, query, size, pages, DEFAULT_ISOLATION_LEVEL);
109 	}
110 
111 	/**
112 	 * Creates an SQLPaginator.
113 	 * 
114 	 * @param pool the SQL connection pool.
115 	 * @param query the selection query for pagination results.
116 	 * @param size the number of elements composing each page.
117 	 * @param pages the number of pages composing each page block.
118 	 * @param isolation the isolation level to be used while accessing the
119 	 *            database.
120 	 * @throws SQLException if something unexpected occurs while accessing the
121 	 *             database.
122 	 */
123 	public SQLPaginator(DataSource pool, String query, int size, int pages, int isolation) throws SQLException {
124 		super(size, pages);
125 		this.pool = pool;
126 		this.query = query;
127 		this.isolation = isolation;
128 		Connection connection = null;
129 		try {
130 			connection = pool.getConnection();
131 			if (isolation != DEFAULT_ISOLATION_LEVEL) {
132 				connection.setTransactionIsolation(isolation);
133 			}
134 			Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
135 			statement.setFetchDirection(ResultSet.TYPE_FORWARD_ONLY);
136 			ResultSet rows = connection.createStatement().executeQuery(query);
137 			rows.last();
138 			super.setCount(rows.getRow());
139 		} finally {
140 			try {
141 				connection.close();
142 			} catch (SQLException sqle) {
143 				logger.warn("SQLPaginator(pool = " + pool + ", query = " + query + ", size = " + size + ", pages = " + pages + ", isolation = "
144 						+ isolation + ") - failed connection close", sqle);
145 			}
146 		}
147 	}
148 
149 	/**
150 	 * @see net.smartlab.web.Paginator#setArray()
151 	 */
152 	protected void setArray() {
153 		if (logger.isTraceEnabled()) {
154 			logger.trace("setArray() - start");
155 		}
156 		Connection connection = null;
157 		try {
158 			connection = pool.getConnection();
159 			if (isolation != DEFAULT_ISOLATION_LEVEL) {
160 				connection.setTransactionIsolation(isolation);
161 			}
162 			Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
163 			ResultSet rows = statement.executeQuery(query);
164 			rows.absolute(((this.getPage() - 1) * array.length) + 1);
165 			for (int i = 0; i < array.length; i++) {
166 				array[i] = this.getObject(rows);
167 				if (!rows.next() && !(i == array.length - 1)) {
168 					array[i + 1] = null;
169 					break;
170 				}
171 			}
172 		} catch (SQLException sqle) {
173 			throw new PaginationException(sqle);
174 		} finally {
175 			try {
176 				connection.close();
177 			} catch (SQLException sqle) {
178 				logger.warn("setArray() - failed connection close", sqle);
179 			}
180 		}
181 	}
182 
183 	/**
184 	 * Returns the current row datas.
185 	 * 
186 	 * @param rows the paginated results.
187 	 * @return the current row datas as an array of objects.
188 	 */
189 	protected Object[] getObject(ResultSet rows) {
190 		if (logger.isTraceEnabled()) {
191 			logger.trace("getObject(rows = " + rows + ") - start");
192 		}
193 		try {
194 			ResultSetMetaData info = rows.getMetaData();
195 			Object[] values = new Object[info.getColumnCount()];
196 			for (int i = 0; i < values.length; i++) {
197 				values[i] = rows.getObject(i + 1);
198 			}
199 			return values;
200 		} catch (SQLException sqle) {
201 			logger.error("getObject(rows = " + rows + ") - error", sqle);
202 			return null;
203 		}
204 	}
205 }