Source code: javatools/db/DbSelector.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.util.*;
23 import java.sql.*;
24 import java.io.*;
25 import javatools.util.FileLog;
26 import javatools.util.Props;
27
28 /**
29 * A class used to select tabular data from an SQL database. The constructor is
30 * not public. To obtain a DbSelector call DbDatabase.selector(); Example: To
31 * select FRED's record from the people table... <PRE>
32 * DbDatabase db = ...;
33 * DbTable people = db.getTable("PEOPLE");
34 * DbSelector selector = db.selector();
35 * selector.addColumn(people.getColumn("NAME"));
36 * selector.addColumn(people.getColumn("AGE"));
37 * selector.setWhere(people.getColumn("NAME").equal("FRED"));
38 * DbTable result = selector.execute();
39 * DbIterator it = result.iterator();
40 * while (it.hasNextRow()) {
41 * DbRow row = it.nextRow();
42 * System.out.println(row.getValue("NAME") + " " + row.getValue("AGE"));
43 * }
44 * </PRE> This is equivilent to... <PRE>
45 * SELECT NAME, AGE FROM PEOPLE WHERE PEOPLE.NAME='FRED';
46 * </PRE> To get more fancy we can join the people table with the team table to
47 * find the captain of the person's favourite team. Then we can also order by
48 * the person's name, while igoring upper/lower case distinctions... <PRE>
49 * DbDatabase db = ...;
50 * DbSelector selector = db.selector();
51 * DbTable people = db.getTable("PEOPLE");
52 * DbTable team = db.getTable("TEAM");
53 * DbSelector selector = db.selector();
54 * selector.addColumn(people.getColumn("NAME"));
55 * selector.addColumn(team.getColumn("CAPTAIN"));
56 * selector.setWhere(team.getColumn("NAME").equal(people.getColumn("FAVOURITE_TEAM"));
57 * selector.addOrderBy(people.getColumn("NAME").lower(), false) // Order by NAME ignoring case.
58 * DbTable result = selector.execute();
59 * DbIterator it = result.iterator();
60 * while (it.hasNextRow()) {
61 * DbRow row = it.nextRow();
62 * System.out.println(row.getValue("NAME") + " " + row.getValue("CAPTAIN"));
63 * }
64 * </PRE> This is equivilent to... <PRE>
65 * SELECT PEOPLE.NAME, TEAM.CAPTAIN FROM PEOPLE, TEAM WHERE TEAM.NAME = PEOPLE.FAVOURITE_TEAM
66 * ORDER BY LOWER(PEOPLE.NAME)
67 * </PRE> To get fancier still, we can make use of sub-selects. To find all the
68 * people who happen to be captains of teams... <PRE>
69 * DbDatabase db = ...;
70 * DbTable people = db.getTable("PEOPLE");
71 * DbTable team = db.getTable("TEAM");
72 * DbSelector subselector = db.selector();
73 * subselector.addColumn(team.getColumn("CAPTAIN"));
74 * DbSelector selector = db.selector();
75 * selector.addAll(people);
76 * selector.setWhere(people.getColumn("NAME").in(subselector));
77 * DbTable result = selector.execute();
78 * DbIterator it = result.iterator();
79 * while (it.hasNextRow()) {
80 * DbRow row = it.nextRow();
81 * System.out.println(row.toString());
82 * }
83 * </PRE> This is equivilent to... <PRE>
84 * SELECT * from PEOPLE WHERE PEOPLE.NAME IN (SELECT CAPTAIN FROM TEAM);
85 *
86 *
87 *
88 *
89 *
90 *
91 *
92 * @author Chris Bitmead (original), Antonio Petrelli (modified)
93 * @created 5 September 2001
94 * @version 0.0.2
95 */
96 public class DbSelector extends DbExpr {
97 /** The result of the SELECT operation.
98 */
99 DbTable result;
100 /** Maps columns to "AS" arguments.
101 */
102 Map asMap = new HashMap();
103 /** Contains the columns used in this operation.
104 */
105 List columnList = new ArrayList();
106 /** The where clause for the SELECT.
107 */
108 DbExpr where;
109 /** Contains the columns for sorting.
110 */
111 List orderBy = new LinkedList();
112 /** Contains the columns for grouping.
113 */
114 List groupBy = new LinkedList();
115
116 /** Contains the list of joined tables.
117 */
118 List joinedTables = new LinkedList();
119 /** Contains the real result set.
120 */
121 ResultSet resultSet;
122 /** It's the prepared statement to execute.
123 */
124 PreparedStatement stmt;
125 /** Limits the search to a certain amount of rows.
126 */
127 DbExpr limit;
128 /** Puts an offset.
129 */
130 DbExpr offset;
131
132 /** <CODE>true</CODE>: clause DISTINCT will be put;
133 * <CODE>false</CODE>: clause DISTINCT will not be put.
134 */
135 boolean distinct;
136
137 /** Create a new DbSelector.
138 * @param db The database that will be used.
139 * @throws DbException If something goes wrong.
140 */
141 DbSelector(DbDatabase db) throws DbException {
142 super(db);
143 result = new DbCachedTable(db);
144 distinct = false;
145 }
146
147 /**
148 * Set the where condition for this query.
149 *
150 * @param where The new where value
151 */
152 public void setWhere(DbExpr where) {
153 this.where = where;
154 }
155
156 /** Returns the where condition.
157 * @return The stored where condition.
158 */
159 public DbExpr getWhere() {
160 return where;
161 }
162
163 /**
164 * Set the entire orderby list in one go.
165 *
166 * @param l The new orderBy value
167 */
168 public void setOrderBy(List l) {
169 orderBy = l;
170 }
171
172 /** Puts data into statement.
173 * @param stmt The statement to use.
174 * @param i An index (obscure).
175 * @throws DbException If something goes wrong.
176 * @throws SQLException If something goes wrong.
177 * @return An index (obscure).
178 */
179 public int setSqlValues(PreparedStatement stmt, int i) throws DbException, SQLException {
180 return setSqlValues(stmt, i, null);
181 }
182
183 /**
184 * Don't get the whole result set, get only a limited number of rows. Should
185 * be used in conjunction with ORDER BY in order to make the returned rows
186 * deterministic.
187 *
188 * @param n The new limit value
189 * @exception DbException If something goes wrong.
190 */
191 public void setLimit(int n) throws DbException {
192 try {
193 Props props = Props.singleton("dbvendor");
194 String sql = props.getProperty(db.getProperty("vendor") + ".limit");
195 limit = new DbMiscExpr(db, sql, new Integer(n));
196 } catch (IOException e) {
197 throw new DbException(e);
198 }
199 }
200
201 /**
202 * Don't get the first results, but skip n result rows. Should be used in
203 * conjunction with ORDER BY in order to make the returned rows deterministic.
204 *
205 * @param n The new offset value
206 * @exception DbException If something goes wrong.
207 */
208 public void setOffset(int n) throws DbException {
209 try {
210 Props props = Props.singleton("dbvendor");
211 String sql = props.getProperty(db.getProperty("vendor") + ".offset");
212 offset = new DbMiscExpr(db, sql, new Integer(n));
213 } catch (IOException e) {
214 throw new DbException(e);
215 }
216 }
217
218 /**
219 * Get the query string represented by this query.
220 *
221 * @return The queryString value
222 * @exception DbException If something goes wrong.
223 */
224 public String getQueryString() throws DbException {
225 String rtn = "SELECT ";
226 if (distinct)
227 rtn += "DISTINCT ";
228 int i = 0;
229 Iterator fieldi = columnList.iterator();
230 Set tables = new HashSet();
231 selectTables(tables);
232 while (fieldi.hasNext()) {
233 Object col = fieldi.next();
234 if (i != 0) {
235 rtn += ", ";
236 }
237 if (col != null) {
238 rtn += getString(col);
239 String as = (String) asMap.get(col);
240 if (as != null) {
241 rtn += " AS " + as;
242 }
243 }
244 else
245 rtn += " null";
246 i++;
247 }
248 i = 0;
249 rtn += " FROM ";
250 if (tables.size() == 0 && joinedTables.size() == 0) {
251 try {
252 Props props = Props.singleton("dbvendor");
253 String dummyTable = props.getProperty(db.getProperty("vendor") + ".dummyTable");
254 if (dummyTable != null) {
255 // for Oracle
256 rtn += dummyTable;
257 }
258 } catch (IOException e) {
259 throw new DbException(e);
260 }
261 } else {
262 Iterator joinedTableIt = joinedTables.iterator();
263 while (joinedTableIt.hasNext()) {
264 AbstractTable tab = (AbstractTable) joinedTableIt.next();
265 if (i++ != 0) {
266 rtn += ", ";
267 }
268 rtn += tab.getFullTableName();
269 }
270 Iterator tablei = tables.iterator();
271 while (tablei.hasNext()) {
272 AbstractTable tab = (AbstractTable) tablei.next();
273 if (i++ != 0) {
274 rtn += ", ";
275 }
276 rtn += tab.getFullTableName();
277 }
278 }
279 if (where != null) {
280 rtn += " WHERE ";
281 rtn += where.getQueryString();
282 }
283 rtn += groupByClause(groupBy);
284 rtn += orderByClause(orderBy);
285 if (limit != null) {
286 rtn += " " + limit.getQueryString();
287 }
288 if (offset != null) {
289 rtn += " " + offset.getQueryString();
290 }
291 FileLog.singleton().debug("DbSelector", rtn);
292 return rtn;
293 }
294
295 /**
296 * Add the given object to the select column list.
297 *
298 * @param col A DbColumn, DbExpr or literal value
299 * @return The column itself.
300 * @exception DbException If something goes wrong.
301 */
302 public DbColumn addColumn(Object col) throws DbException {
303 return addColumn(col, null);
304 }
305
306 /**
307 * Add the given object to the select column list with an "AS" alias.
308 *
309 * @param col a DbColumn, DbExpr or literal value
310 * @param as a column alias
311 * @return The column itself.
312 * @exception DbException If something goes wrong.
313 */
314 public DbColumn addColumn(Object col, String as) throws DbException {
315 columnList.add(col);
316 asMap.put(col, as);
317 return result.getAddColumn();
318 }
319
320 /** Gets a column in the column list.
321 * @param index The column to be got.
322 * @return The requested column.
323 */
324 public DbColumn getColumn(int index) {
325 return (DbColumn) columnList.get(index);
326 }
327
328 /**
329 * Add all the columns from the given table to the select list. A bit like
330 * SELECT * from table.
331 *
332 * @param table the table whose columns we wish to add
333 * @exception DbException If something goes wrong.
334 */
335 public void addAll(DbAbstractTable table) throws DbException {
336 for (int i = 0; i < table.names.length; i++) {
337 addColumn(table.getColumn(i));
338 }
339 }
340
341 /**
342 * Add all the columns from the given table to the select list. A bit like
343 * SELECT * from table.
344 *
345 * @param table the table whose columns we wish to add
346 * @param o The feature to be added to the AllExcept attribute
347 * @exception DbException If something goes wrong.
348 */
349 public void addAllExcept(DbAbstractTable table, DbColumn o) throws DbException {
350 for (int i = 0; i < table.names.length; i++) {
351 DbColumn col = table.getColumn(i);
352 if (!col.equals(o)) {
353 addColumn(col);
354 }
355 }
356 }
357
358 /**
359 * Add all the columns from the given table to the select list. A bit like
360 * SELECT * from table.
361 *
362 * @param table the table whose columns we wish to add
363 * @param set The feature to be added to the AllExcept attribute
364 * @exception DbException If something goes wrong.
365 */
366 public void addAllExcept(DbAbstractTable table, Set set) throws DbException {
367 for (int i = 0; i < table.names.length; i++) {
368 DbColumn col = table.getColumn(i);
369 if (!set.contains(col)) {
370 addColumn(col);
371 }
372 }
373 }
374
375 /**
376 * Add an ORDER BY clause to this select. The column actually need not be a
377 * plain column. It could be a column with a function applied. e.g.
378 * addOrderBy(table.getColumn("NAME").upper, false);
379 *
380 * @param column the column to order by
381 * @param desc whether to sort in descending order
382 */
383 public void addOrderBy(DbExpr column, boolean desc) {
384 orderBy.add(new DbOrderBy(column, desc));
385 }
386
387 /** Adds a GROUP BY column to this select.
388 * @param column The column to add in the GROUP BY clause.
389 */
390 public void addGroupBy(DbExpr column) {
391 groupBy.add(column);
392 }
393
394 /** Sets the value to control whether to put the DISTINCT clause or not.
395 * @param pDistinct <CODE>true</CODE>: clause DISTINCT will be put;
396 * <CODE>false</CODE>: clause DISTINCT will not be put.
397 */
398 public void setDistinct(boolean pDistinct) {
399 distinct = pDistinct;
400 }
401
402 /** Adds a joined table.
403 * @param joinedTable The joined table to add.
404 */
405 public void addJoinedTable (DbJoinedTable joinedTable) {
406 joinedTables.add(joinedTable);
407 }
408
409 /**
410 * Execute and get a JDBC ResultSet.
411 *
412 * @param dbcon The connection to use.
413 * @exception DbException If something goes wrong.
414 */
415 public void executeToResultSet(DbConnection dbcon) throws DbException {
416 try {
417 stmt = dbcon.con.prepareStatement(getQueryString());
418 setSqlValues(stmt, 1, null);
419 resultSet = stmt.executeQuery();
420 } catch (SQLException e) {
421 throw new DbException(e);
422 }
423 }
424
425 /**
426 * Execute and return a DbTable.
427 *
428 * @param dbcon The connection to use.
429 * @return The result, in a DbTable.
430 * @exception DbException If something goes wrong.
431 */
432 public DbTable execute(DbConnection dbcon) throws DbException {
433 executeToResultSet(dbcon);
434 // ResultSetMetaData md = rs.getMetaData();
435 // int count = md.getColumnCount();
436 result.setResultSet(stmt, resultSet);
437 return result;
438 }
439
440 /**
441 * Execute and return a DbTable with the default DbConnection.
442 *
443 * @return The result, in a DbTable
444 * @exception DbException If something goes wrong.
445 */
446 public DbTable execute() throws DbException {
447 return execute(db.getThreadConnection());
448 }
449
450 /** Converts this selector in a string.
451 * @return The requested string.
452 */
453 public String toString() {
454 try {
455 return getQueryString();
456 } catch (DbException e) {
457 return e.toString();
458 }
459 }
460
461 /** Creates a SELECT instruction with given tables.
462 * @param c A set of tables.
463 */
464 public void selectTables(Set c) {
465 Iterator fieldi = columnList.iterator();
466 while (fieldi.hasNext()) {
467 Object col = fieldi.next();
468 usesTables(c, col);
469 }
470 if (where != null) {
471 where.usesTables(c);
472 }
473 purgeTables(c);
474 }
475
476 private void purgeTables(Set c) {
477 HashSet usedTables;
478
479 Iterator tableIt;
480 tableIt = joinedTables.iterator();
481 usedTables = new HashSet();
482 while (tableIt.hasNext())
483 ((DbJoinedTable) tableIt.next()).usesTables(usedTables);
484 c.removeAll(usedTables);
485 }
486
487 /**
488 * Substitute the literal values in the Prepared Statement.
489 *
490 * @param stmt the PreparedStatement
491 * @param i the parameter number we are up to
492 * @param intoList The new sqlValues value
493 * @return An index (obscure).
494 * @exception DbException If something goes wrong.
495 * @exception SQLException If something goes wrong.
496 */
497 int setSqlValues(PreparedStatement stmt, int i, List intoList) throws DbException, SQLException {
498 Iterator columni = columnList.iterator();
499 Iterator intoi = null;
500 if (intoList != null) {
501 intoi = intoList.iterator();
502 }
503 while (columni.hasNext()) {
504 Object col = columni.next();
505 if (col != null) {
506 DbColumn intocol = null;
507 if (intoi != null) {
508 intocol = (DbColumn) intoi.next();
509 }
510 i = setSqlValue(stmt, i, col, intocol);
511 }
512 }
513 if (where != null) {
514 i = where.setSqlValues(stmt, i);
515 }
516 return i;
517 }
518
519 /**
520 * Generate the order by clause.
521 *
522 * @param orderBy A list of columns.
523 * @return The generated string.
524 * @exception DbException If something goes wrong.
525 */
526 String orderByClause(List orderBy) throws DbException {
527 String rtn = "";
528 if (orderBy != null) {
529 int i = 0;
530 Iterator keys = orderBy.iterator();
531 while (keys.hasNext()) {
532 DbOrderBy orderField = (DbOrderBy) keys.next();
533 if (i++ == 0) {
534 rtn += " ORDER BY ";
535 } else {
536 rtn += ", ";
537 }
538 rtn += orderField.getQueryString();
539 }
540 }
541 return rtn;
542 }
543
544 /** Generates the GROUP Bye clause.
545 * @param groupBy A list of columns.
546 * @throws DbException If something goes wrong.
547 * @return The generated string.
548 */
549 String groupByClause(List groupBy) throws DbException {
550 String rtn = "";
551 if (groupBy != null) {
552 int i = 0;
553 Iterator keys = groupBy.iterator();
554 while (keys.hasNext()) {
555 DbColumn groupField = (DbColumn) keys.next();
556 if (i++ == 0) {
557 rtn += " GROUP BY ";
558 } else {
559 rtn += ", ";
560 }
561 rtn += groupField.getQueryString();
562 }
563 }
564 return rtn;
565 }
566
567 /*public DbExpr getSubExpr(DbColumn[] cols) throws DbException {
568 DbSelector tempSel;
569 DbColumn tempCol;
570 Iterator columnIt;
571 boolean found;
572 int i, j, numSearchCols, numCols;
573
574 tempSel = new DbSelector(db);
575 columnIt = columnList.iterator();
576 numCols = cols.length;
577 while (columnIt.hasNext()) {
578 tempCol = (DbColumn) columnIt.next();
579 found = false;
580 for (i=0; i < numCols && !found; i++)
581 if (tempCol.equals(cols[i]))
582 found = true;
583 if (found)
584 tempSel.addColumn(tempCol);
585 }
586 tempSel.setWhere(where.getSubExpr(cols));
587 return tempSel;
588 } */
589
590 /*public DbExpr substituteColumn(DbColumn oldCol, DbColumn newCol) throws DbException {
591 DbSelector tempSel;
592 DbColumn tempCol;
593 Iterator columnIt;
594
595 tempSel = new DbSelector(db);
596 columnIt = columnList.iterator();
597 while (columnIt.hasNext()) {
598 tempCol = (DbColumn) columnIt.next();
599 if (tempCol.equals(oldCol))
600 tempSel.addColumn(newCol);
601 else
602 tempSel.addColumn(tempCol);
603 }
604 tempSel.setWhere(where.substituteColumn(oldCol, newCol));
605 return tempSel;
606 } */
607 }