Source code: javatools/db/DbExpr.java
1 /*
2 Javatools (modified version) - Some useful general classes.
3 Copyright (C) 2002-2003 Chris Bitmead (original) Antonio Petrelli (modified)
4
5 This program is free software; you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation; either version 2 of the License, or
8 (at your option) any later version.
9
10 This program 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
13 GNU General Public License for more details.
14
15 You should have received a copy of the GNU General Public License
16 along with this program; if not, write to the Free Software
17 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18
19 Contact me at: brenmcguire@users.sourceforge.net
20 */
21 package javatools.db;
22 import java.sql.*;
23 import java.io.*;
24 import java.util.*;
25 import javatools.util.FileLog;
26 import javatools.util.Props;
27
28 /**
29 * An sql expression class. This is the abstract base class for any type of SQL
30 * expression.
31 *
32 * @author Chris Bitmead
33 * @created 29 August 2001
34 * @version 0.7
35 * @commentedby Antonio Petrelli
36 */
37
38 public abstract class DbExpr implements DbTableUser {
39 /** The database to be used to represent this expression.
40 */
41 DbDatabase db;
42
43
44 /**
45 * Constructor for the DbExpr object
46 *
47 * @param db The database to be used.
48 */
49 public DbExpr(DbDatabase db) {
50 this.db = db;
51 }
52
53
54 /**
55 * Adds to the passed set all the tables used by an expression.
56 *
57 * @param c The set to add used tables to.
58 * @param o The expression to find tables from.
59 */
60 public static void usesTables(Set c, Object o) {
61 if (o != null && o instanceof DbTableUser) {
62 ((DbExpr) o).usesTables(c);
63 }
64 }
65
66
67 /**
68 * Substitute the literal value in the Prepared Statement. This is a utility
69 * method used by several other classes.
70 *
71 * @param stmt the PreparedStatement
72 * @param i the parameter number we are up to
73 * @param intocol optional list of columns the result will be
74 * selected into
75 * @param col The new sqlValue value
76 * @return Description of the Returned Value
77 * @exception SQLException If something goes wrong.
78 * @exception DbException If something goes wrong.
79 */
80 static int setSqlValue(PreparedStatement stmt, int i, Object col, DbColumn intocol) throws DbException, SQLException {
81 int type = Types.VARCHAR;
82 if (intocol != null) {
83 type = intocol.table.types[intocol.index];
84 }
85 if (col instanceof DbExpr) {
86 i = ((DbExpr) col).setSqlValues(stmt, i);
87 } else {
88 FileLog.singleton().debug("setSqlValue", "val = '" + col + "'");
89 if (col == null) {
90 stmt.setNull(i++, type);
91 } else if (col instanceof String && (type == Types.BLOB || type == Types.BINARY || type == Types.VARBINARY || type == Types.LONGVARBINARY)) {
92 stmt.setBytes(i++, ((String) col).getBytes());
93 } else {
94 stmt.setObject(i++, col);
95 }
96 }
97 return i;
98 }
99
100
101 /**
102 * Utility function to turn o into an SQL expression.
103 *
104 * @param o The object that usually represents a DbExpr object.
105 * @return The string value
106 * @exception DbException If something goes wrong.
107 */
108 static String getString(Object o) throws DbException {
109 if (o instanceof DbExpr) {
110 return ((DbExpr) o).getQueryString();
111 } else {
112 return " ? ";
113 }
114 }
115
116
117 /**
118 * Any DbExpr needs to be able to substitute any parameters as per JDBC "?"
119 * substitutions.
120 *
121 * @param ps The PreparedStatement
122 * @param i The new sqlValues value
123 * @return An index (obscure).
124 * @exception DbException If something goes wrong.
125 * @exception SQLException If something goes wrong.
126 */
127 public abstract int setSqlValues(PreparedStatement ps, int i) throws DbException, SQLException;
128
129
130 /**
131 * Any DbExpr needs to be able to convert into the SQL string equivilent.
132 *
133 * @return The queryString value
134 * @exception DbException If something goes wrong.
135 */
136 public abstract String getQueryString() throws DbException;
137
138 /**
139 * Gets the null attribute of the DbExpr object
140 *
141 * @return The null value
142 */
143 public DbCriterion isNull() {
144 return new DbCriterion(db, this, "IS", new DbLiteral(db, "NULL"));
145 }
146
147
148 /**
149 * Gets the notNull attribute of the DbExpr object
150 *
151 * @return The notNull value
152 */
153 public DbCriterion isNotNull() {
154 return new DbCriterion(db, this, "IS NOT", new DbLiteral(db, "NULL"));
155 }
156
157
158 /**
159 * Adds to the passed set all the tables used by this expression.
160 * @param c The set to add tables to.
161 */
162 public void usesTables(Set c) {
163 }
164
165
166 /**
167 * Returns an expression created as an "and" between the current expression and
168 * another expression.
169 *
170 * @param e The expression with which it will build the "and" expression.
171 * @return The resulting "and" expression.
172 */
173 public DbExpr and(DbExpr e) {
174 return new DbAndExpr(db, this, e);
175 }
176
177
178 /**
179 * Returns an expression created as an "or" between the current expression and
180 * another expression.
181 *
182 * @param e The expression with which it will build the "and" expression.
183 * @return The resulting "or" expression.
184 */
185 public DbExpr or(DbExpr e) {
186 return new DbOrExpr(db, this, e);
187 }
188
189
190 /**
191 * Returns an expression in which it will take the max value for current
192 * expression.
193 *
194 * @return The expression representing the max value for this expression.
195 */
196 public DbExpr max() {
197 return new DbExprFuncDef(db, "MAX", this);
198 }
199
200
201 /**
202 * Returns an expression in which it will take the max value for current
203 * expression.
204 * @return The expression representing the max value for this expression.
205 */
206 public DbExpr min() {
207 return new DbExprFuncDef(db, "MIN", this);
208 }
209
210
211 /**
212 * Returns an expression in which it will take the uppercase value for current
213 * expression.
214 *
215 * @return The uppercase for this expression.
216 */
217 public DbExpr upper() {
218 return new DbExprFuncDef(db, "UPPER", this);
219 }
220
221
222 /**
223 * Returns an expression in which it will take the lowercase value for current
224 * expression.
225 *
226 * @return The lowercase for this expression.
227 */
228 public DbExpr lower() {
229 return new DbExprFuncDef(db, "LOWER", this);
230 }
231
232 /** Returns the count expression for the current expression. It is effective only
233 * with a <CODE>DbLiteral</CODE> expression, containing <CODE>"*"</CODE>.
234 * @return The expression containing the count for the current expression.
235 */
236 public DbExpr count() {
237 return new DbExprFuncDef(db, "COUNT", this);
238 }
239
240 /**
241 * Truncs the date? Obscure.
242 *
243 * @return The final expression.
244 * @exception DbException If something goes wrong.
245 */
246 public DbExpr dateTrunc() throws DbException {
247 try {
248 Props props = Props.singleton("dbvendor");
249 String sql = props.getProperty(db.getProperty("vendor") + ".dateTrunc");
250 return new DbMiscExpr(db, sql, this);
251 } catch (IOException e) {
252 throw new DbException(e);
253 }
254 }
255
256
257 /**
258 * Return an expression representing this column being equal to another value.
259 *
260 * @param o The object that the current expression should be equal to.
261 * @return The final expression.
262 */
263 public DbCriterion equal(Object o) {
264 return new DbCriterion(db, this, "=", o);
265 }
266
267
268 /**
269 * Return an expression representing this column being equal to another value.
270 *
271 * @param o The object that the current expression should be not equal to.
272 * @return The final expression.
273 */
274 public DbCriterion notEqual(Object o) {
275 return new DbCriterion(db, this, "<>", o);
276 }
277
278
279 /**
280 * Return an expression representing this column being LIKE another value.
281 *
282 * @param o The object that the current expression should be LIKE.
283 * @return The final expression.
284 */
285 public DbCriterion like(Object o) {
286 return new DbCriterion(db, this, "LIKE", o);
287 }
288
289 /** Creates an expression to be used to search in char columns containing one of the
290 * specified strings.
291 * @param values The array of strings.
292 * @return The needed expression.
293 */
294 public DbExpr containsAllStrings(String[] values) {
295 int i, numValues;
296 DbExpr tempExpr;
297
298 numValues = values.length;
299 tempExpr = null;
300 if (numValues > 0) {
301 tempExpr = this.upper().like("%" + values[0] + "%");
302 for (i=1; i < numValues; i++)
303 tempExpr = tempExpr.and(this.upper().like("%" + values[i] + "%"));
304 }
305 return tempExpr;
306 }
307
308 /** Creates an expression to be used to search in char columns containing one of the
309 * specified strings.
310 * @param values The list of strings.
311 * @return The needed expression.
312 */
313 public DbExpr containsAllStrings(List values) {
314 Iterator tempIt;
315 DbExpr tempExpr;
316
317 tempExpr = null;
318 tempIt = values.iterator();
319 if (tempIt.hasNext()) {
320 tempExpr = this.upper().like("%" + (String) tempIt.next() + "%");
321 while (tempIt.hasNext())
322 tempExpr = tempExpr.and(this.upper().like("%" +
323 (String) tempIt.next() + "%"));
324 }
325 return tempExpr;
326 }
327
328 /**
329 * Return an expression representing this column being greater than another
330 * value.
331 *
332 * @param o The object that the current expression should be greater than.
333 * @return The final expression.
334 */
335 public DbCriterion greaterThan(Object o) {
336 return new DbCriterion(db, this, ">", o);
337 }
338
339
340 /**
341 * Return an expression representing this column being greater than or equal
342 * to another value.
343 *
344 * @param o The object that the current expression should be greater than or equal.
345 * @return The final expression.
346 */
347 public DbCriterion greaterThanOrEqual(Object o) {
348 return new DbCriterion(db, this, ">=", o);
349 }
350
351
352 /**
353 * Return an expression representing this column being less than another
354 * value.
355 *
356 * @param o The object that the current expression should be less than.
357 * @return The final expression.
358 */
359 public DbCriterion lessThan(Object o) {
360 return new DbCriterion(db, this, "<", o);
361 }
362
363
364 /**
365 * Return an expression representing this column being less than or equal to
366 * another value.
367 *
368 * @param o The object that the current expression should be less than or equal.
369 * @return The final expression.
370 */
371 public DbCriterion lessThanOrEqual(Object o) {
372 return new DbCriterion(db, this, "<=", o);
373 }
374
375
376 /**
377 * Returns an expression to represent the "in" clause.
378 *
379 * @param o The object representing usually the selector in which the expression should
380 * find its values.
381 * @return The final expression.
382 */
383 public DbCriterion in(Object o) {
384 return new DbCriterion(db, this, "IN", new DbParenthesis(db, (DbExpr) o));
385 }
386
387
388 /** Returns an expression that means "IN (value1, value2, ..., valuen)"
389 * @param valueList The value list.
390 * @return The needed expression.
391 */
392 public DbCriterion in(Collection valueList) {
393 return new DbCriterion(db, this, "IN", new DbValueList(db, valueList));
394 }
395 /**
396 * Returns an expression to represent the "not in" clause.
397 *
398 * @param o The object representing usually the selector in which the expression should NOT
399 * find its values.
400 * @return The final expression.
401 */
402 public DbCriterion notIn(Object o) {
403 return new DbCriterion(db, this, "NOT IN", new DbParenthesis(db, (DbExpr) o));
404 }
405
406 /** Returns an expression that means "NOT IN (value1, value2, ..., valuen)"
407 * @param valueList The list of values.
408 * @return The needed expression
409 */
410 public DbCriterion notIn(Collection valueList) {
411 return new DbCriterion(db, this, "NOT IN", new DbValueList(db, valueList));
412 }
413 }