JDBCAppender


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