groovy.sql.Sql Maven / Gradle / Ivy

Go to download
/*
 *  Licensed to the Apache Software Foundation (ASF) under one
 *  or more contributor license agreements.  See the NOTICE file
 *  distributed with this work for additional information
 *  regarding copyright ownership.  The ASF licenses this file
 *  to you under the Apache License, Version 2.0 (the
 *  "License"); you may not use this file except in compliance
 *  with the License.  You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing,
 *  software distributed under the License is distributed on an
 *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 *  KIND, either express or implied.  See the License for the
 *  specific language governing permissions and limitations
 *  under the License.
 */
package groovy.sql;

import groovy.lang.Closure;
import groovy.lang.GString;
import groovy.lang.MissingPropertyException;
import groovy.lang.Tuple;
import groovy.transform.NamedParam;
import groovy.transform.stc.ClosureParams;
import groovy.transform.stc.FromString;
import groovy.transform.stc.SimpleType;
import org.codehaus.groovy.runtime.InvokerHelper;

import javax.sql.DataSource;
import java.security.PrivilegedExceptionAction;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static org.apache.groovy.sql.extensions.SqlExtensions.toRowResult;

/**
 * A facade over Java's normal JDBC APIs providing greatly simplified
 * resource management and result set handling. Under the covers the
 * facade hides away details associated with getting connections,
 * constructing and configuring statements, interacting with the
 * connection, closing resources and logging errors. Special
 * features of the facade include using closures to iterate
 * through result sets, a special GString syntax for representing
 * prepared statements and treating result sets like collections
 * of maps with the normal Groovy collection methods available.
 *
 * Typical usage
 *
 * First you need to set up your sql instance. There are several constructors
 * and a few newInstance factory methods available to do this.
 * In simple cases, you can just provide
 * the necessary details to set up a connection (e.g. for hsqldb):
 *  * def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbc.JDBCDriver']
 * def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
 * 
 * or if you have an existing connection (perhaps from a connection pool) or a
 * datasource use one of the constructors:
 *  * def sql = new Sql(datasource)
 * 
 * Now you can invoke sql, e.g. to create a table:
 *  * sql.execute '''
 *     create table PROJECT (
 *         id integer not null,
 *         name varchar(50),
 *         url varchar(100),
 *     )
 * '''
 * 
 * Or insert a row using JDBC PreparedStatement inspired syntax:
 *  * def params = [10, 'Groovy', 'http://groovy.codehaus.org']
 * sql.execute 'insert into PROJECT (id, name, url) values (?, ?, ?)', params
 * 
 * Or insert a row using GString syntax:
 *  * def map = [id:20, name:'Grails', url:'http://grails.codehaus.org']
 * sql.execute "insert into PROJECT (id, name, url) values ($map.id, $map.name, $map.url)"
 * 
 * Or a row update:
 *  * def newUrl = 'http://grails.org'
 * def project = 'Grails'
 * sql.executeUpdate "update PROJECT set url=$newUrl where name=$project"
 * 
 * Now try a query using eachRow:
 *  * println 'Some GR8 projects:'
 * sql.eachRow('select * from PROJECT') { row {@code ->}
 *     println "${row.name.padRight(10)} ($row.url)"
 * }
 * 
 * Which will produce something like this:
 *  * Some GR8 projects:
 * Groovy     (http://groovy.codehaus.org)
 * Grails     (http://grails.org)
 * Griffon    (http://griffon.codehaus.org)
 * Gradle     (http://gradle.org)
 * 
 * Now try a query using rows:
 *  * def rows = sql.rows("select * from PROJECT where name like 'Gra%'")
 * assert rows.size() == 2
 * println rows.join('\n')
 * 
 * with output like this:
 *  * [ID:20, NAME:Grails, URL:http://grails.org]
 * [ID:40, NAME:Gradle, URL:http://gradle.org]
 * 
 * Also, eachRow and rows support paging.  Here's an example:
 *  * sql.eachRow('select * from PROJECT', 2, 2) { row {@code ->}
 *     println "${row.name.padRight(10)} ($row.url)"
 * }
 * 
 * Which will start at the second row and return a maximum of 2 rows.  Here's an example result:
 *  * Grails     (http://grails.org)
 * Griffon    (http://griffon.codehaus.org)
 * 
 *
 * Finally, we should clean up:
 *  * sql.close()
 * 
 * If we are using a DataSource and we haven't enabled statement caching, then
 * strictly speaking the final close() method isn't required - as all connection
 * handling is performed transparently on our behalf; however, it doesn't hurt to
 * have it there as it will return silently in that case.
 * 
 * If instead of newInstance you use withInstance, then
 * close() will be called automatically for you.
 *
 * 
Avoiding SQL injection
 *
 * If you find yourself creating queries based on any kind of input from the user or
 * a 3rd party application you might wish to avoid the pure string method variants in this class.
 * While this is safe:
 * sql.firstRow('select * from PersonTable')
 * This example is potentially at risk of SQL injection:
 * sql.firstRow('select * from PersonTable where SurnameColumn = ' + userInput)
 * This in turn will be fine if 'userInput' is something like 'Smith' but maybe
 * not so fine if 'userInput' is something like 'Smith; DROP table PersonTable'.
 * Instead, use one of the variants with parameters and placeholders:
 * sql.firstRow("select * from PersonTable where SurnameColumn = ?", [userInput])
 * or the GString variants which will be converted to the placeholder variants under the covers:
 * sql.firstRow("select * from PersonTable where SurnameColumn = $userInput")
 * or the named parameter variants discussed next.
 *
 * Named and named ordinal parameters
 *
 * Several of the methods in this class (ones which have a String-based sql query and params in
 * a List<Object> or Object[] or Map) support named or named ordinal parameters.
 * These methods are useful for queries with large numbers of parameters - though the GString
 * variations are often preferred in such cases too. Reminder: when you see a variant with Object[] as
 * the type of the last parameter, Groovy allows vararg style parameters so you don't explicitly need to
 * create an Object[] and if the first parameter is of type Map, Groovy supports named arguments - examples
 * of both are contained in the examples below.
 * 
 * Named parameter queries use placeholder values in the query String. Two forms are supported
 * ':propname1' and '?.propname2'. For these variations, a single model object is
 * supplied in the parameter list/array/map. The propname refers to a property of that model object.
 * The model object could be a map, Expando or domain class instance. Here are some examples:
 * 
 * // using rows() with a named parameter with the parameter supplied in a map
 * println sql.rows('select * from PROJECT where name=:foo', [foo:'Gradle'])
 * // as above for eachRow()
 * sql.eachRow('select * from PROJECT where name=:foo', [foo:'Gradle']) {
 *     // process row
 * }
 *
 * // an example using both the ':' and '?.' variants of the notation
 * println sql.rows('select * from PROJECT where name=:foo and id=?.bar', [foo:'Gradle', bar:40])
 * // as above but using Groovy's named arguments instead of an explicit map
 * println sql.rows('select * from PROJECT where name=:foo and id=?.bar', foo:'Gradle', bar:40)
 *
 * // an example showing rows() with a domain object instead of a map
 * class MyDomainClass { def baz = 'Griffon' }
 * println sql.rows('select * from PROJECT where name=?.baz', new MyDomainClass())
 * // as above for eachRow() with the domain object supplied in a list
 * sql.eachRow('select * from PROJECT where name=?.baz', [new MyDomainClass()]) {
 *     // process row
 * }
 * 
 * Named ordinal parameter queries have multiple model objects with the index number (starting
 * at 1) also supplied in the placeholder. Only the question mark variation of placeholder is supported.
 * Here are some examples:
 *  * // an example showing the model objects as vararg style parameters (since rows() has an Object[] variant)
 * println sql.rows("select * from PROJECT where name=?1.baz and id=?2.num", new MyDomainClass(), [num:30])
 *
 * // an example showing the model objects (one domain class and one map) provided in a list
 * sql.eachRow("select * from PROJECT where name=?1.baz and id=?2.num", [new MyDomainClass(), [num:30]]) {
 *     // do something with row
 * }
 * 
 *
 * More details
 *
 * See the method and constructor JavaDoc for more details.
 * 
 * For advanced usage, the class provides numerous extension points for overriding the
 * facade behavior associated with the various aspects of managing
 * the interaction with the underlying database.
 * 

 * This class is not thread-safe.
 */
public class Sql implements AutoCloseable {

    /**
     * Hook to allow derived classes to access the log
     */
    protected static final Logger LOG = Logger.getLogger(Sql.class.getName());

    private static final List