Loader


1   package com.quadcap.sql.tools;
2   
3   /* Copyright 1999 - 2003 Quadcap Software.  All rights reserved.
4    *
5    * This software is distributed under the Quadcap Free Software License.
6    * This software may be used or modified for any purpose, personal or
7    * commercial.  Open Source redistributions are permitted.  Commercial
8    * redistribution of larger works derived from, or works which bundle
9    * this software requires a "Commercial Redistribution License"; see
10   * http://www.quadcap.com/purchase.
11   *
12   * Redistributions qualify as "Open Source" under  one of the following terms:
13   *   
14   *    Redistributions are made at no charge beyond the reasonable cost of
15   *    materials and delivery.
16   *
17   *    Redistributions are accompanied by a copy of the Source Code or by an
18   *    irrevocable offer to provide a copy of the Source Code for up to three
19   *    years at the cost of materials and delivery.  Such redistributions
20   *    must allow further use, modification, and redistribution of the Source
21   *    Code under substantially the same terms as this license.
22   *
23   * Redistributions of source code must retain the copyright notices as they
24   * appear in each source code file, these license terms, and the
25   * disclaimer/limitation of liability set forth as paragraph 6 below.
26   *
27   * Redistributions in binary form must reproduce this Copyright Notice,
28   * these license terms, and the disclaimer/limitation of liability set
29   * forth as paragraph 6 below, in the documentation and/or other materials
30   * provided with the distribution.
31   *
32   * The Software is provided on an "AS IS" basis.  No warranty is
33   * provided that the Software is free of defects, or fit for a
34   * particular purpose.  
35   *
36   * Limitation of Liability. Quadcap Software shall not be liable
37   * for any damages suffered by the Licensee or any third party resulting
38   * from use of the Software.
39   */
40  
41  import java.io.BufferedInputStream  ;
42  import java.io.File  ;
43  import java.io.FileInputStream  ;
44  import java.io.IOException  ;
45  import java.io.InputStream  ;
46  import java.io.PrintWriter  ;
47  
48  import java.sql.Connection  ;
49  import java.sql.DriverManager  ;
50  import java.sql.ResultSet  ;
51  import java.sql.ResultSetMetaData  ;
52  import java.sql.SQLException  ;
53  import java.sql.Statement  ;
54  
55  import com.quadcap.util.ConfigNumber;
56  import com.quadcap.util.ConfigString;
57  import com.quadcap.util.Debug;
58  import com.quadcap.util.Util;
59  
60  /**
61   * A simple SQL loader utility which scans the input file for 
62   * semicolon-delimited SQL statements which are executed using JDBC
63   * against a database connection.  If a statement generates a ResultSet,
64   * the ResultSet is displayed to a PrintWriter.
65   *
66   * @author Stan Bailes
67   */
68  public class Loader {
69      static final ConfigNumber   trace =
70      ConfigNumber.find("trace.sql.tools.Loader", "0");
71  
72      Connection            conn;
73      StringBuffer          buffer = new StringBuffer  ();
74      PrintWriter           writer = null;
75      int             rsLimit = Integer.MAX_VALUE;
76      static final int        COLMAX  = 12;
77  
78      /**
79       * No-argument bean constructor.  We need a connection to do something
80       * useful.
81       */
82      public Loader() {
83      }
84  
85      /**
86       * Construct a new Loader object bound to the specified database
87       * connection
88       *
89       * @param conn the database connection
90       */
91      public Loader(Connection   conn) {
92      this.conn = conn;
93      }
94  
95      /**
96       * Set this loader's database connection
97       *
98       * @param conn the new database connection
99       */
100     public void setConnection(Connection   conn) {
101     this.conn = conn;
102     }
103 
104     /**
105      * Get this loader's database connection
106      *
107      * @return the current database connection
108      */
109     public Connection   getConnection() {
110     return this.conn;
111     }
112     
113     /**
114      * Set the loader's writer object.  Depending on the loader's 
115      * trace level, the loader will write a trace of SQL statement execution
116      * to this writer.
117      *
118      * @param writer the new writer object
119      */
120     public void setWriter(PrintWriter   writer) {
121     this.writer = writer;
122     }
123 
124     /**
125      * Return the loader's writer.
126      *
127      * @return the current writer.
128      */
129     public PrintWriter   getWriter() {
130     return writer;
131     }
132 
133     final void print(String   s) {
134     if (trace.intValue() > 1) {
135         buffer.append(s);
136     }
137     if (writer != null) writer.print(s);
138     }
139 
140     final void println(String   s) {
141     if (trace.intValue() > 1) {
142         buffer.append(s);
143         Debug.println(buffer.toString());
144         buffer.setLength(0);
145     }
146     if (writer != null) writer.println(s);
147     }
148 
149     final void print(Throwable   t) {
150     if (trace.intValue() > 1) Debug.print(t);
151         if (writer != null) {
152             t.printStackTrace(writer);
153         }
154     if (t instanceof SQLException  ) {
155         SQLException   e = ((SQLException  )t).getNextException();
156         if (e != null) {
157         print(e);
158         }
159     }
160     }
161 
162     static final String   pad(int wid, String   s) {
163     StringBuffer   sb = new StringBuffer  ();
164     if (s.length() > wid) {
165         sb.append(s.substring(0, wid));
166     } else {
167         sb.append(s);
168     }
169     while (sb.length() < wid) sb.append(' ');
170     return sb.toString();
171     }
172 
173     final void showResultSet(ResultSet   rs) throws SQLException   {
174     showResultSet(rs, Integer.MAX_VALUE);
175     }
176 
177     final void showResultSet(ResultSet   rs, int lim) throws SQLException   {
178     ResultSetMetaData   rmeta = rs.getMetaData();
179     String   delim = "";
180     for (int i = 1; i <= rmeta.getColumnCount(); i++) {
181         int wid = Math.max(rmeta.getColumnDisplaySize(i),
182                                rmeta.getColumnLabel(i).length());
183         if (wid > COLMAX) wid = COLMAX;
184         print(delim);
185         delim = " ";
186         print(pad(wid, rmeta.getColumnName(i)));
187     }
188     println("");
189     while (lim-- > 0 && rs.next()) {
190         delim = "";
191         for (int i = 1; i <= rmeta.getColumnCount(); i++) {
192                 int wid = Math.max(rmeta.getColumnDisplaySize(i),
193                                    rmeta.getColumnLabel(i).length());
194         if (wid > COLMAX) wid = COLMAX;
195         print(delim);
196         delim = " ";
197         Object   obj = rs.getObject(i);
198         if (obj == null) obj = "<null>";
199         if (obj instanceof byte[]) {
200             obj = Util.hexBytes((byte[])obj);
201         }
202         print(pad(wid, obj.toString()));
203         }
204         println("");
205     }
206     }
207 
208     public static String   getLine(InputStream   is) throws IOException   {
209     StringBuffer   sb = new StringBuffer  ();
210     int ch;
211     int state = 0;
212     while ((ch = is.read()) > 0) {
213         char c = (char)ch;
214         switch (state) {
215         case 0:
216         if (c == '-') {
217             state = 1;
218         } else if (c == ';') {
219             if (sb.length() > 0) {
220             return sb.toString();   // Found ';', we're outta here
221             }
222         } else if (Character.isWhitespace(c) && sb.length() == 0) {
223         } else {
224             sb.append(c);
225         }
226         break;
227         case 1:
228         if (c == '-') {
229             state = 2;
230         } else {
231             sb.append('-');
232             sb.append(c);
233             state = 0;
234         }
235         break;
236         case 2:
237         if (c == '\r') state = 3;
238         if (c == '\n') state = 0;
239         break;
240         case 3:
241         if (c == '\n') state = 0;
242         break;
243         }
244     }
245         if (sb.length() > 0) {
246             return sb.toString();               // 'add' missing trailing ';'
247         }
248     return null;
249     }
250 
251     /**
252      * Execute a single SQL statement.  There are several statements that
253      * are not passed directly to the JDBC driver, but instead are executed
254      * by the loader:
255      * 
256      * <p><table border=1>
257      * <tr><th align=left>BEGINTRANSACTION</th>
258      *     <td>Upon seeing this, the loader performs a
259      *         <code>Connection.setAutoCommit(false)</code> call on the
260      *         current database connection.</td></tr>
261      * <tr><th align=left>ENDTRANSACTION</th>
262      *     <td>The loader performs:
263      *     <pre><code>
264      *          Connection.commit()
265      *          Connection.setAutocommit(true);
266      *     </code></pre></td></tr>
267      * <tr><th align=left>LIMITK</th>
268      *     <td>The loader performs:
269      *     <pre><code>
270      *          Connection.rollback()
271      *          Connection.setAutocommit(true);
272      *     </code></pre></td></tr>
273      * </table></p>
274      *
275      * @param sql the SQL statement to execute.
276      */
277     public void execute(String   sql) {
278     try {
279         Statement   stmt = conn.createStatement();
280         try {
281         if (sql.equals("BEGINTRANSACTION")) {
282             conn.setAutoCommit(false);
283         } else if (sql.equals("ENDTRANSACTION")) {
284             conn.commit();
285             conn.setAutoCommit(true);
286         } else if (sql.startsWith("LIMIT")) {
287             rsLimit = Integer.parseInt(sql.substring(5).trim());
288         } else {
289             if (stmt.execute(sql)) {
290             ResultSet   rs = stmt.getResultSet();
291             try {
292                 showResultSet(rs, rsLimit);
293             } finally {
294                 rs.close();
295             }
296             }
297         }
298         } catch (Throwable   t) {
299         print(t);
300         } finally {
301         stmt.close();
302         }
303     } catch (Throwable   t) {
304         print(t);
305     }
306     }
307 
308     public void loadStream(InputStream   in)
309         throws IOException  , SQLException  
310     {
311         Statement   stmt = conn.createStatement();
312         try {
313             String   sql = null;
314             while ((sql = getLine(in)) != null) {
315                 if (trace.intValue() > 0) {
316                     Debug.println(0, "Executing: " + sql);
317                 }
318                 try {
319                     if (sql.equals("BEGINTRANSACTION")) {
320                         conn.setAutoCommit(false);
321                     } else if (sql.equals("ENDTRANSACTION") ||
322                                sql.equals("COMMIT")) {
323                         conn.commit();
324                         conn.setAutoCommit(true);
325                     } else if (sql.equals("ROLLBACK")) {
326                         conn.rollback();
327                         conn.setAutoCommit(true);
328                     } else if (sql.startsWith("LIMIT")) {
329                         rsLimit = Integer.parseInt(sql.substring(5).trim());
330                     } else {
331                         if (stmt.execute(sql)) {
332                             ResultSet   rs = stmt.getResultSet();
333                             showResultSet(rs, rsLimit);
334                         }
335                     }
336                 } catch (Throwable   t) {
337                     print("Exception: " + t.toString());
338                     print("Statement: " + sql);
339                     print(t);
340                 }
341             }
342         } finally {
343             stmt.close();
344         }
345     }
346 
347     /**
348      * The specified file is scanned for semicolon-delimited SQL statements
349      * which are executed one at a time.
350      *
351      * @param filename the name of the input file
352      * @see execute
353      */
354     public void loadFile(String   filename) {
355     try {
356         FileInputStream   fis = new FileInputStream  (filename);
357             BufferedInputStream   bis = new BufferedInputStream  (fis);
358             try {
359                 loadStream(bis);
360             } finally {
361                 fis.close();
362             }
363         } catch (Throwable   t) {
364             print(t);
365         }
366     }
367 }
368
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags