001 package com.sptci.rwt;
002
003 import java.sql.Clob;
004 import java.sql.Connection;
005 import java.sql.ResultSet;
006 import java.sql.ResultSetMetaData;
007 import java.sql.Statement;
008 import java.sql.SQLException;
009 import java.sql.Types;
010 import java.util.Date;
011
012 import com.sptci.util.CloseJDBCResources;
013 import com.sptci.util.StringUtilities;
014
015 /**
016 * A utility class to execute SQL statement (only one statement allowed).
017 *
018 * <p>© Copyright 2007 <a href='http://sptci.com/' target='_new'>Sans Pareil Technologies, Inc.</a></p>
019 * @author Rakesh Vidyadharan 2007-10-01
020 * @version $Id: AbstractQueryExecutor.java 4123 2008-05-25 21:49:01Z rakesh $
021 * @see BatchQueryExecutor
022 */
023 public abstract class AbstractQueryExecutor
024 {
025 /** The manager to use to fetch database connections. */
026 protected final ConnectionManager manager;
027
028 /**
029 * Create a new instance using the specified manager to fetch connections.
030 *
031 * @param manager The connection manager to use.
032 */
033 protected AbstractQueryExecutor( final ConnectionManager manager )
034 {
035 this.manager = manager;
036 }
037
038 /**
039 * Create a {@link java.sql.Statement} for the specified
040 * SQL statement(s) and return it.
041 *
042 * @param connection The database connection to use.
043 * @throws SQLException If errors are encountered while creating the
044 * statement.
045 */
046 protected Statement createStatement( final Connection connection )
047 throws SQLException
048 {
049 return connection.createStatement(
050 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
051 }
052
053 /**
054 * Create a {@link Rows} object that represent the rows in the result
055 * set within the specified row range.
056 *
057 * @see #getClobValue
058 * @param statement The statement whose result set to process.
059 * @param page The page number being retrieved. Page number is specified
060 * using zero based indexing as opposed to 1 based.
061 * @param rowsPerPage The number of rows to display per page.
062 * @param maxColumnLength The maximum number of characters to retrieve
063 * from <code>CLOB</code> type columns to avoid memory issues.
064 * @throws SQLException If errors are encountered while executing the
065 * statement(s) and fetching the results.
066 */
067 protected Rows processResultSet( final Statement statement,
068 final int page, final int rowsPerPage, final int maxColumnLength )
069 throws SQLException
070 {
071 final int start = ( page * rowsPerPage ) + 1;
072 final int end = start + rowsPerPage - 1;
073 final int columnLength =
074 ( maxColumnLength > 0 ) ? maxColumnLength : Integer.MAX_VALUE;
075 final ResultSet resultSet = statement.getResultSet();
076 final Rows rows = new Rows();
077
078 resultSet.last();
079 rows.setTotalRows( resultSet.getRow() );
080
081 if ( start == 1 ) resultSet.beforeFirst();
082 else resultSet.absolute( start - 1 );
083
084 ResultSetMetaData rsmd = resultSet.getMetaData();
085 final int columnCount = rsmd.getColumnCount();
086
087 if ( rows.getTotalRows() < start )
088 {
089 final Row row = new Row();
090
091 for ( int i = 1; i <= columnCount; ++i )
092 {
093 final Column column = new Column();
094 column.setName( rsmd.getColumnLabel( i ) );
095 column.setType( rsmd.getColumnType( i ) );
096 row.addColumn( column );
097 }
098
099 rows.addRow( row );
100 }
101 else
102 {
103 while ( resultSet.next() && resultSet.getRow() <= end )
104 {
105 final Row row = new Row();
106
107 for ( int i = 1; i <= columnCount; ++i )
108 {
109 Object value = null;
110
111 switch ( rsmd.getColumnType( i ) )
112 {
113 case Types.BINARY:
114 case Types.BLOB:
115 case Types.DATALINK:
116 case Types.JAVA_OBJECT:
117 case Types.LONGVARBINARY:
118 case Types.OTHER:
119 case Types.REF:
120 case Types.VARBINARY:
121 value = "Undisplayable";
122 break;
123 case Types.CLOB:
124 value = getClobValue( resultSet.getClob( i ), columnLength );
125 break;
126 case Types.DATE:
127 if ( resultSet.getDate( i ) != null )
128 {
129 value = new Date( resultSet.getDate( i ).getTime() );
130 }
131 break;
132 case Types.TIME:
133 if ( resultSet.getTime( i ) != null )
134 {
135 value = new Date( resultSet.getTime( i ).getTime() );
136 }
137 break;
138 case Types.TIMESTAMP:
139 if ( resultSet.getTimestamp( i ) != null )
140 {
141 value = new Date( resultSet.getTimestamp( i ).getTime() );
142 }
143 break;
144 default:
145 value = processText( resultSet.getString( i ), columnLength );
146 break;
147 }
148
149 final Column column = new Column();
150 column.setName( rsmd.getColumnLabel( i ) );
151 column.setType( rsmd.getColumnType( i ) );
152 column.setContent( value );
153 row.addColumn( column );
154 }
155
156 rows.addRow( row );
157 }
158 }
159
160 CloseJDBCResources.close( resultSet );
161 return rows;
162 }
163
164 /**
165 * Return a {@link Rows} object that represents the
166 * <code>updateCount</code> obtained by executing a {@link
167 * java.sql.Statement}.
168 *
169 * @param statement The statement from which update count is to be
170 * retrieved.
171 * @return The rows object representing the update count.
172 * @throws SQLException If errors are encountered while fetching the
173 * update count from <code>statement</code>.
174 */
175 protected Rows processUpdateCount( final Statement statement )
176 throws SQLException
177 {
178 final Rows rows = new Rows();
179 final Row row = new Row();
180 final Column column = new Column();
181 column.setName( "Update Count" );
182 column.setType( Types.INTEGER );
183 column.setContent( statement.getUpdateCount() );
184
185 row.addColumn( column );
186 rows.addRow( row );
187
188 return rows;
189 }
190
191 /**
192 * Return the value that is to be displayed for a {@link java.sql.Clob}
193 * type.
194 *
195 * @since Version 1.3
196 * @param clob The CLOB whose content is to be retrieved.
197 * @param length The maximum number of characters to display.
198 * @throws SQLException If errors are encountered while fetching the
199 * content of the CLOB.
200 */
201 protected String getClobValue( final Clob clob, final int length )
202 throws SQLException
203 {
204 String value = ( clob.length() > length ) ?
205 clob.getSubString( 0, length ) + "..." :
206 clob.getSubString( 0, (int) clob.length() );
207 value = StringUtilities.stripInvalidXMLCharacters( value );
208 return value;
209 }
210
211 /**
212 * Return the value that is to be displayed for a text value. Strips
213 * any invalid XML characters and truncates if necessary.
214 *
215 * @since Version 1.3
216 * @param text The text content that is to be processed.
217 * @param length The maximum number of characters to display
218 */
219 protected String processText( final String text, final int length )
220 {
221 if ( text == null ) return null;
222
223 String value = ( text.length() > length ) ?
224 text.substring( 0, length ) + "..." : text;
225 value = StringUtilities.stripInvalidXMLCharacters( value );
226 return value;
227 }
228 }