groovy学习7groovy sql

发现groovy操作数据库很简单，一行import，2行操作代码。

import groovy.sql.Sql

/**
 * @author <a href="mailto:zhangting@taobao.com">张挺</a>
 * @since 2010-4-2 15:07:35
 *
 */
piEstimate = 3;
println("Pi is about ${piEstimate}");
println("Pi is closer to ${22 / 7}");

//使用Sql对象查询
//第一个it是普通的对象应用，第二个it是groovy表达式
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}"

//执行插入
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])

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

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