KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > mckoi > tools > JDBCScriptTool


1 /**
2  * com.mckoi.tools.JDBCScriptTool 22 Sep 2000
3  *
4  * Mckoi SQL Database ( http://www.mckoi.com/database )
5  * Copyright (C) 2000, 2001, 2002 Diehl and Associates, Inc.
6  *
7  * This program is free software; you can redistribute it and/or
8  * modify it under the terms of the GNU General Public License
9  * Version 2 as published by the Free Software Foundation.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License Version 2 for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * Version 2 along with this program; if not, write to the Free Software
18  * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
19  *
20  * Change Log:
21  *
22  *
23  */
24
25 package com.mckoi.tools;
26
27 import com.mckoi.util.CommandLine;
28 import com.mckoi.util.ResultOutputUtil;
29 import java.sql.*;
30 import java.io.*;
31 import java.util.Vector JavaDoc;
32 import java.util.Hashtable JavaDoc;
33 import java.util.Enumeration JavaDoc;
34
35 /**
36  * A tool that reads an input SQL script and output information for the
37  * result either to an output file or through System.out. This is a useful
38  * command line tool that can be used for regression testing and database
39  * diagnostics (as well as for basic SQL scripting needs).
40  * <p>
41  * This class is able to output result sets in textual form.
42  *
43  * @author Tobias Downer
44  */
45
46 public class JDBCScriptTool {
47
48   /**
49    * The Reader we are reading the script commands from.
50    */
51   private Reader in;
52
53   /**
54    * The Writer we are outputing the script results to.
55    */
56   private PrintWriter out;
57
58   /**
59    * Constructs the tool.
60    */
61   public JDBCScriptTool(Reader input, PrintWriter output) {
62     this.in = input;
63     this.out = output;
64   }
65
66   /**
67    * Fetches the next query from the input. Returns null if no more queries
68    * available.
69    */
70   private String JavaDoc nextQuery() throws IOException {
71     StringBuffer JavaDoc query = new StringBuffer JavaDoc();
72     int c = in.read();
73     boolean command = false;
74     while (c != -1) {
75
76       if (command == true ||
77           (c != ' ' && c != '\n' && c != '\r' && c != '\t')) {
78          command = true;
79       }
80
81       query.append((char) c);
82       if (c == ';') {
83         return new String JavaDoc(query);
84       }
85       c = in.read();
86     }
87     if (command == false) {
88       return null;
89     }
90     return new String JavaDoc(query);
91   }
92
93   /**
94    * Evaluates the input script and outputs the result to the output stream.
95    * Returns the list of Connections established.
96    */
97   public Connection[] evaluate(Connection connection, String JavaDoc url,
98                 String JavaDoc username, String JavaDoc password) throws IOException {
99     // Read in the query. The query is everything up to the ';' character
100 // which denotes the end of an SQL command.
101 // ISSUE: It currently does not recognise ';' if used inside a string...
102
103     Statement statement;
104     try {
105       statement = connection.createStatement();
106     }
107     catch (SQLException e) {
108       out.println("SQL Error creating statement: " + e.getMessage());
109       return new Connection[] { connection };
110     }
111
112     // A hash table of connections.
113 Hashtable JavaDoc connections = new Hashtable JavaDoc();
114     connections.put("default", connection);
115
116     String JavaDoc query = nextQuery();
117     while (query != null) {
118
119       try {
120         // Check it's not an internal command.
121 String JavaDoc command =
122                  query.substring(0, query.length() - 1).trim().toLowerCase();
123         if (command.startsWith("switch to connection ")) {
124           String JavaDoc connection_name = command.substring(21);
125           Connection c = (Connection) connections.get(connection_name);
126           if (c == null) {
127             c = DriverManager.getConnection(url, username, password);
128             connections.put(connection_name, c);
129             out.println("Established Connection: " + connection_name);
130           }
131           statement = c.createStatement();
132           out.println("Switched to Connection: " + connection_name);
133           out.flush();
134         }
135         else if (command.startsWith("//")) {
136           out.println();
137           out.println(command);
138           out.flush();
139         }
140         else {
141
142           out.println();
143           out.print("> ");
144           out.println(query.trim());
145           ResultSet result_set = statement.executeQuery(query);
146           ResultOutputUtil.formatAsText(result_set, out);
147           out.flush();
148         }
149       }
150       catch (SQLException e) {
151         out.println("SQL Error running query (\n" + query + "\n)\nError: " +
152                     e.getMessage());
153         out.println("Error code: " + e.getErrorCode());
154 // return;
155 }
156       out.flush();
157
158       // Fetch the next query
159 query = nextQuery();
160     }
161
162     Enumeration JavaDoc e = connections.elements();
163     Vector JavaDoc v = new Vector JavaDoc();
164     while (e.hasMoreElements()) {
165       v.addElement(e.nextElement());
166     }
167
168     Connection[] arr = new Connection[v.size()];
169     for (int i = 0; i < arr.length; ++i) {
170       arr[i] = (Connection) v.elementAt(i);
171     }
172     return arr;
173
174   }
175
176
177   // ---------- Application methods ----------
178
179   /**
180    * Prints the syntax to System.out.
181    */
182   private static void printSyntax() {
183     System.out.println(
184        "JDBCScriptTool [-jdbc JDBC_Driver_Class] [-url JDBC_URL] \n" +
185        " -u username -p password \n" +
186        " [-in Input_SQL_File] [-out Output_Result_File] \n" +
187        "\n" +
188        " If -in or -out are not specified then the tool uses System.in \n" +
189        " and System.out respectively.\n");
190   }
191
192   /**
193    * The application start point.
194    */
195   public static void main(String JavaDoc[] args) {
196     CommandLine cl = new CommandLine(args);
197
198     String JavaDoc driver = cl.switchArgument("-jdbc", "com.mckoi.JDBCDriver");
199     String JavaDoc url = cl.switchArgument("-url", ":jdbc:mckoi:");
200     String JavaDoc username = cl.switchArgument("-u");
201     String JavaDoc password = cl.switchArgument("-p");
202     final String JavaDoc input_file = cl.switchArgument("-in");
203     final String JavaDoc output_file = cl.switchArgument("-out");
204
205     if (username == null) {
206       System.out.println("Please provide a username");
207       System.out.println();
208       printSyntax();
209       System.exit(1);
210     }
211     else if (password == null) {
212       System.out.println("Please provide a password");
213       System.out.println();
214       printSyntax();
215       System.exit(1);
216     }
217
218     Reader reader = null;
219     PrintWriter writer = null;
220
221     try {
222       if (input_file == null) {
223         reader = new InputStreamReader(System.in);
224       }
225       else {
226         reader = new BufferedReader(new FileReader(input_file));
227       }
228
229       if (output_file == null) {
230         writer = new PrintWriter(
231                     new BufferedWriter(new OutputStreamWriter(System.out)));
232       }
233       else {
234         writer = new PrintWriter(
235                            new BufferedWriter(new FileWriter(output_file)));
236       }
237
238     }
239     catch (IOException e) {
240       System.err.println("IO Error: " + e.getMessage());
241       printSyntax();
242       System.exit(1);
243     }
244
245     // Create the JDBCScriptTool object
246 JDBCScriptTool tool = new JDBCScriptTool(reader, writer);
247
248     // Establish the connection,
249 Connection connection = null;
250     try {
251       writer.println("Using JDBC Driver: " + driver);
252
253       // Register the driver.
254 Class.forName(driver).newInstance();
255
256       // Make a connection to the server.
257 connection = DriverManager.getConnection(url, username, password);
258
259       writer.println("Connection established to: " + url);
260       writer.flush();
261
262     }
263     catch (ClassNotFoundException JavaDoc e) {
264       writer.println("JDBC Driver not found.");
265       printSyntax();
266       System.exit(1);
267     }
268     catch (Exception JavaDoc e) {
269       e.printStackTrace();
270       printSyntax();
271       System.exit(1);
272     }
273
274     try {
275
276       if (input_file != null) {
277         System.out.println("Script input from: " + input_file);
278       }
279
280       // Evaluate the script...
281 Connection[] conl = tool.evaluate(connection, url, username, password);
282
283       // Close the input/output
284 reader.close();
285       writer.println();
286       writer.println(" --- FINISHED");
287       writer.close();
288
289 // try {
290 // Thread.sleep(4000);
291 // }
292 // catch (InterruptedException e) {
293 // }
294
295       for (int i = 0; i < conl.length; ++i) {
296         conl[i].close();
297       }
298 // connection.close();
299
300       if (output_file != null) {
301         System.out.println("Script output to: " + output_file);
302       }
303
304     }
305     catch (IOException e) {
306       System.err.println("IO Error: " + e.getMessage());
307       e.printStackTrace(System.err);
308       System.exit(1);
309     }
310     catch (SQLException e) {
311       System.err.println("SQL Error: " + e.getMessage());
312       e.printStackTrace(System.err);
313       System.exit(1);
314     }
315
316   }
317
318
319 }
320
Popular Tags