001: /**
002: * Licensed under the GNU LESSER GENERAL PUBLIC LICENSE, version 2.1, dated February 1999.
003: *
004: * This program is free software; you can redistribute it and/or modify
005: * it under the terms of the latest version of the GNU Lesser General
006: * Public License as published by the Free Software Foundation;
007: *
008: * This program is distributed in the hope that it will be useful,
009: * but WITHOUT ANY WARRANTY; without even the implied warranty of
010: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
011: * GNU Lesser General Public License for more details.
012: *
013: * You should have received a copy of the GNU Lesser General Public License
014: * along with this program (LICENSE.txt); if not, write to the Free Software
015: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
016: */package org.jamwiki.db;
017:
018: import java.sql.Connection;
019: import java.sql.PreparedStatement;
020: import java.sql.ResultSet;
021: import java.sql.Timestamp;
022: import java.sql.Types;
023: import org.apache.commons.lang.StringUtils;
024: import org.jamwiki.utils.WikiLogger;
025:
026: /**
027: * This class is a wrapper around the java.sql.PreparedStatement class, allowing a
028: * statement to be prepared without requiring that a database connection be
029: * held. The main advantage of this approach is that all connection handling can
030: * be done by low-level database functions, and the user can build and execute a
031: * query without the need to ensure that the connection is properly closed after
032: * the data is processed.
033: */
034: public class WikiPreparedStatement {
035:
036: private static final WikiLogger logger = WikiLogger
037: .getLogger(WikiPreparedStatement.class.getName());
038: private Object[] params = null;
039: private int[] paramTypes = null;
040: private final String sql;
041: private PreparedStatement statement = null;
042: private final int numElements;
043:
044: /**
045: *
046: */
047: public WikiPreparedStatement(String sql) {
048: this .sql = sql;
049: this .numElements = StringUtils.countMatches(sql, "?");
050: this .params = new Object[numElements];
051: this .paramTypes = new int[numElements];
052: }
053:
054: /**
055: *
056: */
057: public WikiResultSet executeQuery() throws Exception {
058: Connection conn = null;
059: try {
060: conn = DatabaseConnection.getConnection();
061: return this .executeQuery(conn);
062: } finally {
063: DatabaseConnection.closeConnection(conn);
064: }
065: }
066:
067: /**
068: *
069: */
070: public WikiResultSet executeQuery(Connection conn) throws Exception {
071: ResultSet rs = null;
072: try {
073: long start = System.currentTimeMillis();
074: this .statement = conn.prepareStatement(this .sql);
075: this .loadStatement();
076: rs = this .statement.executeQuery();
077: long execution = System.currentTimeMillis() - start;
078: if (execution > DatabaseConnection.SLOW_QUERY_LIMIT) {
079: logger.warning("Slow query: " + sql + " ("
080: + (execution / 1000.000) + " s.)");
081: }
082: logger.fine("Executed " + this .sql + " ("
083: + (execution / 1000.000) + " s.)");
084: return new WikiResultSet(rs);
085: } catch (Exception e) {
086: throw new Exception("Failure while executing " + this .sql,
087: e);
088: } finally {
089: DatabaseConnection
090: .closeConnection(null, this .statement, rs);
091: }
092: }
093:
094: /**
095: *
096: */
097: public int executeUpdate() throws Exception {
098: Connection conn = null;
099: try {
100: conn = DatabaseConnection.getConnection();
101: return this .executeUpdate(conn);
102: } finally {
103: DatabaseConnection.closeConnection(conn);
104: }
105: }
106:
107: /**
108: *
109: */
110: public int executeUpdate(Connection conn) throws Exception {
111: try {
112: long start = System.currentTimeMillis();
113: this .statement = conn.prepareStatement(this .sql);
114: this .loadStatement();
115: int result = this .statement.executeUpdate();
116: long execution = System.currentTimeMillis() - start;
117: if (execution > DatabaseConnection.SLOW_QUERY_LIMIT) {
118: logger.warning("Slow query: " + sql + " ("
119: + (execution / 1000.000) + " s.)");
120: }
121: logger.fine("Executed " + this .sql + " ("
122: + (execution / 1000.000) + " s.)");
123: return result;
124: } catch (Exception e) {
125: throw new Exception("Failure while executing " + this .sql,
126: e);
127: } finally {
128: DatabaseConnection.closeConnection(null, this .statement);
129: }
130: }
131:
132: /**
133: *
134: */
135: private void loadStatement() throws Exception {
136: for (int i = 0; i < this .paramTypes.length; i++) {
137: if (params[i] == null) {
138: this .statement.setNull(i + 1, paramTypes[i]);
139: } else if (paramTypes[i] == Types.CHAR) {
140: char value = ((Character) params[i]).charValue();
141: this .statement.setString(i + 1, Character
142: .toString(value));
143: } else if (paramTypes[i] == Types.INTEGER) {
144: int value = ((Integer) params[i]).intValue();
145: this .statement.setInt(i + 1, value);
146: } else if (paramTypes[i] == Types.TIMESTAMP) {
147: Timestamp value = (Timestamp) params[i];
148: this .statement.setTimestamp(i + 1, value);
149: } else if (paramTypes[i] == Types.VARCHAR) {
150: String value = (String) params[i];
151: this .statement.setString(i + 1, value);
152: }
153: }
154: }
155:
156: /**
157: * Sets the designated parameter to the given Java character value. The
158: * driver converts this to an SQL CHAR value when it sends it to the database.
159: *
160: * @param parameterIndex The first parameter is 1, the second is 2, ...
161: * @param x The parameter value.
162: * @throws Exception If a parameter is invalid.
163: */
164: public void setChar(int parameterIndex, char x) throws Exception {
165: this .verifyParams(parameterIndex);
166: this .paramTypes[parameterIndex - 1] = Types.CHAR;
167: this .params[parameterIndex - 1] = new Character(x);
168: }
169:
170: /**
171: * Sets the designated parameter to the given Java int value. The driver
172: * converts this to an SQL INTEGER value when it sends it to the database.
173: *
174: * @param parameterIndex The first parameter is 1, the second is 2, ...
175: * @param x The parameter value.
176: * @throws Exception If a parameter is invalid.
177: */
178: public void setInt(int parameterIndex, int x) throws Exception {
179: this .verifyParams(parameterIndex);
180: this .paramTypes[parameterIndex - 1] = Types.INTEGER;
181: this .params[parameterIndex - 1] = new Integer(x);
182: }
183:
184: /**
185: * Sets the designated parameter to the given Java int value. The driver
186: * converts this to an SQL INTEGER value when it sends it to the database.
187: *
188: * @param parameterIndex The first parameter is 1, the second is 2, ...
189: * @param x The parameter value.
190: * @throws Exception If a parameter is invalid.
191: */
192: public void setInt(int parameterIndex, long x) throws Exception {
193: // this is a bit kludgy - cast the long to an int. problem for very big values.
194: this .verifyParams(parameterIndex);
195: this .paramTypes[parameterIndex - 1] = Types.INTEGER;
196: this .params[parameterIndex - 1] = new Integer((int) x);
197: }
198:
199: /**
200: * Sets the designated parameter to SQL NULL.
201: *
202: * <b>Note</b>: You must specify the parameter's SQL type.
203: *
204: * @param parameterIndex The first parameter is 1, the second is 2, ...
205: * @param sqlType The SQL type code defined in java.sql.Types
206: * @throws Exception If a parameter is invalid.
207: */
208: public void setNull(int parameterIndex, int sqlType)
209: throws Exception {
210: this .verifyParams(parameterIndex);
211: this .paramTypes[parameterIndex - 1] = sqlType;
212: this .params[parameterIndex - 1] = null;
213: }
214:
215: /**
216: * Sets the designated parameter to the given Java String value. The driver
217: * converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the
218: * argument's size relative to the driver's limits on VARCHAR values) when
219: * it sends it to the database.
220: *
221: * @param parameterIndex The first parameter is 1, the second is 2, ...
222: * @param x The parameter value.
223: * @throws Exception If a parameter is invalid.
224: */
225: public void setString(int parameterIndex, String x)
226: throws Exception {
227: this .verifyParams(parameterIndex);
228: this .paramTypes[parameterIndex - 1] = Types.VARCHAR;
229: this .params[parameterIndex - 1] = x;
230: }
231:
232: /**
233: * Sets the designated parameter to the given java.sql.Timestamp value. The
234: * driver converts this to an SQL TIMESTAMP value when it sends it to the database.
235: *
236: * @param parameterIndex The first parameter is 1, the second is 2, ...
237: * @param x The parameter value.
238: * @throws Exception If a parameter is invalid.
239: */
240: public void setTimestamp(int parameterIndex, Timestamp x)
241: throws Exception {
242: this .verifyParams(parameterIndex);
243: this .paramTypes[parameterIndex - 1] = Types.TIMESTAMP;
244: this .params[parameterIndex - 1] = x;
245: }
246:
247: /**
248: *
249: */
250: private void verifyParams(int pos) throws Exception {
251: if (pos <= 0) {
252: throw new Exception("Invalid PreparedStatement index "
253: + pos);
254: }
255: }
256: }
|