Back to index

wims  3.65+svn20090927
Database.java
Go to the documentation of this file.
00001 /*
00002  * @ (#)Database.java
00003  *
00004  * $Id: Database.java,v 1.7 2002/08/08 05:15:02 huaz Exp $
00005  *
00006  * Created on May 19, 2001, 09:10:28 PM
00007  *
00008  * Tested with Oracle and Hsql
00009  *
00010  *  TODO
00011  *
00012  *  1) Add error handling - DONE
00013  *  2) Add a Save function
00014  *  3) Add to recent tables on menu
00015  *  4) Save connection params in props - DONE (saving fails, Why? Gotta study Config.java)
00016  *  5) Select from a list of tables - DONE
00017  *  6) Save from file to db and from db to file.
00018  *  7) Draw proper icons - DONE (could be better).
00019  *
00020  * This is not the most efficient implementation - just the easy way out.
00021  *
00022  * Currently the SharpTableModel knows how to create itself from a tab
00023  * delimited string so we simply construct one of these from the database
00024  * ResultSet and give it to the SharpTableModel. A better way would be to
00025  * teach SharpTableModel how to create itself from a ResultSet too.
00026  *
00027  */
00028 package SharpTools;
00029 import javax.swing.*;
00030 import java.util.*;
00031 import java.io.*;
00032 import java.awt.*;
00033 import java.awt.event.*;
00034 import java.awt.datatransfer.*;
00035 import java.sql.*;
00036 import javax.swing.*;
00037 
00044 public class Database
00045 {
00046     // these variables correspond to the variables in SharpTools
00047     private SharpTools sharp;
00048     private JTable table;
00049     private SharpTableModel tableModel;
00050     final ImageIcon connectedIcon=new ImageIcon(getClass().getResource("/images/database32.gif"));
00051     //final private static ImageIcon connectedIcon = SharpTools.getImageIcon ("database32.gif");
00052     //    final private static ImageIcon selectTableIcon = SharpTools.getImageIcon ("table32.gif");
00053 
00054     final private static ImageIcon dbErrorIcon = null;
00055 
00061     Database (SharpTools sharp)
00062     {
00063        this.sharp = sharp;
00064        table = sharp.getTable ();
00065        tableModel = sharp.getTableModel ();
00066     }
00067     
00068     public void connectDb ()
00069     {
00070        String dbUsername = null;
00071        String dbPassword = null;
00072        String dbUrl = null;
00073        String dbDriver = null;
00074        String dbTable = null;
00075        String connectName = null;
00076        
00077        boolean lockTable;
00078        boolean verifySave;
00079        boolean newConnection = false;
00080        
00081        StringBuffer textBuf = new StringBuffer ();
00082        
00083        ConnectDialog connectDialog = new ConnectDialog (sharp);
00084        connectDialog.setVisible (true);
00085        if (connectDialog.isCancelled())
00086            return;
00087        
00088        Connection dbConnection = connectDialog.getConnection();
00089        /*
00090          connectName = connectDialog.getConnectName ();
00091          
00092          dbUsername = connectDialog.getDbUsername ();
00093          dbPassword = connectDialog.getDbPassword ();
00094          dbUrl = connectDialog.getDbUrl ();
00095          dbDriver = connectDialog.getDbDriver ();  
00096        */
00097        
00098        try {
00099            DatabaseMetaData dma = dbConnection.getMetaData ();
00100               
00101            SharpOptionPane.showMessageDialog (sharp, "Connected to database " + dma.getDatabaseProductName () + " v" + dma.getDatabaseProductVersion () + "\nusing driver " + dma.getDriverName () + " v" + dma.getDriverVersion (),
00102                                           "Connected", JOptionPane.INFORMATION_MESSAGE, connectedIcon);
00103            
00104            if ((dbTable == null) || dbTable.equals (""))
00105               dbTable = selectDbTable (dma);
00106 
00107            // need to check cancel - huaz
00108            if (dbTable == null)
00109               return;
00110            
00111            Statement stmt = dbConnection.createStatement ();
00112            ResultSet dbResults = stmt.executeQuery ("SELECT * FROM " + dbTable);
00113            
00114            String warnings = checkForWarning (dbConnection.getWarnings ());
00115            if ((warnings!=null) && !warnings.equals (""))
00116               SharpOptionPane.showMessageDialog (sharp, warnings,
00117                                              "Warning", JOptionPane.INFORMATION_MESSAGE, connectedIcon);
00118            
00119            // metadata can supply information about the schema
00120            ResultSetMetaData rsmd = dbResults.getMetaData ();
00121            int numCols = rsmd.getColumnCount ();
00122            
00123            // first print header labels from meta-data
00124            for (int i=1; i<=numCols; i++) {
00125               if (i != 1) textBuf.append ("\t");
00126               textBuf.append (rsmd.getColumnLabel (i));
00127            }
00128            textBuf.append ("\n");
00129            
00130            while (dbResults.next ()) {
00131               // for one row
00132               for (int j=1; j<=numCols; j++) {
00133                   if (j != 1) textBuf.append ("\t");
00134                   textBuf.append (dbResults.getString (j));
00135               }
00136               textBuf.append ("\n");
00137            }
00138            
00139            String text = textBuf.toString ();
00140            // create new table model
00141            CellPoint size = SharpTableModel.getSize (text);
00142            //      System.out.println (size);
00143            sharp.newTableModel (size.getRow (), size.getCol ());
00144            tableModel = sharp.getTableModel ();
00145            tableModel.fromString (text, 0, 0, new CellRange (1, size.getRow (), 1, size.getCol ()));
00146            
00147            // tableModel.setModified (false);
00148            // set new title for spreadsheet
00149            sharp.setTitle (dbTable + " - Sharp Tools Spreadsheet");
00150            
00151            // update recent files
00152            //addRecentFile (dbTable);         
00153            
00154            dbResults.close ();
00155            stmt.close ();
00156        }
00157        catch (SQLException e) {
00158            SharpOptionPane.showMessageDialog (sharp, "Unable to get data from the database.\n" + e.toString (),
00159                                           "ERROR", JOptionPane.INFORMATION_MESSAGE, dbErrorIcon);
00160            return;
00161        }
00162     }
00163 
00164     /*
00165      * check if the database server has anything to say
00166      */
00167     private String checkForWarning (SQLWarning warn)
00168        throws SQLException {
00169        StringBuffer textBuf = new StringBuffer ();
00170        
00171        if (warn != null) {
00172            textBuf.append ("Warning:\n\n");
00173            while (warn != null) {
00174               textBuf.append ("Message:  " + warn.getMessage () + "\n");
00175               textBuf.append ("SQLState: " + warn.getSQLState () + "\n");
00176               textBuf.append ("Vendor:   " + warn.getErrorCode () + "\n");
00177               warn = warn.getNextWarning ();
00178            }
00179        }
00180        return textBuf.toString ();
00181     }
00182     
00183     private String selectDbTable (DatabaseMetaData dma)
00184        throws SQLException {
00185        // JDBC exposes must meta data as ResultSets
00186        // Change the second parameter below to retrieve information
00187        // about a particular schema in the database
00188        ResultSet dbResults = dma.getTables (null, null, "%", null);
00189        Vector vec = new Vector ();
00190        
00191        /*
00192          Each table description row has the following columns:
00193          TABLE_CAT String => table catalog (may be null)
00194          TABLE_SCHEM String => table schema (may be null)
00195          TABLE_NAME String => table name
00196          TABLE_TYPE String => table type
00197          Common types are "TABLE", "VIEW", "SYSTEM TABLE"
00198          REMARKS String => explanatory comment on the table
00199          We are only want column 3 (table name)
00200        */
00201        
00202        while (dbResults.next ())
00203            vec.addElement (dbResults.getString (3));
00204 
00205        if (vec.size() == 0) {
00206            SharpOptionPane.showMessageDialog(sharp,
00207                                          "This database has no tables defined.",
00208                                          "Empty Database",
00209                                          JOptionPane.WARNING_MESSAGE);
00210            return null;
00211        }   
00212        
00213        Object[] possibleValues = new Object[vec.size ()];
00214        vec.copyInto (possibleValues);
00215        Object selectedValue = SharpOptionPane.showInputDialog(sharp,
00216                                                         "Please select the table you wish to load: ", "Select Table",
00217                                                         0,
00218                                                         /*selectTableIcon*/
00219                                                         connectedIcon,
00220                                                         possibleValues,
00221                                                         possibleValues[0]);
00222        if (selectedValue == null)
00223            return null;
00224        else
00225            return selectedValue.toString ();
00226     }
00227 }
00228 
00229 
00230 
00231