C:\code\src\poolman\src\main\com\codestudio\bean\velocity\PoolManVelocityServlet.java
|
1 /*
2 * PoolMan Java Object Pooling and Caching Library
3 * Copyright (C) 1999-2001 The Code Studio
4 *
5 * This library is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU Lesser General Public
7 * License as published by the Free Software Foundation; either
8 * version 2 of the License, or (at your option) any later version.
9 *
10 * This library is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 * Lesser General Public License for more details.
14 *
15 * The full license is located at the root of this distribution
16 * in the LICENSE file.
17 */
18 package com.codestudio.bean.velocity;
19
20 import com.codestudio.sql.PoolMan;
21 import com.codestudio.util.JDBCPool;
22 import com.codestudio.util.SQLManager;
23 import org.apache.velocity.Template;
24 import org.apache.velocity.app.Velocity;
25 import org.apache.velocity.context.Context;
26 import org.apache.velocity.exception.ParseErrorException;
27 import org.apache.velocity.exception.ResourceNotFoundException;
28 import org.apache.velocity.servlet.VelocityServlet;
29
30 import javax.servlet.ServletConfig;
31 import javax.servlet.http.HttpServletRequest;
32 import javax.servlet.http.HttpServletResponse;
33 import javax.sql.DataSource;
34 import java.io.FileNotFoundException;
35 import java.io.IOException;
36 import java.sql.*;
37 import java.util.ArrayList;
38 import java.util.Enumeration;
39 import java.util.Properties;
40
41 /**
42 * A simple VelocityServlet extension for creating a web application using the
43 * poolman-velocity.vm XML template.
44 *
45 * @author PS Neville
46 */
47 public class PoolManVelocityServlet
48 extends VelocityServlet {
49
50 /* CONSTANTS USED TO IDENTIFY ATTRIBUTES IN THE VELOCITY CONTEXT */
51
52 public static final String SQL_QUERY = "sql";
53 public static final String CURRENT_DATABASE = "currentDatabaseName";
54 public static final String ALL_DB_NAMES = "allDatabaseNames";
55 public static final String HEADER_COLS = "headerColumns";
56 public static final String RESULTS_ROWS = "resultsRows";
57 public static final String ERRORS = "errors";
58 public static final String QUERY_COUNT = "queryCount";
59 public static final String QUERY_DURATION = "lastQueryDuration";
60 public static final String USING_CACHE = "usingCache";
61 public static final String DB_URL = "url";
62 public static final String DB_DRIVER = "driver";
63 public static final String DB_USER = "user";
64 public static final String TOTAL_CONS = "totalConnections";
65 public static final String AVAIL_CONS = "availableConnections";
66 public static final String UNAVAIL_CONS = "unavailableConnections";
67
68 /**
69 * A simplistic load method used to prepare Velocity.
70 */
71 protected Properties loadConfiguration(ServletConfig config)
72 throws IOException, FileNotFoundException {
73
74 Properties p = new Properties();
75 String path = config.getServletContext().getRealPath("/");
76
77 if (path == null) {
78 System.out.println(" com.codestudio.bean.velocity.PoolManVelocityServlet.loadConfiguration():" +
79 " unable to get the current webapp root. Using '/'. Please fix.");
80
81 path = "/";
82 }
83
84 p.setProperty(Velocity.FILE_RESOURCE_LOADER_PATH, path);
85 p.setProperty("runtime.log", path + "velocity.log");
86
87 return p;
88 }
89
90
91 /**
92 * The servlet entry point. It passes control off to a method that will
93 * introspect the Context and the request for the sake of transforming the Context
94 * and template.
95 */
96 public Template handleRequest(HttpServletRequest req,
97 HttpServletResponse res,
98 Context ctx) {
99
100 // get the request params and possibly execute some sql
101 if (inspectRequest(ctx, req)) {
102 try {
103 executeSQL(ctx);
104 } catch (SQLException sqle) {
105 // record any errors
106 ctx.put(ERRORS, sqle.toString());
107 }
108 }
109
110 // set metrics
111 try {
112 String currentDB = (String) ctx.get(CURRENT_DATABASE);
113 JDBCPool pool = (JDBCPool) SQLManager.getInstance().getPool(currentDB);
114 ctx.put(TOTAL_CONS, "" + pool.numTotalObjects());
115 ctx.put(AVAIL_CONS, "" + pool.numCheckedInObjects());
116 ctx.put(UNAVAIL_CONS, "" + pool.numCheckedOutObjects());
117 } catch (Exception e) {
118 // ok, ignorable
119 }
120
121 Template outty = null;
122
123 try {
124 outty = getTemplate("poolman-velocity.vm");
125 } catch (ParseErrorException pee) {
126 System.out.println("com.codestudio.bean.velocity.PoolManVelocityServlet : parse error for template " + pee);
127 } catch (ResourceNotFoundException rnfe) {
128 System.out.println("com.codestudio.bean.velocity.PoolManVelocityServlet : template not found " + rnfe);
129 } catch (Exception e) {
130 System.out.println("Error " + e);
131 }
132 return outty;
133 }
134
135 /**
136 * Handle request parameters and make a decision (boolean) about whether
137 * a SQL invocation should occur.
138 */
139 protected boolean inspectRequest(Context ctx, HttpServletRequest req) {
140
141 boolean executeSQL = false;
142
143 // get all database names
144 ArrayList allDatabaseNames = null;
145 try {
146 allDatabaseNames = (ArrayList) ctx.get(ALL_DB_NAMES);
147 } catch (Exception e) {
148 }
149 if ((allDatabaseNames == null) || (allDatabaseNames.size() < 1)) {
150 allDatabaseNames = new ArrayList();
151 Enumeration enum = SQLManager.getInstance().getAllPoolnames();
152 while (enum.hasMoreElements()) {
153 allDatabaseNames.add(enum.nextElement());
154 }
155 if (allDatabaseNames.size() < 1)
156 allDatabaseNames = null;
157 }
158 ctx.put(ALL_DB_NAMES, allDatabaseNames);
159
160 // get the current selected database, if there is one
161 String currentDB = null;
162 try {
163 currentDB = (String) req.getParameter(CURRENT_DATABASE);
164 ctx.put(CURRENT_DATABASE, currentDB);
165 } catch (Exception e) {
166 }
167
168 // get the information about this database
169 if (currentDB != null) {
170 JDBCPool pool = (JDBCPool) SQLManager.getInstance().getPool(currentDB);
171 if (pool.usingCache())
172 ctx.put(USING_CACHE, "true");
173 else
174 ctx.put(USING_CACHE, "false");
175 ctx.put(DB_URL, pool.getURL());
176 ctx.put(DB_DRIVER, pool.getDriver());
177 ctx.put(DB_USER, pool.getUserName());
178 }
179
180
181 // get the sql string, if there is one
182 String sql = null;
183 try {
184 sql = (String) req.getParameter(SQL_QUERY);
185 } catch (Exception e) {
186 }
187 if ((sql != null) && (sql.length() > 0)) {
188 ctx.put(SQL_QUERY, sql);
189 }
190 else {
191 ctx.put(SQL_QUERY, "");
192 }
193
194 // if we have valid data, we need to execute the SQL
195 if ((currentDB != null) && (sql != null) && (sql.length() > 0)) {
196 executeSQL = true;
197 }
198 else {
199 executeSQL = false;
200 }
201
202 return executeSQL;
203 }
204
205 /**
206 * Execute a generic sql statement without knowing much about it or its destination.
207 * A real-world query would be less generic, making the code simpler than this example.
208 */
209 protected void executeSQL(Context ctx)
210 throws SQLException {
211
212 // clear previous data
213 ctx.put(ERRORS, null);
214 ctx.put(HEADER_COLS, null);
215 ctx.put(RESULTS_ROWS, null);
216
217 // retrieve the dbname and sql
218 String currentDB = (String) ctx.get(CURRENT_DATABASE);
219 String sqlQuery = (String) ctx.get(SQL_QUERY);
220
221 // prepare the variables outside the loop to ensure closing
222 DataSource ds = null;
223 Connection con = null;
224 Statement st = null;
225 ResultSet res = null;
226
227 // handle query count
228 int queryCount = 0;
229 try {
230 int n = Integer.parseInt((String) ctx.get(QUERY_COUNT));
231 if (n > queryCount) {
232 queryCount = n;
233 }
234 } catch (Exception e) {}
235 queryCount++;
236 ctx.put(QUERY_COUNT, "" + queryCount);
237
238 try {
239
240 // start the method timer
241 long start = System.currentTimeMillis();
242
243 // get the DataSource
244 ds = PoolMan.findDataSource(currentDB);
245
246 // get the Connection
247 con = ds.getConnection();
248
249 // create the Statement
250 st = con.createStatement();
251
252 // prepare for the returned data
253 ArrayList allRows = new ArrayList();
254
255 if (st.execute(sqlQuery)) {
256
257 // done executing query, set time
258 ctx.put(QUERY_DURATION, "" + (System.currentTimeMillis() - start));
259
260 // there was a ResultSet
261 res = st.getResultSet();
262
263 // get the header
264 ResultSetMetaData meta = res.getMetaData();
265 ArrayList header = new ArrayList();
266 for (int n = 1; n <= meta.getColumnCount(); n++) {
267 header.add(meta.getColumnLabel(n));
268 }
269 ctx.put(HEADER_COLS, header);
270
271 // get the rows
272 while (res.next()) {
273 ArrayList row = new ArrayList();
274 for (int n = 1; n <= meta.getColumnCount(); n++) {
275 Object value = null;
276 try {
277 switch (meta.getColumnType(n)) {
278 case Types.CHAR:
279 try {
280 value = new String(res.getBytes(n));
281 } catch (Exception _e) {
282 value = res.getObject(n);
283 }
284 break;
285 default:
286 value = res.getObject(n);
287 break;
288 }
289 } catch (Exception ee) {
290 }
291 if (value == null)
292 value = new String("NULL");
293 row.add(value);
294 }
295 allRows.add(row);
296 }
297 }
298
299 else {
300
301 // done executing query, set time
302 ctx.put(QUERY_DURATION, "" + (System.currentTimeMillis() - start));
303
304 // no ResultSet
305 String resultsMessage = null;
306 int num = st.getUpdateCount();
307 switch (num) {
308 case 0:
309 resultsMessage = "No rows affected";
310 break;
311 case 1:
312 resultsMessage = "1 row affected";
313 break;
314 default:
315 resultsMessage = num + " rows affected";
316 }
317 ArrayList row = new ArrayList();
318 row.add(resultsMessage);
319 allRows.add(row);
320 }
321
322 // put the returned rows in the context
323 ctx.put(RESULTS_ROWS, allRows);
324
325 } catch (SQLException e) {
326 throw e;
327 }
328 finally {
329 JDBCPool.closeResources(con, st, res);
330 }
331 }
332 }
333
334
335
336
337