• Home
• com.ovea.tajin.framework
• tajin-framework-all
• 2.24
• source code
• Sql.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

groovy.sql.Sql Maven / Gradle / Ivy

/*
 * Copyright 2003-2013 the original author or authors.
 *
 * Licensed 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 java.security.AccessController;
import java.security.PrivilegedActionException;
import java.security.PrivilegedExceptionAction;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.*;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import groovy.lang.Tuple;
import org.codehaus.groovy.runtime.InvokerHelper;
import org.codehaus.groovy.runtime.SqlGroovyMethods;

/**
 * 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 ->
 *     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 ->
 *     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