1 /* 2 * Licensed to the Apache Software Foundation (ASF) under one or more 3 * contributor license agreements. See the NOTICE file distributed with 4 * this work for additional information regarding copyright ownership. 5 * The ASF licenses this file to You under the Apache License, Version 2.0 6 * (the "License"); you may not use this file except in compliance with 7 * the License. You may obtain a copy of the License at 8 * 9 * http://www.apache.org/licenses/LICENSE-2.0 10 * 11 * Unless required by applicable law or agreed to in writing, software 12 * distributed under the License is distributed on an "AS IS" BASIS, 13 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 14 * See the License for the specific language governing permissions and 15 * limitations under the License. 16 */ 17 18 package com.klopotek.utils.log; 19 20 import java.sql.*; 21 import java.util.*; 22 import org.apache.log4j.*; 23 import org.apache.log4j.helpers.*; 24 import org.apache.log4j.spi.*; 25 26 /** 27 The JDBCAppender, writes messages into a database 28 29 <p><b>The JDBCAppender is configurable at runtime by setting options in two alternatives : </b></p> 30 <dir> 31 <p><b>1. Use a configuration-file</b></p> 32 <p>Define the options in a file (<A HREF="configfile_example.txt">example</A>) and call a <code>PropertyConfigurator.configure(filename)</code> in your code.</p> 33 <p><b>2. Use the methods of JDBCAppender to do it</b></p> 34 <p>Call <code>JDBCAppender::setOption(JDBCAppender.xxx_OPTION, String value)</code> to do it analogically without a configuration-file (<A HREF="code_example2.java">example</A>)</p> 35 </dir> 36 37 <p>All available options are defined as static String-constants in JDBCAppender named xxx_OPTION.</p> 38 39 <p><b>Here is a description of all available options :</b></p> 40 <dir> 41 <p><b>1. Database-options to connect to the database</b></p> 42 <p>- <b>URL_OPTION</b> : a database url of the form jdbc:subprotocol:subname</p> 43 <p>- <b>USERNAME_OPTION</b> : the database user on whose behalf the connection is being made</p> 44 <p>- <b>PASSWORD_OPTION</b> : the user's password</p> 45 46 <p><b>2. Connector-option to specify your own JDBCConnectionHandler</b></p> 47 <p>- <b>CONNECTOR_OPTION</b> : a classname which is implementing the JDBCConnectionHandler-interface</p> 48 <p>This interface is used to get a customized connection.</p> 49 <p>If in addition the database-options are given, these options will be used as arguments for the JDBCConnectionHandler-interface to get a connection.</p> 50 <p>Else if no database-options are given, the JDBCConnectionHandler-interface is called without them.</p> 51 <p>Else if this option is not defined, the database-options are required to open a connection by the JDBCAppender.</p> 52 53 <p><b>3. SQL-option to specify a static sql-statement which will be performed with every occuring message-event</b></p> 54 <p>- <b>SQL_OPTION</b> : a sql-statement which will be used to write to the database</p> 55 <p>Use the variable <b>@MSG@</b> on a location in the statement, which has to be dynamically replaced by the message-text.</p> 56 <p>If you give this option, the table-option and columns-option will be ignored !</p> 57 58 <p><b>4. Table-option to specify a table contained by the database</b></p> 59 <p>- <b>TABLE_OPTION</b> : the table in which the logging will be done</p> 60 61 <p><b>5. Columns-option to describe the important columns of the table (Not nullable columns are mandatory to describe!)</b></p> 62 <p>- <b>COLUMNS_OPTION</b> : a formatted list of column-descriptions</p> 63 <p>Each column description consists of</p> 64 <dir> 65 <p>- the <b><i>name</i></b> of the column (required)</p> 66 <p>- a <b><i>logtype</i></b> which is a static constant of class LogType (required)</p> 67 <p>- and a <b><i>value</i></b> which depends by the LogType (optional/required, depending by logtype)</p> 68 </dir> 69 <p>Here is a description of the available logtypes of class <b>{@link LogType}</b> and how to handle the <b><i>value</i></b>:</p> 70 <dir> 71 <p>o <b>MSG</b> = a value will be ignored, the column will get the message. (One columns need to be of this type!)</p> 72 <p>o <b>STATIC</b> = the value will be filled into the column with every logged message. (Ensure that the type of value can be casted into the sql-type of the column!)</p> 73 <p>o <b>ID</b> = value must be a classname, which implements the JDBCIDHandler-interface.</p> 74 <p>o <b>TIMESTAMP</b> = a value will be ignored, the column will be filled with a actually timestamp with every logged message.</p> 75 <p>o <b>EMPTY</b> = a value will be ignored, the column will be ignored when writing to the database (Ensure to fill not nullable columns by a database trigger!)</p> 76 </dir> 77 <p>If there are more than one column to describe, the columns must be separated by a Tabulator-delimiter (unicode0008) !</p> 78 <p>The arguments of a column-description must be separated by the delimiter '~' !</p> 79 <p><i>(Example : name1~logtype1~value1 name2~logtype2~value2...)</i></p> 80 81 <p><b>6. Layout-options to define the layout of the messages (optional)</b></p> 82 <p>- <b>_</b> : the layout wont be set by a xxx_OPTION</p> 83 <p>See the configuration-file and code examples below...</p> 84 <p>The default is a layout of the class {@link org.apache.log4j.PatternLayout} with the pattern=%m which representate only the message.</p> 85 86 <p><b>7. Buffer-option to define the size of the message-event-buffer (optional)</b></p> 87 <p>- <b>BUFFER_OPTION</b> : define how many messages will be buffered until they will be updated to the database.</p> 88 <p>The default is buffer=1, which will do a update with every happened message-event.</p> 89 90 <p><b>8. Commit-option to define a auto-commitment</b></p> 91 <p>- <b>COMMIT_OPTION</b> : define whether updated messages should be committed to the database (Y) or not (N).</p> 92 <p>The default is commit=Y.</p> 93 </dir> 94 95 <p><b>The sequence of some options is important :</b></p> 96 <dir> 97 <p><b>1. Connector-option OR/AND Database-options</b></p> 98 <p>Any database connection is required !</p> 99 <p><b>2. (Table-option AND Columns-option) OR SQL-option</b></p> 100 <p>Anything of that is required ! Whether where to write something OR what to write somewhere...;-)</p> 101 <p><b>3. All other options can be set at any time...</b></p> 102 <p>The other options are optional and have a default initialization, which can be customized.</p> 103 </dir> 104 105 <p><b>Here is a <b>configuration-file example</b>, which can be used as argument for the <b>PropertyConfigurator</b> : </b><A HREF="configfile_example.txt"> configfile_example.txt</A></p> 106 107 <p><b>Here is a <b>code-example</b> to configure the JDBCAppender <b>with a configuration-file</b> : </b><A HREF="code_example1.java"> code_example1.java</A></p> 108 109 <p><b>Here is a <b>another code-example</b> to configure the JDBCAppender <b>without a configuration-file</b> : </b><A HREF="code_example2.java"> code_example2.java</A></p> 110 111 112 113 <p><b>Author : </b><A HREF="mailto:t.fenner@klopotek.de">Thomas Fenner</A></p> 114 115 @since 1.0 116 */ 117 public class JDBCAppender extends AppenderSkeleton 118 { 119 /** 120 A database-option to to set a database url of the form jdbc:subprotocol:subname. 121 */ 122 public static final String URL_OPTION = "url"; 123 124 /** 125 A database-option to set the database user on whose behalf the connection is being made. 126 */ 127 public static final String USERNAME_OPTION = "username"; 128 129 /** 130 A database-option to set the user's password. 131 */ 132 public static final String PASSWORD_OPTION = "password"; 133 134 /** 135 A table-option to specify a table contained by the database 136 */ 137 public static final String TABLE_OPTION = "table"; 138 139 /** 140 A connector-option to specify your own JDBCConnectionHandler 141 */ 142 public static final String CONNECTOR_OPTION = "connector"; 143 144 /** 145 A columns-option to describe the important columns of the table 146 */ 147 public static final String COLUMNS_OPTION = "columns"; 148 149 /** 150 A sql-option to specify a static sql-statement which will be performed with every occuring message-event 151 */ 152 public static final String SQL_OPTION = "sql"; 153 154 /** 155 A buffer-option to define the size of the message-event-buffer 156 */ 157 public static final String BUFFER_OPTION = "buffer"; 158 159 /** 160 A commit-option to define a auto-commitment 161 */ 162 public static final String COMMIT_OPTION = "commit"; 163 164 165 //Variables to store the options values setted by setOption() : 166 private String url = null; 167 private String username = null; 168 private String password = null; 169 private String table = null; 170 private String connection_class = null; 171 private String sql = null; 172 private boolean docommit = true; 173 private int buffer_size = 1; 174 private JDBCConnectionHandler connectionHandler = null; 175 176 //This buffer stores message-events. 177 //When the buffer_size is reached, the buffer will be flushed and the messages will updated to the database. 178 private ArrayList buffer = new ArrayList(); 179 180 //Database-connection 181 private Connection con = null; 182 183 //This class encapsulate the logic which is necessary to log into a table 184 private JDBCLogger jlogger = new JDBCLogger(); 185 186 //Flags : 187 //A flag to indicate a established database connection 188 private boolean connected = false; 189 //A flag to indicate configuration status 190 private boolean configured = false; 191 //A flag to indicate that everything is ready to get append()-commands. 192 private boolean ready = false; 193 194 /** 195 If program terminates close the database-connection and flush the buffer 196 */ finalize()197 public void finalize() 198 { 199 close(); 200 super.finalize(); 201 } 202 203 /** 204 Internal method. Returns a array of strings containing the available options which can be set with method setOption() 205 */ getOptionStrings()206 public String[] getOptionStrings() 207 { 208 // The sequence of options in this string is important, because setOption() is called this way ... 209 return new String[]{CONNECTOR_OPTION, URL_OPTION, USERNAME_OPTION, PASSWORD_OPTION, SQL_OPTION, TABLE_OPTION, COLUMNS_OPTION, BUFFER_OPTION, COMMIT_OPTION}; 210 } 211 212 213 /** 214 Sets all necessary options 215 */ setOption(String _option, String _value)216 public void setOption(String _option, String _value) 217 { 218 _option = _option.trim(); 219 _value = _value.trim(); 220 221 if(_option == null || _value == null) return; 222 if(_option.length() == 0 || _value.length() == 0) return; 223 224 _value = _value.trim(); 225 226 if(_option.equals(CONNECTOR_OPTION)) 227 { 228 if(!connected) connection_class = _value; 229 } 230 else if(_option.equals(URL_OPTION)) 231 { 232 if(!connected) url = _value; 233 } 234 else if(_option.equals(USERNAME_OPTION)) 235 { 236 if(!connected) username = _value; 237 } 238 else if(_option.equals(PASSWORD_OPTION)) 239 { 240 if(!connected) password = _value; 241 } 242 else if(_option.equals(SQL_OPTION)) 243 { 244 sql = _value; 245 } 246 else if(_option.equals(TABLE_OPTION)) 247 { 248 if(sql != null) return; 249 table = _value; 250 } 251 else if(_option.equals(COLUMNS_OPTION)) 252 { 253 if(sql != null) return; 254 255 String name = null; 256 int logtype = -1; 257 String value = null; 258 String column = null; 259 String arg = null; 260 int num_args = 0; 261 int num_columns = 0; 262 StringTokenizer st_col; 263 StringTokenizer st_arg; 264 265 //Columns are TAB-separated 266 st_col = new StringTokenizer(_value, " "); 267 268 num_columns = st_col.countTokens(); 269 270 if(num_columns < 1) 271 { 272 errorHandler.error("JDBCAppender::setOption(), Invalid COLUMN_OPTION value : " + _value + " !"); 273 return; 274 } 275 276 for(int i=1; i<=num_columns; i++) 277 { 278 column = st_col.nextToken(); 279 280 //Arguments are ~-separated 281 st_arg = new StringTokenizer(column, "~"); 282 283 num_args = st_arg.countTokens(); 284 285 if(num_args < 2) 286 { 287 errorHandler.error("JDBCAppender::setOption(), Invalid COLUMN_OPTION value : " + _value + " !"); 288 return; 289 } 290 291 for(int j=1; j<=num_args; j++) 292 { 293 arg = st_arg.nextToken(); 294 295 if(j == 1) name = arg; 296 else if(j == 2) 297 { 298 try 299 { 300 logtype = Integer.parseInt(arg); 301 } 302 catch(Exception e) 303 { 304 logtype = LogType.parseLogType(arg); 305 } 306 307 if(!LogType.isLogType(logtype)) 308 { 309 errorHandler.error("JDBCAppender::setOption(), Invalid COLUMN_OPTION LogType : " + arg + " !"); 310 return; 311 } 312 } 313 else if(j == 3) value = arg; 314 } 315 316 if(!setLogType(name, logtype, value)) return; 317 } 318 } 319 else if(_option.equals(BUFFER_OPTION)) 320 { 321 try 322 { 323 buffer_size = Integer.parseInt(_value); 324 } 325 catch(Exception e) 326 { 327 errorHandler.error("JDBCAppender::setOption(), Invalid BUFFER_OPTION value : " + _value + " !"); 328 return; 329 } 330 } 331 else if(_option.equals(COMMIT_OPTION)) 332 { 333 docommit = _value.equals("Y"); 334 } 335 336 if(_option.equals(SQL_OPTION) || _option.equals(TABLE_OPTION)) 337 { 338 if(!configured) configure(); 339 } 340 } 341 342 /** 343 Internal method. Returns true, you may define your own layout... 344 */ requiresLayout()345 public boolean requiresLayout() 346 { 347 return true; 348 } 349 350 351 /** 352 Internal method. Close the database connection & flush the buffer. 353 */ close()354 public void close() 355 { 356 flush_buffer(); 357 if(connection_class == null) 358 { 359 try{con.close();}catch(Exception e){errorHandler.error("JDBCAppender::close(), " + e);} 360 } 361 this.closed = true; 362 } 363 364 365 /** 366 You have to call this function for all provided columns of your log-table ! 367 */ setLogType(String _name, int _logtype, Object _value)368 public boolean setLogType(String _name, int _logtype, Object _value) 369 { 370 if(sql != null) return true; 371 372 if(!configured) 373 { 374 if(!configure()) return false; 375 } 376 377 try 378 { 379 jlogger.setLogType(_name, _logtype, _value); 380 } 381 catch(Exception e) 382 { 383 errorHandler.error("JDBCAppender::setLogType(), " + e); 384 return false; 385 } 386 387 return true; 388 } 389 390 391 /** 392 Internal method. Appends the message to the database table. 393 */ append(LoggingEvent event)394 public void append(LoggingEvent event) 395 { 396 if(!ready) 397 { 398 if(!ready()) 399 { 400 errorHandler.error("JDBCAppender::append(), Not ready to append !"); 401 return; 402 } 403 } 404 405 buffer.add(event); 406 407 if(buffer.size() >= buffer_size) flush_buffer(); 408 } 409 410 411 /** 412 Internal method. Flushes the buffer. 413 */ flush_buffer()414 public void flush_buffer() 415 { 416 try 417 { 418 int size = buffer.size(); 419 420 if(size < 1) return; 421 422 for(int i=0; i<size; i++) 423 { 424 LoggingEvent event = (LoggingEvent)buffer.get(i); 425 426 //Insert message into database 427 jlogger.append(layout.format(event)); 428 } 429 430 buffer.clear(); 431 432 if(docommit) con.commit(); 433 } 434 catch(Exception e) 435 { 436 errorHandler.error("JDBCAppender::flush_buffer(), " + e + " : " + jlogger.getErrorMsg()); 437 try{con.rollback();} catch(Exception ex){} 438 return; 439 } 440 } 441 442 443 /** 444 Internal method. Returns true, when the JDBCAppender is ready to append messages to the database, else false. 445 */ ready()446 public boolean ready() 447 { 448 if(ready) return true; 449 450 if(!configured) return false; 451 452 ready = jlogger.ready(); 453 454 if(!ready){errorHandler.error(jlogger.getErrorMsg());} 455 456 return ready; 457 } 458 459 460 /** 461 Internal method. Connect to the database. 462 */ connect()463 protected void connect() throws Exception 464 { 465 if(connected) return; 466 467 try 468 { 469 if(connection_class == null) 470 { 471 if(url == null) throw new Exception("JDBCAppender::connect(), No URL defined."); 472 473 if(username == null) throw new Exception("JDBCAppender::connect(), No USERNAME defined."); 474 475 if(password == null) throw new Exception("JDBCAppender::connect(), No PASSWORD defined."); 476 477 connectionHandler = new DefaultConnectionHandler(); 478 } 479 else 480 { 481 connectionHandler = (JDBCConnectionHandler)(Class.forName(connection_class).newInstance()); 482 } 483 484 if(url != null && username != null && password != null) 485 { 486 con = connectionHandler.getConnection(url, username, password); 487 } 488 else 489 { 490 con = connectionHandler.getConnection(); 491 } 492 493 if(con.isClosed()) 494 { 495 throw new Exception("JDBCAppender::connect(), JDBCConnectionHandler returns no connected Connection !"); 496 } 497 } 498 catch(Exception e) 499 { 500 throw new Exception("JDBCAppender::connect(), " + e); 501 } 502 503 connected = true; 504 } 505 506 /** 507 Internal method. Configures for appending... 508 */ configure()509 protected boolean configure() 510 { 511 if(configured) return true; 512 513 if(!connected) 514 { 515 if((connection_class == null) && (url == null || username == null || password == null)) 516 { 517 errorHandler.error("JDBCAppender::configure(), Missing database-options or connector-option !"); 518 return false; 519 } 520 521 try 522 { 523 connect(); 524 } 525 catch(Exception e) 526 { 527 connection_class = null; 528 url = null; 529 errorHandler.error("JDBCAppender::configure(), " + e); 530 return false; 531 } 532 } 533 534 if(sql == null && table == null) 535 { 536 errorHandler.error("JDBCAppender::configure(), No SQL_OPTION or TABLE_OPTION given !"); 537 return false; 538 } 539 540 if(!jlogger.isConfigured()) 541 { 542 try 543 { 544 jlogger.setConnection(con); 545 546 if(sql == null) 547 { 548 jlogger.configureTable(table); 549 } 550 else jlogger.configureSQL(sql); 551 } 552 catch(Exception e) 553 { 554 errorHandler.error("JDBCAppender::configure(), " + e); 555 return false; 556 } 557 } 558 559 //Default Message-Layout 560 if(layout == null) 561 { 562 layout = new PatternLayout("%m"); 563 } 564 565 configured = true; 566 567 return true; 568 } 569 } 570 571 /** 572 This is a default JDBCConnectionHandler used by JDBCAppender 573 */ 574 class DefaultConnectionHandler implements JDBCConnectionHandler 575 { 576 Connection con = null; 577 getConnection()578 public Connection getConnection() 579 { 580 return con; 581 } 582 getConnection(String _url, String _username, String _password)583 public Connection getConnection(String _url, String _username, String _password) 584 { 585 try 586 { 587 if(con != null && !con.isClosed()) con.close(); 588 con = DriverManager.getConnection(_url, _username, _password); 589 con.setAutoCommit(false); 590 } 591 catch(Exception e){} 592 593 return con; 594 } 595 } 596 597 598 599 600 601 602