org.melati.poem
Class TailoredQuery

java.lang.Object
  org.melati.poem.TailoredQuery

Direct Known Subclasses:

PreparedTailoredQuery

public class TailoredQuery
extends Object

This is how you run low-level SQL queries including joins, and get the results back in the form of convenient Melati Fields which can be rendered automatically in templates. A complement to the high-level Table.selection() on the one hand, and the low-level ResultSet on the other.

TailoredQuery is specifically for when you want a few fields back (possibly joined from several tables) rather than whole objects. Suppose you want to do

SELECT a.foo, b.bar FROM a, b WHERE a.something AND b.id = a.b

There is nothing to stop you doing this with a good old ResultSet = Database.sqlQuery("SELECT ..."). However if you want to get the same effect, without forgoing the nice features offered by POEM---e.g. access control, rich metadata that makes rendering trivial---you can use a TailoredQuery.

If Postgresql's ResultSetMetaData supported getTableName even approximately, this would all be "even simpler" to use and somewhat more flexible. Because it doesn't, and because of the requirement to perform (as far as possible) read access checks on the records accessed during the query, the interface necessarily takes a slightly structured form rather than just being raw SQL---although the programmer does get complete freedom as far as the core WHERE clause is concerned.

See Also:

Table.selection(java.lang.String, java.lang.String, boolean)

Nested Class Summary

class

TailoredQuery.FirstRawTailoredResultSetEnumeration<T> Return an Enumeration of the columns in the first row of a ResultSet.

Field Summary

protected Database	database
protected String	sql

Constructor Summary

TailoredQuery(Column[] selectedColumns, Table[] otherTables, String whereClause, String orderByClause)
Construct a low-level SQL query, possibly including joins, from which results come back in form of automatically-renderable Melati Fields.

TailoredQuery(String modifier, Column[] selectedColumns, Table[] otherTables, String whereClause, String orderByClause)
Same as without the first argument except that it is inserted between SELECT and the column list.

Method Summary

Enumeration	selection_firstRaw()
Enumeration	selection() Run the query.
String	toString() The SQL of the query.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

database

protected Database database

sql

protected String sql

Constructor Detail

TailoredQuery

public TailoredQuery(Column[] selectedColumns,
                     Table[] otherTables,
                     String whereClause,
                     String orderByClause)

Construct a low-level SQL query, possibly including joins, from which results come back in form of automatically-renderable Melati Fields. The queries you can construct are of the form

SELECT t1.c1, t2.c2 ... FROM t1, t2 ..., t10, t11 ... WHERE whereClause ORDER BY orderByClause

You specify the columns you want to return (t1.c1 ...) in the selectedColumns parameter, the selection criteria (including joins) in the whereClause parameter, and the ordering criteria in the orderByClause. If your whereClause or orderByClause use tables t10 ... not implied by selectedColumns, you must include them in the otherTables parameter.

Note that there is no provision for aliasing, which does restrict the available queries somewhat.

To get the results of the query, use selection() (below).

Example:

 Column[] columns = {
     database.getUserTable().getNameColumn(),
     database.getGroupTable().getNameColumn(),
 };
 Table[] tables = { database.getGroupMembershipTable() };
 
 TailoredQuery q =
     new TailoredQuery(
    columns, tables,
    "\"user\" = \"user\".id AND \"group\" = \"group\".id",
    null);
 
 for (Enumeration ms = q.selection(); ms.hasMoreElements();) {
   FieldSet fs = (FieldSet)ms.nextElement();
   System.out.println(fs.get("user_name").getCookedString(PoemLocale.HERE, DateFormat.MEDIUM) +
                 ", " +
                 fs.get("group_name").getCookedString(PoemLocale.HERE, DateFormat.MEDIUM));
 }
 

Parameters:

selectedColumns - The columns you want to select out

otherTables - Tables aside from those to which your selectedColumns are attached which you need to use in the whereClause

whereClause - Search criteria for your query; note that you will have to do any necessary quoting of identifiers/values yourself (or use Column.quotedName and PoemType.quotedRaw)

orderByClause - Ordering criteria for your query

See Also:

selection(), Column.quotedName(), BasePoemType.quotedRaw(java.lang.Object)

TailoredQuery

public TailoredQuery(String modifier,
                     Column[] selectedColumns,
                     Table[] otherTables,
                     String whereClause,
                     String orderByClause)

Same as without the first argument except that it is inserted between SELECT and the column list.

Parameters:

modifier - HACK Allow SQL modifier eg DISTINCT

selectedColumns -

otherTables -

whereClause -

orderByClause -

See Also:

TailoredQuery(Column[], Table[], String, String)

Method Detail

selection

public Enumeration selection()

Run the query.

Here's an example of presenting the results of a TailoredQuery in a WebMacro template:

<TABLE>
#foreach $fieldSet in $tailoredQuery.selection() #begin
<TR>
#foreach $field in $fieldSet #begin
<TD>$ml.rendered($field.DisplayName)</TD>
<TD>$ml.rendered($field)</TD>
#end
</TR>
#end
</TABLE>

Read access checks are performed against the POEM access token associated with the thread running this method (see PoemThread.accessToken()) on all the tables implied by the selectedColumns and otherTables arguments given at construct time. If the checks fail for a given row, all the fields in the corresponding FieldSet are booby-trapped to throw the relevant AccessPoemException (all of them, because, without parsing your whereClause, there's no way for POEM to know which columns are `tainted'; note that it's probably possible for you to bypass access checks by using sub-SELECTs).

Normally, Melati's response to an "access-denied" fields is to terminate template expansion and ask the user to log in, then continue where they were left off. If, however, you put $melati.setPassbackExceptionHandling() at the top of the template, or in the servlet, they will be rendered as warnings by e.g. HTMLMarkupLanguage.

NOTE Since this way of doing queries involves named fields rather than whole Persistent objects, it inevitably bypasses any Java-coded access rules put in place by the programmer by overriding Persistent.assertCanRead.

Returns:

An Enumeration of FieldSets, one per row returned from the DBMS. You can invoke each FieldSet's elements method to get an Enumeration of all the Fields in the row, ready for rendering. Or you can fetch them by name using the FieldSet's get method. A field corresponding to column col of table tab is named tab_col.

See Also:

FieldSet.elements(), FieldSet.get(java.lang.String), PoemThread.accessToken(), MarkupLanguage.rendered(Object), Persistent.assertCanRead(org.melati.poem.AccessToken)

selection_firstRaw

public Enumeration selection_firstRaw()

Returns:

the first row of this TailoredQuery

toString

public String toString()

The SQL of the query.

Overrides:

toString in class Object

See Also:

Object.toString()