001: package simpleorm.core;
002:
003: import simpleorm.examples.Employee;
004: import simpleorm.properties.*;
005:
006: import java.sql.*;
007: import java.math.BigDecimal;
008:
009: /** This class represents a SimpleORM prepared query statement. It is
010: analagous to the JDBC prepared statement.<p>
011:
012: The general flow of a query goes as follows:-
013: <xmp>
014: SPreparedStatement stmt = Employee.meta.select("SALARY > ?", "NAME");
015: // SELECT <All columns> FROM XX_EMPLOYEE
016: // WHERE SALARY > ? ORDER BY NAME
017: stmt.setInt(1, 50000);
018: SResultSet res = stmt.execute();
019: while (res.hasNext()) {
020: Employee emp = res.getRecord();
021: emp.getString(Employee.NAME);
022: }
023: </xmp>
024:
025: To avoid a brittle structure with an ever growing number of parameters
026: to SPreparedStatement, the objects now created in two steps one to
027: create the object and set parameters. To utilize this use the following style instead:-
028:
029: <xmp>
030: SPreparedStatement limitps = new SPreparedStatement();
031: limitps.setOffset(1);
032: limitps.setLimit(1);
033: SResultSet limitR = Employee.meta.newQuery()
034: .ascending(Employee.EMPEE_ID)
035: .execute(limitps);
036:
037: // Employee.meta.Select(limitps,...)
038: </xmp>
039:
040: ## This uses of the SPreparedStatement seems a little odd. Maybe newQuery(limitps) would
041: be better, and also use newSPreparedStatement(Employee.meta...) instead of Select. Mabye.
042: But definitely do not want to create a Properties object one per query.
043: */
044: public class SPreparedStatement {
045: SRecordMeta sRecordMeta = null;
046: SConnection sConnection = null;
047: SFieldMeta[] selectList = null;
048: String sqlQuery = null;
049: PreparedStatement jdbcPreparedStatement = null;
050: boolean readOnly = false;
051: boolean unrepeatableRead = false;
052: boolean optimistic = false;
053:
054: SRecordMeta[] joinTables = null;
055:
056: public SRecordMeta[] getJoinTables() {
057: return joinTables;
058: }
059:
060: public void setJoinTables(SRecordMeta[] jts) {
061: joinTables = jts;
062: }
063:
064: boolean distinct = false;
065:
066: /** Select DISTINCT */
067: public boolean getDistinct() {
068: return distinct;
069: }
070:
071: public void setDistinct(boolean jts) {
072: distinct = jts;
073: }
074:
075: long offset = 0;
076:
077: /** Offset and Limit reduce the number of rows retrieved. My be
078: * implemented efficiently by the database, or just by skipping
079: * ahead during execute. Semantics are not that well defined
080: * between transactions, rows may be missed. Useful for paging out
081: * query results. <p>
082: *
083: * Note that if offset = 2 and limit = 5, the rows 0 and 1 are
084: * skipped, and rows 2..6 are returned.
085: */
086: public long getOffset() {
087: return offset;
088: }
089:
090: public void setOffset(long off) {
091: offset = off;
092: }
093:
094: long limit = 0;
095:
096: /** see limit */
097: public long getLimit() {
098: return limit;
099: }
100:
101: public void setLimit(long lim) {
102: limit = lim;
103: }
104:
105: public SPreparedStatement() {
106: }
107:
108: protected void prepareStatement(SRecordMeta meta, String where,
109: String orderBy, long sqy_bitSet, SFieldMeta[] selectList) {
110: sRecordMeta = meta;
111: this .selectList = selectList;
112:
113: SConnection scon = SConnection.getBegunConnection();
114:
115: readOnly = SUte.inBitSet(sqy_bitSet, SCon.SQY_READ_ONLY,
116: SCon.SQY_);
117: unrepeatableRead = SUte.inBitSet(sqy_bitSet,
118: SCon.SQY_UNREPEATABLE_READ, SCon.SQY_);
119: optimistic = SUte.inBitSet(sqy_bitSet, SCon.SQY_OPTIMISTIC,
120: SCon.SQY_)
121: || (!scon.getDriver().supportsLocking() && !readOnly);
122: if (readOnly && optimistic)
123: throw new SException.Error(
124: "Cannot be both Optimistically Locked and ReadOnly "
125: + this );
126:
127: /// Create the Query
128: sqlQuery = scon.sDriver.selectSQL(selectList, sRecordMeta,
129: where, orderBy, !readOnly && !optimistic,
130: unrepeatableRead, this );
131:
132: if (SLog.slog.enableQueries())
133: SLog.slog.queries("select querying '" + sqlQuery + "'...");
134:
135: sConnection = SConnection.getBegunConnection();
136: try {
137: jdbcPreparedStatement = sConnection.jdbcConnection
138: .prepareStatement(sqlQuery);
139: // Let the JDBC driver cache these.
140: } catch (Exception psex) {
141: throw new SException.JDBC("Preparing '" + sqlQuery + "'",
142: psex);
143: }
144: }
145:
146: /** <tt>value</tt> replaces the <tt>parameterIndex</tt>th
147: "<tt>?</tt>" in the query. The first "<tt>?</tt>" is 1, not 0.
148: Analagous to jdbc PreparedStatement.setString. */
149: public void setString(int parameterIndex, String value) {
150: try {
151: jdbcPreparedStatement.setString(parameterIndex, value);
152: } catch (Exception se) {
153: throw new SException.JDBC("Setting " + this + " '?' "
154: + (parameterIndex), se);
155: }
156: }
157:
158: public void setInt(int parameterIndex, int value) {
159: try {
160: jdbcPreparedStatement.setInt(parameterIndex, value);
161: } catch (Exception se) {
162: throw new SException.JDBC("Setting " + this + " '?' "
163: + (parameterIndex), se);
164: }
165: }
166:
167: public void setLong(int parameterIndex, long value) {
168: try {
169: jdbcPreparedStatement.setLong(parameterIndex, value);
170: } catch (Exception se) {
171: throw new SException.JDBC("Setting " + this + " '?' "
172: + (parameterIndex), se);
173: }
174: }
175:
176: public void setDouble(int parameterIndex, double value) {
177: try {
178: jdbcPreparedStatement.setDouble(parameterIndex, value);
179: } catch (Exception se) {
180: throw new SException.JDBC("Setting " + this + " '?' "
181: + (parameterIndex), se);
182: }
183: }
184:
185: public void setObject(int parameterIndex, Object value) {
186: try {
187: jdbcPreparedStatement.setObject(parameterIndex, value);
188: } catch (Exception se) {
189: throw new SException.JDBC("Setting " + this + " '?' "
190: + (parameterIndex), se);
191: }
192: }
193:
194: /** See {@link SRecordInstance#setTimestamp} for discussion of Date parameter.*/
195: public void setTimestamp(int parameterIndex, java.util.Date value) {
196: if (!(value instanceof java.sql.Timestamp))
197: value = new java.sql.Timestamp(value.getTime());
198: try {
199: jdbcPreparedStatement.setTimestamp(parameterIndex,
200: (java.sql.Timestamp) value);
201: } catch (Exception se) {
202: throw new SException.JDBC("Setting " + this + " '?' "
203: + (parameterIndex), se);
204: }
205: }
206:
207: /** See {@link SRecordInstance#setTimestamp} for discussion of Date parameter.*/
208: public void setDate(int parameterIndex, java.util.Date value) {
209: if (!(value instanceof java.sql.Date))
210: value = new java.sql.Date(value.getTime());
211: try {
212: jdbcPreparedStatement.setDate(parameterIndex,
213: (java.sql.Date) value);
214: } catch (Exception se) {
215: throw new SException.JDBC("Setting " + this + " '?' "
216: + (parameterIndex), se);
217: }
218: }
219:
220: /** See {@link SRecordInstance#setTimestamp} for discussion of Date parameter.*/
221: public void setTime(int parameterIndex, java.util.Date value) {
222: if (!(value instanceof java.sql.Time))
223: value = new java.sql.Time(value.getTime());
224: try {
225: jdbcPreparedStatement.setTime(parameterIndex,
226: (java.sql.Time) value);
227: } catch (Exception se) {
228: throw new SException.JDBC("Setting " + this + " '?' "
229: + (parameterIndex), se);
230: }
231: }
232:
233: public void setBigDecimal(int parameterIndex, BigDecimal value) {
234: try {
235: jdbcPreparedStatement.setBigDecimal(parameterIndex, value);
236: } catch (Exception se) {
237: throw new SException.JDBC("Setting " + this + " '?' "
238: + (parameterIndex), se);
239: }
240: }
241:
242: /** Execute the query having set "<tt>?</tt>" paramenters. */
243: public SResultSet execute() {
244: ResultSet rs = null;
245: try {
246: rs = jdbcPreparedStatement.executeQuery();
247: } catch (Exception rsex) {
248: throw new SException.JDBC("Executing " + sqlQuery, rsex);
249: }
250: return new SResultSet(rs, this );
251: }
252:
253: /** Close the underlying JDBC prepared statement.<p>
254:
255: @deprecated Due to bugs in some JDBC drivers, {@link SResultSet#close} now
256: automatically also closes the prepared statement from Version
257: 1.05. This means that there is no reason to ever call this
258: method explicilty. */
259: public void close() {
260: try {
261: jdbcPreparedStatement.close();
262: } catch (Exception ce) {
263: throw new SException.JDBC("Closing " + this , ce);
264: }
265: sRecordMeta = null;
266: sConnection = null;
267: selectList = null;
268: jdbcPreparedStatement = null;
269: }
270:
271: public String getSQL() {
272: return sqlQuery;
273: }
274:
275: /** Retrieves the underlying JDBC PreparedStatement object.
276: Dangerous. The authors can see no reason why this would ever
277: need to be used but it is provided for completeness.
278: */
279: public PreparedStatement getJDBCPreparedStatement() {
280: return jdbcPreparedStatement;
281: }
282:
283: public String toString() {
284: return "[SPreparedStatement " + sqlQuery + "]";
285: }
286: }
|