001    /*
002     * Licensed to the Apache Software Foundation (ASF) under one or more
003     * contributor license agreements.  See the NOTICE file distributed with
004     * this work for additional information regarding copyright ownership.
005     * The ASF licenses this file to You under the Apache License, Version 2.0
006     * (the "License"); you may not use this file except in compliance with
007     * the License.  You may obtain a copy of the License at
008     *
009     *      http://www.apache.org/licenses/LICENSE-2.0
010     *
011     * Unless required by applicable law or agreed to in writing, software
012     * distributed under the License is distributed on an "AS IS" BASIS,
013     * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014     * See the License for the specific language governing permissions and
015     * limitations under the License.
016     */
017    package org.apache.commons.dbutils;
018    
019    import java.beans.IntrospectionException;
020    import java.beans.Introspector;
021    import java.beans.PropertyDescriptor;
022    import java.lang.reflect.InvocationTargetException;
023    import java.lang.reflect.Method;
024    import java.sql.Connection;
025    import java.sql.ParameterMetaData;
026    import java.sql.PreparedStatement;
027    import java.sql.ResultSet;
028    import java.sql.SQLException;
029    import java.sql.Statement;
030    import java.sql.Types;
031    import java.util.Arrays;
032    
033    import javax.sql.DataSource;
034    
035    /**
036     * Executes SQL queries with pluggable strategies for handling 
037     * <code>ResultSet</code>s.  This class is thread safe.
038     * 
039     * @see ResultSetHandler
040     */
041    public class QueryRunner {
042    
043        /**
044         * Is {@link ParameterMetaData#getParameterType(int)} broken (have we tried it yet)?
045         */
046        private volatile boolean pmdKnownBroken = false;
047        
048        /**
049         * The DataSource to retrieve connections from.
050         */
051        protected final DataSource ds;
052    
053        /**
054         * Constructor for QueryRunner.
055         */
056        public QueryRunner() {
057            super();
058            ds = null;
059        }
060    
061        /**
062         * Constructor for QueryRunner, allows workaround for Oracle drivers
063         * @param pmdKnownBroken Oracle drivers don't support {@link ParameterMetaData#getParameterType(int) };
064         * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
065         * and if it breaks, we'll remember not to use it again.
066         */
067        public QueryRunner(boolean pmdKnownBroken) {
068            super();
069            this.pmdKnownBroken = pmdKnownBroken; 
070            ds = null;
071        }
072        
073        /**
074         * Constructor for QueryRunner, allows workaround for Oracle drivers.  Methods that do not take a 
075         * <code>Connection</code> parameter will retrieve connections from this
076         * <code>DataSource</code>.
077         * 
078         * @param ds The <code>DataSource</code> to retrieve connections from.
079         */
080        public QueryRunner(DataSource ds) {
081            super();
082            this.ds = ds;
083        }
084        
085        /**
086         * Constructor for QueryRunner, allows workaround for Oracle drivers.  Methods that do not take a 
087         * <code>Connection</code> parameter will retrieve connections from this
088         * <code>DataSource</code>.
089         * 
090         * @param ds The <code>DataSource</code> to retrieve connections from.
091         * @param pmdKnownBroken Oracle drivers don't support {@link ParameterMetaData#getParameterType(int) };
092         * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
093         * and if it breaks, we'll remember not to use it again.
094         */
095        public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
096            super();
097            this.pmdKnownBroken = pmdKnownBroken;
098            this.ds = ds;
099        }
100        
101        /**
102         * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
103         * 
104         * @param conn The Connection to use to run the query.  The caller is
105         * responsible for closing this Connection.
106         * @param sql The SQL to execute.
107         * @param params An array of query replacement parameters.  Each row in
108         * this array is one set of batch replacement values. 
109         * @return The number of rows updated per statement.
110         * @throws SQLException if a database access error occurs
111         * @since DbUtils 1.1
112         */
113        public int[] batch(Connection conn, String sql, Object[][] params)
114            throws SQLException {
115    
116            PreparedStatement stmt = null;
117            int[] rows = null;
118            try {
119                stmt = this.prepareStatement(conn, sql);
120    
121                for (int i = 0; i < params.length; i++) {
122                    this.fillStatement(stmt, params[i]);
123                    stmt.addBatch();
124                }
125                rows = stmt.executeBatch();
126    
127            } catch (SQLException e) {
128                this.rethrow(e, sql, params);
129            } finally {
130                close(stmt);
131            }
132    
133            return rows;
134        }
135    
136        /**
137         * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.  The 
138         * <code>Connection</code> is retrieved from the <code>DataSource</code> 
139         * set in the constructor.  This <code>Connection</code> must be in 
140         * auto-commit mode or the update will not be saved. 
141         * 
142         * @param sql The SQL to execute.
143         * @param params An array of query replacement parameters.  Each row in
144         * this array is one set of batch replacement values. 
145         * @return The number of rows updated per statement.
146         * @throws SQLException if a database access error occurs
147         * @since DbUtils 1.1
148         */
149        public int[] batch(String sql, Object[][] params) throws SQLException {
150            Connection conn = this.prepareConnection();
151    
152            try {
153                return this.batch(conn, sql, params);
154            } finally {
155                close(conn);
156            }
157        }
158    
159        /**
160         * Fill the <code>PreparedStatement</code> replacement parameters with 
161         * the given objects.
162         * @param stmt PreparedStatement to fill
163         * @param params Query replacement parameters; <code>null</code> is a valid
164         * value to pass in.
165         * @throws SQLException if a database access error occurs
166         */
167        public void fillStatement(PreparedStatement stmt, Object[] params)
168            throws SQLException {
169    
170            if (params == null) {
171                return;
172            }
173            
174            ParameterMetaData pmd = stmt.getParameterMetaData();
175            if (pmd.getParameterCount() < params.length) {
176                throw new SQLException("Too many parameters: expected "
177                        + pmd.getParameterCount() + ", was given " + params.length);
178            }
179            for (int i = 0; i < params.length; i++) {
180                if (params[i] != null) {
181                    stmt.setObject(i + 1, params[i]);
182                } else {
183                    // VARCHAR works with many drivers regardless
184                    // of the actual column type.  Oddly, NULL and 
185                    // OTHER don't work with Oracle's drivers.
186                    int sqlType = Types.VARCHAR;
187                    if (!pmdKnownBroken) {
188                        try {
189                            sqlType = pmd.getParameterType(i + 1);
190                        } catch (SQLException e) {
191                            pmdKnownBroken = true;
192                        }
193                    }
194                    stmt.setNull(i + 1, sqlType);
195                }
196            }
197        }
198    
199        /**
200         * Fill the <code>PreparedStatement</code> replacement parameters with the
201         * given object's bean property values.
202         * 
203         * @param stmt
204         *            PreparedStatement to fill
205         * @param bean
206         *            a JavaBean object
207         * @param properties
208         *            an ordered array of properties; this gives the order to insert
209         *            values in the statement
210         * @throws SQLException
211         *             if a database access error occurs
212         */
213        public void fillStatementWithBean(PreparedStatement stmt, Object bean,
214                PropertyDescriptor[] properties) throws SQLException {
215            Object[] params = new Object[properties.length];
216            for (int i = 0; i < properties.length; i++) {
217                PropertyDescriptor property = properties[i];
218                Object value = null;
219                Method method = property.getReadMethod();
220                if (method == null) {
221                    throw new RuntimeException("No read method for bean property "
222                            + bean.getClass() + " " + property.getName());
223                }
224                try {
225                    value = method.invoke(bean, new Object[0]);
226                } catch (InvocationTargetException e) {
227                    throw new RuntimeException("Couldn't invoke method: " + method, e);
228                } catch (IllegalArgumentException e) {
229                    throw new RuntimeException("Couldn't invoke method with 0 arguments: " + method, e);
230                } catch (IllegalAccessException e) {
231                    throw new RuntimeException("Couldn't invoke method: " + method, e);
232                } 
233                params[i] = value;
234            }
235            fillStatement(stmt, params);
236        }
237    
238        /**
239         * Fill the <code>PreparedStatement</code> replacement parameters with the
240         * given object's bean property values.
241         * 
242         * @param stmt
243         *            PreparedStatement to fill
244         * @param bean
245         *            a JavaBean object
246         * @param propertyNames
247         *            an ordered array of property names (these should match the
248         *            getters/setters); this gives the order to insert values in the
249         *            statement
250         * @throws SQLException
251         *             if a database access error occurs
252         */
253        public void fillStatementWithBean(PreparedStatement stmt, Object bean,
254                String[] propertyNames) throws SQLException {
255            PropertyDescriptor[] descriptors;
256            try {
257                descriptors = Introspector.getBeanInfo(bean.getClass())
258                        .getPropertyDescriptors();
259            } catch (IntrospectionException e) {
260                throw new RuntimeException("Couldn't introspect bean " + bean.getClass().toString(), e);
261            }
262            PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length];
263            for (int i = 0; i < propertyNames.length; i++) {
264                String propertyName = propertyNames[i];
265                if (propertyName == null) {
266                    throw new NullPointerException("propertyName can't be null: " + i);
267                }
268                boolean found = false;
269                for (int j = 0; j < descriptors.length; j++) {
270                    PropertyDescriptor descriptor = descriptors[j];
271                    if (propertyName.equals(descriptor.getName())) {
272                        sorted[i] = descriptor;
273                        found = true;
274                        break;
275                    }
276                }
277                if (!found) {
278                    throw new RuntimeException("Couldn't find bean property: "
279                            + bean.getClass() + " " + propertyName);
280                }
281            }
282            fillStatementWithBean(stmt, bean, sorted);
283        }
284    
285        /**
286         * Returns the <code>DataSource</code> this runner is using.  
287         * <code>QueryRunner</code> methods always call this method to get the
288         * <code>DataSource</code> so subclasses can provide specialized
289         * behavior.
290         *
291         * @return DataSource the runner is using
292         */
293        public DataSource getDataSource() {
294            return this.ds;
295        }
296    
297        /**
298         * Factory method that creates and initializes a 
299         * <code>PreparedStatement</code> object for the given SQL.  
300         * <code>QueryRunner</code> methods always call this method to prepare 
301         * statements for them.  Subclasses can override this method to provide 
302         * special PreparedStatement configuration if needed.  This implementation
303         * simply calls <code>conn.prepareStatement(sql)</code>.
304         *  
305         * @param conn The <code>Connection</code> used to create the 
306         * <code>PreparedStatement</code>
307         * @param sql The SQL statement to prepare.
308         * @return An initialized <code>PreparedStatement</code>.
309         * @throws SQLException if a database access error occurs
310         */
311        protected PreparedStatement prepareStatement(Connection conn, String sql)
312            throws SQLException {
313                
314            return conn.prepareStatement(sql);
315        }
316        
317        /**
318         * Factory method that creates and initializes a 
319         * <code>Connection</code> object.  <code>QueryRunner</code> methods 
320         * always call this method to retrieve connections from its DataSource.  
321         * Subclasses can override this method to provide 
322         * special <code>Connection</code> configuration if needed.  This 
323         * implementation simply calls <code>ds.getConnection()</code>.
324         * 
325         * @return An initialized <code>Connection</code>.
326         * @throws SQLException if a database access error occurs
327         * @since DbUtils 1.1
328         */
329        protected Connection prepareConnection() throws SQLException {
330            if(this.getDataSource() == null) {
331                throw new SQLException("QueryRunner requires a DataSource to be " +
332                    "invoked in this way, or a Connection should be passed in");
333            }
334            return this.getDataSource().getConnection();
335        }
336    
337        /**
338         * Execute an SQL SELECT query with a single replacement parameter. The
339         * caller is responsible for closing the connection.
340         * 
341         * @param conn The connection to execute the query in.
342         * @param sql The query to execute.
343         * @param param The replacement parameter.
344         * @param rsh The handler that converts the results into an object.
345         * @return The object returned by the handler.
346         * @throws SQLException if a database access error occurs
347         * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object[])} instead
348         */
349        public Object query(Connection conn, String sql, Object param,
350                ResultSetHandler rsh) throws SQLException {
351    
352            return this.query(conn, sql, rsh, new Object[] { param });
353        }
354    
355        /**
356         * Execute an SQL SELECT query with replacement parameters.  The
357         * caller is responsible for closing the connection.
358         * 
359         * @param conn The connection to execute the query in.
360         * @param sql The query to execute.
361         * @param params The replacement parameters.
362         * @param rsh The handler that converts the results into an object.
363         * @return The object returned by the handler.
364         * @throws SQLException if a database access error occurs
365         * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object[])} instead
366         */
367        public Object query(Connection conn, String sql, Object[] params,
368                ResultSetHandler rsh) throws SQLException {
369                    return query(conn, sql, rsh, params);
370                }
371    
372        /**
373         * Execute an SQL SELECT query with replacement parameters.  The
374         * caller is responsible for closing the connection.
375         * 
376         * @param conn The connection to execute the query in.
377         * @param sql The query to execute.
378         * @param rsh The handler that converts the results into an object.
379         * @param params The replacement parameters.
380         * @return The object returned by the handler.
381         * @throws SQLException if a database access error occurs
382         */
383        public Object query(Connection conn, String sql, ResultSetHandler rsh,
384                Object[] params) throws SQLException {
385    
386            PreparedStatement stmt = null;
387            ResultSet rs = null;
388            Object result = null;
389    
390            try {
391                stmt = this.prepareStatement(conn, sql);
392                this.fillStatement(stmt, params);
393                rs = this.wrap(stmt.executeQuery());
394                result = rsh.handle(rs);
395    
396            } catch (SQLException e) {
397                this.rethrow(e, sql, params);
398    
399            } finally {
400                try {
401                    close(rs);
402                } finally {
403                    close(stmt);
404                }
405            }
406    
407            return result;
408        }
409    
410        /**
411         * Execute an SQL SELECT query without any replacement parameters.  The
412         * caller is responsible for closing the connection.
413         * 
414         * @param conn The connection to execute the query in.
415         * @param sql The query to execute.
416         * @param rsh The handler that converts the results into an object.
417         * @return The object returned by the handler.
418         * @throws SQLException if a database access error occurs
419         */
420        public Object query(Connection conn, String sql, ResultSetHandler rsh)
421            throws SQLException {
422    
423            return this.query(conn, sql, rsh, (Object[]) null);
424        }
425    
426        /**
427         * Executes the given SELECT SQL with a single replacement parameter.
428         * The <code>Connection</code> is retrieved from the
429         * <code>DataSource</code> set in the constructor.
430         * 
431         * @param sql The SQL statement to execute.
432         * @param param The replacement parameter.
433         * @param rsh The handler used to create the result object from 
434         * the <code>ResultSet</code>.
435         * 
436         * @return An object generated by the handler.
437         * @throws SQLException if a database access error occurs
438         * @deprecated Use {@link #query(String,ResultSetHandler,Object[])} instead
439         */
440        public Object query(String sql, Object param, ResultSetHandler rsh)
441            throws SQLException {
442    
443            return this.query(sql, rsh, new Object[] { param });
444        }
445    
446        /**
447         * Executes the given SELECT SQL query and returns a result object.
448         * The <code>Connection</code> is retrieved from the 
449         * <code>DataSource</code> set in the constructor.
450         * 
451         * @param sql The SQL statement to execute.
452         * @param params Initialize the PreparedStatement's IN parameters with 
453         * this array.
454         * 
455         * @param rsh The handler used to create the result object from 
456         * the <code>ResultSet</code>.
457         * 
458         * @return An object generated by the handler.
459         * @throws SQLException if a database access error occurs
460         * @deprecated Use {@link #query(String,ResultSetHandler,Object[])} instead
461         */
462        public Object query(String sql, Object[] params, ResultSetHandler rsh)
463            throws SQLException {
464                return query(sql, rsh, params);
465            }
466    
467        /**
468         * Executes the given SELECT SQL query and returns a result object.
469         * The <code>Connection</code> is retrieved from the 
470         * <code>DataSource</code> set in the constructor.
471         * 
472         * @param sql The SQL statement to execute.
473         * @param rsh The handler used to create the result object from 
474         * the <code>ResultSet</code>.
475         * @param params Initialize the PreparedStatement's IN parameters with 
476         * this array.
477         * @return An object generated by the handler.
478         * @throws SQLException if a database access error occurs
479         */
480        public Object query(String sql, ResultSetHandler rsh, Object[] params)
481            throws SQLException {
482    
483            Connection conn = this.prepareConnection();
484    
485            try {
486                return this.query(conn, sql, rsh, params);
487            } finally {
488                close(conn);
489            }
490        }
491    
492        /**
493         * Executes the given SELECT SQL without any replacement parameters.
494         * The <code>Connection</code> is retrieved from the
495         * <code>DataSource</code> set in the constructor.
496         * 
497         * @param sql The SQL statement to execute.
498         * @param rsh The handler used to create the result object from 
499         * the <code>ResultSet</code>.
500         * 
501         * @return An object generated by the handler.
502         * @throws SQLException if a database access error occurs
503         */
504        public Object query(String sql, ResultSetHandler rsh) throws SQLException {
505            return this.query(sql, rsh, (Object[]) null);
506        }
507    
508        /**
509         * Throws a new exception with a more informative error message.
510         * 
511         * @param cause The original exception that will be chained to the new 
512         * exception when it's rethrown. 
513         * 
514         * @param sql The query that was executing when the exception happened.
515         * 
516         * @param params The query replacement parameters; <code>null</code> is a 
517         * valid value to pass in.
518         * 
519         * @throws SQLException if a database access error occurs
520         */
521        protected void rethrow(SQLException cause, String sql, Object[] params)
522            throws SQLException {
523    
524            String causeMessage = cause.getMessage();
525            if (causeMessage == null) {
526                causeMessage = "";
527            }
528            StringBuffer msg = new StringBuffer(causeMessage);
529    
530            msg.append(" Query: ");
531            msg.append(sql);
532            msg.append(" Parameters: ");
533    
534            if (params == null) {
535                msg.append("[]");
536            } else {
537                msg.append(Arrays.asList(params));
538            }
539    
540            SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
541                    cause.getErrorCode());
542            e.setNextException(cause);
543    
544            throw e;
545        }
546    
547        /**
548         * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
549         * parameters.
550         * 
551         * @param conn The connection to use to run the query.
552         * @param sql The SQL to execute.
553         * @return The number of rows updated.
554         * @throws SQLException if a database access error occurs
555         */
556        public int update(Connection conn, String sql) throws SQLException {
557            return this.update(conn, sql, (Object[]) null);
558        }
559    
560        /**
561         * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
562         * parameter.
563         * 
564         * @param conn The connection to use to run the query.
565         * @param sql The SQL to execute.
566         * @param param The replacement parameter.
567         * @return The number of rows updated.
568         * @throws SQLException if a database access error occurs
569         */
570        public int update(Connection conn, String sql, Object param)
571            throws SQLException {
572    
573            return this.update(conn, sql, new Object[] { param });
574        }
575    
576        /**
577         * Execute an SQL INSERT, UPDATE, or DELETE query.
578         * 
579         * @param conn The connection to use to run the query.
580         * @param sql The SQL to execute.
581         * @param params The query replacement parameters.
582         * @return The number of rows updated.
583         * @throws SQLException if a database access error occurs
584         */
585        public int update(Connection conn, String sql, Object[] params)
586            throws SQLException {
587    
588            PreparedStatement stmt = null;
589            int rows = 0;
590    
591            try {
592                stmt = this.prepareStatement(conn, sql);
593                this.fillStatement(stmt, params);
594                rows = stmt.executeUpdate();
595    
596            } catch (SQLException e) {
597                this.rethrow(e, sql, params);
598    
599            } finally {
600                close(stmt);
601            }
602    
603            return rows;
604        }
605    
606        /**
607         * Executes the given INSERT, UPDATE, or DELETE SQL statement without
608         * any replacement parameters. The <code>Connection</code> is retrieved 
609         * from the <code>DataSource</code> set in the constructor.  This 
610         * <code>Connection</code> must be in auto-commit mode or the update will 
611         * not be saved. 
612         * 
613         * @param sql The SQL statement to execute.
614         * @throws SQLException if a database access error occurs
615         * @return The number of rows updated.
616         */
617        public int update(String sql) throws SQLException {
618            return this.update(sql, (Object[]) null);
619        }
620    
621        /**
622         * Executes the given INSERT, UPDATE, or DELETE SQL statement with
623         * a single replacement parameter.  The <code>Connection</code> is 
624         * retrieved from the <code>DataSource</code> set in the constructor.
625         * This <code>Connection</code> must be in auto-commit mode or the 
626         * update will not be saved. 
627         * 
628         * @param sql The SQL statement to execute.
629         * @param param The replacement parameter.
630         * @throws SQLException if a database access error occurs
631         * @return The number of rows updated.
632         */
633        public int update(String sql, Object param) throws SQLException {
634            return this.update(sql, new Object[] { param });
635        }
636    
637        /**
638         * Executes the given INSERT, UPDATE, or DELETE SQL statement.  The 
639         * <code>Connection</code> is retrieved from the <code>DataSource</code> 
640         * set in the constructor.  This <code>Connection</code> must be in 
641         * auto-commit mode or the update will not be saved. 
642         * 
643         * @param sql The SQL statement to execute.
644         * @param params Initializes the PreparedStatement's IN (i.e. '?') 
645         * parameters.
646         * @throws SQLException if a database access error occurs
647         * @return The number of rows updated.
648         */
649        public int update(String sql, Object[] params) throws SQLException {
650            Connection conn = this.prepareConnection();
651    
652            try {
653                return this.update(conn, sql, params);
654            } finally {
655                close(conn);
656            }
657        }
658        
659        /**
660         * Wrap the <code>ResultSet</code> in a decorator before processing it.
661         * This implementation returns the <code>ResultSet</code> it is given
662         * without any decoration.
663         *
664         * <p>
665         * Often, the implementation of this method can be done in an anonymous 
666         * inner class like this:
667         * </p>
668         * <pre> 
669         * QueryRunner run = new QueryRunner() {
670         *     protected ResultSet wrap(ResultSet rs) {
671         *         return StringTrimmedResultSet.wrap(rs);
672         *     }
673         * };
674         * </pre>
675         * 
676         * @param rs The <code>ResultSet</code> to decorate; never 
677         * <code>null</code>.
678         * @return The <code>ResultSet</code> wrapped in some decorator. 
679         */
680        protected ResultSet wrap(ResultSet rs) {
681            return rs;
682        }
683        
684        /**
685         * Close a <code>Connection</code>.  This implementation avoids closing if 
686         * null and does <strong>not</strong> suppress any exceptions.  Subclasses
687         * can override to provide special handling like logging.
688         * @param conn Connection to close
689         * @throws SQLException if a database access error occurs
690         * @since DbUtils 1.1
691         */
692        protected void close(Connection conn) throws SQLException {
693            DbUtils.close(conn);
694        }
695        
696        /**
697         * Close a <code>Statement</code>.  This implementation avoids closing if 
698         * null and does <strong>not</strong> suppress any exceptions.  Subclasses
699         * can override to provide special handling like logging.
700         * @param stmt Statement to close
701         * @throws SQLException if a database access error occurs
702         * @since DbUtils 1.1
703         */
704        protected void close(Statement stmt) throws SQLException {
705            DbUtils.close(stmt);
706        }
707    
708        /**
709         * Close a <code>ResultSet</code>.  This implementation avoids closing if 
710         * null and does <strong>not</strong> suppress any exceptions.  Subclasses
711         * can override to provide special handling like logging.
712         * @param rs ResultSet to close
713         * @throws SQLException if a database access error occurs
714         * @since DbUtils 1.1
715         */
716        protected void close(ResultSet rs) throws SQLException {
717            DbUtils.close(rs);
718        }
719    
720    }