001: package com.quadcap.sql.tools;
002:
003: /* Copyright 1999 - 2003 Quadcap Software. All rights reserved.
004: *
005: * This software is distributed under the Quadcap Free Software License.
006: * This software may be used or modified for any purpose, personal or
007: * commercial. Open Source redistributions are permitted. Commercial
008: * redistribution of larger works derived from, or works which bundle
009: * this software requires a "Commercial Redistribution License"; see
010: * http://www.quadcap.com/purchase.
011: *
012: * Redistributions qualify as "Open Source" under one of the following terms:
013: *
014: * Redistributions are made at no charge beyond the reasonable cost of
015: * materials and delivery.
016: *
017: * Redistributions are accompanied by a copy of the Source Code or by an
018: * irrevocable offer to provide a copy of the Source Code for up to three
019: * years at the cost of materials and delivery. Such redistributions
020: * must allow further use, modification, and redistribution of the Source
021: * Code under substantially the same terms as this license.
022: *
023: * Redistributions of source code must retain the copyright notices as they
024: * appear in each source code file, these license terms, and the
025: * disclaimer/limitation of liability set forth as paragraph 6 below.
026: *
027: * Redistributions in binary form must reproduce this Copyright Notice,
028: * these license terms, and the disclaimer/limitation of liability set
029: * forth as paragraph 6 below, in the documentation and/or other materials
030: * provided with the distribution.
031: *
032: * The Software is provided on an "AS IS" basis. No warranty is
033: * provided that the Software is free of defects, or fit for a
034: * particular purpose.
035: *
036: * Limitation of Liability. Quadcap Software shall not be liable
037: * for any damages suffered by the Licensee or any third party resulting
038: * from use of the Software.
039: */
040:
041: import java.io.BufferedInputStream;
042: import java.io.File;
043: import java.io.FileInputStream;
044: import java.io.IOException;
045: import java.io.InputStream;
046: import java.io.PrintWriter;
047:
048: import java.sql.Connection;
049: import java.sql.DriverManager;
050: import java.sql.ResultSet;
051: import java.sql.ResultSetMetaData;
052: import java.sql.SQLException;
053: import java.sql.Statement;
054:
055: import com.quadcap.util.ConfigNumber;
056: import com.quadcap.util.ConfigString;
057: import com.quadcap.util.Debug;
058: import com.quadcap.util.Util;
059:
060: /**
061: * A simple SQL loader utility which scans the input file for
062: * semicolon-delimited SQL statements which are executed using JDBC
063: * against a database connection. If a statement generates a ResultSet,
064: * the ResultSet is displayed to a PrintWriter.
065: *
066: * @author Stan Bailes
067: */
068: public class Loader {
069: static final ConfigNumber trace = ConfigNumber.find(
070: "trace.sql.tools.Loader", "0");
071:
072: Connection conn;
073: StringBuffer buffer = new StringBuffer();
074: PrintWriter writer = null;
075: int rsLimit = Integer.MAX_VALUE;
076: static final int COLMAX = 12;
077:
078: /**
079: * No-argument bean constructor. We need a connection to do something
080: * useful.
081: */
082: public Loader() {
083: }
084:
085: /**
086: * Construct a new Loader object bound to the specified database
087: * connection
088: *
089: * @param conn the database connection
090: */
091: public Loader(Connection conn) {
092: this .conn = conn;
093: }
094:
095: /**
096: * Set this loader's database connection
097: *
098: * @param conn the new database connection
099: */
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)
138: writer.print(s);
139: }
140:
141: final void println(String s) {
142: if (trace.intValue() > 1) {
143: buffer.append(s);
144: Debug.println(buffer.toString());
145: buffer.setLength(0);
146: }
147: if (writer != null)
148: writer.println(s);
149: }
150:
151: final void print(Throwable t) {
152: if (trace.intValue() > 1)
153: Debug.print(t);
154: if (writer != null) {
155: t.printStackTrace(writer);
156: }
157: if (t instanceof SQLException) {
158: SQLException e = ((SQLException) t).getNextException();
159: if (e != null) {
160: print(e);
161: }
162: }
163: }
164:
165: static final String pad(int wid, String s) {
166: StringBuffer sb = new StringBuffer();
167: if (s.length() > wid) {
168: sb.append(s.substring(0, wid));
169: } else {
170: sb.append(s);
171: }
172: while (sb.length() < wid)
173: sb.append(' ');
174: return sb.toString();
175: }
176:
177: final void showResultSet(ResultSet rs) throws SQLException {
178: showResultSet(rs, Integer.MAX_VALUE);
179: }
180:
181: final void showResultSet(ResultSet rs, int lim) throws SQLException {
182: ResultSetMetaData rmeta = rs.getMetaData();
183: String delim = "";
184: for (int i = 1; i <= rmeta.getColumnCount(); i++) {
185: int wid = Math.max(rmeta.getColumnDisplaySize(i), rmeta
186: .getColumnLabel(i).length());
187: if (wid > COLMAX)
188: wid = COLMAX;
189: print(delim);
190: delim = " ";
191: print(pad(wid, rmeta.getColumnName(i)));
192: }
193: println("");
194: while (lim-- > 0 && rs.next()) {
195: delim = "";
196: for (int i = 1; i <= rmeta.getColumnCount(); i++) {
197: int wid = Math.max(rmeta.getColumnDisplaySize(i), rmeta
198: .getColumnLabel(i).length());
199: if (wid > COLMAX)
200: wid = COLMAX;
201: print(delim);
202: delim = " ";
203: Object obj = rs.getObject(i);
204: if (obj == null)
205: obj = "<null>";
206: if (obj instanceof byte[]) {
207: obj = Util.hexBytes((byte[]) obj);
208: }
209: print(pad(wid, obj.toString()));
210: }
211: println("");
212: }
213: }
214:
215: public static String getLine(InputStream is) throws IOException {
216: StringBuffer sb = new StringBuffer();
217: int ch;
218: int state = 0;
219: while ((ch = is.read()) > 0) {
220: char c = (char) ch;
221: switch (state) {
222: case 0:
223: if (c == '-') {
224: state = 1;
225: } else if (c == ';') {
226: if (sb.length() > 0) {
227: return sb.toString(); // Found ';', we're outta here
228: }
229: } else if (Character.isWhitespace(c)
230: && sb.length() == 0) {
231: } else {
232: sb.append(c);
233: }
234: break;
235: case 1:
236: if (c == '-') {
237: state = 2;
238: } else {
239: sb.append('-');
240: sb.append(c);
241: state = 0;
242: }
243: break;
244: case 2:
245: if (c == '\r')
246: state = 3;
247: if (c == '\n')
248: state = 0;
249: break;
250: case 3:
251: if (c == '\n')
252: state = 0;
253: break;
254: }
255: }
256: if (sb.length() > 0) {
257: return sb.toString(); // 'add' missing trailing ';'
258: }
259: return null;
260: }
261:
262: /**
263: * Execute a single SQL statement. There are several statements that
264: * are not passed directly to the JDBC driver, but instead are executed
265: * by the loader:
266: *
267: * <p><table border=1>
268: * <tr><th align=left>BEGINTRANSACTION</th>
269: * <td>Upon seeing this, the loader performs a
270: * <code>Connection.setAutoCommit(false)</code> call on the
271: * current database connection.</td></tr>
272: * <tr><th align=left>ENDTRANSACTION</th>
273: * <td>The loader performs:
274: * <pre><code>
275: * Connection.commit()
276: * Connection.setAutocommit(true);
277: * </code></pre></td></tr>
278: * <tr><th align=left>LIMITK</th>
279: * <td>The loader performs:
280: * <pre><code>
281: * Connection.rollback()
282: * Connection.setAutocommit(true);
283: * </code></pre></td></tr>
284: * </table></p>
285: *
286: * @param sql the SQL statement to execute.
287: */
288: public void execute(String sql) {
289: try {
290: Statement stmt = conn.createStatement();
291: try {
292: if (sql.equals("BEGINTRANSACTION")) {
293: conn.setAutoCommit(false);
294: } else if (sql.equals("ENDTRANSACTION")) {
295: conn.commit();
296: conn.setAutoCommit(true);
297: } else if (sql.startsWith("LIMIT")) {
298: rsLimit = Integer.parseInt(sql.substring(5).trim());
299: } else {
300: if (stmt.execute(sql)) {
301: ResultSet rs = stmt.getResultSet();
302: try {
303: showResultSet(rs, rsLimit);
304: } finally {
305: rs.close();
306: }
307: }
308: }
309: } catch (Throwable t) {
310: print(t);
311: } finally {
312: stmt.close();
313: }
314: } catch (Throwable t) {
315: print(t);
316: }
317: }
318:
319: public void loadStream(InputStream in) throws IOException,
320: SQLException {
321: Statement stmt = conn.createStatement();
322: try {
323: String sql = null;
324: while ((sql = getLine(in)) != null) {
325: if (trace.intValue() > 0) {
326: Debug.println(0, "Executing: " + sql);
327: }
328: try {
329: if (sql.equals("BEGINTRANSACTION")) {
330: conn.setAutoCommit(false);
331: } else if (sql.equals("ENDTRANSACTION")
332: || sql.equals("COMMIT")) {
333: conn.commit();
334: conn.setAutoCommit(true);
335: } else if (sql.equals("ROLLBACK")) {
336: conn.rollback();
337: conn.setAutoCommit(true);
338: } else if (sql.startsWith("LIMIT")) {
339: rsLimit = Integer.parseInt(sql.substring(5)
340: .trim());
341: } else {
342: if (stmt.execute(sql)) {
343: ResultSet rs = stmt.getResultSet();
344: showResultSet(rs, rsLimit);
345: }
346: }
347: } catch (Throwable t) {
348: print("Exception: " + t.toString());
349: print("Statement: " + sql);
350: print(t);
351: }
352: }
353: } finally {
354: stmt.close();
355: }
356: }
357:
358: /**
359: * The specified file is scanned for semicolon-delimited SQL statements
360: * which are executed one at a time.
361: *
362: * @param filename the name of the input file
363: * @see execute
364: */
365: public void loadFile(String filename) {
366: try {
367: FileInputStream fis = new FileInputStream(filename);
368: BufferedInputStream bis = new BufferedInputStream(fis);
369: try {
370: loadStream(bis);
371: } finally {
372: fis.close();
373: }
374: } catch (Throwable t) {
375: print(t);
376: }
377: }
378: }
|