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