001    package net.sourceforge.mp3dings.export;
002    
003    /* Mp3dings - manage mp3 meta-information
004     * Copyright (C) 2003 Moritz Ringler
005     * $Id: DatabaseImporter.java 43 2007-10-20 17:39:59Z 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.Frame;
023    import java.io.FileInputStream;
024    import java.io.IOException;
025    import java.sql.PreparedStatement;
026    import java.sql.ResultSet;
027    import java.sql.SQLException;
028    import java.util.Arrays;
029    import java.util.HashMap;
030    import java.util.Iterator;
031    import java.util.List;
032    import java.util.Map;
033    import java.util.Properties;
034    import java.util.List;
035    import java.util.ArrayList;
036    import java.util.regex.Matcher;
037    import java.util.regex.Pattern;
038    import de.mospace.lang.ClassLoaderProvider;
039    import net.sourceforge.mp3dings.Field;
040    import net.sourceforge.mp3dings.Globals;
041    import de.mospace.swing.text.FullTextStringInputDialog;
042    
043    /** Imports data from a JDBC database.
044     * @author Moritz Ringler
045     * @version $Revision: 43 $ ($Date: 2007-10-20 19:39:59 +0200 (Sa, 20 Okt 2007) $)
046     */
047    public class DatabaseImporter extends DatabaseConnection{
048        private final int[] fromColumn;
049        private final int[] forPosition;
050        private final Field[] dataStructure;
051        private final PreparedStatement pquery;
052    
053        /** The name of the property holding the where clause for the
054        *SQL <code>SELECT</code> statement. */
055        public static String P_WHERE = "database.search.condition";
056    
057        private ResultSet rs;
058        private boolean hasresult = false;
059    
060        /** Establishes a new database connection that can import data from a
061        * database table to mp3dings. A suitable configuration can be created
062        * interactively with the {@link #editConfig editConfig} method.
063        *
064        * @param config the database import configuration
065        * @param clp a classloader provider that manages the dynamic class path
066        * for loading the JDBC driver, may be <code>null</code>
067        * @param dataStructure the fields that are available in the mp3dings table
068        * that will exchange data with the database
069        *
070        * @throws ExportException if a required property is missing in
071        * <code>config</code>, or if the JDBC driver cannot be loaded
072        * @throws SQLException if an SQL/JDBC error occurs
073        *
074        * @see net.sourceforge.mp3dings.Mp3dingsTableModel#getFields
075        */
076        public DatabaseImporter(Properties config,
077                ClassLoaderProvider clp, Field[] dataStructure)
078                throws SQLException, ExportException {
079    
080            super(config, clp);
081            String whereClause = config.getProperty(P_WHERE);
082            if(whereClause == null || whereClause.length() == 0){
083                throw new ExportException(
084                        Globals.getMessage("EXPORT_DB_MISSING_PROPERTY",
085                        P_WHERE));
086            }
087    
088            this.dataStructure = dataStructure;
089            List fieldList = Arrays.asList(dataStructure);
090    
091            StringBuffer query = new StringBuffer("SELECT ");
092    
093            /* construct the database-table/view to mp3dings mapping */
094            List indx = new ArrayList();
095            boolean appendComma = false;
096            for(Iterator it = config.keySet().iterator();it.hasNext();){
097                String key = it.next().toString();
098                if(key.startsWith(P_FIELD_PREFIX)){
099                    Field f = Field.forName(key.substring(P_FIELD_PREFIX.length()));
100                    if(f != null){
101                        String column = config.getProperty(key);
102                        if(column != null && column.length() != 0){
103                            int i = fieldList.indexOf(f);
104                            if(i >= 0){
105                                if(appendComma){
106                                    query.append(", ");
107                                } else {
108                                    appendComma = true;
109                                }
110                                query.append(column);
111                                indx.add(new Integer(i));
112                            }
113                        }
114                    }
115                }
116            }
117            fromColumn = new int[indx.size()];
118            for(int i=0, n = fromColumn.length; i<n; i++){
119                fromColumn[i] = ((Integer) indx.get(i)).intValue();
120            }
121    
122            query.append(" FROM ").append(config.getProperty(P_TABLE));
123    
124            /* analyze the search condition and build the SQL where clause */
125            query.append(" WHERE ");
126            Matcher matcher = Pattern.compile(
127                    "\\bmp3dings\\('(.*?)'\\)").matcher(whereClause);
128    
129            indx.clear();
130            Map fields = new HashMap();
131            for(Iterator it = Field.values().iterator();it.hasNext();){
132                Field f = (Field) it.next();
133                fields.put(f.longname().toLowerCase(), f);
134            }
135            while(matcher.find()){
136                String ff = matcher.group(1);
137                Field f = Field.forName(ff);
138                if(f == null){
139                    f = (Field) fields.get(ff.toLowerCase());
140                }
141                if(f == null){
142                    throw new ExportException("No such mp3dings field " + ff);
143                }
144                int i = fieldList.indexOf(f);
145                if(i >= 0){
146                    matcher.appendReplacement(query, "?");
147                    indx.add(new Integer(i));
148                } else {
149                    matcher.appendReplacement(query, "NULL");
150                }
151            }
152            matcher.appendTail(query);
153            forPosition = new int[indx.size()];
154    
155            for(int i=0, n = forPosition.length; i<n; i++){
156                forPosition[i] = ((Integer) indx.get(i)).intValue();
157            }
158    
159            pquery = getConnection().prepareStatement(query.toString());
160        }
161    
162        /**
163        * Looks for database entries matching the input. An SQL
164        * <code>SELECT</code> statement is sent to the database
165        * whose <code>WHERE</code>-clause is formed from
166        * <code>config.getProperty(P_WHERE)</code> by replacing expressions
167        * like <code>mp3dings('ARTIST')</code> with the corresponding input
168        * values. The default conversion between java and database
169        * types is used.<p>
170        * The results of the database query can be retrieved iteratively
171        * with {@link #nextResult}.
172        *
173        * @param input values for the fields specified with the
174        *              <code>dataStructure</code>
175        *              argument of the constructor. These values will be used
176        *              to substitute expressions like
177        *              <code>mp3dings('ARTIST')</code> in the search condition.
178        * @return whether a match was found in the database.
179        */
180        public boolean query(Object[] input) throws SQLException{
181            hasresult = false;
182            rs = null;
183            pquery.clearParameters();
184            for (int i = 0; i< forPosition.length; i++){
185                pquery.setObject(i+1, input[forPosition[i]]);
186            }
187            //System.out.println(pquery);
188            rs = pquery.executeQuery();
189            hasresult = rs.next();
190            return hasresult;
191        }
192    
193        // /** @deprecated This method is unnecessary and should not be used.
194        // * Returns whether the current result is the last result. */
195        // public boolean isLastResult() throws SQLException{
196            // return rs.isLast();
197        // }
198    
199        /** Returns wether the last query returned another result not yet
200        * retrieved with {@link #nextResult}. */
201        public boolean hasResult(){
202            return hasresult;
203        }
204    
205        /** Copies the next result of the last {@link #query query} to the
206        * provided array and returns it.
207        * The default conversion between java and database types is used.<p>
208        * Each result can be retrieved only once. If all results of the last
209        * query have already been retrieved the argument array will be returned
210        * unchanged.
211        *
212        * @param result an array to hold the query result. If <code>null</code> or
213        * an array of the wrong length is passed a new array will be allocated if
214        * necessary.
215        *
216        * @return the next result of the last query
217        */
218        public Object[] nextResult(Object[] result) throws SQLException{
219            if(hasresult){
220                if(result == null ||
221                   result.length != dataStructure.length){
222                    result = new Object[dataStructure.length];
223                }
224                Arrays.fill(result, null);
225                for(int i=0; i<fromColumn.length; i++){
226                    result[fromColumn[i]] = rs.getObject(i+1);
227                }
228            }
229            hasresult = rs.next();
230            if(!hasresult){
231                rs = null;
232            }
233            return result;
234        }
235    
236        /** Displays a wizard to interactively create or modify a JDBC
237        * configuration that can be used to establish a database import
238        * connection.
239        * @param parent the frame to use as a dialog owner
240        * @param config the previous configuration if any, may be <code>null</code>
241        * @param availableFields the fields that are available in the mp3dings table
242        * that will exchange data with the database
243        * @param clp a classloader provider that manages the dynamic class path
244        * for loading the JDBC driver, may be <code>null</code>
245        *
246        * @see net.sourceforge.mp3dings.Mp3dingsTableModel#getFields
247        */
248        public static Properties editConfig(Frame parent, Properties config,
249                Field[] availableFields, ClassLoaderProvider clp) throws SQLException{
250            Properties p = DatabaseConnection.editConfig(parent, config,
251                    availableFields, clp, true);
252            return (p == null)? null : editWhere(parent, p);
253        }
254    
255        /** Tries to load a previously saved database import configuration.
256        * @return a previously stored database import configuration or
257        * <code>null</code> if no such configuration exists
258        *
259        * @throws IOException if the JDBC configuration cannot be read from
260        * the properties file whose location was stored in the preferences
261        */
262        public static Properties loadConfig() throws IOException{
263            return DatabaseConnection.loadConfig(PREF_IMPORT);
264        }
265    
266        /** Displays a wizard to create or modify an SQL <code>WHERE</code>
267        * clause that can be used to restrict a query to the database.
268        *
269        * @param parent the frame to use as a dialog owner
270        * @param config a database configuration to modify or complement. Such
271        * a configuration should be the result of a previous call to
272        * {@link #editConfig editConfig} or {@link #loadConfig loadConfig}.
273        *
274        * @see #query
275        */
276        public static Properties editWhere(Frame parent, Properties config){
277            String whereClause = config.getProperty(P_WHERE);
278    
279            if(whereClause == null || whereClause.length() == 0){
280                StringBuffer newWhere = new StringBuffer();
281                boolean appendOR = false;
282                for(Iterator it = config.keySet().iterator();it.hasNext();){
283                    String key = it.next().toString();
284                    if(key.startsWith(P_FIELD_PREFIX)){
285                        Field f = Field.forName(key.substring(P_FIELD_PREFIX.length()));
286                        if(f != null){
287                            String column = config.getProperty(key);
288                            if(column != null && column.length() != 0){
289                                if(appendOR){
290                                    newWhere.append(" OR\n");
291                                } else {
292                                    appendOR = true;
293                                }
294                                newWhere.append(column).append(" = ");
295                                newWhere.append("mp3dings('").append(f.longname()).append("')");
296                            }
297                        }
298                    }
299                }
300                whereClause = newWhere.toString();
301            }
302    
303            whereClause = queryWhereClause(parent, whereClause);
304            if(whereClause != null && whereClause.length() != 0){
305                config.setProperty(P_WHERE, whereClause);
306                return config;
307            } else {
308                return null;
309            }
310        }
311    
312        private static String queryWhereClause(Frame parent, String proposal){
313            return FullTextStringInputDialog.showDialog(parent, Globals.getString("DB_IMPORT_WHERE_PROMPT"), proposal);
314        }
315    }