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 }