001 package com.sptci.rwt;
002
003 import java.sql.Statement;
004 import java.sql.Connection;
005 import java.sql.ResultSet;
006 import java.sql.SQLException;
007
008 import com.sptci.util.CloseJDBCResources;
009
010 /**
011 * A utility class to execute SQL statement (only one statement allowed).
012 *
013 * <p>© Copyright 2007 <a href='http://sptci.com/' target='_new'>Sans Pareil Technologies, Inc.</a></p>
014 * @author Rakesh Vidyadharan 2007-10-01
015 * @version $Id: QueryExecutor.java 4123 2008-05-25 21:49:01Z rakesh $
016 * @see BatchQueryExecutor
017 */
018 public class QueryExecutor extends AbstractQueryExecutor
019 {
020 /**
021 * Create a new instance using the specified manager to fetch connections.
022 *
023 * @param manager The connection manager to use.
024 */
025 public QueryExecutor( final ConnectionManager manager )
026 {
027 super( manager );
028 }
029
030 /**
031 * Execute the specified statement and return a {@link Rows}
032 * object that represent the data in the {@link java.sql.ResultSet}
033 * obtained by executing the statement.
034 *
035 * @see #createStatement
036 * @see #processResultSet
037 * @see #processUpdateCount
038 * @param sql The statement to be executed.
039 * @param parameters Additional parameters to specify the following values
040 * in the order listed:
041 * <ul>
042 * <li><code>maxRows</code> The maximum number of rows to fetch from
043 * the database.</li>
044 * <li><code>page</code> The current page of data being viewed. Page
045 * is specified using zero based indexing.</li>
046 * <li><code>rowsPerPage</code> The number of rows of data to display
047 * on a page</li>.
048 * <li><code>maxColumnLength</code> The maximum length for a column
049 * value. This is used to truncate the value of <code>CLOB</code>
050 * type objects to avoid memory issues.</li>
051 * </ul>
052 * Note that specifying <code>page</code> should be combined with
053 * specifying <code>rowsPerPage</code> for proper behaviour. A default
054 * value of <code>100</code> will be used otherwise.
055 * @return The value object representing rows in the specified range in
056 * the {@link java.sql.ResultSet}.
057 * @throws SQLException If errors are encountered while executing the
058 * statement.
059 */
060 public Rows execute( final String sql, final int... parameters )
061 throws SQLException
062 {
063 int maxRows = 0;
064 int page = 0;
065 int rowsPerPage = Integer.MAX_VALUE;
066 int maxColumnLength = 0;
067
068 if ( parameters != null )
069 {
070 switch ( parameters.length )
071 {
072 case 1:
073 maxRows = parameters[0];
074 break;
075 case 2:
076 maxRows = parameters[0];
077 page = parameters[1];
078 rowsPerPage = 100;
079 break;
080 case 3:
081 maxRows = parameters[0];
082 page = parameters[1];
083 rowsPerPage = parameters[2];
084 break;
085 case 4:
086 maxRows = parameters[0];
087 page = parameters[1];
088 rowsPerPage = parameters[2];
089 maxColumnLength = parameters[3];
090 break;
091 }
092 }
093
094 Connection connection = null;
095 Statement statement = null;
096 Rows rows = null;
097
098 try
099 {
100 connection = manager.open();
101 statement = createStatement( connection );
102 statement.setMaxRows( maxRows );
103
104 if ( rowsPerPage > 0 && rowsPerPage != Integer.MAX_VALUE )
105 {
106 statement.setFetchSize( rowsPerPage );
107 }
108
109 boolean status = statement.execute( sql );
110
111 if ( status )
112 {
113 rows =
114 processResultSet( statement, page, rowsPerPage, maxColumnLength );
115 }
116 else
117 {
118 rows = processUpdateCount( statement );
119 }
120 }
121 finally
122 {
123 CloseJDBCResources.closeAll( statement );
124 CloseJDBCResources.close( connection );
125 }
126
127 return rows;
128 }
129
130 /**
131 * Return the total number of rows that can be retrieved by executing the
132 * specified SQL statement.
133 *
134 * @param sql The statement to be executed.
135 * @return The total number of rows that are available in the database.
136 * @throws SQLException If errors are encountered while executing the
137 * statement.
138 */
139 public int getTotalRows( final String sql ) throws SQLException
140 {
141 Connection connection = null;
142 Statement statement = null;
143 ResultSet resultSet = null;
144 int total = 0;
145
146 try
147 {
148 connection = manager.open();
149 statement = createStatement( connection );
150 boolean status = statement.execute( sql );
151
152 if ( status )
153 {
154 resultSet = statement.getResultSet();
155 status = resultSet.last();
156 if ( status ) total = resultSet.getRow();
157 }
158 else
159 {
160 if ( statement.getUpdateCount() != -1 )
161 {
162 total = 1;
163 }
164 }
165 }
166 finally
167 {
168 CloseJDBCResources.closeAll( resultSet );
169 CloseJDBCResources.close( connection );
170 }
171
172 return total;
173 }
174 }