GroovySql 查询数据库示例(groovy 2.2)

版本:groovy 2.2

API:JDBC/GroovySQL


package DbUtil_Shield
import groovy.sql.Sql
import java.sql.Connection;

import net.sourceforge.jtds.jdbc.Driver
/**
 *
 * @author Administrator
 */
class DefaultGroovySqlDbUtil {
      
    def url="localhost";
        def user="sa";
        def password="sa";
        def port=8087;
        def dbName="shiled";
        
    Sql  getGroovySql(){
        
            def sql=Sql.newInstance("jdbc:jtds:sqlserver://localhost:4512/dbxxx"sa","sa","net.sourceforge.jtds.jdbc.Driver");
           
        
        def sql_id_list="select id  from v_products where ctid=65535 and is_auditing=1 and trueeship='0' and subsite_auditing='1'"
        
   
            sql.eachRow(sql_id_list){
                row  ->
                println "${ row.id}"
            }
            
        
    }
    
    static void main(argvs){
        
        
        def dbutil=new DefaultGroovySqlDbUtil();
        
        println dbutil.getUser();
        
        dbutil.getGroovySql();
        
        
        
    }
}







官方文档:

groovy.sql
Class Sql

    java.lang.Object
        groovy.sql.Sql

    Direct Known Subclasses:
        DataSet


    public class Sql
    extends Object

    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<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.

    Author:
        Chris Stevenson, James Strachan, Paul King, Marc DeXeT, John Bito, John Hurst, David Durham, Daniel Henrique Alves Lima, David Sutherland