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 }