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><tt>formatted</tt> - 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><tt>unformatted</tt> - column values are parsed using {@link ConvertColumn} into
028 <tt>Integer</tt>, <tt>Date</tt>, and so on, but formatting is deferred to the JSP.
029 <li><tt>raw</tt> - column values are all treated as simple text. To allow for various escaping styles in the view,
030 the text is actually returned as {@link SafeText}. By default, the text will be properly escaped for presentation
031 in HTML. If desired, some formatting can be applied directly in the underlying SQL statement itself, using database
032 formatting functions.
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 <tt>raw</tt> or <tt>formatted</tt>:
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 <tt>unformatted</tt> 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 <tt>formatted</tt>.
053
054 <P>If <tt>raw</tt> or <tt>unformatted</tt> 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 aCriteria possible <em>dynamic</em> <tt>WHERE</tt> or <tt>ORDER BY</tt> clause. If no dynamic criteria, then
088 just pass {@link DynamicSql#NONE}.
089 @param aParams parameters for the <tt>SELECT</tt> statement, in the same order as in the underlying <tt>SELECT</tt> statement
090 */
091 public static List<Map<String, SafeText>> raw(SqlId aSqlId, DynamicSql aCriteria, Object... aParams) throws DAOException {
092 List<Map<String,SafeText>> result = new ArrayList<Map<String, SafeText>>();
093 ModelBuilder<Map<String, SafeText>> builder = new ReportBuilder();
094 SqlFetcher fetcher = getFetcher(aSqlId, aCriteria, aParams);
095 fetcher.fetchObjects(builder, result);
096 return result;
097 }
098
099 /**
100 Return column values after processing into formatted building block objects.
101
102 <P>For the returned {@code Map<String, SafeText>} objects,
103 <ul>
104 <li>key is the column name
105 <li>value is the processed column value, as <tt>SafeText</tt>. Column values are first parsed into
106 building block objects using {@link ConvertColumn}. Then the objects are formatted in a 'standard'
107 way using the configured {@link Formats}.
108 </ul>
109
110 @param aTargetClasses defines the target class for each column.
111 The order of the classes in the array corresponds one-to-one with the column order of the underlying <tt>ResultSet</tt>.
112 The size of the array matches the number of columns.
113 Each class in the array must be supported by the configured {@link ConvertColumn}.
114 @param aLocale <tt>Locale</tt> returned by {@link hirondelle.web4j.request.LocaleSource}
115 @param aTimeZone <tt>TimeZone</tt> returned by {@link hirondelle.web4j.request.TimeZoneSource}
116 @param aSqlId identifies the underlying <tt>SELECT</tt> statement
117 @param aCriteria possible <em>dynamic</em> <tt>WHERE</tt> or <tt>ORDER BY</tt> clause. If no dynamic criteria, then
118 just pass {@link DynamicSql#NONE}.
119 @param aParams parameters for the <tt>SELECT</tt> statement, in the same order as in the underlying <tt>SELECT</tt> statement
120 */
121 public static List<Map<String, SafeText>> formatted(Class<?>[] aTargetClasses, Locale aLocale, TimeZone aTimeZone, SqlId aSqlId, DynamicSql aCriteria, Object... aParams) throws DAOException {
122 List<Map<String, SafeText>> result = new ArrayList<Map<String, SafeText>>();
123 //any date columns must be formatted in a Locale-sensitive manner
124 Formats formats = new Formats(aLocale, aTimeZone);
125 ModelBuilder<Map<String, SafeText>> builder = new ReportBuilder(aTargetClasses, formats);
126 SqlFetcher fetcher = getFetcher(aSqlId, aCriteria, aParams);
127 fetcher.fetchObjects(builder, result);
128 return result;
129 }
130
131 /**
132 Return column values as unformatted building block objects.
133
134 <P>For the returned {@code Map<String, Object>} objects,
135 <ul>
136 <li>key is the column name
137 <li>value is the processed column value, parsed into a building block Object using {@link ConvertColumn}.
138 </ul>
139
140 @param aTargetClasses defines the target class for each column.
141 The order of the classes in the array corresponds one-to-one with the column order of the underlying <tt>ResultSet</tt>.
142 The size of the array matches the number of columns.
143 Each class in the array must be supported by the configured {@link ConvertColumn}.
144 @param aSqlId identifies the underlying <tt>SELECT</tt> statement
145 @param aCriteria possible <em>dynamic</em> <tt>WHERE</tt> or <tt>ORDER BY</tt> clause. If no dynamic criteria, then
146 just pass {@link DynamicSql#NONE}.
147 @param aParams parameters for the <tt>SELECT</tt> statement, in the same order as in the underlying <tt>SELECT</tt> statement
148 */
149 public static List<Map<String, Object>> unformatted(Class<?>[] aTargetClasses, SqlId aSqlId, DynamicSql aCriteria, Object... aParams) throws DAOException {
150 List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
151 ModelBuilder<Map<String, Object>> builder = new ReportBuilderUnformatted(aTargetClasses);
152 SqlFetcher fetcher = getFetcher(aSqlId, aCriteria, aParams);
153 fetcher.fetchObjects(builder, result);
154 return result;
155 }
156
157 // PRIVATE //
158
159 private Report(){
160 //empty - prevent construction by caller
161 }
162
163 private static SqlFetcher getFetcher(SqlId aSqlId, DynamicSql aCriteria, Object[] aParams) throws DAOException {
164 SqlFetcher result = null;
165 if( DynamicSql.NONE == aCriteria ) {
166 result = SqlFetcher.forSingleOp(aSqlId, aParams);
167 }
168 else {
169 result = SqlFetcher.forSearch(aSqlId, aCriteria, aParams);
170 }
171 return result;
172 }
173 }