|
/Users/lyon/j4p/src/rdbms/SqlBean.java
|
1 package rdbms;
2
3 import java.sql.*;
4 import java.util.Vector;
5
6 /**
7 * This class provides an implementation some of the methods in the
8 * java.sql package. This init method creates the connection to the
9 * database and retrieves additional database information. The information
10 * is retrieved and contained in the class variables for further
11 * accessibility.
12 *
13 * @author Douglas Lyon
14 * @version 1.00
15 */
16 public class SqlBean {
17
18 private String url = null;
19 private String driver = null;
20 private String userId = null;
21 private String password = null;
22 private Connection c = null;
23 private Statement statement = null;
24 private DatabaseMetaData dbmd = null;
25 private ResultSet rs = null;
26 private String catalogSeparator = null;
27
28 private ResultSet rs_tabletypes = null;
29
30 private boolean isReadOnly = false;
31 private boolean usesLocalFiles = false;
32 private String driverName = null;
33 private String catalogName = null;
34 private String productName = null;
35
36 private String tablename = null;
37 private String currentquery = null;
38
39 private String tableNames[] = null;
40 private String nonMSysTables[] = null;
41
42 /**
43 * Gets whether or not this database stores tables in a local file. *
44 * The value is set using the DatabaseMetaData.usesLocalFiles() method
45 * * contained in the java.sql package. This method is called and this
46 * value is * set in this classes init() method.
47 *
48 * @return true if it does, false if not
49 */
50 public boolean getUsesLocalFiles() {
51 return usesLocalFiles;
52 }
53
54 /**
55 * Gets whether or not this connection to this database is read only.
56 * The value is set using the Connection.isReadOnly() method
57 * contained in the java.sql package. This method is called and this
58 * value is * set in this classes init() method.
59 *
60 * @return true if it is, false if not
61 */
62 public boolean getReadOnly() {
63 return isReadOnly;
64 }
65
66 /**
67 * Gets this Connection's current catalog name. * The value is set
68 * using the Connection.getCatalog() method * contained in the java.sql
69 * package. This method is called and this value is * set in this
70 * classes init() method.
71 *
72 * @return the current catalog name or null
73 */
74 public String getCatalogName() {
75 return catalogName;
76 }
77
78 /**
79 * Gets the name of this JDBC driver.
80 * <p/>
81 * The value is set using the DatabaseMetaData.getDriverName() method *
82 * contained in the java.sql package. This method is called and this
83 * value is * set in this classes init() method.
84 *
85 * @return the JDBC Driver name
86 */
87 public String getDriverName() {
88 return driverName;
89 }
90
91 /**
92 * Gets the name of this database product. * The value is set using the
93 * DatabaseMetaData.getDatabaseProductName() method * contained in the
94 * java.sql package. This method is called and this value is * set in
95 * this classes init() method.
96 *
97 * @return the database product name
98 */
99 public String getProductName() {
100 return productName;
101 }
102
103 /**
104 * Gets the ResultSet contained in this instance variable rs. *
105 *
106 * @return this classes ResultSet
107 */
108 public ResultSet getResultSet() {
109 return rs;
110 }
111
112
113 /**
114 * Gets the value of this current table name. * The value is set using
115 * the SqlBean.setTableName() method *
116 *
117 * @return the current table name
118 */
119 public String getTableName() {
120 return tablename;
121 }
122
123 /**
124 * Gets the value of this current SQL.
125 * <p/>
126 * The value is set using the SqlBean.query() method
127 *
128 * @return the current SQL query
129 */
130 public String getCurrentQuery() {
131 return currentquery;
132 }
133
134
135 /**
136 * Gets the table names contained in this current database.
137 * The table
138 * names are placed in a ResultSet using the DatabaseMetaData.getTables()
139 * method.
140 * From the ResultSet, the tables are added to a vector and
141 * then
142 * converted into a String array. This method can be used at
143 * anytime after the init()
144 * method is called to set the
145 * DataBaseMetaData.
146 *
147 * @return the table names
148 */
149 public String[] getTableNames() {
150 Vector tableVector = new Vector();
151 try {
152 rs = dbmd.getTables(null, null, null, null);
153 rs.getMetaData();
154 while (rs.next())
155 tableVector.addElement(rs.getString("TABLE_NAME"));
156 } catch (SQLException e) {
157 print(e);
158 }
159 int n = tableVector.size();
160 tableNames = new String[n];
161 for (int i = 0; i < n; i++)
162 tableNames[i] = (String) tableVector.elementAt(i);
163 return tableNames;
164 }
165
166
167 /**
168 * Gets the names of all the Non-System Tables in this Database. *
169 * Retrieves all the tables using the getTableNames() method. Then uses
170 * the * getNumberOfMSysTables() to determine the number of
171 * SystemTables contained in the * database and places those names in a
172 * String array. This method can be used at anytime * after the init()
173 * method is called to set the DataBaseMetaData. *
174 *
175 * @return the names of the non-system tables
176 */
177 public String[] getNonMSysTables() {
178
179 String tn[] = getTableNames();
180 int n = tableNames.length - getNumberOfMSysTables();
181 nonMSysTables = new String[n];
182 for (int i = 0; i < n; i++) {
183 nonMSysTables[i] = tn[i + getNumberOfMSysTables()];
184 }
185
186 return nonMSysTables;
187 }
188
189
190 /**
191 * Gets the number of the System Tables in this Database. * Used to
192 * help determine the table names in the getNonMSysTables() method. *
193 * Determine the number of SystemTables contained in the database my
194 * searching for * names starting with MSys.
195 *
196 * @return the number of system tables
197 */
198 private int getNumberOfMSysTables() {
199
200 int k = 0;
201 for (int i = 0; i < tableNames.length; i++)
202 if (tableNames[i].startsWith("MSys")) k++;
203 return k;
204 }
205
206 /**
207 * Gets the table types available in this database connection. The
208 * results are ordered by table type. * The results are ordered by
209 * table type. Typical types are: * <br>"TABLE", "VIEW", "SYSTEM
210 * TABLE", "GLOBAL", "TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
211 * "SYNONYM". *
212 *
213 * @return the current SQL query
214 */
215
216 public ResultSet getTableTypes() {
217 return rs_tabletypes;
218 }
219
220
221 /**
222 * Sets this classes resultset instance variable, rs, based on a
223 * provided SQL query. *
224 *
225 * @param myquery the SQL query
226 */
227 public void setResultSet(String myquery) {
228 rs = query(myquery);
229 }
230
231
232 /**
233 * Sets this classes instance variable, userId, for this database
234 * connection. * This is the database user on whose behalf the
235 * Connection is being made for *
236 *
237 * @param _userId the database UserId
238 */
239 public void setUserId(String _userId) {
240 userId = _userId;
241 }
242
243 /**
244 * Sets this classes instance variable, password, for this database
245 * connection. * The password associated with the database user on
246 * whose behalf the * Connection is being made for.
247 *
248 * @param _password the database Password
249 */
250 public void setPassword(String _password) {
251 password = _password;
252 }
253
254 /**
255 * Sets this classes instance variable, url, for this database url. *
256 * The url is in the form of jdbc:subprotocol:subname *
257 *
258 * @param _url the database url
259 */
260 public void setUrl(String _url) {
261 url = _url;
262 }
263
264 /**
265 * Sets the name of the instance variable, driver, which this class is
266 * loadeding. * This is the string representation of the driver being
267 * loaded to make the connection to the database. *
268 *
269 * @param _driver the driver name
270 */
271 public void setDriver(String _driver) {
272 driver = _driver;
273 }
274
275 /**
276 * Sets this classes instance variable, tablename, for the current
277 * database tablename. *
278 *
279 * @param _tablename the database tablename
280 */
281 public void setTableName(String _tablename) {
282 tablename = _tablename;
283 }
284
285 /**
286 * Constructor.
287 */
288 public SqlBean() {
289 }
290
291 /**
292 * Loads specified driver and initializes the Connection to this
293 * Database. * <p>Loads the driver and Connects to the Database *
294 * <br>Retrieves and sets Database/Connection Information as follows: *
295 * <br>   MetaData of this Database *
296 * <br>   Read Only Property * <br>   Uses
297 * Local Files Property * <br>   Driver Name Used for
298 * the Connection * <br>   Database Product Name *
299 * <br>   Table Types in this Database
300 */
301 public void init() {
302 try {
303
304 Class.forName(driver);
305 c = DriverManager.getConnection(url, userId, password);
306 dbmd = c.getMetaData();
307 catalogName = c.getCatalog();
308 isReadOnly = c.isReadOnly();
309 usesLocalFiles = dbmd.usesLocalFiles();
310 driverName = dbmd.getDriverName();
311 productName = dbmd.getDatabaseProductName();
312 rs_tabletypes = dbmd.getTableTypes();
313 catalogSeparator = dbmd.getCatalogSeparator();
314
315
316 } catch (ClassNotFoundException e) {
317 println(e);
318 } catch (SQLException e) {
319 print(e);
320 }
321 System.out.println("Opened Connection:" + url);
322 }
323
324 /**
325 * Prints information about this database connection. * <p> <br>Prints
326 * the following information: <br>   The name of this
327 * database product * <br>   This Connection's current
328 * catalog name * <br>   Is this connection in read-only
329 * mode * <br>   Does this database store tables in a
330 * local files * <br>   The name of this JDBC driver *
331 * <br>   The SQL keywords of this database *
332 * <br>   The table types of all the tables in this
333 * database * <br>   The names of all the tables in this
334 * database * <br>   The names of all the non-system
335 * tables in this database
336 */
337 public void printDataBaseInfo() {
338 println("*****");
339 println("productName=" + productName);
340 println("*****");
341 println("catalogName=" + catalogName);
342 println("*****");
343 try {
344 println("allProceduresAreCallable=" +
345 dbmd.allProceduresAreCallable());
346 } catch (SQLException e) {
347 e.printStackTrace();
348 }
349 println("is ReadOnly=" + getReadOnly());
350 println("*****");
351 println("usesLocalFiles=" + getUsesLocalFiles());
352 println("*****");
353 println("driverName=" + driverName);
354 println("*****");
355 println("Non SL92 keywords:");
356 println(getKeyWords());
357 println("*****");
358 println("TableTypes:");
359 print(getTableTypes());
360 println("*****");
361 println("TableNames - All:");
362 println(getTableNames());
363 println("*****");
364 println("TableNames - NonSystem:");
365 println(getNonMSysTables());
366
367
368 }
369
370 /**
371 * Prints information about this current ResultSet. <p> <br>Prints
372 * the following information: <br>   The column names
373 * (fields) of this database * <br>   The type name of
374 * the columns (fields) used by this database
375 *
376 * @param _rs the current ResultSet
377 */
378 public void printResultSetInfo(ResultSet _rs) {
379 println("*****");
380 println("Column Names:");
381 println(getTableFields(getResultSetMetaData(_rs)));
382 println("*****");
383 println("Column Types:");
384 println(getColumnTypeNames(getResultSetMetaData(_rs)));
385 println("*****");
386 println("Number of Rows:");
387 println(Integer.toString((getNumberofRows(getRows(_rs)))));
388 println("*****");
389 println("Print The First Row:");
390
391 }
392
393 /**
394 * Closes the connections to this database.
395 */
396 public void close() {
397
398 try {
399
400 rs.close();
401 rs_tabletypes.close();
402 statement.close();
403 c.close();
404
405 System.out.println("closed connection");
406 } catch (SQLException e) {
407 print(e);
408 }
409 }
410
411 /**
412 * Creates a Result Set based on an sql query. *
413 *
414 * @param sql the sql query
415 * @return the ResultSet
416 */
417 public ResultSet query(String sql) {
418
419 try {
420 statement = c.createStatement();
421 currentquery = sql;
422 return statement.executeQuery(sql);
423 } catch (SQLException e) {
424 print(e);
425 }
426 return null;
427 }
428
429 /**
430 * Gets the MetaData for a specified ResultSet. *
431 *
432 * @param _rs the ResultSet
433 * @return the ResultSetMetaData
434 */
435 public ResultSetMetaData getResultSetMetaData(ResultSet _rs) {
436 try {
437 return _rs.getMetaData();
438 } catch (SQLException e) {
439 print(e);
440 }
441 return null;
442 }
443
444 /**
445 * Gets the number of columns in a ResultSet. *
446 *
447 * @param _rsmd the ResultSetMetaData
448 * @return number of Columns (fields)
449 */
450 public int getColumnCount(ResultSetMetaData _rsmd) {
451 try {
452 return _rsmd.getColumnCount();
453 } catch (SQLException e) {
454 print(e);
455 }
456 return 0;
457 }
458
459 /**
460 * Gets the keywords associated with this database. *
461 *
462 * @return the keywords
463 */
464
465 public String getKeyWords() {
466
467 try {
468 return dbmd.getSQLKeywords();
469 } catch (SQLException e) {
470 print(e);
471 }
472 return null;
473 }
474
475 /**
476 * Gets the database types of the columns in a ResultSet. * These are
477 * the type name used by this database. If the column type is a
478 * user-defined type, * then a fully-qualified type name is returned.
479 * *
480 *
481 * @param _rsmd the ResultSetMetaData
482 * @return the column types
483 */
484
485 public String[] getColumnTypeNames(ResultSetMetaData _rsmd) {
486
487 int count = getColumnCount(_rsmd);
488
489 String sa [] = new String[count];
490 try {
491 for (int i = 0; i < sa.length; i++) {
492 sa[i] = _rsmd.getColumnTypeName(i + 1);
493 }
494 } catch (SQLException e) {
495 print(e);
496 }
497 return sa;
498 }
499
500 /**
501 * Converts a row in a ResultSet to a String. *
502 *
503 * @param _rs the ResultSet pointed to a particular row * @return the
504 * contents of the ResultSet
505 */
506 public String[] getRowAsString(ResultSet _rs) {
507
508 int N = getColumnCount(getResultSetMetaData(_rs));
509 String s[] = new String[N];
510 try {
511 for (int i = 0; i < N; i++)
512 s[i] = _rs.getString(i + 1);
513 } catch (SQLException e) {
514 print(e);
515 }
516 return s;
517 }
518
519 /**
520 * Converts a ResultSet to a Vector of Strings. *
521 *
522 * @param _rs the ResultSet
523 * @return the vector containing the ResultSet with each row as a
524 * String
525 */
526
527 public Vector getRows(ResultSet _rs) {
528 Vector v = new Vector();
529 while (nextRow(_rs))
530 v.addElement(getRowAsString(_rs));
531 return v;
532 }
533
534 /**
535 * Returns the size of a vector. Used with the getRows() method that
536 * converts a ResultSet to a vector. *
537 *
538 * @param v the Vector
539 * @return the number of rows
540 */
541 public int getNumberofRows(Vector v) {
542 return v.size();
543 }
544
545 /**
546 * Moves to the top of this RecordSet.
547 *
548 * @param _rs the ResultSet
549 */
550 public void moveToTop(ResultSet _rs) {
551 try {
552 //_rs.beforeFirst();
553 throw new SQLException("Not implemented until jdk 1.2");
554 } catch (SQLException e) {
555 print(e);
556 }
557 }
558
559 /**
560 * Prints the contents of a provided ResultSet.
561 *
562 * @param _rs the ResultSet
563 */
564 public void print(ResultSet _rs) {
565 String cn[] = getTableFields(getResultSetMetaData(_rs));
566 println(cn);
567 for (boolean more = false; more; more = nextRow(_rs))
568 println(getRowAsString(_rs));
569 }
570
571 /**
572 * Prints the contents of a provided Object. * Uses the toString()
573 * method for the provided object.
574 *
575 * @param o the Object
576 */
577
578 public void println(Object o) {
579
580 System.out.println(o);
581 }
582
583 /**
584 * Prints the contents of a String array.
585 *
586 * @param s an array of string to be printed
587 */
588
589 public void println(String s[]) {
590
591 for (int i = 0; i < s.length; i++)
592 System.out.print(s[i] + '\t');
593 System.out.println();
594
595 }
596
597 /**
598 * Prints messages about this SQL Exception. *
599 *
600 * @param ex the exception
601 */
602
603 private void print(SQLException ex) {
604 println("\n*** SQLException caught ***\n");
605 while (ex != null) {
606 println("SQLState: " + ex.getSQLState());
607 println("Message: " + ex.getMessage());
608 println("Vendor: " + ex.getErrorCode());
609 ex = ex.getNextException();
610 println("");
611 }
612 ex.printStackTrace();
613 }
614
615
616 /**
617 * Moves to the next row of a provided ResultSet. *
618 *
619 * @param _rs the ResultSet
620 */
621 public boolean nextRow(ResultSet _rs) {
622 try {
623 return _rs.next();
624 } catch (SQLException e) {
625 return false;
626 }
627 }
628
629 /**
630 * Gets the names of the columns (fields) in a provided ResultSet. *
631 *
632 * @param _rsmd the ResultSetMetaData
633 * @return the table names
634 */
635 public String[] getTableFields(ResultSetMetaData _rsmd) {
636
637 String s [] = new String[getColumnCount(_rsmd)];
638 try {
639 for (int i = 1; i <= s.length; i++)
640 s[i - 1] = _rsmd.getColumnLabel(i);
641 } catch (SQLException e) {
642 print(e);
643 }
644 return s;
645 }
646
647 /**
648 * Gets this DataBaseMetaData object.
649 *
650 * @return this DataBaseMetaData
651 */
652
653 public DatabaseMetaData getDatabaseMetaData() {
654
655 return dbmd;
656 }
657
658 /**
659 * Process an SQL INSERT, DELETE or UPDATE statement string. *
660 * Additionally, this could be an SQL statement that returns nothing
661 * such as SQL DDL statments. *
662 *
663 * @param sql the SQL INSERT, UPDATE or DELETE statement or an SQL
664 * statement that returns nothing
665 */
666
667 public void modifyDatabase(String sql) {
668 try {
669 Statement s = c.createStatement();
670 s.executeUpdate(sql);
671 println("UPDATE SUCCESSFUL");
672 println("SQL Statment= " + sql);
673 } catch (SQLException e) {
674 println("UPDATE NOT SUCCESSFUL");
675 println("SQL Statment= " + sql);
676 print(e);
677 }
678 }
679
680 /**
681 * Main entry point for application.
682 *
683 * @param args the command line arguments
684 */
685
686 public static void main(String args[]) {
687 testSqlBean();
688 }
689
690 private static void testSqlBean() {
691 SqlBean sb = new SqlBean();
692 sb.setUrl("jdbc:odbc:addresses");
693 sb.setDriver("sun.jdbc.odbc.JdbcOdbcDriver");
694 sb.setUserId("");
695 sb.setPassword("");
696 sb.println("Calling Init Method");
697 sb.init();
698 sb.println("****************");
699 sb.println("Calling printDataBaseInfo Method");
700 sb.printDataBaseInfo();
701 sb.setTableName("mailing");
702 sb.println("****************");
703 sb.println("Calling printResultSetInfo Method for Table Name: "
704 + sb.getTableName());
705 sb.printResultSetInfo(
706 sb.query("SELECT * FROM " + sb.getTableName()));
707
708 sb.println("****************");
709 sb.println("Calling modifyDatabase Method for Table Name: " +
710 sb.getTableName());
711 for (int i = 0; i < 100; i++) {
712 sb.modifyDatabase("INSERT INTO "
713 +
714 sb.getTableName()
715 +
716 "(FirstName,LastName) VALUES "
717 +
718 "('vegg#" +
719 i +
720 "','Fresh Produce');");
721 }
722 sb.println("****************");
723 sb.println("Calling Method to Print the Result Set for Table Name: " +
724 sb.getTableName());
725 //sb.print(sb.query("SELECT CategoryID,CategoryName, Description FROM " + sb.getTableName()));
726 //sb.setTableName("Employees");
727 //sb.println("****************");
728 //sb.println("Calling Method to Print the Result Set for Table Name: " + sb.getTableName());
729 //sb.print(sb.query("SELECT EmployeeID, LastName, FirstName FROM " + sb.getTableName()));
730 //sb.close();
731 }
732
733 }