ixql


1   /*
2    * Copyright (c) 2004 David Flanagan.  All rights reserved.
3    * This code is from the book Java Examples in a Nutshell, 3nd Edition.
4    * It is provided AS-IS, WITHOUT ANY WARRANTY either expressed or implied.
5    * You may study, use, and modify it for any non-commercial purpose,
6    * including teaching and use in open-source projects.
7    * You may distribute it non-commercially as long as you retain this notice.
8    * For a commercial use license, or to purchase the book, 
9    * please visit http://www.davidflanagan.com/javaexamples3.
10   */
11  package com.nilostep.xlsql.ui;
12  import java.sql.*;
13  import java.io.*;
14  
15  /**
16   * A general-purpose SQL interpreter program.
17   **/
18  public class ixql {
19      public static void main(String  [] args) {
20          Connection conn = null;  // Our JDBC connection to the database server
21          try {
22              String   driver = null, url = null, user = "", password = "";
23  
24              // Parse all the command-line arguments
25              for(int n = 0; n < args.length; n++) {
26                  if (args[n].equals("-d")) driver = args[++n];
27                  else if (args[n].equals("-u")) user = args[++n];
28                  else if (args[n].equals("-p")) password = args[++n];
29                  else if (url == null) url = args[n];
30                  else throw new IllegalArgumentException  ("Unknown argument.");
31              }
32  
33              // The only required argument is the database URL.
34              if (url == null) 
35                  throw new IllegalArgumentException  ("No database specified");
36  
37              // If the user specified the classname for the DB driver, load
38              // that class dynamically.  This gives the driver the opportunity
39              // to register itself with the DriverManager.
40              if (driver != null) Class.forName(driver);
41  
42              // Now open a connection the specified database, using the
43              // user-specified username and password, if any.  The driver
44              // manager will try all of the DB drivers it knows about to try to
45              // parse the URL and connect to the DB server.
46              conn = DriverManager.getConnection(url, user, password);
47  
48              // Now create the statement object we'll use to talk to the DB
49              Statement s = conn.createStatement();
50  
51              // Get a stream to read from the console
52              BufferedReader in =
53          new BufferedReader(new InputStreamReader(System.in));
54  
55              // Loop forever, reading the user's queries and executing them
56              while(true) {
57                  System.out.print("sql> ");   // prompt the user
58                  System.out.flush();          // make the prompt appear now.
59                  String   sql = in.readLine();  // get a line of input from user
60  
61                  // Quit when the user types "quit".
62                  if ((sql == null) || sql.equals("quit")) break;
63  
64                  // Ignore blank lines
65                  if (sql.length() == 0) continue;
66                  
67                  // Now, execute the user's line of SQL and display results.
68                  try {
69                      // We don't know if this is a query or some kind of
70                      // update, so we use execute() instead of executeQuery()
71                      // or executeUpdate() If the return value is true, it was
72                      // a query, else an update.
73                      boolean status = s.execute(sql);
74                  
75              // Some complex SQL queries can return more than one set
76              // of results, so loop until there are no more results
77                      do {
78                          if (status) { // it was a query and returns a ResultSet
79                              ResultSet rs = s.getResultSet();   // Get results
80                              printResultsTable(rs, System.out); // Display them
81                          }
82                          else {
83                              // If the SQL command that was executed was some
84                              // kind of update rather than a query, then it
85                              // doesn't return a ResultSet.  Instead, we just
86                              // print the number of rows that were affected.
87                              int numUpdates = s.getUpdateCount();
88                              System.out.println("Ok. " + numUpdates +
89                             " rows affected.");
90                          }
91  
92                          // Now go see if there are even more results, and
93                          // continue the results display loop if there are.
94                          status = s.getMoreResults();
95                      } while(status || s.getUpdateCount() != -1);
96                  }
97                  // If a SQLException is thrown, display an error message.
98                  // Note that SQLExceptions can have a general message and a
99                  // DB-specific message returned by getSQLState()
100                 catch (SQLException e) {
101                     System.err.println("SQLException: " + e.getMessage()+ ":" +
102                        e.getSQLState());
103                 }
104                 // Each time through this loop, check to see if there were any
105                 // warnings.  Note that there can be a whole chain of warnings.
106                 finally { // print out any warnings that occurred
107             SQLWarning w;
108                     for(w=conn.getWarnings(); w != null; w=w.getNextWarning())
109                         System.err.println("WARNING: " + w.getMessage() + 
110                        ":" + w.getSQLState());
111                 }
112             }
113         }
114         // Handle exceptions that occur during argument parsing, database
115         // connection setup, etc.  For SQLExceptions, print the details.
116         catch (Exception   e) {
117             System.err.println(e);
118             if (e instanceof SQLException)
119                 System.err.println("SQL State: " +
120                    ((SQLException)e).getSQLState());
121             System.err.println("Usage: java ExecuteSQL [-d <driver>] " +
122                    "[-u <user>] [-p <password>] <database URL>");
123         }
124 
125         // Be sure to always close the database connection when we exit,
126         // whether we exit because the user types 'quit' or because of an
127         // exception thrown while setting things up.  Closing this connection
128         // also implicitly closes any open statements and result sets
129         // associated with it.
130         finally {
131             try { conn.close(); } catch (Exception   e) {}
132         }
133     }
134     
135     /**
136      * This method attempts to output the contents of a ResultSet in a 
137      * textual table.  It relies on the ResultSetMetaData class, but a fair
138      * bit of the code is simple string manipulation.
139      **/
140     static void printResultsTable(ResultSet rs, OutputStream output) 
141     throws SQLException
142     {
143         // Set up the output stream
144         PrintWriter out = new PrintWriter(output);
145     
146         // Get some "meta data" (column names, etc.) about the results
147         ResultSetMetaData metadata = rs.getMetaData();
148         
149         // Variables to hold important data about the table to be displayed
150         int numcols = metadata.getColumnCount(); // how many columns
151         String  [] labels = new String  [numcols];   // the column labels
152         int[] colwidths = new int[numcols];      // the width of each
153         int[] colpos = new int[numcols];         // start position of each
154         int linewidth;                           // total width of table
155         
156         // Figure out how wide the columns are, where each one begins, 
157         // how wide each row of the table will be, etc.
158         linewidth = 1; // for the initial '|'.
159         for(int i = 0; i < numcols; i++) {             // for each column
160             colpos[i] = linewidth;                     // save its position
161             labels[i] = metadata.getColumnLabel(i+1);  // get its label 
162             // Get the column width.  If the db doesn't report one, guess
163             // 30 characters.  Then check the length of the label, and use
164             // it if it is larger than the column width
165             int size = metadata.getColumnDisplaySize(i+1);
166             if (size == -1) size = 30;  // Some drivers return -1...
167         if (size > 500) size = 30;  // Don't allow unreasonable sizes
168             int labelsize = labels[i].length();
169             if (labelsize > size) size = labelsize;   
170             colwidths[i] = size + 1;              // save the column the size  
171             linewidth += colwidths[i] + 2;        // increment total size
172         }
173         
174         // Create a horizontal divider line we use in the table.
175         // Also create a blank line that is the initial value of each 
176         // line of the table
177         StringBuffer   divider = new StringBuffer  (linewidth);
178         StringBuffer   blankline = new StringBuffer  (linewidth);
179         for(int i = 0; i < linewidth; i++) { 
180             divider.insert(i, '-');
181             blankline.insert(i, " ");
182         }
183         // Put special marks in the divider line at the column positions
184         for(int i=0; i<numcols; i++) divider.setCharAt(colpos[i]-1,'+');
185         divider.setCharAt(linewidth-1, '+');
186         
187         // Begin the table output with a divider line
188         out.println(divider);
189         
190         // The next line of the table contains the column labels.
191         // Begin with a blank line, and put the column names and column
192         // divider characters "|" into it.  overwrite() is defined below.
193         StringBuffer   line = new StringBuffer  (blankline.toString());
194         line.setCharAt(0, '|');
195         for(int i = 0; i < numcols; i++) {
196             int pos = colpos[i] + 1 + (colwidths[i]-labels[i].length())/2;
197             overwrite(line, pos, labels[i]);
198             overwrite(line, colpos[i] + colwidths[i], " |");
199         }
200         
201         // Then output the line of column labels and another divider
202         out.println(line);
203         out.println(divider);
204         
205         // Now, output the table data.  Loop through the ResultSet, using
206         // the next() method to get the rows one at a time. Obtain the 
207         // value of each column with getObject(), and output it, much as 
208         // we did for the column labels above.
209         while(rs.next()) {
210             line = new StringBuffer  (blankline.toString());
211             line.setCharAt(0, '|');
212             for(int i = 0; i < numcols; i++) {
213                 Object   value = rs.getObject(i+1);
214         if (value != null) 
215             overwrite(line, colpos[i] + 1, value.toString().trim());
216                 overwrite(line, colpos[i] + colwidths[i], " |");
217             }
218             out.println(line);
219         }
220         
221         // Finally, end the table with one last divider line.
222         out.println(divider);
223         out.flush();
224     }
225     
226     /** This utility method is used when printing the table of results */
227     static void overwrite(StringBuffer   b, int pos, String   s) {
228         int slen = s.length();                 // string length
229         int blen = b.length();                 // buffer length
230         if (pos+slen > blen) slen = blen-pos;  // does it fit?
231         for(int i = 0; i < slen; i++)          // copy string into buffer
232             b.setCharAt(pos+i, s.charAt(i));
233     }
234 }
235
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags