00001
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
00052 public class VirtualDatabaseConsole extends AbstractConsoleModule
00053 {
00054
00056 private static final int MAX_COLUMN_DISPLAY_WIDTH = 25;
00057
00058 private Connection
connection = null;
00059
00061 private int timeout = 60;
00062
00063 private int fetchsize = 0;
00064
00065 private int maxrows = 0;
00066
00067 private String
login,
url;
00068
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
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
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
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
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
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
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
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
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
00556 public String getDescriptionString()
00557 {
00558
return "SQL Console";
00559 }
00560
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
00582 protected void loadCommands()
00583 {
00584
00585 }
00586
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
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 }