001 package hirondelle.web4j.database;
002
003 import java.util.*;
004
005 import hirondelle.web4j.request.Formats;
006 import hirondelle.web4j.security.SafeText;
007 import hirondelle.web4j.util.Util;
008
009 /**
010 Utility for producing reports quickly from a <tt>ResultSet</tt>.
011
012 <P>Most web applications need to produce reports of summary or transaction information.
013 If a Model Object already exists for the given data, then it may be used to fetch and render the data, likely
014 with {@link Db}.
015
016 <P>If a Model Object does <em>not</em> already exist for the given report data, then this class may
017 be used to quickly implement the report, without the need to create a full Model Object.
018
019 <P>This class translates each <tt>ResultSet</tt> row into a <tt>Map</tt> of some form. This <tt>Map</tt> is meant not as a
020 robust Model Object, but rather as a rather dumb data carrier, built only for the purpose of reporting.
021 The <tt>Map</tt> key is always the column name, and the <tt>Map</tt> value takes various forms, according
022 to how the <tt>ResultSet</tt> is processed :
023 <ul>
024 <li>{@link #formatted(Class[], Locale, TimeZone, SqlId, DynamicCriteria, Object[])} - column values are parsed using
025 {@link ConvertColumn} into <tt>Integer</tt>, <tt>Date</tt>, and so on, and then standard formatting is applied
026 using {@link Formats#objectToText(Object)}. (This is the recommended style.)
027 <li>{@link #raw(SqlId, DynamicCriteria, Object[] )} - column values are all treated as simple text. To allow for various escaping styles in the view,
028 the text is actually returned as {@link SafeText}. By default, the text will be properly escaped for presentation
029 in HTML. If desired, some formatting can be applied directly in the underlying SQL statement itself, using database
030 formatting functions.
031 <li>{@link #unformatted(Class[], SqlId, DynamicCriteria, Object[])} - column values are parsed using {@link ConvertColumn} into
032 <tt>Integer</tt>, <tt>Date</tt>, and so on, but formatting is deferred to the JSP.
033 </ul>
034
035 <P>Example of using the <tt>Map</tt> in a JSP. Here, column values are assumed to be already formatted, using
036 either {@link #raw(SqlId, DynamicCriteria, Object[])} or {@link #formatted(Class[], Locale, TimeZone, SqlId, DynamicCriteria, Object[])}:
037 <PRE>
038 {@code
039 <c:forEach var="row" items="${reportMap}" >
040 <tr>
041 <td>${row['Name']}</td>
042 <td>${row['Visits']}</td>
043 </tr>
044 </c:forEach>
045 }
046 </PRE>
047
048 <P>If {@link #unformatted(Class[], SqlId, DynamicCriteria, Object[])} is used to build the <tt>Map</tt>, then formatting
049 of the resulting objects must be applied in the JSP.
050
051 <h3>Recommended Style</h3>
052 The recommended style is to use {@link #formatted(Class[], Locale, TimeZone, SqlId, DynamicCriteria, Object[])}.
053
054 <P>If {@link #raw(SqlId, DynamicCriteria, Object[])} or {@link #unformatted(Class[], SqlId, DynamicCriteria, Object[])} is used, then
055 the question usually arises of where to apply formatting :
056 <ul>
057 <li>format with database formatting functions - then there will often be much repetition of formatting function calls
058 across different <tt>SELECT</tt>s.
059 <li>format in the JSP (usually with JSTL) - be aware that there is often
060 significant work involved. Not one but three operations can be necessary : a parse operation,
061 a format operation, and possibly a check-for-null.
062 </ul>
063
064 <h3><a name="emptyContent"></a>Empty Values</h3>
065 When the <tt>Map</tt> returned by this class has values as text, then any <tt>Strings</tt> which do not satisfy
066 {@link Util#textHasContent(String)} are replaced with the
067 return value of {@link hirondelle.web4j.request.Formats#getEmptyOrNullText} (which is in
068 turn configured in <tt>web.xml</tt>). This is a workaround for the fact that most browsers do
069 not render empty <tt>TD</tt> tags very well when the cell has a border. An alternate
070 (and likely superior) workaround is to set the
071 <tt><a href="http://www.w3.org/TR/REC-CSS2/tables.html#empty-cells">empty-cells</a></tt>
072 property of Cascading Style Sheets to <tt>'show'</tt>.
073 */
074 public final class Report {
075
076 /**
077 Return column values without any processing.
078
079 <P>For the returned {@code Map<String, SafeText>} objects,
080 <ul>
081 <li>key is the column name
082 <li>value is the unprocessed column value, passed to a {@link SafeText}. <tt>SafeText</tt> is
083 used instead of <tt>String</tt> to allow easy escaping of special characters in the view.
084 </ul>
085
086 @param aSqlId identifies the underlying <tt>SELECT</tt> statement
087 @param aParams parameters for the <tt>SELECT</tt> statement, in the same order as in the underlying <tt>SELECT</tt> statement
088 */
089 public static List<Map<String, SafeText>> raw(SqlId aSqlId, DynamicCriteria aCriteria, Object... aParams) throws DAOException {
090 List<Map<String,SafeText>> result = new ArrayList<Map<String, SafeText>>();
091 ModelBuilder<Map<String, SafeText>> builder = new ReportBuilder();
092 SqlFetcher fetcher = getFetcher(aSqlId, aCriteria, aParams);
093 fetcher.fetchObjects(builder, result);
094 return result;
095 }
096
097 /**
098 Return column values after processing into formatted building block objects.
099
100 <P>For the returned {@code Map<String, SafeText>} objects,
101 <ul>
102 <li>key is the column name
103 <li>value is the processed column value, as <tt>SafeText</tt>. Column values are first parsed into
104 building block objects using {@link ConvertColumn}. Then the objects are formatted in a 'standard'
105 way using the configured {@link Formats}.
106 </ul>
107
108 @param aTargetClasses defines the target class for each column.
109 The order of the classes in the array corresponds one-to-one with the column order of the underlying <tt>ResultSet</tt>.
110 The size of the array matches the number of columns.
111 Each class in the array must be supported by the configured {@link ConvertColumn}.
112 @param aLocale <tt>Locale</tt> returned by {@link hirondelle.web4j.request.LocaleSource}
113 @param aTimeZone <tt>TimeZone</tt> returned by {@link hirondelle.web4j.request.TimeZoneSource}
114 @param aSqlId identifies the underlying <tt>SELECT</tt> statement
115 @param aCriteria possible <em>dynamic</em> <tt>WHERE</tt> or <tt>ORDER BY</tt> clause. If no dynamic criteria, then
116 just pass {@link DynamicCriteria#NONE}.
117 @param aParams parameters for the <tt>SELECT</tt> statement, in the same order as in the underlying <tt>SELECT</tt> statement
118 */
119 public static List<Map<String, SafeText>> formatted(Class<?>[] aTargetClasses, Locale aLocale, TimeZone aTimeZone, SqlId aSqlId, DynamicCriteria aCriteria, Object... aParams) throws DAOException {
120 List<Map<String, SafeText>> result = new ArrayList<Map<String, SafeText>>();
121 //any date columns must be formatted in a Locale-sensitive manner
122 Formats formats = new Formats(aLocale, aTimeZone);
123 ModelBuilder<Map<String, SafeText>> builder = new ReportBuilder(aTargetClasses, formats);
124 SqlFetcher fetcher = getFetcher(aSqlId, aCriteria, aParams);
125 fetcher.fetchObjects(builder, result);
126 return result;
127 }
128
129 /**
130 Return column values as unformatted building block objects.
131
132 <P>For the returned {@code Map<String, Object>} objects,
133 <ul>
134 <li>key is the column name
135 <li>value is the processed column value, parsed into a building block Object using {@link ConvertColumn}.
136 </ul>
137
138 @param aTargetClasses defines the target class for each column.
139 The order of the classes in the array corresponds one-to-one with the column order of the underlying <tt>ResultSet</tt>.
140 The size of the array matches the number of columns.
141 Each class in the array must be supported by the configured {@link ConvertColumn}.
142 @param aSqlId identifies the underlying <tt>SELECT</tt> statement
143 @param aCriteria possible <em>dynamic</em> <tt>WHERE</tt> or <tt>ORDER BY</tt> clause. If no dynamic criteria, then
144 just pass {@link DynamicCriteria#NONE}.
145 @param aParams parameters for the <tt>SELECT</tt> statement, in the same order as in the underlying <tt>SELECT</tt> statement
146 */
147 public static List<Map<String, Object>> unformatted(Class<?>[] aTargetClasses, SqlId aSqlId, DynamicCriteria aCriteria, Object... aParams) throws DAOException {
148 List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
149 ModelBuilder<Map<String, Object>> builder = new ReportBuilderUnformatted(aTargetClasses);
150 SqlFetcher fetcher = getFetcher(aSqlId, aCriteria, aParams);
151 fetcher.fetchObjects(builder, result);
152 return result;
153 }
154
155 // PRIVATE //
156
157 private Report(){
158 //empty - prevent construction by caller
159 }
160
161 private static SqlFetcher getFetcher(SqlId aSqlId, DynamicCriteria aCriteria, Object[] aParams) throws DAOException {
162 SqlFetcher result = null;
163 if( DynamicCriteria.NONE == aCriteria ) {
164 result = SqlFetcher.forSingleOp(aSqlId, aParams);
165 }
166 else {
167 result = SqlFetcher.forSearch(aSqlId, aCriteria, aParams);
168 }
169 return result;
170 }
171 }