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