00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025 package org.objectweb.cjdbc.console.text.module;
00026
00027 import java.io.BufferedReader;
00028 import java.io.FileReader;
00029 import java.io.IOException;
00030 import java.sql.CallableStatement;
00031 import java.sql.Connection;
00032 import java.sql.DatabaseMetaData;
00033 import java.sql.DriverManager;
00034 import java.sql.PreparedStatement;
00035 import java.sql.ResultSet;
00036 import java.sql.ResultSetMetaData;
00037 import java.sql.SQLException;
00038
00039 import org.objectweb.cjdbc.common.i18n.ConsoleTranslate;
00040 import org.objectweb.cjdbc.common.util.Constants;
00041 import org.objectweb.cjdbc.console.text.Console;
00042 import org.objectweb.cjdbc.console.text.ConsoleException;
00043
00044
00045
00046
00047
00048
00049
00050
00051
00052 public class VirtualDatabaseConsole extends AbstractConsoleModule
00053 {
00054
00055
00056 private static final int MAX_COLUMN_DISPLAY_WIDTH = 25;
00057
00058 private Connection connection = null;
00059
00060
00061 private int timeout = 60;
00062
00063 private int fetchsize = 0;
00064
00065 private int maxrows = 0;
00066
00067 private String login, url;
00068
00069
00070
00071
00072
00073
00074
00075 public VirtualDatabaseConsole(Console console)
00076 {
00077 super(console);
00078 try
00079 {
00080 Class.forName("org.objectweb.cjdbc.driver.Driver");
00081 }
00082 catch (Exception e)
00083 {
00084 console.printError(ConsoleTranslate
00085 .get("sql.login.cannot.load.driver", e), e);
00086 Runtime.getRuntime().exit(1);
00087 }
00088 console.println(ConsoleTranslate.get("sql.login.loaded.driver",
00089 Constants.VERSION));
00090 }
00091
00092
00093
00094
00095
00096
00097
00098
00099
00100
00101 public Connection getConnection(String url, String login, String password)
00102 throws ConsoleException
00103 {
00104 try
00105 {
00106 return DriverManager.getConnection(url, login, password);
00107 }
00108 catch (Exception e)
00109 {
00110 throw new ConsoleException(ConsoleTranslate.get("sql.login.connection.failed",
00111 new String[]{url, e.getMessage()}));
00112 }
00113 }
00114
00115 private void displaySeparatorLine(int columnCount, ResultSetMetaData meta)
00116 throws SQLException
00117 {
00118
00119 console.print("+");
00120 for (int i = 1; i <= columnCount; i++)
00121 {
00122 int size = meta.getColumnDisplaySize(i);
00123 if (size > MAX_COLUMN_DISPLAY_WIDTH)
00124 size = MAX_COLUMN_DISPLAY_WIDTH;
00125 console.print("-");
00126 for (int j = 0; j < size; j++)
00127 console.print("-");
00128 console.print("-+");
00129 }
00130 console.println();
00131 }
00132
00133 private void displayPad(String text, int size)
00134 {
00135 if (size > MAX_COLUMN_DISPLAY_WIDTH)
00136 size = MAX_COLUMN_DISPLAY_WIDTH;
00137 if (size < text.length())
00138 {
00139 console.print(text.substring(0, size - 1) + "~");
00140 return;
00141 }
00142 StringBuffer toPad = new StringBuffer(size);
00143 toPad.insert(0, text);
00144 while (toPad.length() < size)
00145 toPad.append(' ');
00146 console.print(toPad.toString());
00147 }
00148
00149
00150
00151
00152
00153
00154
00155 public void displayResultSet(ResultSet rs) throws SQLException
00156 {
00157
00158 ResultSetMetaData meta = rs.getMetaData();
00159 int columnCount = meta.getColumnCount();
00160
00161 displaySeparatorLine(columnCount, meta);
00162
00163
00164 console.print("|");
00165 for (int i = 1; i <= columnCount; i++)
00166 {
00167 console.print(" ");
00168
00169 int size = meta.getColumnDisplaySize(i);
00170 String columnName = meta.getColumnName(i);
00171 if (size <= 0)
00172 {
00173 if (columnName != null)
00174 size = columnName.length();
00175 else
00176 size = 0;
00177 }
00178 displayPad(columnName, size);
00179 console.print(" |");
00180 }
00181 console.println();
00182
00183 displaySeparatorLine(columnCount, meta);
00184
00185
00186 Object object;
00187 int line = 0;
00188 while (rs.next())
00189 {
00190 console.print("|");
00191 for (int i = 1; i <= columnCount; i++)
00192 {
00193 console.print(" ");
00194 object = rs.getObject(i);
00195 String value = (object != null) ? rs.getObject(i).toString() : "";
00196
00197 int size = meta.getColumnDisplaySize(i);
00198 if (size <= 0)
00199 {
00200 if (value != null)
00201 size = value.length();
00202 else
00203 size = 0;
00204 }
00205 displayPad(value, size);
00206 console.print(" |");
00207 }
00208 console.println("");
00209 line++;
00210 if (fetchsize != 0)
00211 {
00212 if (line % fetchsize == 0)
00213 {
00214 try
00215 {
00216 console.readLine(ConsoleTranslate.get("sql.display.next.rows",
00217 new Integer[]{new Integer(fetchsize), new Integer(line)}));
00218 }
00219 catch (ConsoleException ignore)
00220 {
00221 }
00222 }
00223 }
00224 }
00225
00226 displaySeparatorLine(columnCount, meta);
00227 }
00228
00229
00230
00231
00232
00233
00234
00235
00236 public synchronized void callStoredProcedure(String proc,
00237 boolean displayResult)
00238 {
00239 CallableStatement cs = null;
00240 try
00241 {
00242 cs = connection.prepareCall(proc);
00243 cs.setQueryTimeout(timeout);
00244
00245 long start = System.currentTimeMillis();
00246 long end;
00247 ResultSet rs = cs.executeQuery();
00248 end = System.currentTimeMillis();
00249 if (displayResult)
00250 displayResultSet(rs);
00251 console.println(ConsoleTranslate.get("sql.display.query.time",
00252 new Long[]{new Long((end - start) / 1000),
00253 new Long((end - start) % 1000)}));
00254 }
00255 catch (Exception e)
00256 {
00257 console.printError(ConsoleTranslate.get(
00258 "sql.command.storeProcedure.error", e), e);
00259 }
00260 finally
00261 {
00262 try
00263 {
00264 cs.close();
00265 }
00266 catch (Exception ignore)
00267 {
00268 }
00269 }
00270 }
00271
00272
00273
00274
00275
00276
00277
00278
00279 public synchronized void execSQL(String request, boolean displayResult)
00280 {
00281 PreparedStatement stmt = null;
00282 try
00283 {
00284 stmt = connection.prepareStatement(request);
00285 stmt.setQueryTimeout(timeout);
00286 if (fetchsize != 0)
00287 stmt.setFetchSize(fetchsize);
00288 if (maxrows != 0)
00289 stmt.setMaxRows(maxrows);
00290
00291 long start = System.currentTimeMillis();
00292 long end;
00293 if (request.regionMatches(true, 0, "select ", 0, 7))
00294 {
00295 ResultSet rs = stmt.executeQuery();
00296 end = System.currentTimeMillis();
00297 if (displayResult)
00298 displayResultSet(rs);
00299 }
00300 else
00301 {
00302 int result = stmt.executeUpdate();
00303 end = System.currentTimeMillis();
00304 if (displayResult)
00305 console.println(ConsoleTranslate.get("sql.display.affected.rows",
00306 result));
00307 }
00308 console.println(ConsoleTranslate.get("sql.display.query.time",
00309 new Long[]{new Long((end - start) / 1000), new Long((end - start) % 1000)}));
00310 }
00311 catch (Exception e)
00312 {
00313 console.printError(ConsoleTranslate.get("sql.command.sqlquery.error", e),
00314 e);
00315 }
00316 finally
00317 {
00318 try
00319 {
00320 stmt.close();
00321 }
00322 catch (Exception ignore)
00323 {
00324 }
00325 }
00326 }
00327
00328
00329 public void showtables()
00330 {
00331 try
00332 {
00333 DatabaseMetaData dbmd = connection.getMetaData();
00334 ResultSet tableSet = dbmd.getTables(null, null, null, null);
00335 while (!tableSet.isLast())
00336 {
00337 tableSet.next();
00338 console.println(tableSet.getString(tableSet.findColumn("TABLE_NAME")));
00339 }
00340 }
00341 catch (Exception e)
00342 {
00343 console.printError(ConsoleTranslate.get("sql.command.sqlquery.error", e),
00344 e);
00345 }
00346
00347 }
00348
00349
00350
00351
00352
00353
00354 public void load(String fileName)
00355 {
00356 BufferedReader file = null;
00357 try
00358 {
00359 file = new BufferedReader(new FileReader(fileName));
00360 }
00361 catch (Exception e)
00362 {
00363 console.printError(
00364 ConsoleTranslate.get("sql.command.load.file.error", e), e);
00365 return;
00366 }
00367
00368 console.println(ConsoleTranslate.get("sql.command.loading.file", fileName));
00369 try
00370 {
00371 String request;
00372
00373 while ((request = file.readLine()) != null)
00374 {
00375 request = request.trim();
00376 console.println(request);
00377
00378 if (request.equalsIgnoreCase("begin"))
00379 connection.setAutoCommit(false);
00380 else if (request.equalsIgnoreCase("commit"))
00381 connection.commit();
00382 else if (request.equalsIgnoreCase("rollback"))
00383 connection.rollback();
00384 else
00385 {
00386 execSQL(request, false);
00387 }
00388 }
00389 }
00390 catch (Exception e)
00391 {
00392 console.printError(ConsoleTranslate.get("sql.command.load.execute.error",
00393 e), e);
00394 }
00395 finally
00396 {
00397 try
00398 {
00399 file.close();
00400 }
00401 catch (IOException ignore)
00402 {
00403 }
00404 }
00405 }
00406
00407
00408
00409
00410 public void help()
00411 {
00412 console.println(ConsoleTranslate.get("module.commands.available",
00413 getDescriptionString()));
00414 console.println(" begin");
00415 console.println(" " + ConsoleTranslate.get("sql.command.begin"));
00416 console.println(" commit");
00417 console.println(" " + ConsoleTranslate.get("sql.command.commit"));
00418 console.println(" fetchsize x");
00419 console.println(" " + ConsoleTranslate.get("sql.command.fetchsize"));
00420 console.println(" help");
00421 console.println(" " + ConsoleTranslate.get("console.command.help"));
00422 console.println(" load <file>");
00423 console.println(" " + ConsoleTranslate.get("sql.command.load"));
00424 console.println(" maxrows x");
00425 console.println(" " + ConsoleTranslate.get("sql.command.maxrows"));
00426 console.println(" quit");
00427 console.println(" " + ConsoleTranslate.get("sql.command.quit"));
00428 console.println(" rollback");
00429 console.println(" " + ConsoleTranslate.get("sql.command.rollback"));
00430 console.println(" showtables");
00431 console.println(" " + ConsoleTranslate.get("sql.command.showtables"));
00432 console.println(" timeout x");
00433 console.println(" " + ConsoleTranslate.get("sql.command.timeout"));
00434 console.println(" {call proc_name(?,?,...)}");
00435 console.println(" " + ConsoleTranslate.get("sql.command.procedure"));
00436 console.println(ConsoleTranslate.get("sql.command.other"));
00437 }
00438
00439
00440
00441
00442 public void handlePrompt()
00443 {
00444 boolean quit = false;
00445 while (!quit)
00446 {
00447 try
00448 {
00449 String cmd = console.readLine(this.getPromptString());
00450 if (cmd == null)
00451 cmd = "quit";
00452
00453 if (cmd.length() == 0)
00454 continue;
00455
00456 if (cmd.equalsIgnoreCase("begin"))
00457 {
00458 connection.setAutoCommit(false);
00459 console.println(ConsoleTranslate
00460 .get("sql.display.transaction.started"));
00461 }
00462 else if (cmd.equalsIgnoreCase("commit"))
00463 {
00464 connection.commit();
00465 connection.setAutoCommit(true);
00466 }
00467 else if (cmd.equalsIgnoreCase("rollback"))
00468 {
00469 connection.rollback();
00470 connection.setAutoCommit(true);
00471 }
00472 else if (cmd.equalsIgnoreCase("showtables"))
00473 {
00474 showtables();
00475 }
00476 else if (cmd.toLowerCase().startsWith("{call"))
00477 {
00478 callStoredProcedure(cmd, true);
00479 }
00480 else if (cmd.toLowerCase().startsWith("load"))
00481 {
00482 cmd = cmd.substring(4).trim();
00483 load(cmd);
00484 }
00485 else if (cmd.toLowerCase().startsWith("fetchsize"))
00486 {
00487 cmd = cmd.substring(9).trim();
00488 try
00489 {
00490 fetchsize = new Integer(cmd).intValue();
00491 console.println(ConsoleTranslate.get("sql.display.new.fetchsize",
00492 fetchsize));
00493 }
00494 catch (NumberFormatException e)
00495 {
00496 console.printError(ConsoleTranslate.get(
00497 "sql.display.new.fetchsize.failed", e), e);
00498 }
00499 }
00500 else if (cmd.toLowerCase().startsWith("maxrows"))
00501 {
00502 cmd = cmd.substring(7).trim();
00503 try
00504 {
00505 maxrows = new Integer(cmd).intValue();
00506 console.println(ConsoleTranslate.get("sql.display.new.maxrows",
00507 maxrows));
00508 }
00509 catch (NumberFormatException e)
00510 {
00511 console.printError(ConsoleTranslate.get(
00512 "sql.display.new.maxrows.failed", e), e);
00513 }
00514 }
00515 else if (cmd.toLowerCase().startsWith("timeout"))
00516 {
00517 cmd = cmd.substring(7).trim();
00518 try
00519 {
00520 timeout = new Integer(cmd).intValue();
00521 console.println(ConsoleTranslate.get("sql.display.new.timeout",
00522 timeout));
00523 }
00524 catch (NumberFormatException e)
00525 {
00526 console.printError(ConsoleTranslate.get(
00527 "sql.display.new.timeout.failed", e), e);
00528 }
00529 }
00530 else if (cmd.equalsIgnoreCase("help"))
00531 help();
00532 else if (cmd.equalsIgnoreCase("quit"))
00533 {
00534 quit = true;
00535 if (connection != null)
00536 connection.close();
00537 }
00538 else
00539
00540 execSQL(cmd, true);
00541 }
00542 catch (Exception e)
00543 {
00544 console.printError(ConsoleTranslate.get("sql.login.exception", e), e);
00545 if (e instanceof RuntimeException)
00546 {
00547 System.exit(0);
00548 }
00549 }
00550 }
00551 }
00552
00553
00554
00555
00556 public String getDescriptionString()
00557 {
00558 return "SQL Console";
00559 }
00560
00561
00562
00563
00564 public String getPromptString()
00565 {
00566 int ind1 = url.lastIndexOf('?');
00567 int ind2 = url.lastIndexOf(';');
00568 if (ind1 != -1 || ind2 != -1)
00569 {
00570 String prompt;
00571 prompt = (ind1 != -1) ? url.substring(0, ind1) : url;
00572 prompt = (ind2 != -1) ? url.substring(0, ind2) : url;
00573 return prompt + " (" + login + ")";
00574 }
00575 else
00576 return url + " (" + login + ")";
00577 }
00578
00579
00580
00581
00582 protected void loadCommands()
00583 {
00584
00585 }
00586
00587
00588
00589
00590 public void login(String[] params) throws Exception
00591 {
00592 login = null;
00593 url = (params.length > 0 && params[0] != null) ? params[0].trim() : null;
00594 try
00595 {
00596 if ((url == null) || url.trim().equals(""))
00597 {
00598 url = console.readLine(ConsoleTranslate.get("sql.login.prompt.url"));
00599 if (url == null)
00600 return;
00601 }
00602 login = console.readLine(ConsoleTranslate.get("sql.login.prompt.user"));
00603 if (login == null)
00604 return;
00605 String password = console.readPassword(ConsoleTranslate
00606 .get("sql.login.prompt.password"));
00607 if (password == null)
00608 return;
00609
00610 connection = getConnection(url, login, password);
00611 }
00612 catch (Exception e)
00613 {
00614 throw new ConsoleException(ConsoleTranslate.get("sql.login.exception", e));
00615 }
00616 }
00617
00618
00619
00620
00621 public void quit()
00622 {
00623 quit = true;
00624 if (connection != null)
00625 {
00626 try
00627 {
00628 connection.close();
00629 }
00630 catch (Exception e)
00631 {
00632
00633 }
00634 }
00635 }
00636 }