Groovy Gsql 知识点整理

import groovy.sql.Sql;                             

//使用Sql对象查询
//Sql sql = new Sql(dataSource)
sql = Sql.newInstance("jdbc:mysql://localhost:3306/test", "root",  "xxxxxxx", "com.mysql.jdbc.Driver");

//处理每一行
sql.eachRow("select * from user", { println it.id + " -- ${it.username} --"});

//取第一行
row = sql.firstRow("select username, password from user")
println "Row: columnA = ${row.username} and columnB = ${row.password}"

//获得所有行的集合
def rows = sql.rows("select * from PROJECT where name like 'Gra%'")
assert rows.size() == 2
println rows.join('/n')

输出结果:
[ID:20, NAME:Grails, URL:http://grails.org]
[ID:40, NAME:Gradle, URL:http://gradle.org]

 
//执行插入
username = "yue"
password = "O'shea"

sql.execute("insert into user (username, password) values (${username}, ${password})")

//another insert demo use prepareStatment
username = "yue"
password = "wu"

sql.execute("insert into user (username, password) values (?,?)", [username, password])

//更新操作，也可以用sql.execute("update user set password = ? where id=?", [comment,4])
comment = "test"

sql.executeUpdate("update user set password = ? where id=?", [comment,4])

//delete
sql.execute("delete from word where word_id = ?" , [5])

//备注--------------------------------------------------------------------------
executeUpdate()，更新、删除或插入操作

new File( "foo.csv" ). splitEachLine ( "," ) { params ->
result = sql.executeUpdate("INSERT INTO cities(CITY_ID,CITY_NAME,/
            COUNTRY,COUNTRY_ISO_CODE) values(?,?,?,?)",params);
println "${result} line has been added." ;

foo.csv 文件的内容如下：

88,ShenZhen,China,CN
89,Ji'an,China,CN

executeUpdate() 返回受影响的行数，每次 insert 只影会一行，所以执行后控制台输出为：

1 lines has been added
1 lines has been added

execute()，可用来执行所有的 Sql 语句。它用来执行更新、删除或插入语句后，可用 sql.getUpdateCount() 得到受影响的行数。可惜的是执行查询时没法直接获得查得记录的行数。

最后，对 GroovySQL 使用的延伸还是必须去看 groovy.sql.Sql 的 API 说明(http://groovy.codehaus.org/gapi/groovy/sql/Sql.html )，还需去关注其他一些方法如：

executeInsert()、firstRow()、query()、queryEach()、 rows()、等各方法的使用，还有 GroovySQL 是如何处理 Blob 类型的。

------------------------------------------------------------------------------------

//在业务逻辑层就可以这么用直接返回list
def getPersons() {
  def persons = []
  sql.eachRow("Select * from user") {
    persons << it.toRowResult()
  }
  return persons
}

//如果返回的类的属性和表明一致的话就可以这么做
Person p = new Person( it.toRowResult() )

//调用存储过程
sql.call("call PA_PPRM_PivotCar.P_Tran(?,?,?)",['3','cheng','m'])

//控制事务的一种方法
def conn = dataSource.getConnection()

要对事务进行控制，首先要获取数据源的 Connection 对象，不能直接控制 dataSource 对象的 autoCommit 方法，我还没有搞清楚为什么。
然后是设置 Connection 对象的 autoCommit 为 false ，即由我们自己来控制事务。
     conn.setAutoCommit( false )
 
使用 Connection 对象来初始化 Sql 对象。
       Sql sql = new Sql(conn)
 
然后是我们属性的事务控制方法， try…catch 语句。
       try
       {
 
然后调用多个存储过程。
               sql. call ( "call PA_PPRM_PivotCar.P_Tran(?,?,?)" ,[ '3' , 'cheng' , 'm' ])
             
              sql. call ( "call PA_PPRM_PivotCar.P_Tran(?,?,?)" ,[ '1' , 'feng' , 'm' ])
 
commit 事务。
              conn.commit()
       }
       catch (java.sql.SQLException e)
       {
 
如果失败，则回滚。
           conn.rollback()
           throw e;
       }
       finally
       {
 
最后，记得关闭 Connection 对象，因为 dataSource 对象会为你自动关闭 Connection 对象，但是你从 dataSource 对象中获取了 Connection 对象的话，就需要你自己关闭 Connection 对象。
        sql.close()
           conn.close()
    }

//关闭Sql

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.

 

//补充

DataSet将SQL语句隐藏，将数据集放入到一个Map中。可以对这个Map中内容进行查询、增加。请看如下代码： 

def blogs=db.dataSet('blog') /*new DataSet(db, 'blog')或者db.dataSet(Blog)*/ 
blogs.each{ println it } 
blogs.add(      
    content:'dateset using', 
    author_id:3, 
    date_created:new Date()

)
blogs.findAll {it.id>1 }.sort{it.version desc}.each { println it }
从上述代码中可以看到，创建DateSet实例后，就能够获得一个Map，可以对这个Map执行findAll(whereClosure)以及 sort(sortClosure)。而这里增加数据使用的是add(Map)方法。

在对数据库进行增加、更新或者删除时，需要考虑事务性，以保证数据的完整性。对于此，Groovy同样提供了一个非常方便的用法。对于需要在同一个事务中完成的代码，可以使用Sql或者DataSet的withTransaction(Closure)方法实现，参见如下代码：

db.withTransaction{ 

    for (int i=0;i<5;i++){ 

        blogs.add( 

        content:"dateset using"+i, 

        author_id:3, 

        date_created:new Date()) 

    } 

    db.execute ("delete from iblog where i>20")     //第8行

}
上述代码中，由于在第8行代码会出错，所以整段代码不会在blog表中插入新的纪录的。读者可以将withTransaction方法去掉，再看看程序运行的结果。

DataSet是不是简化了代码？但是DataSet不能取代SQL，DataSet只适用于单一的表，对于更复杂一些的结构比如JOIN返回的结果，就要使用SQL了。比如使用SQL将复杂的结果生成一个视图，之后用DataSet来访问这个视图。