001package bradleyross.j2ee.servlets; 002import java.io.*; 003import java.sql.SQLException; 004import java.sql.Types; 005import java.sql.ResultSet; 006import java.sql.ResultSetMetaData; 007import javax.servlet.*; 008import javax.servlet.http.*; 009import bradleyross.library.database.DatabaseProperties; 010/** 011 * Serves as a template for writing servlets that 012 * display the contains of a java.sql.ResultSet object 013 * as a CSV (comma separated values) page. 014 * 015 * <p>The methods getResultSet and makeConnection are 016 * abstract and will need to be overridden before 017 * this class can be used. The user may also wish to 018 * override several of the methods for formatting 019 * information.</p> 020 * <p>This code needs to be changed since many of the fields that belong 021 * to the instance must be moved within the service method since there 022 * can be multiple copies of the service method running at the same time.</p> 023 * @see #getResultSet(HttpServletRequest, DatabaseProperties) 024 * @author Bradley Ross 025 */ 026public abstract class CsvWriter extends HttpServlet 027{ 028 /** 029 * Provided for compliance with Serializable interface. 030 */ 031 private static final long serialVersionUID = 1L; 032 /** 033 * ServletConfig object as passed to the init method. 034 * <p>Must be moved to inner class</p> 035 */ 036 protected ServletConfig config = null; 037 /** 038 * Called by the servlet container when the servlet object is first placed 039 * in service. 040 */ 041 public void init(ServletConfig configIn) throws ServletException 042 { 043 super.init(configIn); 044 config = configIn; 045 } 046 /** 047 * Called by the servlet container when the servlet object is being taken out 048 * of service. 049 */ 050 public void destroy() 051 { super.destroy(); } 052 /** 053 * Encapsulates methods and properties for a given instance of the 054 * service method. 055 * 056 * <p>Methods and properties in this class will normally not be 057 * overridden.</p> 058 * @author Bradley Ross 059 * 060 */ 061 protected class Instance extends Object 062 { 063 /** 064 * Object containing HTTP request. 065 */ 066 protected HttpServletRequest request = null; 067 /** 068 * Object used for preparing HTTP response. 069 */ 070 protected HttpServletResponse response = null; 071 public Instance (HttpServletRequest req, HttpServletResponse res) 072 { 073 request = req; 074 response = res; 075 try 076 { 077 output = res.getWriter(); 078 } 079 catch (IOException e) 080 { 081 e.printStackTrace(); 082 } 083 } 084 /** 085 * File name to be used when creating file. File will be 086 * suffixed with .csv. 087 * <p>Changes to this field can be made using the setValues method of the 088 * enclosing class. 089 * </p> 090 * @see #setOutputFilename(String) 091 * @see #getOutputFilename() 092 * @see CsvWriter#setValues(Instance) 093 * 094 */ 095 protected String outputFilename = "default"; 096 /** 097 * String to be placed in CSV file as the first line in the file. 098 * <p>Changes to this field can be made by using the setValues 099 * method of the enclosing class.</p> 100 * @see CsvWriter#setValues(Instance) 101 * 102 */ 103 protected String message = null; 104 /** 105 * Object containing database connection information. 106 * <p>This field is set by the makeConnection method.</p> 107 * @see CsvWriter#makeConnection() 108 */ 109 protected bradleyross.library.database.DatabaseProperties data = null; 110 /** 111 * Object containing the result set. 112 * <p>This object is created by the getResultSet method which 113 * is called by the service method.</p> 114 * @see CsvWriter#getResultSet(HttpServletRequest, DatabaseProperties) 115 */ 116 protected java.sql.ResultSet rs = null; 117 /** 118 * Object containing meta data on result set. 119 * <p>This object is created by the service method after the 120 * execution of the getResultSet method and before the 121 * execution of the printHeader method.</p> 122 * <p>Must be moved to inner class</p> 123 */ 124 protected java.sql.ResultSetMetaData rsmd = null; 125 /** 126 * Object representing the output to the HTTP response. 127 */ 128 protected java.io.PrintWriter output = null; 129 /** 130 * Number of columns in result set 131 */ 132 protected int columnCount; 133 /** 134 * Getter for rs. 135 * @return ResultSet object 136 * @see #rs 137 */ 138 public ResultSet getResultSet() 139 { 140 return rs; 141 } 142 /** 143 * Setter for rs. 144 * <p>This would not normally be called from outside classes.</p> 145 * @param value Value to be used for field 146 * @see #rs 147 */ 148 public void setResultSet(ResultSet value) 149 { 150 try 151 { 152 rs = value; 153 rsmd = rs.getMetaData(); 154 columnCount = rsmd.getColumnCount(); 155 } 156 catch (SQLException e) 157 { 158 159 e.printStackTrace(); 160 } 161 } 162 /** 163 * Getter for rsmd. 164 * 165 * @return ResultSetMetaData object 166 * @see #rsmd 167 */ 168 public ResultSetMetaData getResultSetMetaData() 169 { 170 return rsmd; 171 } 172 /** 173 * Setter for rsmd. 174 * 175 * <p>This would not normally be called by external classes.</p> 176 * 177 * @param value ResultSetMetaData object 178 * @see #rsmd 179 */ 180 public void setResultSetMetaData(ResultSetMetaData value) 181 { 182 rsmd = value; 183 } 184 public DatabaseProperties getDatabaseProperties() 185 { 186 return data; 187 } 188 public void setDatabaseProperties (DatabaseProperties value) 189 { 190 data = value; 191 } 192 public String getOutputFilename() 193 { 194 return outputFilename; 195 } 196 public void setOutputFilename(String value) 197 { 198 outputFilename = value; 199 } 200 /** 201 * Obtains number of columns in result set 202 * @return Number of columns 203 * @see #columnCount 204 */ 205 public int getColumnCount() 206 { 207 return columnCount; 208 } 209 /** 210 * Sets number of columns in result set. 211 * <p>This method would not normally be used.</p> 212 * @param value Value to be used 213 * @see #columnCount 214 */ 215 public void setColumnCount(int value) 216 { 217 columnCount = value; 218 } 219 /** 220 * Getter for output. 221 * @return PrintWriter object. 222 * @see #output 223 */ 224 public PrintWriter getPrintWriter() 225 { 226 return output; 227 } 228 /** 229 * Getter for message. 230 * 231 * @return Message text 232 * @see #message 233 */ 234 public String getMessage() 235 { 236 return message; 237 } 238 /** 239 * Getter for request. 240 * 241 * @return HttpServletRequest object 242 * @see #request 243 */ 244 public HttpServletRequest getRequest() 245 { 246 return request; 247 } 248 /** 249 * Getter for response. 250 * @return HttpServletResponse object 251 * @see #response 252 */ 253 public HttpServletResponse getResponse() 254 { 255 return response; 256 } 257 } 258 /** 259 * Called by servlet container when an HTTP transaction requests a response 260 * from the server. 261 * 262 * @param req Object containing request information 263 * @param res Object containing response information 264 */ 265 public void service (HttpServletRequest req, 266 HttpServletResponse res) throws IOException 267 { 268 Instance instance = new Instance(req, res); 269 PrintWriter output = instance.getPrintWriter(); 270 try 271 { 272 instance.setDatabaseProperties(makeConnection()); 273 instance.getDatabaseProperties().connect(); 274 ResultSet rs = getResultSet(req, instance.getDatabaseProperties()); 275 instance.setResultSet(rs); 276 setValues(instance); 277 res.setContentType("text/csv"); 278 res.setHeader("Content-Disposition", "attachment;filename=" + 279 instance.getOutputFilename() + ".csv"); 280 if (instance.getMessage() != null) 281 { output.print(instance.getMessage() + "\r\n"); } 282 if (rs == null) 283 { 284 output.print("No result set provided\r\n"); 285 return; 286 } 287 boolean moreRecords = rs.next(); 288 if (!moreRecords) 289 { 290 output.print("Result set is empty\r\n"); 291 return; 292 } 293 else 294 { printHeader(instance); } 295 while (true) 296 { 297 printLine(instance); 298 moreRecords = rs.next(); 299 if (!moreRecords) { break; } 300 } 301 instance.getResultSet().close(); 302 instance.getDatabaseProperties().close(); 303 } 304 catch (java.io.IOException e) 305 { throw new java.io.IOException("Error in processing file: " + 306 e.getClass().getName() + " : " + e.getMessage()); } 307 catch (java.sql.SQLException e) 308 { 309 System.out.println("SQL error in processing CSV file"); 310 throw new java.io.IOException("SQL error: " + e.getClass().getName() + 311 " : " + e.getMessage()); 312 } 313 } 314 /** 315 * Obtain the database connection information. 316 * @return database information object 317 * @throws IOException if io errors 318 */ 319 protected abstract bradleyross.library.database.DatabaseProperties 320 makeConnection() throws IOException; 321 /** 322 * Will be overridden with the method for obtaining the SQL 323 * result set. 324 * <p>This should be kept separate from the getResultSet method within the 325 * Instance class. The {@link Instance#getResultSet() } method returns the 326 * ResultSet object stored in the Instance object. This method actually generates 327 * the ResultSet object. The service method then stores the result of this method 328 * in the Instance object.</p> 329 * @param req Request information 330 * @param data database object 331 * @return Result set 332 * @throws IOException if io errors 333 * @see Instance#message 334 * @see Instance#outputFilename 335 */ 336 protected abstract java.sql.ResultSet getResultSet(HttpServletRequest req, DatabaseProperties data) 337 throws java.io.IOException; 338 /** 339 * Sets values in Instance object. 340 * <p>This must be overridden in a subclass in order to set parameters.</p> 341 * @param instance Instance object being affected 342 * @throws java.io.IOException if io errors 343 */ 344 protected void setValues(Instance instance) throws java.io.IOException 345 { ; } 346 /** 347 * Will print header line for CSV file. 348 * @param instance instance of code processing request 349 * @throws IOException if io error 350 */ 351 protected void printHeader (Instance instance) 352 throws java.io.IOException 353 { 354 ResultSetMetaData rsmd = instance.getResultSetMetaData(); 355 PrintWriter output = instance.getPrintWriter(); 356 try 357 { 358 for (int i = 1; i <= rsmd.getColumnCount(); i++) 359 { 360 if (i != 1) { output.print(","); } 361 output.print(rsmd.getColumnName(i)); 362 } 363 output.print("\r\n"); 364 } 365 catch (Exception e) 366 { throw new java.io.IOException("Error detected: " + e.getClass().getName() + 367 " : " + e.getMessage()); } 368 } 369 /** 370 * Will print one line of CSV file. 371 * <p>This method will be changed at a later time to allow the 372 * specification of overriding methods for formatting timestamps, 373 * integers, floating point numbers, character strings, etc.</p> 374 * @param instance instance of object processing request 375 * @throws IOException if io error 376 */ 377 protected void printLine (Instance instance) 378 throws java.io.IOException 379 { 380 int columnCount = 0; 381 ResultSet rs = instance.getResultSet(); 382 ResultSetMetaData rsmd = instance.getResultSetMetaData(); 383 PrintWriter output = instance.getPrintWriter(); 384 try 385 { 386 columnCount = rsmd.getColumnCount(); 387 } 388 catch (SQLException e1) 389 { 390 e1.printStackTrace(); 391 } 392 for (int i = 1; i <= columnCount; i++) 393 { 394 try 395 { 396 if (i != 1) { output.print(","); } 397 int columnType = rsmd.getColumnType(i); 398 if (columnType == Types.VARCHAR || columnType == Types.CHAR || 399 columnType == Types.LONGVARCHAR) 400 { 401 String working = rs.getString(i); 402 if (rs.wasNull()) { working = new String(); } 403 output.print(formatString(working, i)); 404 } 405 else if (columnType == Types.FLOAT || columnType == Types.REAL || 406 columnType == Types.DOUBLE) 407 { 408 double working = rs.getDouble(i); 409 if (rs.wasNull()) 410 { output.print(new String()); } 411 else 412 { output.print(formatDouble(working)); } 413 } 414 else if (columnType == Types.TINYINT || columnType == Types.SMALLINT || 415 columnType == Types.INTEGER || columnType == Types.BIGINT) 416 { 417 int working = rs.getInt(i); 418 if (rs.wasNull()) 419 { output.print(new String()); } 420 else 421 { output.print(formatInteger(working)); } 422 } 423 else if (columnType == Types.TIMESTAMP) 424 { 425 java.sql.Timestamp working = rs.getTimestamp(i); 426 if (rs.wasNull()) 427 { output.print(new String()); } 428 else 429 { 430 java.util.Date date = new java.util.Date(working.getTime()); 431 output.print(formatDateTime(date)); 432 } 433 } 434 else 435 /* 436 ** Haven't decided how to handle Types.DECIMAL and 437 ** TYPES.GENERIC. However, this may be academic as I don't 438 ** think that these will occur in the databases 439 ** that I will be using. 440 */ 441 { 442 String working = rs.getString(i); 443 if (rs.wasNull()) 444 { output.print(new String()); } 445 else 446 { output.print(working); } 447 } 448 } 449 catch (Exception e) 450 { throw new java.io.IOException("Error detected in column " + 451 Integer.toString(i) + ":" + 452 e.getClass().getName() + " : " + 453 e.getMessage()); } 454 } 455 try 456 { output.print("\r\n"); } 457 catch (Exception e) 458 { throw new java.io.IOException("Error detected: " + 459 e.getClass().getName() + " : " + 460 e.getMessage()); } 461 } 462 /** 463 * Method of calling formatString when it doesn't 464 * depend on the column in the result set. 465 * @param valueIn String to be processed 466 * @return Processed string 467 */ 468 protected final String formatString(String valueIn) 469 { return formatString(valueIn, -1); } 470 /** 471 * Double up on all double quotes in string and surround the string with 472 * double quotes so that it will be in the proper format for the CSV file. 473 * @param valueIn String to be processed 474 * @param column Column number of item - This will allow different formatting to be used 475 * for each column. This parameter is not used unless the method is 476 * overridden. 477 * @return Processed string 478 */ 479 protected String formatString(String valueIn, int column) 480 { 481 String working = null; 482 if (valueIn == null) 483 { working = " "; } 484 else if (valueIn.length() == 0) 485 { working = " "; } 486 else 487 { 488 java.util.regex.Pattern pattern = java.util.regex.Pattern.compile("\\\""); 489 java.util.regex.Matcher matcher = pattern.matcher(valueIn); 490 working = matcher.replaceAll("\"\""); 491 } 492 if (working == null) { working = " "; } 493 return "\"" + working + "\""; 494 } 495 /** 496 * Alternate calling sequence, leaving out the column number. 497 * @param valueIn Floating point number to be formatted 498 * @return Formatted string 499 */ 500 protected final String formatDouble (double valueIn) 501 { return formatDouble (valueIn, -1); } 502 /** 503 * Formatter for floating point numbers. 504 * <p>This method can be overridden to control printing of 505 * the values in the CSV file.</p> 506 * @param valueIn Floating point number to be processed 507 * @param column Column number - This will allow different formatting to be used 508 * for each column. This parameter is not used unless the method 509 * is overridden. 510 * @return Formatted string 511 */ 512 protected String formatDouble (double valueIn, int column) 513 { return Double.toString(valueIn); } 514 /** 515 * Alternate calling sequence, omitting the column number. 516 * @param valueIn Integer value to be formatted 517 * @return Formatted string 518 */ 519 protected final String formatInteger (int valueIn) 520 { return formatInteger(valueIn, -1); } 521 /** 522 * Formatter for integer values. 523 * <p>This method can be overridden to control printing of 524 * the values in the CSV file.</p> 525 * @param valueIn Integer to be formatted 526 * @param column Column number - This will allow different formatting to be used 527 * for each column. This parameter is not used unless the method is 528 * overridden. 529 * @return Formatted string 530 */ 531 protected String formatInteger (int valueIn, int column) 532 { return Integer.toString(valueIn); } 533 /** 534 * Alternate calling sequence, leaving out the column number 535 * @param valueIn java.util.Date object containing date and time 536 * @return Formatted string 537 */ 538 protected final String formatDateTime (java.util.Date valueIn) 539 { return formatDateTime(valueIn, 1); } 540 /** 541 * Formatter for values involving date and time. 542 * <p>This method can be overridden to control printing of 543 * the values in the CSV file.</p> 544 * @param valueIn java.util.Date object containing date and time 545 * @param column Column number - This will allow different formatting to be used 546 * for each column. This parameter is not used unless the method is 547 * overridden. 548 * @return Formatted string 549 */ 550 protected String formatDateTime (java.util.Date valueIn, int column) 551 { 552 java.text.SimpleDateFormat format = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm"); 553 return format.format(valueIn); 554 } 555}