001    package net.sourceforge.mp3dings.export;
002    
003    /* Mp3dings - manage mp3 meta-information
004     * Copyright (C) 2003 Moritz Ringler
005     * $Id: DatabaseConnection.java 143 2008-05-14 07:18:25Z ringler $
006     *
007     * This program is free software; you can redistribute it and/or
008     * modify it under the terms of the GNU General Public License
009     * as published by the Free Software Foundation; either version 2
010     * of the License, or (at your option) any later version.
011     *
012     * This program is distributed in the hope that it will be useful,
013     * but WITHOUT ANY WARRANTY; without even the implied warranty of
014     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
015     * GNU General Public License for more details.
016     *
017     * You should have received a copy of the GNU General Public License
018     * along with this program; if not, write to the Free Software
019     * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
020     */
021    
022    import java.awt.Component;
023    import java.awt.event.ActionEvent;
024    import java.awt.event.ActionListener;
025    import java.awt.GridLayout;
026    import java.io.IOException;
027    import java.io.File;
028    import java.io.FileInputStream;
029    import java.io.FileNotFoundException;
030    import java.sql.Connection;
031    import java.sql.DatabaseMetaData;
032    import java.sql.Driver;
033    import java.sql.ResultSet;
034    import java.sql.SQLException;
035    import java.util.Collections;
036    import java.util.Enumeration;
037    import java.util.Properties;
038    import java.util.Vector;//must use vector
039    import java.util.ArrayList;
040    import java.util.List;
041    import javax.swing.JComboBox;
042    import javax.swing.JLabel;
043    import javax.swing.JOptionPane;
044    import javax.swing.JPanel;
045    import javax.swing.JPasswordField;
046    import javax.swing.JTextField;
047    import de.mospace.lang.ClassLoaderProvider;
048    import de.mospace.lang.DefaultClassLoaderProvider;
049    import net.sourceforge.mp3dings.Field;
050    import net.sourceforge.mp3dings.Globals;
051    import de.mospace.swing.PathInput;
052    
053    /** Manages and establishes a connection to a JDBC database.
054     * @author Moritz Ringler
055     * @version $Revision: 143 $ ($Date: 2008-05-14 09:18:25 +0200 (Mi, 14 Mai 2008) $)
056     */
057    public class DatabaseConnection{
058        private final transient Connection conn;
059    
060        /** The name of the property holding the name of the JDBC driver class. */
061        public final static String P_DRIVER = "jdbc.driver.class";
062    
063        /** The name of the property holding the file path of the
064        * jar archive with the JDBC driver. */
065        public final static String P_CLASSPATH = "jdbc.classpath";
066    
067        /** The name of the property holding the database user name. */
068        public final static String P_USER = "database.user";
069    
070        /** The name of the property holding the database password. */
071        public final static String P_PASSWORD = "database.password";
072    
073        /** The name of the property holding the jdbc: URL of the database. */
074        public final static String P_URL = "database.url";
075    
076        /** The name of the property holding the name of the database table
077        * to interchange data with.
078        */
079        public final static String P_TABLE = "database.table";
080    
081        /** The prefix for property names that designate an mp3dings field.
082        * These properties hold the name of the corresponding column of
083        * the database table.
084        */
085        public final static String P_FIELD_PREFIX = "field.";
086    
087        /** The preferences key at which the database export configuration
088        * is saved. */
089        public final static String PREF_EXPORT = "JDBCExport";
090    
091        /** The preferences key at which the database import configuration
092        * is saved. */
093        public final static String PREF_IMPORT = "JDBCImport";
094    
095        /** These properties must be present in the config
096        * object passed to the DatabaseConnection constructor.
097        */
098        private final static String[] REQUIRED_CONFIG =
099            new String[]{
100                P_DRIVER,
101                P_USER,
102                P_URL,
103                P_TABLE
104            };
105    
106        /** These properties will be used if they are present in the config
107        * object passed to the DatabaseConnection constructor.
108        */
109        private final static String[] OPTIONAL_CONFIG =
110            new String[]{
111                P_CLASSPATH,
112                P_PASSWORD
113            };
114    
115        /** Establishes a new DatabaseConnection using the specified configuration.
116        * Such a configuration can be created interactively with the {@link
117        * #editConfig editConfig} method.
118        * @param config the jdbc configuration
119        * @param clp a classloader provider that manages the dynamic class path
120        * for loading the JDBC driver, may be <code>null</code>
121        * @throws ExportException if a required property is missing in
122        * <code>config</code>, or if the JDBC driver cannot be loaded
123        * @throws SQLException if an SQL/JDBC error occurs
124        */
125        public DatabaseConnection(Properties config, ClassLoaderProvider clp) throws
126                ExportException, SQLException {
127    
128            /* check whether we have all configuration information that we need. */
129            for (int i = 0; i <  REQUIRED_CONFIG.length; i++){
130                String p = config.getProperty(REQUIRED_CONFIG[i]);
131                if(p == null || p.length() == 0){
132                    throw new ExportException(
133                            Globals.getMessage("EXPORT_DB_MISSING_PROPERTY",
134                            REQUIRED_CONFIG[i]));
135                }
136            }
137    
138            /* load the JDBC driver */
139            Driver jdbcDriver = getDriver(config, clp);
140    
141            /* try to establish a database connection */
142            conn = connect(config, jdbcDriver);
143            conn.setAutoCommit(false); //safe because connect never returns null
144        }
145    
146        /** Tries to load a previously saved JDBC configuration.
147        * @param prefkey the preferences key at which the configuration was
148        * stored, usually this will be PREF_EXPORT or PREF_IMPORT
149        * @return a previously stored JDBC configuration or <code>null</code>
150        * if no such configuration exists
151        *
152        * @throws IOException if the JDBC configuration cannot be read from
153        * the properties file whose location was stored in the preferences
154        */
155        public static Properties loadConfig(String prefkey) throws IOException{
156            String currentConfig = Globals.getPrefNode().get(prefkey, null);
157            File config = (currentConfig == null)
158                ? null
159                : new File(currentConfig);
160            Properties p = null;
161            if(config != null){
162                p = new Properties();
163                FileInputStream fis = null;
164                try{
165                    fis = new FileInputStream(config.getAbsolutePath());
166                    p = new Properties();
167                    p.load(fis);
168                } catch (FileNotFoundException ex){
169                    //empty config
170                } catch (IOException ex){
171                    p = null;
172                    throw ex;
173                } finally {
174                    if(fis != null){
175                        fis.close();
176                    }
177                }
178            }
179            return p;
180        }
181    
182        /** Displays a wizard to interactively create or modify a JDBC
183        * configuration that can be used to establish a database connection.
184        * @param parent the GUI component to use as a dialog owner
185        * @param config the previous configuration if any, may be <code>null</code>
186        * @param availableFields the fields that are available in the mp3dings table
187        * that will exchange data with the database
188        * @param clp a classloader provider that manages the dynamic class path
189        * for loading the JDBC driver, may be <code>null</code>
190        * @param allowViews whether to allow data exchange with views as well as
191        * database tables. This can be true for an import configuration,
192        * but should usually be false for an export configuration.
193        *
194        * @see net.sourceforge.mp3dings.Mp3dingsTableModel#getFields
195        */
196        public static Properties editConfig(Component parent, Properties config,
197                Field[] availableFields, ClassLoaderProvider clp, boolean allowViews) throws SQLException{
198            Properties prop = (config == null)
199                ? new Properties()
200                : (Properties) config.clone();
201    
202            /* 1. Configure jdbc driver and driver classpath */
203    
204            /* 1.1 create an editable combo box for the driver class name */
205            /* 1.1.1 known drivers */
206            Vector drivers = new Vector(); //must use vector
207            drivers.add("org.postgresql.Driver");
208            drivers.add("org.gjt.mm.mysql.Driver");
209            drivers.add("oracle.jdbc.driver.OracleDriver");
210            /* 1.1.2 current driver */
211            String driverClass = prop.getProperty(P_DRIVER);
212            if(driverClass != null && !drivers.contains(driverClass)){
213                drivers.add(driverClass);
214            }
215            JComboBox selector = new JComboBox(drivers);
216            if(driverClass != null){
217                selector.setSelectedItem(driverClass);
218            }
219            selector.setEditable(true);
220    
221            /* 1.2 create a container for the dialog content pane
222            *      and add the combo box to it. */
223            JPanel box = new JPanel(new GridLayout(0,1));
224            box.add(new JLabel(Globals.getString("DB_DRIVER_CLASS")));
225            box.add(selector);
226    
227            /* 1.3 create a path input for the jar archive with the jdbc driver */
228            String driverPath = prop.getProperty(P_CLASSPATH);
229            PathInput ff = null;
230            JLabel ffLabel = new JLabel(Globals.getString("DB_DRIVER_JAR"));
231            /* By default we assume that the JDBC driver is on the
232             * mp3dings classpath. We display the 'driver jar file'
233             * input only when we fail to find the driver on the
234             * mp3dings classpath or when the current configuration
235             * has a non-null jdbc.classpath property.
236             */
237            if(driverPath != null && driverPath.length() != 0){
238                ff = new PathInput(driverPath, ".jar");
239                box.add(ffLabel);
240                box.add(ff);
241            }
242    
243            /* 1.4 Display the driver configuration dialog until we can load
244            *      a driver */
245            Driver driver = null;
246            while(driver == null){
247                do {
248                    int resp = JOptionPane.showConfirmDialog(parent, box,
249                        Globals.getString("DB_DRIVER_PROMPT"),
250                        JOptionPane.OK_CANCEL_OPTION,
251                        JOptionPane.QUESTION_MESSAGE);
252                    if(resp == JOptionPane.OK_OPTION) {
253                        Object selection = selector.getSelectedItem();
254                        if(selection != null){
255                            driverClass = selection.toString();
256                            prop.setProperty(P_DRIVER, driverClass);
257                        }
258                        if(ff != null){
259                            String f = ff.getPath();
260                            if(f == null || f.length() == 0){
261                                prop.remove(P_CLASSPATH);
262                            } else {
263                                prop.setProperty(P_CLASSPATH, (new File(f)).getAbsolutePath());
264                            }
265                        }
266                    } else {
267                        return null;
268                    }
269                } while (driverClass == null || driverClass.length() == 0);
270    
271                try{
272                    driver = getDriver(prop, clp);
273                } catch (Exception ex){
274                    if (ff == null){
275                        /* if we have failed to load the driver and we have not
276                        * displayed the driver jar file input so far, then make
277                        * it visible and redisplay the dialog.
278                        */
279                        ff = new PathInput("", ".jar");
280                        box.add(ffLabel);
281                        box.add(ff);
282                    } else {
283                        JOptionPane.showMessageDialog(parent,
284                        ex.getLocalizedMessage(), Globals.getString("ERROR"), JOptionPane.WARNING_MESSAGE);
285                    }
286                }
287            }
288    
289            /* 2. Configure database url and user */
290            /* Out of security considerations we do not offer to save the database,
291             * password here. You can however manually set the database.password
292             * property.
293             */
294            box =  new JPanel(new GridLayout(0,1));
295            box.add(new JLabel(Globals.getString("DB_URL")));
296            String url = prop.getProperty(P_URL);
297            if(url == null || url.length() == 0){
298                url = "jdbc:";
299            }
300            JTextField editUrl = new JTextField(url);
301            box.add(editUrl);
302    
303            box.add(new JLabel(Globals.getString("DB_USER")));
304            String user = prop.getProperty(P_USER);
305            if(user == null){
306                user = "";
307            }
308            JTextField editUser = new JTextField(user);
309            box.add(editUser);
310    
311            Connection cnnx = null;
312            while(cnnx == null){
313                int resp = JOptionPane.showConfirmDialog(parent, box,
314                        Globals.getString("DB_INFO"),
315                        JOptionPane.OK_CANCEL_OPTION,
316                        JOptionPane.QUESTION_MESSAGE);
317                    if(resp == JOptionPane.OK_OPTION) {
318                        prop.setProperty(P_URL, editUrl.getText());
319                        prop.setProperty(P_USER, editUser.getText());
320                    } else {
321                        return null;
322                    }
323                try{
324                    cnnx = connect(prop, driver);
325                } catch (Exception ex){
326                    prop.remove(P_PASSWORD);
327                    JOptionPane.showMessageDialog(parent,
328                        ex.getLocalizedMessage(), Globals.getString("DB_INFO"), JOptionPane.WARNING_MESSAGE);
329                }
330            }
331    
332            /* 3. Let the user pick one of the tables in the database. */
333            /* Maybe we should provide for the case when there are no tables
334             * in the database?
335             * Maybe we should offer to create a new table? */
336            box =  new JPanel(new GridLayout(0,1));
337            box.add(new JLabel(Globals.getString("DB_TABLE")));
338            ResultSet rs = null;
339            DatabaseMetaData meta = cnnx.getMetaData();
340            rs = meta.getTables(null, null, null, allowViews
341                ? new String[]{"TABLE", "VIEW"}
342                : new String[]{"TABLE"});
343            List tables = new ArrayList();
344            while (rs.next()) {
345                tables.add(rs.getString("TABLE_NAME"));
346            }
347            selector = new JComboBox(tables.toArray());
348            box.add(selector);
349    
350            String table = prop.getProperty(P_TABLE);
351            if(table != null && tables.contains(table)){
352                selector.setSelectedItem(table);
353            }
354            do {
355                int resp = JOptionPane.showConfirmDialog(parent, box,
356                        Globals.getString("DB_PICK_TABLE"),
357                        JOptionPane.OK_CANCEL_OPTION,
358                        JOptionPane.QUESTION_MESSAGE);
359                if(resp == JOptionPane.OK_OPTION) {
360                    Object selection = selector.getSelectedItem();
361                    if(selection != null){
362                        table = selection.toString();
363                        prop.setProperty(P_TABLE, table);
364                    }
365                } else {
366                    return null;
367                }
368            } while(table == null || table.length() == 0);
369    
370            /* 4. Configure the one-to-one mapping
371             * between mp3dings fields and database table columns.
372             *
373            /* So far we do nothing do ascertain that
374             * the types of mp3dings field and database table column are
375             * compatible. The user will get an error message when she or he
376             * tries to export or import incompatible values.
377             */
378            ResultSet rsColumns = null;
379            rsColumns = meta.getColumns(null, null, table, null);
380            List columns = new ArrayList();
381            while (rsColumns.next()) {
382              columns.add(rsColumns.getString("COLUMN_NAME"));
383            }
384            /* we sort the columns so that we can use
385             * Collections.binarySearch on them */
386            Collections.sort(columns);
387            String[] cols = (String[]) columns.toArray(new String[0]);
388            final JComboBox[] fields = new JComboBox[cols.length];
389            Vector fieldSelection = new Vector();//must use Vector here
390            for(int i=0; i< availableFields.length; i++){
391                fieldSelection.add(availableFields[i].longname());
392            }
393            fieldSelection.add("");
394    
395            /* This actionlistener assures that the same item can be selected
396             * in only one JComboBox, which in turn assures that the mapping
397             * between mp3dings fields and database table columns will be
398             * one-to-one. */
399            ActionListener oneToOneChecker = new ActionListener(){
400                public void actionPerformed(ActionEvent e){
401                    Object src = e.getSource();
402                    if(src instanceof JComboBox){
403                        JComboBox srcbox = (JComboBox) src;
404                        Object newItem = srcbox.getSelectedItem();
405                        if(newItem != null && newItem.toString().length() != 0){
406                            final int n = fields.length;
407                            for(int i=0; i<n; i++){
408                                if(
409                                    fields[i] != srcbox &&
410                                    newItem.equals(fields[i].getSelectedItem())
411                                ){
412                                    fields[i].setSelectedItem("");
413                                }
414                            }
415                        }
416                    }
417                }
418            };
419    
420            JPanel panel = new JPanel(new GridLayout(0,2));
421            for(int i=0; i<cols.length; i++){
422                panel.add(new JLabel(cols[i]));
423                fields[i] = new JComboBox(fieldSelection);
424                fields[i].setSelectedItem("");
425                fields[i].addActionListener(oneToOneChecker);
426                panel.add(fields[i]);
427            }
428    
429            /* make the GUI display the current settings */
430            if(config != null){
431                Enumeration e = config.propertyNames();
432                while(e.hasMoreElements()){
433                    String key = (String) e.nextElement();
434                    if(key.startsWith(P_FIELD_PREFIX)){
435                        String col = config.getProperty(key);
436                        /* columns are sorted, therefore we can use binarySearch */
437                        int indx = Collections.binarySearch(columns, col);
438                        boolean keep = (indx >= 0);
439                        if(keep){
440                            Field field = Field.forName(key.substring(P_FIELD_PREFIX.length()));
441                            keep = (field != null)
442                                    && fieldSelection.contains(field.longname());
443                            if(keep){
444                                fields[indx].setSelectedItem(field.longname());
445                            }
446                        }
447                        prop.remove(key);
448                    }
449                }
450            }
451    
452            int resp = JOptionPane.showConfirmDialog(parent, panel,
453                        Globals.getString("DB_EDIT_MAPPING"),
454                        JOptionPane.OK_CANCEL_OPTION,
455                        JOptionPane.QUESTION_MESSAGE);
456            if(resp == JOptionPane.OK_OPTION) {
457                int nmax = availableFields.length;
458                for(int i=0; i<cols.length; i++){
459                    int sel = fields[i].getSelectedIndex();
460                    if(sel >= 0 && sel < nmax){
461                        prop.put(P_FIELD_PREFIX + availableFields[sel].name(),
462                            cols[i]);
463                    }
464                }
465            } else {
466                return null;
467            }
468    
469            cnnx.close();
470            return prop;
471        }
472    
473        private static Driver getDriver(Properties config, ClassLoaderProvider clp)
474            throws ExportException{
475            String driverClasspath = config.getProperty(P_CLASSPATH);
476            ClassLoader cl = DatabaseConnection.class.getClassLoader();
477            if(driverClasspath != null && driverClasspath.trim().length() != 0){
478                ClassLoaderProvider clp2 = (clp == null)
479                    ? new DefaultClassLoaderProvider()
480                    : clp;
481                clp2.registerLibrary(new File(driverClasspath));
482                cl = clp2.getClassLoader();
483            }
484            Driver jdbcDriver = null;
485            try{
486                jdbcDriver =  (Driver) Class.forName(
487                    config.getProperty(P_DRIVER), true, cl).newInstance();
488            } catch (Exception ex){
489                throw new ExportException(ex);
490            }
491            return jdbcDriver;
492        }
493    
494        /** @return either returns a non-null Connection or throws an Exception **/
495        private static Connection connect(Properties config, Driver d) throws SQLException{
496            /* Note that we cannot use DriverManager here,
497               since DriverManager only uses Drivers loaded by the same classloader
498               as the caller. */
499            Properties connectionInfo = new Properties();
500            String user = config.getProperty(P_USER);
501            connectionInfo.put("user", user);
502            String pw = config.getProperty(P_PASSWORD);
503            if(pw == null || pw.length() == 0){
504                pw = queryPassword(user);
505            }
506            if(pw != null){
507                connectionInfo.put("password", pw);
508                config.setProperty(P_PASSWORD, pw);
509            }
510    
511            Connection result = d.connect(config.getProperty(P_URL),
512                        connectionInfo);
513            if(result == null){
514               /* see documentation of java.sql.Driver.connect() */
515                throw new SQLException(Globals.getString("EXPORT_DB_WRONG_DRIVER_ERR"));
516            }
517            return result;
518        }
519    
520        private static String queryPassword(String user){
521            JPasswordField pwd = new JPasswordField();
522            Object[] message = new Object[]{
523                    Globals.getMessage("EXPORT_DB_PASSWORD_PROMPT", user), pwd };
524            String pass = null;
525            int resp = JOptionPane.showConfirmDialog(null, message,
526                Globals.getString("EXPORT_DB_PASSWORD"),
527                JOptionPane.OK_CANCEL_OPTION,
528                JOptionPane.QUESTION_MESSAGE);
529            if(resp == JOptionPane.OK_OPTION) {
530                pass = new String(pwd.getPassword());
531            }
532            return pass;
533        }
534    
535        /** Gets the jdbc connection established at instantiation.
536        * @return a jdbc connection
537        */
538        protected Connection getConnection(){
539            return conn;
540        }
541    
542        /** Returns whether the database connection established at
543        * instantiation is still alive.
544        * @return whether the jdbc connection is still alive
545        * @see java.sql.Connection#isClosed
546        */
547        public boolean isAlive(){
548            boolean result = false;
549            try{
550                result = !conn.isClosed();
551            } catch (SQLException ignore){
552                ignore.printStackTrace();
553            }
554            return result;
555        }
556    
557        /** Closes the Database connection. **/
558        public void close() throws SQLException{
559            conn.close();
560        }
561    
562    }